Exploring SQL Server 2016 Always Encrypted – Part 4 – Encrypting Existing Data

As with most new technology it is not only intended for new development, but it is also targeted at fixing existing issues with current applications.  This is also true with the new SQL Server Always Encrypted feature.  In my “Exploring SQL Server 2016 Always Encrypted” series so far I’ve only shown how to create new Always Encrypted data using .Net code that performs an INSERT statement.  In this article I will be exploring the different ways that you can encrypt your existing confidential data using Always Encrypted Columns.

Sample Data

In order to show you how to convert an existing table that doesn’t contain Always Encrypted columns, to a table that does contain some Always Encrypted columns I will use two different machines.  One machine will be named SERVER1, and the other machine will be named CLIENT1.  The first machine, named SERVER1, is the machine that contains the actual data that needs to be encrypted. The other machine I will be using is a client machine named CLIENT1.  On CLIENT1, I’ve install just SQL Server Management Studio (SSMS) for SQL Server 2016.  I will use the CLIENT1 machine to generate the encryption keys and perform the actual encryption since it will be the only machine that has access to the column master key (CMK).

In order to show you how to encrypt existing data I will first create and populate a table that contains unencrypted data.  I will do that by running the code below on my SQL Server 2016 instance, which resides on my machine SERVER1:

CREATE DATABASE Demo_Encrypt_Existing;
GO
USE Demo_Encrypt_Existing;
GO
CREATE TABLE MyConfidentialData (
   ConfidentialId int,
   FirstName nvarchar(45),
   LastName nvarchar(45),
   SSN nvarchar(11),
   DriverLicenseNumber nvarchar(20)
);
GO
INSERT INTO MyConfidentialData VALUES
   (1,'Marty','Doe','555-66-7777','DOEJ1028AZ0123'),
   (2,'Sue','Jones','123-55-7890','JONS1121XB4567'),  
   (3,'David','Smith','984-98-9843','SMID0429QA8909'),
   (4,'Randy','Johnson','251-87-9736','JOHR0714PR8765'),
   (5,'Doris','Hoffman','666-01-1235','HOFD1202TB4321');
GO
 

By reviewing this code you can see that I first create a database named Demo_Encrypted_Existing and then created and populated a table named MyConfidentialData with five different records that contain clear text data for my two confidential columns SSN and DriverLicenseNumber.

Additionally I need to create a SQL Server authenticated user named AppAdmin on Server1.  This will be the user that is used by the CLIENT1 machine to perform some of the setup work and actual encryption of the existing data.   To create this user I run the following scripts:

USE [master]
GO
CREATE LOGIN [AppAdmin] WITH PASSWORD='AppAdm1n', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [AppAdmin]
GO

Now that our test data and our Server1 instance is set up correctly I can continue and show you different examples of how to encrypt existing data with Always Encrypted.

Encrypting Existing SQL Server Data Using .Net Code

In this example I have developing some .Net code that reads the unencrypted data from MyConfidentialData table, and then will write it to temporary table.  While writing to the temporary table the SSN and DriverLicenseNumber columns will be encrypted.   Once the data is encrypted I will rename the original table MyConfidentialData to MyConfidentialDataOriginal, and then rename my temporary table that contains the newly encrypted data to MyConfidentialData.

Prior to creating my temporary table that will hold the encrypted data, I need set up the CMK and CEK in my Demo_Encrypting_Existing database on SERVER1.  To do that I go through the following steps.

Step 1: Create Certificate Part4_Demo1 on CLIENT1

To create the certificate I run the following command at a command prompt on my CLIENT1 machine:

makecert.exe -n "CN=Part4_Demo1" -pe -sr
LocalMachine -r -eku 1.3.6.1.5.5.8.2.2,1.3.6.1.4.1.311.10.3.11 -ss my -sky
exchange -sp "Microsoft Strong Cryptographic Provider" -sy 1 -len
2048 -a sha256

This command creates a certificate named “Part4_Demo1” on my CLIENT1 machine.   Remember, I only want my certificate to be available to my .Net application.  Since my .Net application will be running on CLIENT1, is why I create my certificate on this machine.  

Step 2: Create Column Master Key (CMK) on SERVER1

Once my certificate to encrypt and decrypt my confidential data has been set up on my CLIENT1, I need to create a CMK on SERVER1 that points to the cert I create on CLIENT1.  I create the CMK by running the following TSQL script on SERVER1:

USE [Demo_Encrypt_Existing]
CREATE COLUMN MASTER KEY [Part4_Demo1]
WITH
(
       KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
       KEY_PATH = N'LocalMachine/My/558DA1820E61EF0C5CAF9ED04306B49B8FDB932B'
)
 

I generated this script using SSMS running on my CLIENT1 machine.  To create this script I used the “New Column Master Key” dialog under the “Always Encrypted Keys/Column Master Keys” item under the “Security” item in database Demo_Encrypt_Existing” database.   By running this command I am telling SERVER1 where the certificate is stored on CLIENT1 machine.

Step 3: Create Column Encryption Key (CEK) on SERVER1

Now that I have my CMK defined I need to define my CEK on SERVER1.  To do that I first need to generate the script below using the New Encryption Key Dialog in SSMS on my CLIENT1 machine.  When I run through that dialog I generate the following script:

USE [Demo_Encrypt_Existing]
CREATE COLUMN ENCRYPTION KEY [Part4_Demo1]
WITH VALUES
(
       COLUMN_MASTER_KEY = [Part4_Demo1],
       ALGORITHM = 'RSA_OAEP',
       ENCRYPTED_VALUE = 
0x01700000016C006F00630061006C006D0061006300680069006E0065002F006D0079002F0035003500380064006100310038003200300065003600310065006600300063003500630061006600
390065006400300034003300300036006200340039006200380066006400620039003300320062009B0D1BDDD4B8B612E2BE99F6181A8461E99FA9FE0E707BDDB0DE306BB79CA3909D8F908D2E93
110D364A3509DDC1B49D82509EBE437EA22AB20BDBEE60EEE7112E7D5B4B33E6FA23957C0F506D709333B46ABD250D572795D958670C7EDE072C931EC6A9481F9DF0889BB64512AD797B4458DCC1
1F17BCF3841619DDBA3EEE83BF21E08DF46E6F1B999E471B8E8B5247B30B803F5B79197F0283636E811B080467F37435FF80DEA16306DC1E364BEAE0140CC82161517A0975DC23678B2A41BDBB67
5B77A9E690DFDB2DB4BB3AA1921AA0F910ACA4CA401D1ED2F0799D2DEBF3A4AEFE9744CA84F33B17A265EF7CACFA48D6E5304C8833F512069727B2D97E4B5DD196EC6CD199A099B99B304050E7BC
9F161FD94133E60FBED38AD7011C02D7D8D2B0271A75A468D68360E5E9A0846ACB64FA11DF3753FDE557B06E4A0026CFEC6685C81818E397F4BE0765BC7BB158EA9673EDF46F9EC9FC0E58115210
ADCF2FD171E8CA847C4BF39141D20984D34963329FBB0862EF54215D3E21B0427F67299DF4C01C2799C47850AB331450C5FF00EBED7E4F16C44F7EE4B7F7E663E040BFAE6C7865AE45AE88946C4B
4C6835A5AF8B28FEE4FCBD5866165CBA0211BA45F7DE8EC9E48EAEECC5A7B2D7B49F7A5C74130F82E678CFC339445B52E45A9BE5F4B2EE718781A27345C1C6920CEB3862DD41C7138363652C2257
6DD9F8F2FA81
)
 

Once I have this script I then run in on SERVER1 to define the CEK.

By performing these 3 steps I have my CMK and CEK all set up and ready for me to create a table that contains Always Encrypted Columns on SERVER1.  To create that table I execute the following CREATE table statement:

USE [Demo_Encrypt_Existing]
GO
CREATE TABLE [dbo].[MyConfidentialDataTemp](
       [ConfidentialId] [int] ,
       [FirstName] [nvarchar](45),
       [LastName] [nvarchar](45),
       [SSN] [nvarchar](11) COLLATE Latin1_General_BIN2 ENCRYPTED WITH 
    (
       ENCRYPTION_TYPE = Deterministic, 
       ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
       COLUMN_ENCRYPTION_KEY = Part4_Demo1
    ),
       [DriverLicenseNumber] [nvarchar](20) COLLATE Latin1_General_BIN2 ENCRYPTED WITH 
    (
       ENCRYPTION_TYPE = RANDOMIZED, 
       ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
       COLUMN_ENCRYPTION_KEY = Part4_Demo1
    )
) ON [PRIMARY]

Now I’m ready to execute my .Net code to encrypt my data.  My .Net code reads the unencrypted data in MyConfidentialData table and writes to a temporary table named MyConfidentialDataTemp.  You can find my C Sharp code that migrates my data in the section labeled “Code to Encrypt Data in MyConfidentialdata table” at the end of this article.    

Once I have executed this .Net code I can validate that the data in table MyConfidentialDataTemp table got encrypted.  I can perform that verification by running the following SELECT statement:

SELECT TOP 1 SSN, DriverLicenseNumber FROM MyConfidentialDataTemp;

When I run this code I get the following output:

SSN                                                                                                                                                                  
-------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
0x015745EF4B66A8B95C565D775CC01D7CAC0FAE10CBEE43DA05142A1AB1553995F96C72D4A3B1583AD4E3FC88D4CF862FD9E33B5EE7D6BC6FD2D8919A38BAF097E7003A22BA0259F2BA08FAD20525EC790F 
DriverLicenseNumber
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
0x01C2084F57898B773140FDBBCC969DDBE45989D78A55804A87DB33075A322802B69BA5A6AF0D6272EBE212C834F717262607A2A3ADB9F36BDE7842E5D81895267EA3EEB071D16D6C0A360A346175BFD1DB

Note: Output reformatted for ease of reading

Here you can see my temporary table does contain encrypted data. The last thing I need to do is to swap my table names around by running the following TSQL Code:

sp_rename MyConfidentialData, MyConfidentialDataOriginal;
sp_rename MyConfidentialDataTemp, MyConfidentialData;

As you can see it isn’t all that difficult to encrypt existing data using this method. 

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles