Decoder Ring Not Needed

Like "My Top Ten+ Favorite Replication Tricks", this post took a long time for me to get it written and it started as a SQL Saturday presentation. I gave this one a couple times in 2018 and 2019, slides were posted here for one of the SQL Saturday presentations.

Why do we need to encrypt our data?

In this era of GDPR and similar laws there is very real need to protect you data. It should have always been protected, there is just additional incentive now. Here are a few of the biggest reasons to encrypt.

• Regulatory Requirements
• Good Data Stewards
• Due Diligence
• Job Security
• Avoid Lawsuits
• Stay Out Of Jail

Which type of encryption best fit our needs?

Encryption is not a one size fits all. Below is a quick list of the strengths and weaknesses of each encryption type. If needed, you can use more than one encryption type in a database.

Transparent Data Encryption (TDE)

Pros Protect against loss, or theft, of data files or backups No changes to applications required Fairly easy to implement

Cons In flight data not protected Doesn't make sensitive data unreadable to users with select permissions Additional considerations for backups and disaster recovery Could have an impact on performance

Column Level Encryption

Pros Selective encryption can have less impact on performance Tighter access control to sensitive data Many options for implementation

Cons In flight data not protected Can have lot of pieces (keys) to manage Likely to require changes to existing application code

Always Encrypted

Pros In flight data is protected Easy to implement Selective encryption can have less impact on performance Tighter access control to sensitive data Can enable for parameterization of queries

Cons There are some limitations which you can read about by following this link.

A quick look a each encryption architecture

Transparent Data Encryption (TDE)

The encryption architecture starts with the Windows Data Protection API/Local Machine Key. This key encrypts the SQL Server Service Master Key which is generated when it is needed to encrypt a linked server password, credential or database master key. Next is the Database Master Key which is then used to create a certificate that is used to create the Database Encryption Key. This is used to encrypt the database.

Column Level Encryption

Just like with TDE, Column Level Encryption starts with the Windows Data Protection/Local Machine Key and the Service Master Key. Once the Database Master Key has been created it is then used to create a certificate which is then used to create a Symmetric Key which is used to encrypt the data. Symmetric Keys can also be encrypted by the Database Master Key directly.

Always Encrypted

Always Encrypted is quite different. The certificate used to generate Column Master Keys and Column Encryption Keys need not be present on the SQL Server. The encryption work can be done remotely from a client server that has the certificate installed. Once the data is encrypted the encryption/decryption is done at the client server by the client driver (such as the .NET framework).

Always Encrypted Client Development

Setting up each encryption method

The scripts below are my demo scripts. As always, if you use these scripts be sure to change database names, file paths, file names, passwords and anything else that needs adjusting to fit your environment.

Transparent Data Encryption (TDE)

USE master
GO

-- View the Service Master Key
SELECT * FROM master.sys.symmetric_keys
WHERE name = '##MS_ServiceMasterKey##'

-- Backup the Service Master Key naming the file appropriately
BACKUP SERVICE MASTER KEY TO FILE = 'C:\KEY_BACKUPS\SMK_KeyBackup.key'
ENCRYPTION BY PASSWORD = '$MK_P@$$w0rd';

-- Use the block comment section to Restore Service Master Key
/*
RESTORE SERVICE MASTER KEY FROM FILE = 'C:\KEY_BACKUPS\SMK_KeyBackup.key'
-- Change this path to where the file is located
DECRYPTION BY PASSWORD = '$MK_P@$$w0rd'
-- Use password supplied when making the backup
-- FORCE option will cause data loss
*/

-- Create the DMK in master database that will protect the certificate
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '@V3ry$tr0ngP@$$w0rd';
GO
SELECT * FROM sys.symmetric_keys


-- Backup the DMK
BACKUP MASTER KEY TO FILE = 'C:\KEY_BACKUPS\MasterDB_DMK.dmk'
ENCRYPTION BY PASSWORD = 'M@$t3r_DMK_P@$$w0rd';

-- Create the certificate in master database used to create the Database Encryption Key for TDE
CREATE CERTIFICATE MyTDECertificate WITH SUBJECT = 'My Certificate For TDE';
GO


-- Backup the certificate using a private key
BACKUP CERTIFICATE MyTDECertificate
TO FILE = 'C:\KEY_BACKUPS\TDECertificateBackup.cer'
WITH PRIVATE KEY
(
FILE = 'C:\KEY_BACKUPS\TDECertificatePrivateKeyFile.pvk',
ENCRYPTION BY PASSWORD = '@n0th3r$Tr0ngP@$$W0rd'
);
GO

SELECT * FROM sys.certificates

USE NORTHWND
GO

-- Create the DEK in the user database
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyTDECertificate;
GO

-- Turn on TDE
ALTER DATABASE NORTHWND
SET ENCRYPTION ON;
GO

-- Check if encrypted
SELECT name FROM sys.databases WHERE is_encrypted = 1

SELECT * FROM sys.dm_database_encryption_keys;
-- State: 1 = unencrypted, 2 = in-process, 3 = encrypted

-- Lets back it up and try restoring on a different instance/server

BACKUP DATABASE NORTHWND TO DISK = 'C:\SQL_BACKUPS\NORTHWND.bak';


-- Change connection to a different instance/server and try to restore
-- This will fail since the master key and certificate are not present

RESTORE DATABASE [NORTHWND] FROM DISK = N'C:\SQL_BACKUPS\NORTHWND.bak' WITH
MOVE N'NORTHWND' TO N'C:\SQL2\NORTHWND.MDF',
MOVE N'NORTHWND_log' TO N'C:\SQL2\NORTHWND_log.ldf', STATS = 5;

-- To restore the certificate a different instance/server, copy the files and run this script
-- Once this is done you should be able to restore the database

USE master
GO

-- Change paths to where the file is located
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '@V3ry$tr0ngP@$$w0rd';
GO

CREATE CERTIFICATE MyTDECertificate FROM FILE = 'C:\KEY_BACKUPS\TDECertificateBackup.cer'
WITH PRIVATE KEY
(
FILE = 'C:\KEY_BACKUPS\TDECertificatePrivateKeyFile.pvk',
DECRYPTION BY PASSWORD = '@n0th3r$Tr0ngP@$$W0rd'
-- Use password supplied when making the backup
);

USE master
GO

-- Check if encrypted
SELECT name FROM sys.databases WHERE is_encrypted = 1

SELECT * FROM sys.dm_database_encryption_keys;
-- State: 1 = unencrypted, 2 = in-process, 3 = encrypted

Column Level Encryption

There were two scripts for this demo. The first script goes through the process of creating the symmetric keys. The second script, further down the page, goes over creating functions and stored procedures to more easily encrypt and decrypt the data.

In the first script you will see that you can create multiple passwords for the symmetric key, this would allow you able to give different passwords for the same key to control access. Likewise, you can also create multiple keys and encrypt different data with different keys to control access. However a more efficient method would be to create a stored procedure for data retrieval that automatically does the decryption, via a function, then restrict execute permissions on the stored procedure. If there are multiple columns of encrypted data and multiple groups that require access to some, but not all, of the columns you could create multiple stored procedures then only grant execute on the needed stored procedures to the groups our users that need them. For example, if COL1, COL3 and COL7 of a table are encrypted and the Personnel department needs the data in all the columns you could create a stored procedure for them that pulls back all the encrypted columns, but if Accounting only needs the data in COL3 you create a stored procedure for them that only pulls back COL3 decrypted.

-- Backup service master key if it hasn't been done already.

-- MAKE SURE YOU RUN IN THE RIGHT DATABASE CONTEXT!!!!!!!!!!!!!!!

-- Create Database Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'DMK_N0rthW!nd';

-- You should now see the database master key you created
SELECT * FROM sys.symmetric_keys

-- Backup Database Master Key
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'DMK_N0rthW!nd'
BACKUP MASTER KEY TO FILE = 'C:\KEY_BACKUPS\NORTHWND.key'
ENCRYPTION BY PASSWORD = 'DMK_N0rthW!nd'
GO
CLOSE MASTER KEY;

-- Use the block comment section to Restore Service Master Key
/*
RESTORE MASTER KEY FROM FILE = 'C:\KEY_BACKUPS\NORTHWND.key'
-- Change this path to where the file is located
DECRYPTION BY PASSWORD = 'DMK_N0rthW!nd'
-- Use password supplied when making the backup
ENCRYPTION BY PASSWORD = 'DMK_N0rthW!nd';
*/

-- Create Symmetric Key
-- There are multiple ways to do this

-- Protected by a password
CREATE SYMMETRIC KEY ExampleKey1
WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = '3x@mp73P@$$w0rd';

SELECT * FROM sys.symmetric_keys;

OPEN SYMMETRIC KEY ExampleKey1 DECRYPTION BY PASSWORD = '3x@mp73P@$$w0rd';

SELECT * FROM sys.openkeys;

CLOSE SYMMETRIC KEY ExampleKey1;

SELECT * FROM sys.openkeys;

-- Protected by multiple passwords
CREATE SYMMETRIC KEY ExampleKey2
WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = '3x@Mp731',
PASSWORD = '2nd3X@mpL3',
PASSWORD = '0n3m0r3PW';

SELECT * FROM sys.symmetric_keys;

OPEN SYMMETRIC KEY ExampleKey2 DECRYPTION BY PASSWORD = '3x@Mp731';
OPEN SYMMETRIC KEY ExampleKey2 DECRYPTION BY PASSWORD = '2nd3X@mpL3';
OPEN SYMMETRIC KEY ExampleKey2 DECRYPTION BY PASSWORD = '0n3m0r3PW';

SELECT * FROM sys.openkeys;

CLOSE ALL SYMMETRIC KEYS;

SELECT * FROM sys.openkeys;

-- We can drop one password and still use the remaining passwords
OPEN SYMMETRIC KEY ExampleKey2 DECRYPTION BY PASSWORD = '3x@Mp731';--'0n3m0r3PW';

ALTER SYMMETRIC KEY ExampleKey2 DROP ENCRYPTION BY PASSWORD = '3x@Mp731';

CLOSE SYMMETRIC KEY ExampleKey2;

SELECT * FROM sys.symmetric_keys; -- key is still there

-- OPEN SYMMETRIC KEY ExampleKey2 DECRYPTION BY PASSWORD = '3x@Mp731';
OPEN SYMMETRIC KEY ExampleKey2 DECRYPTION BY PASSWORD = '2nd3X@mpL3';
OPEN SYMMETRIC KEY ExampleKey2 DECRYPTION BY PASSWORD = '0n3m0r3PW';

SELECT * FROM sys.openkeys;

CLOSE ALL SYMMETRIC KEYS;

-- We can add additional passwords
OPEN SYMMETRIC KEY ExampleKey2 DECRYPTION BY PASSWORD = '0n3m0r3PW';

ALTER SYMMETRIC KEY ExampleKey2 ADD ENCRYPTION BY PASSWORD = 'N3wP@$$w0rd';

CLOSE SYMMETRIC KEY ExampleKey2;

OPEN SYMMETRIC KEY ExampleKey2 DECRYPTION BY PASSWORD = 'N3wP@$$w0rd';
OPEN SYMMETRIC KEY ExampleKey2 DECRYPTION BY PASSWORD = '2nd3X@mpL3';
OPEN SYMMETRIC KEY ExampleKey2 DECRYPTION BY PASSWORD = '0n3m0r3PW';

SELECT * FROM sys.openkeys;

CLOSE ALL SYMMETRIC KEYS;

/*
NOTE: Symmetric keys cannot be backed up.
If you need to regenerate a symmetric key or move the key to a new server to
share data you will want to include a KEY_SOURCE and IDENTITY_VALUE
in the create statement. This can be done with any of the methods used
to create keys. You will want more complex phrases than I am using in this example.
*/

-- Protected by certificate

-- Create certificate used to encrypt the symmetric keys
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'DMK_N0rthW!nd'
CREATE CERTIFICATE NorthwindEncryptionCertificate WITH SUBJECT = 'Protect Symmetric Keys';
CLOSE MASTER KEY;

-- You should see the certificate you just created
SELECT * FROM SYS.certificates

-- Backup the certificate using a private key
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'DMK_N0rthW!nd'
BACKUP CERTIFICATE NorthwindEncryptionCertificate
TO FILE = 'C:\KEY_BACKUPS\NORTHWND.cer'
WITH PRIVATE KEY
(
FILE = 'C:\KEY_BACKUPS\NORTHWND.pvk',
ENCRYPTION BY PASSWORD = N'PVK_N0rthW!nd'
);
CLOSE MASTER KEY;

--Restore a dropped Certificate by recreating it if it was dropped
/*
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'DMK_N0rthW!nd'
CREATE CERTIFICATE NorthwindEncryptionCertificate
FROM FILE = 'C:\KEY_BACKUPS\NORTHWND.cer'
WITH PRIVATE KEY
(
FILE = 'C:\KEY_BACKUPS\NORTHWND.pvk',
DECRYPTION BY PASSWORD = N'PVK_N0rthW!nd'
);
CLOSE MASTER KEY;
*/

CREATE SYMMETRIC KEY ExampleKey3
WITH ALGORITHM = AES_256,
KEY_SOURCE = 'NORTHWND',
IDENTITY_VALUE = 'Certificate for Encryption'
ENCRYPTION BY CERTIFICATE NorthwindEncryptionCertificate;

SELECT * FROM sys.symmetric_keys;

-- Can create multiple symmetric keys to better control access to encrypted data

-- Great! But how do we use these?

We will now look at a method of creating functions to handle the encryption and decryption of the data as well as stored procedures to use the functions to handle inserting and selecting the encrypted data. The script starts with creating a very simple sample table then setting up the symmetric keys. It then goes on to have some steps to test directly querying the table. Finally it goes on to create stored procedures to open and close the keys, functions to encrypt and decrypt the data and stored procedures to use the other procedures and functions to easily insert and retrieve data. Note, I would recommend encrypting the stored procedures that open and close the keys as well as the functions that do the encryption and decryption.

USE NORTHWND
GO

-- Using queries to encrypt and decrypt data

CREATE TABLE dbo.CRYPTO (
RID INT IDENTITY PRIMARY KEY,
SecretData VARBINARY(MAX)
)
GO

OPEN SYMMETRIC KEY ExampleKey1 DECRYPTION BY PASSWORD = '3x@mp73P@$$w0rd';
INSERT INTO dbo.CRYPTO (SecretData)
VALUES
(CONVERT(VARBINARY(MAX),(ENCRYPTBYKEY(KEY_GUID('ExampleKey1'), 'Lorem ipsum dolor sit amet.')))),
(CONVERT(VARBINARY(MAX),(ENCRYPTBYKEY(KEY_GUID('ExampleKey1'), 'Nam fermentum quam eu mattis venenatis')))),
(CONVERT(VARBINARY(MAX),(ENCRYPTBYKEY(KEY_GUID('ExampleKey1'), 'Sed dignissim nulla vitae ipsum vehicula ultricies.'))));
CLOSE SYMMETRIC KEY ExampleKey1;


-- What happens if we just query the table?
SELECT * FROM dbo.CRYPTO;

-- Let's open the key then try again
OPEN SYMMETRIC KEY ExampleKey1 DECRYPTION BY PASSWORD = '3x@mp73P@$$w0rd';
SELECT * FROM dbo.CRYPTO;

SELECT * FROM sys.openkeys;

-- One more try...remember the key is still open
SELECT RID, CONVERT(VARCHAR(MAX),DECRYPTBYKEY(SecretData)) FROM dbo.CRYPTO
CLOSE SYMMETRIC KEY ExampleKey1;


-- What if we try a different key?
OPEN SYMMETRIC KEY ExampleKey2 DECRYPTION BY PASSWORD = 'N3wP@$$w0rd';
SELECT RID, CONVERT(VARCHAR(MAX),DECRYPTBYKEY(SecretData)) FROM dbo.CRYPTO
CLOSE SYMMETRIC KEY ExampleKey2;

-- Did we leave any keys open?
SELECT * FROM sys.openkeys;

-- What if we don't want to be constantly writing open/close key statements and have passwords everywhere?

CREATE TABLE dbo.CIPHER (
RID INT IDENTITY PRIMARY KEY,
ProtectedData VARBINARY(MAX)
)
GO

-- Create Open Key Stored Procedure

CREATE PROCEDURE dbo.USP_CIPHER_OpenKey --WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON;

OPEN SYMMETRIC KEY ExampleKey3
DECRYPTION BY CERTIFICATE NorthwindEncryptionCertificate;

RETURN 0;
END

GO

-- Create Close Key Stored Procedure

CREATE PROCEDURE dbo.USP_CIPHER_CloseKey --WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON;

CLOSE SYMMETRIC KEY ExampleKey3;

RETURN 0;
END

GO

-- Create Encryption Function

CREATE FUNCTION dbo.FN_CIPHER_Encrypt --WITH ENCRYPTION
(
@ValueToEncrypt VARCHAR(MAX)
)
RETURNS VARBINARY(MAX) WITH ENCRYPTION
AS
BEGIN
-- Declare the return variable here
DECLARE @Result VARBINARY(MAX)

SET @Result = ENCRYPTBYKEY(KEY_GUID('ExampleKey3'), @ValueToEncrypt)

-- Return the result of the function
RETURN @Result
END

GO

-- Create Decryption Function

CREATE FUNCTION dbo.FN_CIPHER_Decrypt --WITH ENCRYPTION
(
@ValueToDecrypt VARBINARY(MAX)
)
RETURNS Varchar(MAX) WITH ENCRYPTION
AS
BEGIN
-- Declare the return variable here
DECLARE @Result VARCHAR(MAX)

SET @Result = CONVERT(VARCHAR(MAX),DECRYPTBYKEY(@ValueToDecrypt))

-- Return the result of the function
RETURN @Result;
END

GO

-- Create Insert Stored Procedure

CREATE PROCEDURE dbo.USP_AddProtectedData_INS
@NewData NVARCHAR(MAX)

AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

EXEC [dbo].[USP_CIPHER_OpenKey]

INSERT INTO dbo.CIPHER (ProtectedData)
VALUES
(dbo.FN_CIPHER_Encrypt(@NewData));

EXEC [dbo].[USP_CIPHER_CloseKey]

END

-- Create Select Stored Procedure

CREATE PROCEDURE dbo.USP_GetProtectedData_SEL

AS

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

EXEC dbo.USP_CIPHER_OpenKey

SELECT dbo.FN_CIPHER_Decrypt(ProtectedData) AS ProtectedData FROM dbo.CIPHER

EXEC dbo.USP_CIPHER_CloseKey


-- Using our new objects
EXEC dbo.USP_AddProtectedData_INS @NewData = N'Quisque in diam eu orci semper fermentum.';

-- Is it there?
SELECT * FROM dbo.CIPHER

-- Now get it decrypted
EXEC dbo.USP_GetProtectedData_SEL;

-- Did we leave any keys open?
SELECT * FROM sys.openkeys;

Always Encrypted

The first set of images will show the quickest/easiest way to get Always Encrypted set up. It is to let SQL Server auto-generate the keys and certificate. Further down we will look at certificates then setting up Always Encrypted using a certificate that is not generated by SQL Server. Where I work we did not want to use a SQL Server self-signed certificate but rather one signed by our certificate authority. If you do use the SQL Server generated certificate and need to access data from other servers you would need to export the certificate created by SQL Server and import it on the other servers.

You get started by right-clicking on the table in which you want to encrypt data and selecting the Encrypt Columns option.

The first screen is the introduction that briefly explains Always Encrypted. You can check the box to not show it in the future.

Next you select the columns in the table that you need to encrypt. My sample table is overly simple with only one column. The thing to notice here is the Encryption Key is automatically generated. You will also need to choose the type of encryption. If the column contains unique data, like social security or credit card numbers, you would probably want Randomized. If it is something that may be used in a filter, like a birth date, you would want to select Deterministic so it can be used with parameterized queries.

You will note that the master key will be auto generated.

If you want you can generate a PowerShell script or click next to proceed to the finish.

Double check your choices on the summary page and if everything is correct click Finish.

Next we will look at how to view certificates on your server and make sure the users have the needed permissions to use an external, not generated by SQL Server, certificate.

The first step is to run the Microsoft Management Console (MMC). I do this by typing MMC into the search box and clicking on the icon when it pops up.

You will notice that by default the console is empty. We need to add the certificate snap-in.

Go to File --> Add/Remove Snap-ins.

Add the certificates snap-in.

For my environment we want to manage certificates for the computer account. Your environment may be different.

Select Local computer unless you are doing this from a remote computer.

Click OK to add the snap-in.

The certificate for my server can be found under Personal --> Certificates. This certificate will need to be on all servers that will be querying encrypted data. It does not have to be on the SQL Server host itself if you do the encryption work remotely from a server that has the certificate installed.

Right-click on the certificate choose All Tasks --> Manage Private Keys.

Make sure the account you will be using to do the encryption has read permissions to the certificate private key. You would also add any application or service account here that will need to access the certificate to do decryption. If the certificate is on multiple machines only the accounts used on the specific machine need the permissions. So different servers may have different accounts with read permissions to the certificate private keys.

Now that the certificate work is done, we can use it to encrypt the data.

Rather than go directly to Encrypt Columns we will first create the Column Master Key (CMK).

Pick the Windows Certificate Store for the Local Machine since that is where our certificate was stored.