Invoke-Sqlcmd gets TypeInitializationException with 22.1.1

[matt-gantz]

I upgraded from 21.1.18256 to 22.1.1, and now I get errors running Invoke-Sqlcmd. I have verified that the newest version has the same behavior.

PS C:\WINDOWS\system32> invoke-sqlcmd -query 'SELECT 1'
invoke-sqlcmd : The type initializer for 'Microsoft.Data.SqlClient.InOutOfProcHelper' threw an exception.
At line:1 char:1
+ invoke-sqlcmd -query 'SELECT 1'
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidResult: (:) [Invoke-Sqlcmd], TypeInitializationException
    + FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

PS C:\WINDOWS\system32> $Error[0].Exception.ToString()
System.TypeInitializationException: The type initializer for 'Microsoft.Data.SqlClient.InOutOfProcHelper' threw an exception. ---> System.IO.FileNot
FoundException: Could not load file or assembly 'System.Runtime.InteropServices.RuntimeInformation, Version=4.0.2.0, Culture=neutral, PublicKeyToken
=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.
   at Microsoft.Data.SqlClient.InOutOfProcHelper..ctor()
   at Microsoft.Data.SqlClient.InOutOfProcHelper..cctor()
   --- End of inner exception stack trace ---
   at Microsoft.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbCo
nnectionOptions& userConnectionOptions)
   at Microsoft.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key)
   at Microsoft.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
   at Microsoft.Data.SqlClient.SqlConnection..ctor(String connectionString, SqlCredential credential)
   at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.CreateSqlConnection()
   at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor..ctor(GetScriptCommand sqlCmdCmdLet)
   at Microsoft.SqlServer.Management.PowerShell.GetScriptCommand.ProcessRecord()

Also interesting, I get error messages when Importing or Removing the module (even though Get-Module suggests that those commands are successful):

PS C:\WINDOWS\system32> import-module sqlserver
Import-Module : Could not load file or assembly 'netstandard, Version=2.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51' or one of its 
dependencies. The system cannot find the file specified.
At C:\Program Files\WindowsPowerShell\Modules\sqlserver\22.1.1\SqlServer.psm1:61 char:25
+ ...       $binaryModule = Import-Module -Name $binaryModulePath -PassThru
+                           ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Import-Module], FileNotFoundException
    + FullyQualifiedErrorId : System.IO.FileNotFoundException,Microsoft.PowerShell.Commands.ImportModuleCommand

PS C:\WINDOWS\system32> $Error[0].Exception.ToString()
System.IO.FileNotFoundException: Could not load file or assembly 'netstandard, Version=2.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51' or
 one of its dependencies. The system cannot find the file specified.
File name: 'netstandard, Version=2.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51'
   at System.Reflection.RuntimeAssembly.GetExportedTypes(RuntimeAssembly assembly, ObjectHandleOnStack retTypes)
   at System.Reflection.RuntimeAssembly.GetExportedTypes()
   at System.Management.Automation.Runspaces.PSSnapInHelpers.GetAssemblyTypes(Assembly assembly, String name)
   at System.Management.Automation.Runspaces.PSSnapInHelpers.AnalyzeModuleAssemblyWithReflection(Assembly assembly, String name, PSSnapInInfo psSnap
InInfo, PSModuleInfo moduleInfo, Boolean isModuleLoad, Dictionary`2& cmdlets, Dictionary`2& aliases, Dictionary`2& providers, String helpFile, Type&
 randomCmdletToCheckLinkDemand, Type& randomProviderToCheckLinkDemand)
   at System.Management.Automation.Runspaces.PSSnapInHelpers.AnalyzePSSnapInAssembly(Assembly assembly, String name, PSSnapInInfo psSnapInInfo, PSMo
duleInfo moduleInfo, Boolean isModuleLoad, Dictionary`2& cmdlets, Dictionary`2& aliases, Dictionary`2& providers, String& helpFile)
   at System.Management.Automation.Runspaces.InitialSessionState.ImportCmdletsFromAssembly(Assembly assembly, PSModuleInfo module)
   at Microsoft.PowerShell.Commands.ModuleCmdletBase.LoadBinaryModule(PSModuleInfo parentModule, Boolean trySnapInName, String moduleName, String fi
leName, Assembly assemblyToLoad, String moduleBase, SessionState ss, ImportModuleOptions options, ManifestProcessingFlags manifestProcessingFlags, S
tring prefix, Boolean loadTypes, Boolean loadFormats, Boolean& found, String shortModuleName, Boolean disableFormatUpdates)
   at Microsoft.PowerShell.Commands.ModuleCmdletBase.LoadModule(PSModuleInfo parentModule, String fileName, String moduleBase, String prefix, Sessio
nState ss, Object privateData, ImportModuleOptions& options, ManifestProcessingFlags manifestProcessingFlags, Boolean& found, Boolean& moduleFileFou
nd)
   at Microsoft.PowerShell.Commands.ImportModuleCommand.ImportModule_LocallyViaName(ImportModuleOptions importModuleOptions, String name)
   at Microsoft.PowerShell.Commands.ImportModuleCommand.ProcessRecord()
   at System.Management.Automation.CommandProcessor.ProcessRecord()

WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.
Note: There is some performance penalty associated with assembly bind failure logging.
To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog].

PS C:\WINDOWS\system32> remove-module sqlserver
Remove-Module : An item with the same key has already been added.
At C:\Program Files\WindowsPowerShell\Modules\SqlServer\22.1.1\SqlServer.psm1:78 char:5
+     Remove-Module -ModuleInfo $importedModules
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Remove-Module], ArgumentException
    + FullyQualifiedErrorId : System.ArgumentException,Microsoft.PowerShell.Commands.RemoveModuleCommand

Version info: Windows Server 2012 R2 ; SQL Server 2014 (12.0.6449.1); Powershell 5.1.14409.2001

[matt-gantz]

Update- I started to look closer at what is happening when running Import-Module. It get's the error while loading dlls, so I walked through the code and found I could reproduce the error by attempting to load this dll:

Microsoft.SqlServer.Assessment.Cmdlets.dll

PS C:\WINDOWS\system32> $binaryModulePath = 'C:\Program Files\WindowsPowerShell\Modules\SqlServer\22.1.1\Microsoft.SqlServer.Assessment.Cmdlets.dll'
Unblock-file -Path $binaryModulePath
$binaryModule = Import-Module -Name $binaryModulePath -PassThru
Import-Module : Could not load file or assembly 'netstandard, Version=2.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51' or one of its dependencies. The 
system cannot find the file specified.
At line:3 char:17
+ $binaryModule = Import-Module -Name $binaryModulePath -PassThru
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Import-Module], FileNotFoundException
    + FullyQualifiedErrorId : System.IO.FileNotFoundException,Microsoft.PowerShell.Commands.ImportModuleCommand

[Matteo-T]

Hi @matt-gantz - this looks like an old problem that was addressed a while back.

What OS are you on? The issue back then was only for people running on some really old OS (Windows Server 2016 maybe?) without NetFx 4.7.2.

The workaround back then was to forcefully include that netstandard "shim". But since the new v22 module got modernized, NetFx472+ is required... and that should make the need for that DLL go away.

[matt-gantz]

Hi @Matteo-T - Yes this was occurring on Windows Server 2016 and 2012 R2.

It does appear that dotnet 4.8 solves the issue. I feel that this dependency should be listed here: https://www.powershellgallery.com/packages/SqlServer

Do you agree?

[Matteo-T]

Uhm... I don't see anything specific for "OS and .Net dependencies". Those OSes are so old that... I would think by now the world had moved forward. :) If that's not the case and I keeep hearing about this problem, I'll think of something.

Thanks.