Set-SqlColumnEncryption throws error

Question

I am encrypting several columns in an existing table using the Encrypt Columns feature in SSMS. I have chosen to generate a Powershell script instead of encrypting the columns in the wizard so I can encrypt the columns at a later point in time. The script is below:

# Generated by SQL Server Management Studio at 3:03 PM on 4/05/2018

Import-Module SqlServer
# Set up connection and database SMO objects

$sqlConnectionString = "Data Source=.;Initial Catalog=MyDatabase;Integrated Security=True;MultipleActiveResultSets=False;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;Packet Size=4096;Application Name=`"Microsoft SQL Server Management Studio`""
$smoDatabase = Get-SqlDatabase -ConnectionString $sqlConnectionString

# If your encryption changes involve keys in Azure Key Vault, uncomment one of the lines below in order to authenticate:
#   * Prompt for a username and password:
#Add-SqlAzureAuthenticationContext -Interactive

#   * Enter a Client ID, Secret, and Tenant ID:
#Add-SqlAzureAuthenticationContext -ClientID '<Client ID>' -Secret '<Secret>' -Tenant '<Tenant ID>'

# Change encryption schema

$encryptionChanges = @()

# Add changes for table [dbo].[Voucher]
$encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName dbo.Voucher.Code -EncryptionType Randomized -EncryptionKey "cek"

Set-SqlColumnEncryption -ColumnEncryptionSettings $encryptionChanges -InputObject $smoDatabase

However when I run the script, I get the below exception from the Set-SqlColumnEncryption cmdlet:

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 'Newtonsoft.Json,
Version=6.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed' or one of its dependencies. The
system cannot find the file specified.

I updated the Sqlserver module also. Surely I don't have to manually drop the Newtonsoft.Json.dll file into the SqlServer module directory. Any ideas?

Answer 1

You're using 'Always Encrypted'. I'm assuming your column master key is stored in Windows Certificate Store on the machine where your script is running. And, I'm assuming your keys are setup correctly.

Let's say I have a table with schema:

CREATE TABLE dbo.property_bag (
id int identity(1,1) primary key
, insert_user sysname not null
, insert_date datetime2 not null
, insert_source sysname not null
, [stuff] varchar(max) ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED
, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
, COLUMN_ENCRYPTION_KEY = [dbo-property_bag]) NULL
);
GO

Here is a script to insert data into my table:

$insert_count = 10
$words = @('bobby','mikey','billy','suzie','kenny','narav','navneet','rachel','jose','juan')

$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = “Server='<db-server>';Database='<db>';Column Encryption Setting=enabled;Integrated Security=True;”
$hostname = $env:computername

try {
    $conn.Open()
 
    for ($i = 1; $i -le $insert_count; $i++) {
        $val = Get-Random -Maximum 10
        $word_to_insert = $words[$val]
        $sqlcmd = New-Object System.Data.SqlClient.SqlCommand
        $sqlcmd.Connection = $conn
        $sqlcmd.CommandText = “INSERT INTO dbo.property_bag ([insert_user], [insert_date], [insert_source], [stuff]) VALUES (SUSER_SNAME(), GETDATE(), '${hostname}', @value)”
        $sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@value",[Data.SQLDBType]::VarChar, 4000))) | Out-Null
        $sqlcmd.Parameters[0].Value = $word_to_insert
        $sqlcmd.ExecuteNonQuery() | Out-Null
        Write-Progress -Activity "Inserting Records..." -Status "Progress $($i / $insert_count * 100)%" -percentComplete ($i / $insert_count)
    }
} finally {
    $conn.Close()
    $conn.Dispose()
}

When I registered the column master key with the database (through Object Explorer) I'd set the path to the "CurrentUser/My/". SQL Server will pass that location back to the driver, which will search that location in the local key store for the certificate matching the thumbprint provided. That key (which in my case is on my app-server where the script is running) will decrypt the column encryption key. None of this is detailed in the script. It's all setup in the database. Here is the data flow for this example:

In your example, the connection string doesn't contain the "Column Encryption Setting=enabled". I don't believe the Get-SqlDatabase commandlet takes a connection string parameter. I believe you'd pass it an instance object. There are just a bunch of things that don't look quiet right.

If your keys aren't setup correctly, I'd start here.

Answer 2

I was having the exact same issue, and I believe this is some kind of component integration issue on your particular mix of OS and Sql Server.

Case in point, I received this error when trying to run that powershell script on my laptop. My laptop happens to use Windows 10 and Sql Server Management Studio 17.2 (which is what ultimately generates the ps1). And furthermore, my laptop does contain Newtonsoft.Json.dll in the correct directory.

However, I hopped on to a server, which was using Windows 2012 R2 and SSMS 17.2 and the script DOES work!

Ultimately, it's as if there is some kind of assembly binding redirect missing in the Windows10/SSMS17.2 installation which Windows2012R2/SSMS17.2 seems to resolve correctly.