Tag: SQL Server

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