Category: 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’
}

SharePoint 2013, AlwaysOn, Availability group and SQL alias

Bonjour,

I had a story at my company where I had to migrate the SharePoint databases to a fresh AlwaysOn Availability Group (AOAG) SQL Instance. This move wasn’t an issue until we found out that our backup script (which is performing a “Backup-SPFarm” cmdlet) failed to re-provision the User Profile Synchronization (UPS) Service.

Context

The issue all came down from the fact that the UPS database was now configured with the AlwaysOn feature, therefore, any operations to the DB couldn’t occur anymore.

The error message in the ULS was :

SqlError: ‘The operation cannot be performed on database “UserProfile-Sync-DB” because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.’    Source: ‘.Net SqlClient Data Provider’ Number: 1468 State: 1 Class: 16 Procedure: ” LineNumber: 5 Server: ‘AG Listener Name’

When the move to the new AlwaysOn SQL server happened, the operation went pretty smoothly since we are using SQL alias. We only updated our current alias name to redirect to the new SQL instance.

Updated configuration :
SQLAlias-AG-01

After some research, we found out that this is a “internet” known issue and can also occur on the Usage and Health database when performing patching of SharePoint farms.

Hence, we decided to modify the configuration and implement a better integration between SharePoint and AlwaysOn by using the SharePoint Database Availability Group cmdets. It will give us the flexibility to manage the databases in the AOAG directly from SharePoint.

In this initial setup, SharePoint isn’t aware of the AlwaysOn service running beneath it. It only sees the SQL Alias and has no way to know which SQL server is running behind the SQL Alias ; it is a regular connection string.

By explicitly declaring to SharePoint that the databases are hosted by a HADR system, SharePoint Admins keep some visibility and control over the AOAG.

Availability Group Listener (AGL) and port attribution

When your AGL is configured to use the default port (1433) and you don’t use SQL alias, you will surely have no issue when configuring your environment.
The troubles arises when you use SQL alias or have a custom port defined for the AGL, let’s say 25066 for the example.

  • When using a SQL alias (redirecting to the AGL), SharePoint will fail retrieving the “Availability Group listeners”.
    SQLAlias-AG-02
  • When using a custom port for the AGL, SharePoint will check the database server by using a trimmed version of the data source.
    SQLAlias-AG-03
SQL Alias

You can use SQL Alias with Availability Group Listener as long as the SQL alias uses the same DNS entry as the AGL.

Availability Group Custom Port

You must use a SQL Alias to bypass the SharePoint port validation.

Solution (for both scenarios)

In both cases, you must use a SQL alias that mirrors the AGL dns name. For example, if your AGL is “AGListener” on port 25066.
SQLAlias-AG-05

Some in-depth details :

I was able to pinpoint this issue by decompiling the SharePoint assembly and verify the logic behind the powershell cmdlets. I found the root issue in the “UpdateDataSource” method of the Database object.
SQLAlias-AG-04
When trimming the datasource from its port, it then sends the port-less server string to the method “ChangeDatabaseInstance” which itself calls “ValidateDatabaseServer”.
At this point, it won’t be able to validate the connection to the SQL server because of the modified server string.

Cheers !
MW

PS: I wasn’t able to find anything on the web regarding this particular issue except 1 slide that confirmed my troubleshooting.
Ref: http://blogs.technet.com/b/fromthefield/archive/2015/04/23/sharepoint-2013-amp-sql-alwayson-sharepoint-evolution-conference-2015.aspx – Slide 30

PowerPivot thumbnails requires Windows Server 2012 GUI

Hello,

We found out a small issue with PowerPivot 2012 and SharePoint 2013 this week.

You may be aware that the PowerPivot for SharePoint is able to create snapshots/thumbnail previews of your document which is then used to display a nice little image in the carrousel view.
This fancy little feature is generated under the hood by an executable located in the bin folder of the SharePoint installation folder (C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\bin).

While it works well under common context, it will fail in a particular scenario. I won’t give you all the different ways to troubleshoot this as Internet already as a ton of article around it but here’s one that is very useful :
http://blogs.technet.com/b/excel_services__powerpivot_for_sharepoint_support_blog/archive/2012/12/06/invoking-and-analyzing-the-getsnapshot-exe.aspx

Our scenario is as follow :

  • The server hosting SharePoint and Powerpivot is running Windows 2012
  • Windows Server 2012 is configured using MSI (Minimal Server Interface)

In this particular case, the “GallerySnapshot.exe” will crashes when processing the document.

 

 

 

 

If you look in the “info” file generated, you quickly find out that there is nothing relevant to help you. Here’s the content of the log file :
“<SnapshotCaptureLog serverUrl=”http://<webapp name>” workbookUrl=”http://<site url>/SharePoint/PpowerPivot/Book1.xlsx” fileNameBase=”thumbnail” snapshotCount=”26″ timeout=”600″ />”. That’s it ! Nothing else.

In the “GallerySnapshot.exe has stopped working” error window details, we can see a small hint of what’s going on : “Problem Signature 04: System.Windows.Forms”.

I performed the same exercise on a different server with the same result. I then intalled the windows feature Server Graphical Shell (Install-WindowsFeature Server-Gui-Shell) and restarted the server.

Once rebooted, running the GallerySnapshot.exe again with the same arguments went successfully.

To sum-up, if you require the file preview in a PowerPivot Gallery, you will necessarily have to configure your servers with at least the Graphical User Interface. Let’s hope Microsoft will remove this pre-requisite in a future cumulative update or service pack but until then, forget Windows Server 2012 MSI !

Sad, sad, sad.