Use SQL Server as DSC Pull Server Backend?

Recently, I am working on a request to check the possibility to use SQL Server as Powershell DSC Pull Server's backend. As DSC server supports both of ESENT DB and MDB which is connected by OleDB provider, I plan to use OleDB to connect my SQL Server as well. While, after everything configured, I got error to register the client node by the below error.

The error can't give me much hint while after some troubleshooting, I am able to make sure SQL Server is not supported by DSC Pull server via OleDB provider. Yeah, there is no typo mistake here :). I understand 3rd software is always difficult to make troubleshooting (yeah, I am in Microsoft, but not DSC developer, so it is 3rd part to me as well) while sometimes there is really requirement for us to make it. In this blog, I will cover my normal approach to debug a 3rd software.

Configure the Database

Similar to devices.mdb's design, I have used the below schema to define the related tables in SQL Server:

 CREATE TABLE [dbo].[Devices] (
[TargetName] VARCHAR (255) NOT NULL,
[ConfigurationID] VARCHAR (255) NOT NULL,
[ServerCheckSum] VARCHAR (255) NOT NULL,
[TargetCheckSum] VARCHAR (255) NOT NULL,
[NodeCompliant] BIT DEFAULT ((0)) NOT NULL,
[LastComplianceTime] DATETIME NULL,
[LastHeartbeatTime] DATETIME NULL,
[Dirty] BIT DEFAULT ((1)) NULL,
[StatusCode] INT DEFAULT ((-1)) NULL
)
GO

CREATE TABLE [dbo].[RegistrationData] (
[AgentId] VARCHAR (MAX) NOT NULL,
[LCMVersion] VARCHAR (255) NULL,
[NodeName] VARCHAR (255) NULL,
[IPAddress] VARCHAR (255) NULL,
[ConfigurationNames] VARCHAR (MAX) NULL
)
GO

CREATE TABLE [dbo].[StatusReport] (
[JobId] VARCHAR (255) NOT NULL,
[Id] VARCHAR (255) NOT NULL,
[OperationType] VARCHAR (255) NULL,
[RefreshMode] VARCHAR (255) NULL,
[Status] VARCHAR (255) NULL,
[LCMVersion] VARCHAR (255) NULL,
[ReportFormatVersion] VARCHAR (255) NULL,
[ConfigurationVersion] VARCHAR (255) NULL,
[NodeName] VARCHAR (255) NULL,
[IPAddress] VARCHAR (255) NULL,
[StartTime] DATETIME DEFAULT (getdate()) NULL,
[EndTime] DATETIME DEFAULT (getdate()) NULL,
[Errors] VARCHAR (MAX) NULL,
[StatusData] VARCHAR (MAX) NULL,
[RebootRequested] VARCHAR (255) NULL
)
GO

In order to connect Pull Server to the SQL Server instead of devices.mdb, I have changed the connection string in web.config shown below. You can find it it in C:\inetpub\wwwrootPSDSCPullServer\web.config

 
<add key="dbprovider" value="System.Data.OleDb" />
<add key="dbconnectionstr" value="Provider=SQLOLEDB;Data Source=dbsvr\sqlexpress;Initial Catalog=DSCDb;Integrated Security=SSPI;" />

Capture Dump

Though the Powershell error in client node can't tell me any detail information of the root cause, I believe the issue is inside the Pull server side. Considering the server is running inside IIS w3wp worker process, I would like to begin with ProcDump which is one of my favorite tools. Download and extract it to the pull server machine and execute command "procdump -e 1 -f "" w3wp.exe". This command will show all the exceptions happened in the cmd console shown below.

We can clearly see there is an OleDbException complaining "Must declare the scalar variable "@agentId"". This information is still not enough while I am sure it is just something related to the issue, after all I am fighting with the database, right?

So far, what I can confirm is there is really OleDbException blocking the client register, I need dig in more. I plan to capture a dump by command "procdump -ma w3wp.exe -e 1" shown below. As the above OleDbException is the first exception during the execution, this command will generate a full user dump as soon as the exception happens. Notice that the Pull server w3wp process is running as 32 bit, in the next step, we will need a 32 bit windbg to debug it because of this.

Debug Dump

Download windbg from https://developer.microsoft.com/en-us/windows/hardware/download-windbg and install it. Open the 32 bit windbg.exe from wow64 sub folder. Open the dump generated in the last step, execute command " .loadby sos clr", this command provides handy utility to debug .Net application.

Next, I would like to run command " !dso", it will show all the objects in the current thread stack shown below.

On the stack, I can see the extract OleDbException object and the error message "Must declare the scalar variable "@agentId"". Furthermore, I find a OleDbCommand object which is supposed have the query string field. Use command !DumpObj to dump it and also the command text shown below.

Good, looks like I just get some progress, I can see the query string is "Select * from RegistrationData WHERE agentId=@agentId" which looks to be related to the error message. While, there are so many SQL query inside Pull server, how could I figure out where the command is constructed? Easy, let's execute command " !clrstack", it will show current thread call stack, our attention is the Pull server code path.

Check Source Code

Based on the function name I get from the above call stack, I would like to have a look on the source code of ReadRegistrationRecordFromDatabase. What? Source code? But this is a 3rd library. Yes, I know, but we can as this is a .Net application. Download ILSpy and open Microsoft.Powershell.DesiredStateConfiguration.Service.dll, you will be able to find it in C:\inetpub\wwwrootPSDSCPullServer\bin. Now, I got the source code shown below, interesting, looks like the query string is constructed by design and it is expected to work with named parameter. So, probably the named parameter doesn't work as expectation?

Verification

If the named parameter doesn't work, it is supposed to be nothing to do with Pull server itself. Now, I have understood all the code logic, based on this, I am able to write a simple console application to simulate same logic of pull server, finally I have used the console application to reproduce exactly the same issue.

 using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.OleDb;

namespace ConsoleApplication1
{
  class Program
  {
    static void Main(string[] args)
    {
      try
      {
        string connectionString = "Provider=SQLOLEDB;Data Source=webserver3\\sqlexpress;Initial Catalog=DSCDb;Integrated Security=SSPI";
        string databaseProvider = "System.Data.OleDb";
        DbProviderFactory factory = DbProviderFactories.GetFactory(databaseProvider);
        using (DbConnection dbConnection = factory.CreateConnection())
        {
          dbConnection.ConnectionString = connectionString;
          dbConnection.Open();
          using (DbCommand dbCommand = factory.CreateCommand())
          {
            dbCommand.Connection = dbConnection;
            dbCommand.CommandText = "Select * from RegistrationData WHERE agentId=@agentId";
            dbCommand.Parameters.Clear();
            OleDbParameter oleDbParameter = new OleDbParameter("agentId", OleDbType.VarChar);
            oleDbParameter.Value = "dummy";
            dbCommand.Parameters.Add(oleDbParameter);
            using (DbDataReader dbDataReader = dbCommand.ExecuteReader())
            {
              while (dbDataReader.Read())
              {
                Console.WriteLine("Execute read");
              }
            }
          }
        }
     }
     catch (Exception ex)
     {
       Console.WriteLine(ex.Message);
     }
    }
  }
}

After further research, I am able to find the statement " The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text . In this case, the question mark (?) placeholder must be used. " in OleDbCommand.Parameters Description and looks like it can explain the scenario I observed. Finally, I change the above code from "Select * from RegistrationData WHERE agentId=@agentId" to "Select * from RegistrationData WHERE agentId=?", it works smoothly!

Conclusion

Now, I understand DSC server has used named parameter while it is not supported well by OleDbCommand, thus we can't directly use SQL Server by OleDb provider. I used to post another article Use Procdump to troubleshoot white screen issue of Bonobo git server regarding to similar 3rd software debugging experience as well, hope helpful for you. By the way, I also notice another post in https://leandrowpblog.wordpress.com/2016/10/26/using-sql-server-db-for-dsc/ regarding to consume SQL Server in DSC server by different approach, enjoy it.