SQL server Always Encryption Error

Question

Is there a OS dependency for SQL server always encryption? I am trying to encrypt columns using sql server module for power shell and getting certain file not found exception. The server is on Windows Server 2012 and the DB is SQL server 2016. Is DB encryption using PowerShell supported on SQL server 2012?

Answer 1

We could solve this issue by reinstalling .net dev pack 4.7.2.

Answer 2

This is the command

$sqlConnectionString= "Data Source=AAAA;Initial Catalog=BBBBB;User Id=CCCCCC;Password=PPPPPPPPP;Connect Timeout=300;Encrypt=False;TrustServerCertificate=False;Packet Size=4096;Application Name=`'Microsoft SQL Server Management Studio';Column Encryption Setting=Enabled;MultipleActiveResultSets=True"


$smoDatabase = Get-SqlDatabase -ConnectionString $sqlConnectionString

$encryptionChanges = @()
$encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName QC_EGMS_AE_TEST_TABLE1.uuid2 -EncryptionType Deterministic -EncryptionKey QC_AE_CEK_1
Set-SqlColumnEncryption -ColumnEncryptionSettings $encryptionChanges -InputObject $smoDatabase

This is the error:

[06/25/21 18:41:03.949] [10528]  The exception caught when Set-SqlColumnEncryption :  
June 25th 2021 18:40:11Info
[06/25/21 18:41:03.949] [10528]  The exception is :  System.TypeInitializationException: The type initializer for 'Microsoft.SqlServer.Management.AlwaysEncrypted.Types.AlwaysEncryptedManager' threw an exception. ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.TypeInitializationException: The type initializer for 'Microsoft.SqlServer.Management.AlwaysEncrypted.Management.AlwaysEncryptedManagement' threw an exception. ---> System.IO.FileNotFoundException: Could not load file or assembly 'System.Net.Http, Version=4.2.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified. 
June 25th 2021 18:40:11Info
   at Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider.SqlColumnEncryptionAzureKeyVaultProvider..ctor(AuthenticationCallback authenticationCallback, String[] trustedEndPoints) 
June 25th 2021 18:40:11Info
   at Microsoft.SqlServer.Management.AlwaysEncrypted.Management.AlwaysEncryptedManagement.get_CustomProviders() 
June 25th 2021 18:40:11Info
   at Microsoft.SqlServer.Management.AlwaysEncrypted.Management.AlwaysEncryptedManagement..cctor() 
June 25th 2021 18:40:11Info
   --- End of inner exception stack trace --- 
June 25th 2021 18:40:11Info
   at Microsoft.SqlServer.Management.AlwaysEncrypted.Management.AlwaysEncryptedManagement.get_CustomProviders() 
June 25th 2021 18:40:11Info
   --- End of inner exception stack trace --- 
June 25th 2021 18:40:11Info
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) 
June 25th 2021 18:40:11Info
   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) 
June 25th 2021 18:40:11Info
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) 
June 25th 2021 18:40:11Info
   at System.Reflection.RuntimePropertyInfo.GetValue(Object obj, Object[] index) 
June 25th 2021 18:40:11Info
   at Microsoft.SqlServer.Management.AlwaysEncrypted.Types.AlwaysEncryptedManager.get_CustomProviders() 
June 25th 2021 18:40:11Info
   at Microsoft.SqlServer.Management.AlwaysEncrypted.Types.AlwaysEncryptedManager..cctor() 
June 25th 2021 18:40:11Info
   --- End of inner exception stack trace --- 

Power shell version:5.1
SQLCMD VErsion:14.0
SQL SERVER MODULE FOR POWER SHELL:21.1.18245
SQL Server Version:SQL SERVER 2016 SP2
OS Version:Windows 2012 R2