Category: Windows Server 2016

SQL Server 2016 Configuration Manager

Another issue we came across, cannot use the SQL Server Configuration Manager from a machine where SQL Server isn’t installed.
This is kind of sad knowing that MS is pushing administrators to connect remotely on servers and make use of remote tools.

In order to use the SQL Server Configuration Manager snap-in integrated in the Computer Management MMC, you need at least the “SQL Server 20xx Common files” component which is installed when you install the Database Engine feature. You can check this by verifying directly in the uninstall section of the registry for MSI installations.

Get-ItemProperty -Path “HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\*” | Sort-Object Displayname | Select-Object DisplayName, DisplayVersion

So, if you require the Configuration Manager, you need to install a local instance of the Database Engine because the standalone version of SQL Server Management Studio doesn’t give you this ability.

Diagnostic

I used Process Monitor from SysInternals to find out that when loading the compmgmt.msc window on a machine having SQL Server Database Enngine instance, it will load a specific DLL registered in the system. The ‘Computer Management’ snap-in will check if any extension has been registered for specific node types as defined here :
Computer Management Extensible Node Types
Here’s the flow :
First, it will read the extensions.
mRemoteNG_2017-03-15_15-19-24The MMC executable found an extension with the ID {EE7F2DDB-1319-4227-8FD4-4EB51615D34A} referenced as ‘SqlcmSnapin’.
The ID {476E6449-AAFF-11D0-B944-00C04FD8D5B0} is the unique ID for the Computer Management snap-in (CompMgmt.msc).
It will then check the unique ID of the snap-in in the dedicated registry path ‘HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MMC\SnapIns’.
mRemoteNG_2017-03-15_15-20-46
This entry validates the snap-in and gives it a friendly name (SQL Server Configuration Manager). We also see a first reference to the file used by the snap-in (C:\Program Files\Microsoft SQL Server\130\Tools\Binn\SqlManager.dll).
The MMC now will check if the snap-in is correctly registered in the local machine’s classes using the UID (HKEY_CLASSES_ROOT\CLSID{EE7F2DDB-1319-4227-8FD4-4EB51615D34A}).
mRemoteNG_2017-03-15_15-21-41
Again, the entry is found and we can see a second reference to the DLL.

Once the registry keys have been validated, it will try to load the files located in the SQL Server common installation path ‘C:\Program Files\Microsoft SQL Server\130\Tools\Binn\’.
First, the ‘SQLManager.dll’ file and then the different resources files.
mRemoteNG_2017-03-15_15-22-48
mRemoteNG_2017-03-15_15-23-45

Knowing this, it was just a question of registering the correct files on my machine to force the loading of the SQL Server Configuration Manager snap-in.
For the files themselves, you need to copy them from an existing SQL Server installation though.

Below is the Powershell script to automate this little configuration :

$myserver = ‘tst-s04’

$SQLInstallPath = ‘Program Files\Microsoft SQL Server\130\Tools\Binn’

# Copy the SQLmanager dll file
If ( -not (Test-Path -Path “C:\$SQLInstallPath\SqlManager.dll”) ) {
Copy-Item -Path “\\$myserver\c$\$SQLInstallPath\SqlManager.dll” -Destination “C:\$SQLInstallPath”
}
# Copy the Resources folder
If ( -not (Test-Path -Path “C:\$SQLInstallPath\Resources”) ) {
Copy-Item -Path “\\$myserver\c$\$SQLInstallPath\Resources” -Destination “C:\$SQLInstallPath” -Recurse
}

# Register the snap-in keys
#Extension
If ( -not ((Get-ItemProperty “HKLM:\SOFTWARE\Microsoft\MMC\NodeTypes\{476E6449-AAFF-11D0-B944-00C04FD8D5B0}\Extensions\NameSpace”).'{EE7F2DDB-1319-4227-8FD4-4EB51615D34A}’) -eq ‘SqlcmSnapin’ ) {
New-ItemProperty -Path “HKLM:\SOFTWARE\Microsoft\MMC\NodeTypes\{476E6449-AAFF-11D0-B944-00C04FD8D5B0}\Extensions\NameSpace” -Name ‘{EE7F2DDB-1319-4227-8FD4-4EB51615D34A}’ -Value ‘SqlcmSnapin’ -PropertyType String
}
# MMC
$RegPath = ‘HKLM:\SOFTWARE\Microsoft\MMC\SnapIns\{EE7F2DDB-1319-4227-8FD4-4EB51615D34A}’
If ( -not (Test-Path -Path $RegPath) ) {
New-Item -Path $RegPath -Force | Out-Null
New-ItemProperty -PropertyType String -Path $RegPath -Name ‘(default)’ -Value ‘SqlcmSnapin’
New-ItemProperty -PropertyType String -Path $RegPath -Name ‘NameString’ -Value ‘SQL Server Configuration Manager’
New-ItemProperty -PropertyType String -Path $RegPath -Name ‘NameStringIndirect’ -Value ‘@C:\Program Files\Microsoft SQL Server\130\Tools\Binn\SqlManager.dll,-3’
New-ItemProperty -PropertyType String -Path $RegPath -Name ‘About’ -Value ‘{E84BEF4D-385C-4113-AE37-2795FE726A18}’
$RegPath = ‘HKLM:\SOFTWARE\Microsoft\MMC\SnapIns\{EE7F2DDB-1319-4227-8FD4-4EB51615D34A}\NodeTypes’
If ( -not (Test-Path -Path $RegPath) ) { New-Item -Path $RegPath -Force | Out-Null }
$RegPath = ‘HKLM:\SOFTWARE\Microsoft\MMC\SnapIns\{EE7F2DDB-1319-4227-8FD4-4EB51615D34A}\NodeTypes\{1D59FD70-D8B8-4425-B12B-72E32516A9E9}’
If ( -not (Test-Path -Path $RegPath) ) { New-Item -Path $RegPath -Force | Out-Null }
$RegPath = ‘HKLM:\SOFTWARE\Microsoft\MMC\SnapIns\{EE7F2DDB-1319-4227-8FD4-4EB51615D34A}\NodeTypes\{B919722D-5ED6-44A2-A034-40C796E3E38E}’
If ( -not (Test-Path -Path $RegPath) ) { New-Item -Path $RegPath -Force | Out-Null }
$RegPath = ‘HKLM:\SOFTWARE\Microsoft\MMC\SnapIns\{EE7F2DDB-1319-4227-8FD4-4EB51615D34A}\StandAlone’
If ( -not (Test-Path -Path $RegPath) ) { New-Item -Path $RegPath -Force | Out-Null }
}

# SQL Manager Class
If (-not (Get-PSDrive -Name ‘HKCR’ -ErrorAction SilentlyContinue) ) { New-PSDrive -Name ‘HKCR’ -PSProvider Registry -Root ‘HKEY_CLASSES_ROOT’ | Out-Null }

$RegPath = ‘HKCR:\CLSID\{EE7F2DDB-1319-4227-8FD4-4EB51615D34A}’
If ( -not (Test-Path -Path $RegPath) ) {
New-Item -Path $RegPath -Force | Out-Null
New-ItemProperty -PropertyType String -Path $RegPath -Name ‘(default)’ -Value ‘SqlcmSnapin Class’
$RegPath = ‘HKCR:\CLSID\{EE7F2DDB-1319-4227-8FD4-4EB51615D34A}\InprocServer32’
If ( -not (Test-Path -Path $RegPath) ) { New-Item -Path $RegPath -Force | Out-Null }
New-ItemProperty -PropertyType String -Path $RegPath -Name ‘(default)’ -Value ‘C:\Program Files\Microsoft SQL Server\130\Tools\Binn\SqlManager.dll’
New-ItemProperty -PropertyType String -Path $RegPath -Name ‘ThreadingModel’ -Value ‘Apartment’
$RegPath = ‘HKCR:\CLSID\{EE7F2DDB-1319-4227-8FD4-4EB51615D34A}\ProgID’
If ( -not (Test-Path -Path $RegPath) ) { New-Item -Path $RegPath -Force | Out-Null }
New-ItemProperty -PropertyType String -Path $RegPath -Name ‘(default)’ -Value ‘SQLManager.SqlcmSnapin.5’
$RegPath = ‘HKCR:\CLSID\{EE7F2DDB-1319-4227-8FD4-4EB51615D34A}\VersionIndependentProgID’
If ( -not (Test-Path -Path $RegPath) ) { New-Item -Path $RegPath -Force | Out-Null }
New-ItemProperty -PropertyType String -Path $RegPath -Name ‘(default)’ -Value ‘SQLManager.SqlcmSnapin’
}

Advertisements

PowerShell DSC Encryption issue

Context

While working on a new setup, we had to deploy some binaries on a server using DSC.

To make the process scale for many machines, we created a network share to host the binaries in order to centralize the access. In the DSC world, this meant we had 2 options :

1- Add the computer account of each machine accessing the share in the permissions of the share.

2- Use the encryption feature in DSC (define an account in the MOF in order to access the share)

During the testing phase, everything went well. The configuration was as is :
Authoring machine : Windows 10, Powershell 5.1.14393.693
Target machine : Server 2016, Powershell 5.1.14393.0

Issue

We then deployed the configuration on a Windows Server 2012 R2 and the LCM kept getting in error, throwing the message “Decryption failed. LCM failed to start desired state configuration manually.”
powershell_2017-01-19_10-00-07

Digging a little deeper in the ‘Microsoft-Windows-Desired State Configuration/Operational’ event log, just before the “Decryption failed” error, another error was caught :
“Message Invalid provider type specified.”mremoteng_2017-01-19_10-23-16

With the help of the Internet and some querying, I was able to “decrypt” our issue. I began by reading this nice article :
https://hyper-v.nu/archives/bgelens/2015/02/integrating-vm-role-with-desired-state-configuration-part-7-creating-a-configuration-document-with-encrypted-content/
Which led me to review the certificate we used for the DSC encryption.

The initial setup was using a CA issued custom certificate we created following Microsoft’s recommendation stated here :
https://msdn.microsoft.com/en-us/powershell/dsc/securemof#certificate-requirements
firefox_2017-01-19_10-31-36
And since we wanted to follow the MS’ Best practices, we configured our certificate template using the provider ‘Microsoft RSA SChannel Cryptographic Provider’.

Solution

The caveat with this configuration is that Windows Server 2012 R2 doesn’t know how to decrypt anything using the ‘Microsoft RSA SChannel Cryptographic Provider‘. Even if you deploy WMF 5.1 Preview, it won’t help.
If you use the Self-Signed certificate generator script, it will work flawlessly because it actually uses the legacy provider named ‘Legacy Cryptographic Service Provider‘.

Either you create a certificate template using the Provider category ‘Legacy Cryptographic Service Provider’ thus not following Microsoft’s certificate requirements or you use only self-signed certificate using the custom script or you upgrade your OS to Windows 2016.

Regards,
MW