Currently DSC Pull Server supports using ESENT DB or MDB as back-end to maintain DSC Clients registration. However, this approach is not scalable for highly available scenarios (such as two Pull Server behind a load balancer) or globally distributed environments. For these cases, wouldn’t it be awesome to be able to use SQL Server as back-end? Well… there’s a workaround for it and, in this blog, I’ll show you how to do it.
What’s the approach?
In a nutshell, we will use a pretty old technology to achieve this; we will make use of the linked sources in Access in conjunction with an ODBC connection, the following diagram depicts the approach:
Without further intro, this is how you move from this:
to
Before start
- Use the same SQL Server Login ID and Password through out the whole process
- Stick to the DSN Name you choose, keep it all the way through.
Setting up the SQL Server DB
Depending on where you set your MDB, you might need to repeat this step twice: one in the computer you use to create the MDB and another time in the final SQL Server.
Gotcha: If you are doing these steps in a different SQL Server, for example, a SQL Express in your laptop rather than the final SQL Server; make sure the SQL Login ID and its Password match the one you plan to use later.
- Create an empty database; in my case I called it “DSC” (pretty original)
- Create a SQL Server Login which will be used to connect to this DB and grant the required rights. This User ID must match the one you will use in the actual SQL Server.
- Run the following T-SQL script to create the DSC Schema. If you want to create it yourself, just get a copy of Devices.mdb and import it into SQL Server using “Import Data” Wizard
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Devices]( [TargetName] [nvarchar](255) NOT NULL, [ConfigurationID] [nvarchar](255) NOT NULL, [ServerCheckSum] [nvarchar](255) NOT NULL, [TargetCheckSum] [nvarchar](255) NOT NULL, [NodeCompliant] [bit] NOT NULL, [LastComplianceTime] [datetime] NULL, [LastHeartbeatTime] [datetime] NULL, [Dirty] [bit] NOT NULL, [StatusCode] [int] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[RegistrationData]( [AgentId] [nvarchar](255) NOT NULL, [LCMVersion] [nvarchar](255) NULL, [NodeName] [nvarchar](255) NULL, [IPAddress] [nvarchar](255) NULL, [ConfigurationNames] [nvarchar](max) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO CREATE TABLE [dbo].[StatusReport]( [JobId] [nvarchar](50) NOT NULL, [Id] [nvarchar](50) NOT NULL, [OperationType] [nvarchar](255) NULL, [RefreshMode] [nvarchar](255) NULL, [Status] [nvarchar](255) NULL, [LCMVersion] [nvarchar](50) NULL, [ReportFormatVersion] [nvarchar](255) NULL, [ConfigurationVersion] [nvarchar](255) NULL, [NodeName] [nvarchar](255) NULL, [IPAddress] [nvarchar](255) NULL, [StartTime] [datetime] NULL, [EndTime] [datetime] NULL, [Errors] [nvarchar](max) NULL, [StatusData] [nvarchar](max) NULL, [RebootRequested] [nvarchar](255) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
Setting up the ODBC Connection
Your ODBC connection should match you Microsoft Access platform. In my case, since I don’t have Microsoft Access installed in the server and I’m running Access 64-bit in my laptop, I created a 64-bit ODBC.
Gotcha: The DSN Name must match the one you plan to use later on the Pull Server.
- Go to “Control Panel” -> “Administrative Tools” -> “ODBC Data Sources (64-bit)”
- Select “System DSN” tab and click “Add…”
- Select “Select Server” as the provider
- Configure the connection name and the SQL Server to connect to (remember, in my case I’m doing this in my laptop where I have a SQL Express installed; if you do this directly using the final server, just put that server name in there). The DSN name you choose must be the same one you will use later on.
- Configure the DSN to use SQL Server authentication and use the previously created SQL Login
- Set the default database to the DSC one
- And complete the wizard till you are done
Creating the customized Devices.mdb
- Create a new empty mdb called Devices.mdb
- Close and discard the default created table
- Go to “External Data” tab and select “ODBC Database”
- Choose “Link to the data source….”
- A dialog to choose the DSN will pop-up. Select the tab “Machine Data Source” and select our previously created “DSC”
- A pop-up prompting for the SQL Login password will be shown; enter the SQL Login ID and the password and click OK
- Select which tables you want to link to, obviously you should choose the three DSC tables only and check the option “Save password”.
- Then click “OK” and “Save Password” in the following pop-ups and you will get this
- Rename the tables to remove “dbo_” so DSC can find the same table names it’s used to. Just select the table, hit “F2” and rename it
- Close Access
Putting all together in the Pull Server
- If you performed the steps described in “Setting up the SQL Server DB” on a different SQL Server than the one you plan to use for your Pull Server; then you should follow them again but now against the final SQL Server. Make sure the SQL Login ID and its password match the ones you used to create the MDB.
- Since the Pull Server Application Pool is set to run as a 32-bit one, you should create a 32-bit ODBC connection. So, follow the steps described in “Setting up the ODBC Connection” and named same as the one you used to create the MDB
- Copy the Devices.mdb file you created in step “Creating the customized Devices.mdb” to C:\Program Files\WindowsPowershell\DSCService
- Update the Pull Server web.config to use Devices.mdb
- That’s it.
July 19, 2019 at 10:40
I wanted to send you this very small remark to help give thanks over again with the wonderful basics you have provided here. This has been so strangely generous of you to convey publicly exactly what a number of us could have sold as an e-book to generate some money for their own end, and in particular considering that you might have done it if you ever desired. These thoughts likewise served to be the fantastic way to be sure that someone else have a similar dream really like my own to realize somewhat more on the topic of this issue. I am sure there are a lot more fun occasions ahead for folks who read through your blog.
LikeLike
July 16, 2019 at 10:15
Good day very cool site!! Man .. Excellent .. Superb .. I will bookmark your blog and take the feeds additionally? I’m glad to search out so many helpful info right here in the put up, we need develop more techniques on this regard, thanks for sharing. . . . . .|
LikeLike
July 16, 2019 at 08:30
I think this is among the most important info for me. And i am glad reading your article. But wanna remark on some general things, The web site style is perfect, the articles is really excellent : D. Good job, cheers|
LikeLike
July 15, 2019 at 17:28
Aw, this was a very nice post. Taking a few minutes and
actual effort to produce a good article… but what can I say…
I hesitate a whole lot and don’t manage to get anything done.
LikeLike
July 15, 2019 at 07:34
Heya! I’m at work surfing around your blog from my new iphone 3gs!
Just wanted to say I love reading your blog and look forward to all your posts!
Keep up the excellent work!
LikeLike
July 14, 2019 at 22:47
as soon as I found this internet site I went on reddit to share some of the love with them.
LikeLike
July 13, 2019 at 02:50
This web page is mostly a walk-by means of for the entire information you wanted about this and didn’t know who to ask. Glimpse right here, and also you’ll undoubtedly discover it.
LikeLike
March 27, 2018 at 11:40
do you have to set the IIS App pool explicit to run in 32-bit mode? where can I do this? I try to get this up and running on a 2016 DSC Server…
many thanks in advance
LikeLike
November 25, 2016 at 11:43
I tried to use oledb provider for SQL server in connection string directly, technically speaking, DSC pull server should work in this way. While, looks like DSC pull server has used named parameter in select query widely, and this is not supported by sql server oledb provider. Is this the reason that you are trying ODBC?
LikeLike
November 27, 2016 at 12:40
The problem is the implementation the PullServer DLL is doing for System.Data.OleDB; the way it’s handling parameters is the way that only MS Access would allow it if they were just using “?” for the parameter name, it would had worked;but they aren’t. I’ve reported this to Microsoft.
But in the mean time, this is a workaround for that by using MS Access to do that translation for you.
LikeLike
October 27, 2016 at 16:17
I like the idea of implementing SQL Server, are the sql schema scripts generated form the Devices.mdb ?
LikeLike
October 27, 2016 at 16:27
Yes, the schema I posted it was generated from Devices.mdb. Basically using the Sql server import wizard with the empty mdb.
LikeLike