Skip to main content

SQL Always Encrypted - Re-encrypting the database with a new Column Encryption key failed

In SQL 2016 and Azure SQL there is a new powerful feature - Always Encrypted which allows to keep the encryption key outside of the database (for increased security).

When dealing with encrypted data at rest we need to be able to change (rotate) the encryption key either on schedule or upon a request.

Microsoft provides a example of a PowerShell script that re-encrypts the all data in a database with a new column encryption key.

I used that example to create my own script.
The first setback with the following cmdlet
Set-SqlColumnEncryption -ColumnEncryptionSettings $ces -InputObject $database -UseOnlineApproach -MaxDowntimeInSeconds 120 -LogFileDirectory .
was a syntax error 

Set-SqlColumnEncryption : A parameter cannot be found that matches parameter name 'UseOnlineApproach'.

It was easy to fix - I removed all the parameters except for ColumnEnxryptionSettings and InputObject.
But then the same cmdlet failed at the execution time with this error.
Set-SqlColumnEncryption : Add or update objects failed due to the following errors: 
Error SQL46010: Incorrect syntax near -.
At C:\Users\smizrokhi\Documents\Projects\BVI\Scripts\BOSSprovisioning\RotateCEK.ps1:58 char:1
+ Set-SqlColumnEncryption -ColumnEncryptionSettings $ces -InputObject $ ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Set-SqlColumnEncryption], DacModelException
    + FullyQualifiedErrorId : EncryptionError,Microsoft.SqlServer.Management.PowerShell.AlwaysEncrypted.SetColumnEncryption

It turned out that the column encryption key  name in my was 'CEK-2' and because of the dash character within the name it threw out this SQL incorrect syntax error.
The solution was very simple - enclose he column encryption key  name in square brackets.
The original statement 
$ces += New-SqlColumnEncryptionSettings -ColumnName $threeColPartName -EncryptionType $columns[$j].EncryptionType -EncryptionKey $newCekName


was modified as following


$ces += New-SqlColumnEncryptionSettings -ColumnName $threeColPartName -EncryptionType $columns[$j].EncryptionType -EncryptionKey "[$newCekName]"

















































Comments











Post a Comment




















Popular posts from this blog





















SQL 2014 performance - Local disk vs. Azure Blob vs. Azure VM attached disk




























 Today I decided to compare SQL 2014 (RTM) performance when running a test against    - a local database (created on  WD7500BPKT - 750 GB 7200 RPM)   - a DB created on a disk attached to the A3 (3 cores, 7 GB) VM in Azure  - a DB created on an Azure blob   The VM in Azure was created in the US East region using the SQL 2014 template from the gallary provided by Microsoft.    All databases were created with a single 10 GB data file (10 GB) and 1GB  log file.   On the local SQL instance the DB was created as   CREATE DATABASE TestDBonLocal  ON      (NAME = file_data1, FILENAME = 'C:\TEMP\filedata1.mdf', SIZE = 10GB, FILEGROWTH = 256 MB )   LOG ON   (NAME = file_log1, FILENAME = 'C:\TEMP\filelog1.ldf', SIZE = 1GB, FILEGROWTH = 32 MB)      On the Azure VM the database on the attached disk (the disk was mapped as drive F:) was created as such   CREATE DATABASE TestDBonDisk  ON      (NAME = file_data1, FILENAME = 'F:\TMP\filedat...




















2 comments









Read more






























Create 3-Node Windows 2012 Multi-subnet Cluster




























Environment   There are two Data centers connected via a WAN link.       Two Windows 2012 Servers (called SQLDEV1 and SQLDEV2) are located in the Primary Data Center (on the IP subnet 192.168.79.0/24) and the third server is placed in the Secondary Data Center with the 192.168.69.0/24 subnet. We’ll be creating a three-node Windows cluster with no shared storage on the multi subnet network with a file share witness at the Primary Data Center. We’ll be using a file share witness to protect from the cluster failure in a situation when the network between the Data Centers is unavailable and one of the servers in the Primary Data Center is also down (or being rebooted).     The final state will look like depicted above:   -           Two Virtual IP’s will be assigned (192.168.76.218 and 192.168.69.134) to the cluster   -           The servers at the Primary Data Center will have a vote (Vote=1) and the ...




















2 comments









Read more




























SQL 2012 AlwaysOn: Synchronous vs. Asynchronous commit. Performance impact




























Recently I've had a chance to build a 3-server AlwaysOn environment distributed between the primary and secondary data centers.   The configuration looks like this:   Primary Data Center                         Secondary Data Center                         SQLDEV1                                        SQLDEV3           SQLDEV2    The availability group was crated with synchronous commit replicas on SQLDEV1 and SQLDEV2 and the replica on SQLDEV3 was configured for asynchronous commit.   The link between the data centers was not great and when I pinged SQLDEV3 from SQLDEV1 I got these results     Approximate round trip times in milli-seconds:      Minimum = 39ms, Maximum = 63ms, Average = 42ms    I also created a very simp...




















4 comments









Read more