Always Encrypted Key rotation – Column master Key rotation.

Article
08/13/2015

Update: The syntax for column master keys have been updated. Please refer to https://blogs.msdn.com/b/sqlsecurity/archive/2015/10/28/new-enhancements-in-always-encrypted.aspx for details on what is new in Always Encrypted.

Many standards that are used to regulate data security define key rotation requirements in order to meet compliance. In many cases, there are defined regulations that distinguish between the key-encrypting keys (KEK) and data-encrypting keys (DEK) key rotation strategy. In this article, we will be discussing how to rotate a column master key (CMK, which is a form of key-encrypting key) in Always Encrypted, including some PowerShell sample code that can be used to make the key rotation easier.

To get started, we will assume we are using the same sample application we have been using on previous entries in this blog; where we have a table named Patients, with two columns encrypted: SSN and BirthDate. These columns are encrypted by a column encryption key named CEK1, which is protected by a column master key (based on a certificate) named CMK1.

The ultimate goal for the example in this article is to ensure that CEK1 is protected by a new column master key (CMK2), and CMK1 can be retired.

The first step would be to create a new column master key following the same process, we described on Getting Started With Always Encrypted. Let’s call the new master key CMK2.

We are going to use the following terms throughout this article:

- source CMK – the CMK that is used in the database prior to the rotation.

- target CMK – the new CMK that will be used in the database to protect CEKs once the rotation is completed.

Creating a new CEK value encrypted by the new Column Master Key

We have the assumption that the normal state for the Always Encrypted keys is that any given column encryption key only has one encrypted value during normal operation, and that two encryption values (by two different CMK) only happen for a short period of time during the key rotation process we are describing.

Keeping more than 1 encrypted value for a CEK would not only affect performance (as more metadata would be transmitted between the server and the client driver), but it will prevent any further rotation as a maximum of 2 encrypted values for any CEK is allowed by the system. It is recommended that you verify that the system is ready for key rotation before proceeding with the process, but the provided script also verifies this constraint before starting with the process.

 ## Get the old CMK ID
 $sqlcmd = $sqlcon.CreateCommand()
 $sqlcmd.CommandText = "SELECT column_master_key_id FROM sys.column_master_keys WHERE name = @old_cmk_name"
 $oldCmkParam = New-Object System.Data.SqlClient.SqlParameter( "@old_cmk_name", $oldCmkName )
 $o = $sqlcmd.Parameters.Add( $oldCmkParam );
 $oldCmkId = $sqlcmd.ExecuteScalar();
 
 if( $oldCmkId -eq $null )
 {
 Throw [string]::Format("No column_master_key_id was found for Column Master Key '{0}'. Make sure the CMK was created in the target database.", $oldCmkName )
 }
 
 ## Verify if it is safe to proceed with the key rotation
 $sqlcmd = $sqlcon.CreateCommand()
 $sqlcmd.CommandText = "SELECT count(*) FROM sys.column_encryption_key_values WHERE column_master_key_id != @old_cmk_id AND column_encryption_key_id IN (SELECT cekv.column_encryption_key_id FROM sys.column_encryption_key_values cekv, sys.column_master_keys cmk WHERE cmk.column_master_key_id = cekv.column_master_key_id AND cmk.column_master_key_id = @old_cmk_id)"
 $oldCmkIdParam = New-Object System.Data.SqlClient.SqlParameter( "@old_cmk_id", $oldCmkId )
 $o = $sqlcmd.Parameters.Add( $oldCmkIdParam );
 $otherCekValueCount = $sqlcmd.ExecuteScalar();
 
 if( $otherCekValueCount -ne 0 )
 {
 Throw [string]::Format("There are CEK values encrypted by CMK other than '{0}' for some of the CEKs that would be affected by this rotation. Manually clean these old entries or run 'Sql-Database-Clean-Cmk_Values' to clean these values before continue.", $oldCmkName )
 }

Once we have made sure it is safe to start the process for the CMK rotation (i.e. we only have one CEK value per key), we will have to do the following:

For each CEK protected by CMK1, we will have to decrypt the CEK value, encrypt it with CMK2, and update the CEK in SQL server to add the new value (encrypted with CMK2). In order to accomplish this, it is very important to make sure we run the script from a machine and an account that has been granted access to both CMKs.

For each one of these values, we will decrypt the CEK using the CMK path from the CMK we are going to retire (CMK1):

 $cekPlaintext = $cmkprov.DecryptColumnEncryptionKey($cmkPath, $encryptionAlgorithmId, $encryptedKey)

And then encrypt with the new CMK value (CMK2)

 $newCekCiphertext = $cmkprov.EncryptColumnEncryptionKey($newCmkPath, $encryptionAlgorithmId, $cekPlaintext)

Once we have the new encrypted CEK value, we will issue the DDL statement to add the new value protected by the new CMK:

 $addValueTsql = [string]::Format( "ALTER COLUMN ENCRYPTION KEY [{0}] ADD VALUE (COLUMN_MASTER_KEY = [{1}], ALGORITHM = 'RSA_OAEP', ENCRYPTED_VALUE = {2} );", $cekName.Replace("]", "]]"), $newCmkName.Replace("]", "]]"), $ciphertextInHex );
$sqlcmdDdl.CommandText = $addValueTsql
$o = $sqlcmdDdl.ExecuteNonQuery()

Using the PS module included in the sample, we will simply call:

 Sql-Database-Rotate-Cmk "Data Source=mySqlServer;Initial Catalog=db_always_encrypted_demo;Integrated Security=True;" "CMK1" "CMK2"

Something important to remark is that any machine that still has access to CMK1, but where CMK2 has not yet been deploy would be able to run the app without any problem. It is also important to notice that any already running application that has already decrypted CEK1 will not have to decrypt either CMK until the process restarts as the CEK would be available in the process memory in cleartext.

Deploy the new CMK on all machines where it is needed

Although the details on how to accomplish this step are a bit outside the scope of the article, it is important to make an emphasis on it as it must be completed in order to finish the key rotation process, otherwise we cannot clean-up the CEK values protected by the CMK that we are retiring.

It is a common practice to have a reasonable window for such deployment as it may take some time to deploy and verify the new CMK is in place if you are using a machine-specific CMK source (such as certificates). Make sure to take into consideration such window to decide when to start the key rotation process and avoid being out of compliance.

The destruction of the old CMK itself would depend on your own security policies and best practices. In many occasions it may be recommend to store an archival copy of the key in a separate system (i.e. a secure repository) for disaster recovery scenarios.

Cleaning up older CEK values

Once the new CMK has been deployed to all machines that need to access the sensitive data in cleartext, we need to proceed to remove the CEK values protected by the recently retired CMK.

As we mentioned before, Always Encrypted makes the assumption that during normal operations any given CEK will only be encrypted by one CMK in order to avoid unnecessary overhead in the protocol between the driver and SQL Server as described in Always Encrypted Key Metadata, but during the CMK rotation phase, it will be encrypted by two CMKs: the new CMK as well as the “previous” CMK; giving the operations team an opportunity to deploy the new CMK to all affected machines without service disruption.

In order to remove these older values, we can use once the PS script included in the demo, specifying that CMK2 is the current CMK:

 Sql-Database-Clean-Cmk_Values "Data Source=mySqlServer;Initial catalog=db_always_encrypted_demo;Integrated Security=True;" "CMK2"

The script will store the older CEK values on our archive table (in case you decided to use it). This archive table may help in case you ever need to access the data using the older key and you need to recover them, but if not needed anymore, it is safe to remove these archived values.

Conclusions

Planning for a column master key rotation is important, making sure that all the steps are followed will ensure that the process can be accomplished with the help of a handful of PowerShell scripts, and that the new CMK can be deployed out of band to avoid service disruptions.

Appendix: Powershell Scripts

AlwaysEncrypted_Cmk_rotation

 <#
 .Synopsis
 Rotate the column master key on a database
 
 .Description
 Rotate the column master key (CMK) on a database. The module will use the old CMK to decrypt the corresponding CEK value, then encrypt using the new CMK. 
 If there are older CEK values present, the rotation will fail.
 
 .Parameter ConnStr
 The connection string to the target Database. Two connections will be opened to the target DB
 
 .Parameter OldCmkName
 The name of the old column master key (CMK) defintion 
 
 .Parameter NewCmkName
 The name of the new column master key (CMK) defintion 
 
 .Example
 # Rotate the CMK 'CMK_old', using 'CMK_new' as the new CMK
 Sql-Database-Rotate-Cmk "Data Source=.\ctp2;Initial Catalog=db_always_encrypted_demo;Integrated Security=True;" "CMK1" "CMK2"
 #>
 
 function Sql-Database-Rotate-Cmk {
 param(
 [string] $connstr,
 [string] $oldCmkName,
 [string] $newCmkName
 )
 
 $sqlcmd = New-Object System.Data.SqlClient.SqlCommand
 $sqlcecertprov = New-Object System.Data.SqlClient.SqlColumnEncryptionCertificateStoreProvider
 $sqlcon = New-Object System.Data.SqlClient.SqlConnection($connstr)
 $sqlconDdl = New-Object System.Data.SqlClient.SqlConnection($connstr)
 
 try
 {
 $sqlcon.Open()
 $sqlconDdl.Open()
 
 ## Get the new CMK path from SQL Server
 $sqlcmd = $sqlcon.CreateCommand()
 $query_new_path = "SELECT key_path FROM sys.column_master_keys WHERE name = @new_cmk_name"
 $sqlcmd.CommandText = $query_new_path
 $newCmkParam = New-Object System.Data.SqlClient.SqlParameter( "@new_cmk_name", $newCmkName )
 $o = $sqlcmd.Parameters.Add( $newCmkParam );
 $newCmkPath = $sqlcmd.ExecuteScalar();
 
 if( $newCmkPath -eq $null )
 {
 Throw [string]::Format("No key_path was found for Column Master Key '{0}'. Make sure the CMK was created in the target database.", $newCmkName )
 }
 
 ## Get the old CMK ID
 $sqlcmd = $sqlcon.CreateCommand()
 $sqlcmd.CommandText = "SELECT column_master_key_id FROM sys.column_master_keys WHERE name = @old_cmk_name"
 $oldCmkParam = New-Object System.Data.SqlClient.SqlParameter( "@old_cmk_name", $oldCmkName )
 $o = $sqlcmd.Parameters.Add( $oldCmkParam );
 $oldCmkId = $sqlcmd.ExecuteScalar();
 
 if( $oldCmkId -eq $null )
 {
 Throw [string]::Format("No column_master_key_id was found for Column Master Key '{0}'. Make sure the CMK was created in the target database.", $oldCmkName )
 }
 
 ## Verify if it is safe to proceed with the key rotation
 $sqlcmd = $sqlcon.CreateCommand()
 $sqlcmd.CommandText = "SELECT count(*) FROM sys.column_encryption_key_values WHERE column_master_key_id != @old_cmk_id AND column_encryption_key_id IN (SELECT cekv.column_encryption_key_id FROM sys.column_encryption_key_values cekv, sys.column_master_keys cmk WHERE cmk.column_master_key_id = cekv.column_master_key_id AND cmk.column_master_key_id = @old_cmk_id)"
 $oldCmkIdParam = New-Object System.Data.SqlClient.SqlParameter( "@old_cmk_id", $oldCmkId )
 $o = $sqlcmd.Parameters.Add( $oldCmkIdParam );
 $otherCekValueCount = $sqlcmd.ExecuteScalar();
 
 if( $otherCekValueCount -ne 0 )
 {
 Throw [string]::Format("There are CEK values encrypted by CMK other than '{0}' for some of the CEKs that would be affected by this rotation. Manually clean these old entries or run 'Sql-Database-Clean-Cmk_Values' to clean these values before continue.", $oldCmkName )
 }
 
 
 ## We will get the CEK values defined for the old CMK.
 ## If there is any, we will abort and fail
 $sqlcmd = $sqlcon.CreateCommand()
 $query = "SELECT cek.name, cmk.key_path, cekv.encrypted_value, cekv.encryption_algorithm_name FROM sys.column_encryption_key_values cekv, sys.column_encryption_keys cek, sys.column_master_keys cmk WHERE cek.column_encryption_key_id = cekv.column_encryption_key_id AND cmk.column_master_key_id = cekv.column_master_key_id AND cmk.name = @old_cmk_name"
 $sqlcmd.CommandText = $query
 $oldCmkParam = New-Object System.Data.SqlClient.SqlParameter( "@old_cmk_name", $oldCmkName);
 $o = $sqlcmd.Parameters.Add($oldCmkParam)
 
 $reader = $sqlcmd.ExecuteReader()
 
 $cmkprov = New-Object System.Data.SqlClient.SqlColumnEncryptionCertificateStoreProvider
 
 $queryFindOldestCmkValues = "SELECT cmk.name FROM sys.column_encryption_key_values cekv, sys.column_encryption_keys cek, sys.column_master_keys cmk WHERE cek.column_encryption_key_id = cekv.column_encryption_key_id AND cmk.column_master_key_id = cekv.column_master_key_id AND cmk.name != @old_cmk_name AND cek.name = @cek_name;"
 
 if( $reader.HasRows )
 {
 while( $reader.Read() )
 {
 $cekName = $reader.GetString(0)
 $cmkPath = $reader.GetString(1)
 $encryptedKey = $reader.GetSqlBytes(2).Value
 $encryptionAlgorithmId = $reader.GetString(3)
 
 $cekPlaintext = $cmkprov.DecryptColumnEncryptionKey($cmkPath, $encryptionAlgorithmId, $encryptedKey)
 if( $cekPlaintext -eq $null )
 {
 Throw [string]::Format("Unable to decrypt CEK value. CEK name: '{0}', CMK path: '{1}'", $cekName, $cmkPath)
 }
 
 $newCekCiphertext = $cmkprov.EncryptColumnEncryptionKey($newCmkPath, $encryptionAlgorithmId, $cekPlaintext)
 if( $newCekCiphertext -eq $null )
 {
 Throw [string]::Format("Unable to encrypt the new CEK value. CEK name: '{0}', CMK path: '{1}'", $cekName, $newCmkPath)
 }
 
 ## Generate the new CEK value and alterthe CEK in metadata
 $strbldr= New-Object System.Text.StringBuilder
 $o = $strbldr.Append("0x")
 for($i=0; $i -lt $newCekCiphertext.Length; $i++)
 {
 $o = $strbldr.AppendFormat("{0:x2}", $newCekCiphertext[$i]);
 }
 $ciphertextInHex = $strbldr.ToString()
 
 $sqlcmdDdl = $sqlconDdl.CreateCommand()
 $addValueTsql = [string]::Format( "ALTER COLUMN ENCRYPTION KEY [{0}] ADD VALUE (COLUMN_MASTER_KEY = [{1}], ALGORITHM = 'RSA_OAEP', ENCRYPTED_VALUE = {2} );", $cekName.Replace("]", "]]"), $newCmkName.Replace("]", "]]"), $ciphertextInHex );
 $sqlcmdDdl.CommandText = $addValueTsql
 $o = $sqlcmdDdl.ExecuteNonQuery()
 
 $cekPlaintext = ""
 }
 }
 
 $reader.Close()
 }
 finally
 {
 if( $sqlcon -ne $null )
 {
 $sqlcon.Close()
 }
 
 if( $sqlconDdl -ne $null )
 {
 $sqlconDdl.Close()
 }
 }
 }

AlwaysEncrypted_Cmk_key_cleanup

 <#
 .Synopsis
 Clean up CEK values, archiving the old CEK values into a table named [dbo].[t_column_encryption_key_values_archive].
 The archive table will be created if it does not exist.
 
 .Description
 Clean up CEK values, archiving the old CEK values into a table named [dbo].[t_column_encryption_key_values_archive].
 The CMK name specified will be considered as the current (i.e. valid) CMK value.
 The module will find all CEK protected by this CMK and will archive any other values and delete such values
 The archive table will be created if it does not exist.
 
 .Parameter ConnStr
 The connection string to the target Database. Two connections will be opened to the target DB
 
 .Parameter LatestCmkName
 The name of the current (valid) column master key (CMK) defintion 
 
 .Example
 Sql-Database-Clean-Cmk_Values "Data Source=.\ctp2;Initial Catalog=db_always_encrypted_demo;Integrated Security=True;" "CMK2"
 #>
 
 function Sql-Database-Clean-Cmk_Values {
 param(
 [string] $connstr,
 [string] $LatestCmkName
 )
 
 $sqlcmd = New-Object System.Data.SqlClient.SqlCommand
 $sqlcecertprov = New-Object System.Data.SqlClient.SqlColumnEncryptionCertificateStoreProvider
 $sqlcon = New-Object System.Data.SqlClient.SqlConnection($connstr)
 $sqlconInternal = New-Object System.Data.SqlClient.SqlConnection($connstr)
 
 try
 {
 $sqlcon.Open()
 $sqlconInternal.Open()
 
 $cmkName = $LatestCmkName
 
 ## Create a table for CEK archival
 $sqlcmd = $sqlcon.CreateCommand()
 $sqlcmd.CommandText = "IF( OBJECT_ID('dbo.t_column_encryption_key_values_archive' ) is null ) BEGIN CREATE TABLE [dbo].[t_column_encryption_key_values_archive]( column_encryption_key_id int not null, column_master_key_id int not null, encrypted_value varbinary(8000) null, encryption_algorithm_name sysname, archive_date timestamp); END"
 $CmkId = $sqlcmd.ExecuteNonQuery()
 
 ## Get the current CMK ID
 $sqlcmd = $sqlcon.CreateCommand()
 $sqlcmd.CommandText = 'SELECT column_master_key_id FROM sys.column_master_keys WHERE name = @cmk_name'
 $CmkParam = New-Object System.Data.SqlClient.SqlParameter( "@cmk_name", $cmkName);
 $o = $sqlcmd.Parameters.Add($CmkParam)
 $CmkId = $sqlcmd.ExecuteScalar()
 
 #####################################################################################
 ## We will get the CEK values defined for the old CMK.
 ## NOTE: We will delete CEK values protected by older CMKs than the one specified (if any) after making an archival copy
 
 ## Get the list of CEKs that we will need to cleanup based on the CMK that will soon be rotated
 $sqlcmd = $sqlcon.CreateCommand()
 $query = "SELECT cek.name, cek.column_encryption_key_id FROM sys.column_encryption_key_values cekv, sys.column_encryption_keys cek, sys.column_master_keys cmk WHERE cek.column_encryption_key_id = cekv.column_encryption_key_id AND cmk.column_master_key_id = cekv.column_master_key_id AND cmk.name = @cmk_name"
 $sqlcmd.CommandText = $query
 $CmkParam = New-Object System.Data.SqlClient.SqlParameter( "@cmk_name", $cmkName);
 $o = $sqlcmd.Parameters.Add($CmkParam)
 
 $reader = $sqlcmd.ExecuteReader()
 
 if( $reader.HasRows )
 {
 while( $reader.Read() )
 {
 $cekName = $reader.GetString(0)
 $cekId = $reader.GetInt32(1)
 
 $sqlcmdOldValue = $sqlconInternal.CreateCommand()
 $sqlcmdOldValue.CommandText = 'SELECT cmk.name, cekv.column_master_key_id, cekv.encrypted_value, cekv.encryption_algorithm_name FROM sys.column_encryption_key_values cekv, sys.column_master_keys cmk WHERE cmk.column_master_key_id = cekv.column_master_key_id AND cekv.column_master_key_id <> @cmk_id AND cekv.column_encryption_key_id = @cek_id'
 $CmkIdParam = New-Object System.Data.SqlClient.SqlParameter( "@cmk_id", $CmkId);
 $o = $sqlcmdOldValue.Parameters.Add($CmkIdParam)
 $CekIdParam = New-Object System.Data.SqlClient.SqlParameter( "@cek_id", $cekId);
 $o = $sqlcmdOldValue.Parameters.Add($CekIdParam)
 $cekvalueReader = $sqlcmdOldValue.ExecuteReader()
 
 $oldCekValueFound = 0
 if( $cekvalueReader.HasRows )
 {
 $o = $cekvalueReader.Read()
 $oldCekValueFound = 1
 
 $oldCmkName = $cekvalueReader.GetString(0)
 $oldCmkId = $cekvalueReader.GetInt32(1)
 $oldCekValue = $cekvalueReader.GetSqlBytes(2).Value
 $oldCekEncryptionAlgorithm = $cekvalueReader.GetString(3)
 }
 $cekvalueReader.Close()
 
 if( $oldCekValueFound -eq 1)
 {
 $sqlcmdArchiveAndDeleteOldCekValue = $sqlconInternal.CreateCommand()
 $sqlcmdArchiveAndDeleteOldCekValue.CommandText = "INSERT INTO dbo.t_column_encryption_key_values_archive (column_encryption_key_id, column_master_key_id, encrypted_value, encryption_algorithm_name)
 VALUES (@cek_id, @old_cmk_id, @encrypted_value, @encryption_algorithm_name)
 DECLARE @cmd nvarchar(max);
 SET @cmd = N'ALTER COLUMN ENCRYPTION KEY ' + QUOTENAME(@cek_name) + N' DROP VALUE (COLUMN_MASTER_KEY = '+ QUOTENAME(@old_cmk_name) +N')';
 EXECUTE (@cmd);"
 $CekIdParam = New-Object System.Data.SqlClient.SqlParameter( "@cek_id", $cekId);
 $o = $sqlcmdArchiveAndDeleteOldCekValue.Parameters.Add($CekIdParam)
 $oldCmkIdParam = New-Object System.Data.SqlClient.SqlParameter( "@old_cmk_id", $oldCmkId);
 $o = $sqlcmdArchiveAndDeleteOldCekValue.Parameters.Add($oldCmkIdParam)
 $oldCekValueParam = New-Object System.Data.SqlClient.SqlParameter( "@encrypted_value", $oldCekValue);
 $o = $sqlcmdArchiveAndDeleteOldCekValue.Parameters.Add($oldCekValueParam)
 $oldCekEncryptionAlgorithmParam = New-Object System.Data.SqlClient.SqlParameter( "@encryption_algorithm_name", $oldCekEncryptionAlgorithm);
 $o = $sqlcmdArchiveAndDeleteOldCekValue.Parameters.Add($oldCekEncryptionAlgorithmParam)
 $cekNameParam = New-Object System.Data.SqlClient.SqlParameter( "@cek_name", $cekName);
 $o = $sqlcmdArchiveAndDeleteOldCekValue.Parameters.Add($cekNameParam)
 $oldCmkNameParam = New-Object System.Data.SqlClient.SqlParameter( "@old_cmk_name", $oldCmkName);
 $o = $sqlcmdArchiveAndDeleteOldCekValue.Parameters.Add($oldCmkNameParam)
 $o = $sqlcmdArchiveAndDeleteOldCekValue.ExecuteNonQuery()
 }
 }
 }
 $reader.Close()
 }
 finally
 {
 if( $sqlcon -ne $null )
 {
 $sqlcon.Close()
 }
 
 if( $sqlconInternal -ne $null )
 {
 $sqlconInternal.Close()
 }
 }
 }

AlwaysEncrypted_Cmk_rotation_v2.zip

Comments

Anonymous
January 06, 2016
Technologies used : azure SQL v12 with always encryption . asp.net applciaiton 4.6 to be deployed as azure web sites. My asp.net application works locally Dev area where I have the certificate in my local store. When I deploy top azure web application SAAS it fails, what additional configuration is needed for asp.net web apps. How do iI import the cert to azure. erro : Certificate with thumbprint ' not found in certificate store 'My' in certificate location 'CurrentUser'. Verify the certificate path in the column master key definition in the database is correct, and the certificate has been imported correctly into the certificate location/store. Parameter name: masterKeyPath Any pointer is appreciated. Regards Sunil Prabha
- Anonymous
  May 20, 2016
  Hello Sunil,Are you able to resolve the issue? I am having same issue "Certificate with thumbprint ' not found in certificate store 'My' in certificate location 'CurrentUser'.......".Regards,

Share via