Set-SqlColumnEncryption throwng exception at runtime

Question

Running power shell script in Azure Automate Job to Un-Encrypt Table Columns in Azure SQL Database. I have the latest module import for Sql Server.

Getting this error when this code excecutes

Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $EncryptionSettings -KeyVaultAccessToken $keyVaultAccessToken

System.MissingMethodException: Method not found: 'Void Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider.SqlColumnEncryptionAzureKeyVaultProvider..ctor(Azure.Core.TokenCredential)'.
   at Microsoft.SqlServer.Management.AlwaysEncrypted.Management.SqlAKVProviderWithDisposableCache..ctor(TokenCredential tokenCredential)
   at Microsoft.SqlServer.Management.AlwaysEncrypted.Management.AlwaysEncryptedManagement.SetCustomProvidersWithDisposableCache(Boolean useGlobalCredential)
   at Microsoft.SqlServer.Management.PowerShell.Shared.AzureKeyVaultUtilities.RegisterCustomKeyStoreProviders(String keyVaultAccessToken, String managedHsmAccessToken, SqlConnection conn)
   at Microsoft.SqlServer.Management.PowerShell.AlwaysEncrypted.SetColumnEncryption.InitializeObject(Database db)
   at Microsoft.SqlServer.Management.PowerShell.SqlCmdlet`1.ProcessRecord()
   at Microsoft.SqlServer.Management.PowerShell.SmoCmdlet.ProcessRecord()
   at System.Management.Automation.CommandProcessor.ProcessRecord()

Answer 1

Hello there,

Does your table have a primary key or a clustered index? There is a known issue in the wizard/PowerShell that may cause it to run out of memory, if the table does not have a primary or a clustered index, so one workaround could be to create a primary key/clustered index, if you don't have it already.

You can also try to use 64-bit PowerShell. Using 32-bit PowerShell instead of SSMS will likely not help, as SSMS and PowerShell use the same library (DacFx) to support most Always Encrypted scenarios.

Hope this resolves your Query !!

--If the reply is helpful, please Upvote and Accept it as an answer–