Problems registering SQL2016 PSSnapin for #MIM2016 PS workflow activity

I’ve been using SQLPS as part of a script developed by my colleague Carol which needs to be invoked both interactively and also from a MIM workflow activity.  I ran into problems this week trying to use the SQL2016 feature pack.

I simply figured I should be using the latest platform for my Win2016 MIM platform so didn’t hesitate to install it in preference to the last one I used.  The script worked fine with the Import-Module approach when run interactively, but drops into the add-pssnapin sqlserverprovidersnapin120 alternative when this fails when invoked by the MIM service (due to a known .Net version compatibility issue –  and by the way, 130 corresponds to SQL2016):

try {Import-Module "sqlps" -DisableNameChecking}
catch
{
    if(@(get-pssnapin | where-object {$_.Name -eq “sqlserverprovidersnapin130”} ).count -eq 0) {add-pssnapin sqlserverprovidersnapin130}
    if(@(get-pssnapin | where-object {$_.Name -eq “sqlservercmdletsnapin130”} ).count -eq 0) {add-pssnapin sqlservercmdletsnapin130}
}

The second add-pssnapin call above was failing with a “this assembly is built by a runtime newer than the currently loaded runtime” exception … and so I rolled back to SQL2014 (120) and it worked like a charm:

try {Import-Module "sqlps" -DisableNameChecking}
catch
{
if(@(get-pssnapin | where-object {$_.Name -eq “sqlserverprovidersnapin120”} ).count -eq 0) {add-pssnapin sqlserverprovidersnapin120}
if(@(get-pssnapin | where-object {$_.Name -eq “sqlservercmdletsnapin120”} ).count -eq 0) {add-pssnapin sqlservercmdletsnapin120}
}

So obviously something is changing in this space for this not to work … but until I have an alternative that works for SQL2016 you may want to roll back to SQL2014 like me.

For future reference, the following registration steps were necessary after the SQL2014 feature pack install.

  • download and install (as admin) each of the following from the download collection:
    • SQLSysClrTypes.msi
    • SharedManagementObjects.msi
    • PowerShellTools.msi
  • run the following to register the DLLs (I ran this as a script saved locally to the SQLPS program files folder):
[System.Reflection.Assembly]::Load("System.EnterpriseServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")           
$publish = New-Object System.EnterpriseServices.Internal.Publish           
$publish.GacInstall(“Microsoft.SqlServer.Management.PSProvider.dll”) | Out-String
$publish.GacInstall(“Microsoft.SqlServer.Management.PSSnapins.dll”) | Out-String

Set-Alias installutil C:\windows\Microsoft.NET\Framework64\v2.0.50727\InstallUtil.exe
installutil -i "C:\Program Files\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSProvider.dll"
installutil -i "C:\Program Files\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSSnapins.dll"
Get-PSSnapin -Registered
Advertisements

About bobbradley1967

Microsoft IAM MVP and Solutions Architect (MCTS, MCP) - FIM/ILM/MIIS Specialist, with 20 years SQL database ( OLAP) and MS.Net applications development/SI background, in particular on the SharePoint platform
This entry was posted in MIM (Microsoft Identity Manager) 2016. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s