Invoke-sqlcmd crash on parallel execution

[Ilchert]

Steps to reproduce

I am trying to run parallel queries using Foreach-Object -Parallel and invoke-sqlcmd but get error

InvalidOperation: An item with the same key has already been added. Key: AZURE_KEY_VAULT

Sequential invoke works fine. I am not sure, that this issue is related to powershell core itself.

Or

Microsoft SQL Server 2019 (RTM-CU25-GDR) (KB5036335) - 15.0.4360.2 (X64) …
Invoke-Sqlcmd:
Line |
3 | Invoke-Sqlcmd -ConnectionString "Server=$server;Database=master;T …
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| The cmdlet needs to access a key in a key vault or a managed HSM, but you have not provided a valid authentication token, or you don’t have the required permissions. Use -KeyVaultAccessToken and/or -ManagedHSMAccessToken parameters to provide a valid token or authenticate to Azure using the Add-SqlAzureAuthenticationContext cmdlet.
Microsoft SQL Server 2019 (RTM-CU25-GDR) (KB5036335) - 15.0.4360.2 (X64) …

Script example

1, 2, 3, 4, 5 | ForEach-Object -Parallel { 
    Start-Sleep 2
    Invoke-Sqlcmd -ConnectionString "Server=.;Database=master;TrustServerCertificate=True;Integrated Security=true" -Query "select @@version" 
} -ThrottleLimit 2

Expected behavior

Invoke should works fine

Actual behavior

Fails with InvalidOperation: An item with the same key has already been added. Key: AZURE_KEY_VAULT or The cmdlet needs to access a key in a key vault or a managed HSM, but you have not provided a valid authentication token, or you don’t have the required permissions. Use -KeyVaultAccessToken and/or -ManagedHSMAccessToken parameters to provide a valid token or authenticate to Azure using the Add-SqlAzureAuthenticationContext cmdlet.

Error details

Exception             :
    Type       : System.InvalidOperationException
    TargetSite :
        Name          : RegisterCustomKeyStoreProviders
        DeclaringType : [Microsoft.SqlServer.Management.AlwaysEncrypted.Management.AlwaysEncryptedManagement]
        MemberType    : Method
        Module        : Microsoft.SqlServer.Management.AlwaysEncrypted.Management.dll
    Message    : The cmdlet needs to access a key in a key vault or a managed HSM, but you have not provided a valid authentication token, or
you don’t have the required permissions. Use -KeyVaultAccessToken and/or -ManagedHSMAccessToken parameters to provide a valid token or
authenticate to Azure using the Add-SqlAzureAuthenticationContext cmdlet.

    Source     : Microsoft.SqlServer.Management.AlwaysEncrypted.Management
    HResult    : -2146233079
    StackTrace :
   at Microsoft.SqlServer.Management.AlwaysEncrypted.Management.AlwaysEncryptedManagement.RegisterCustomKeyStoreProviders(SqlConnection
connection)
   at Microsoft.SqlServer.Management.PowerShell.Shared.AzureKeyVaultUtilities.RegisterCustomKeyStoreProviders(String keyVaultAccessToken,
String managedHsmAccessToken, SqlConnection conn)
   at Microsoft.SqlServer.Management.PowerShell.GetScriptCommand.ProcessRecord()
CategoryInfo          : InvalidResult: (:) [Invoke-Sqlcmd], InvalidOperationException
FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
InvocationInfo        :
    MyCommand        : Invoke-Sqlcmd
    ScriptLineNumber : 3
    OffsetInLine     : 5
    HistoryId        : 1
    Line             :     Invoke-Sqlcmd -ConnectionString "Server=.;Database=master;TrustServerCertificate=True;Integrated
Security=true" -Query "select @@version"

    Statement        : Invoke-Sqlcmd -ConnectionString "Server=.;Database=master;TrustServerCertificate=True;Integrated Security=true"
-Query "select @@version"
    PositionMessage  : At line:3 char:5
                       +     Invoke-Sqlcmd -ConnectionString "Server=.;Database=master;T …
                       +     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    InvocationName   : Invoke-Sqlcmd
    CommandOrigin    : Internal
ScriptStackTrace      : at <ScriptBlock>, <No file>: line 3

Environment data

Name                           Value
----                           -----
PSVersion                      7.4.2
PSEdition                      Core
GitCommitId                    7.4.2
OS                             Microsoft Windows 10.0.19045
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

Visuals

[Ilchert]

Might be related to microsoft/SQLServerPSModule/issues/69

[rhubarb-geek-nz]

If you absolutely must do it in parallel and there is no other work around, can you use an SqlConnection directly?

$Connection = New-Object System.Data.SqlClient.SqlConnection -ArgumentList $ConnectionString
try
{
	$Connection.Open()
	$Command = $Connection.CreateCommand()
	$Command.CommandText = $CommandText
	$Reader = $Command.ExecuteReader()
	try
	{
		$DataTable = New-Object System.Data.DataTable
		$DataTable.Load($Reader)
		$DataTable | Format-Table
	}
	finally
	{
		$Reader.Dispose()
	}
}
finally
{
	$Connection.Dispose()
}

[SeeminglyScience]

Thanks for the report!

It's up to the command itself to ensure it doesn't access any static resources in a thread unsafe manner, so marking this as Resolution-External

[microsoft-github-policy-service]

This issue has been marked as external and has not had any activity for 1 day. It has been be closed for housekeeping purposes.

[microsoft-github-policy-service]

📣 Hey @Ilchert, how did we do? We would love to hear your feedback with the link below! 🗣️

🔗 https://aka.ms/PSRepoFeedback

Microsoft Forms