Exam 70-764 Study Notes – Configure Encryption

If you are wanting to share resources and knowledge with others please join our closed study group on Facebook.

The Configure Data Access and Auditing part of the SQL Server 70-764 exam involves 3 main areas and is 20-25% of the exam content.

  • Configure encryption
  • Configure data access and permissions
  • Configure auditing

The book I purchased in order to study for the exam is covers it fairly well (link to the book is right at the bottom of this article) but I also wanted to get hands-on and create my own examples.

You can download Developer Edition of SQL 2017 here.

Configure encryption

Implement cell-level encryption

This is also known as column-level encryption. It has been around for a while now (since 2005).

This type of encryption is known as “encrypting data at rest”.

There are two examples that Microsoft gives:

One using simple symmetric encryption. The other uses symmetric encryption that includes an authenticator. (The examples were actually around the wrong way in the above-linked article but nevertheless, the concepts are there.)

An Authenticator is an extra bit of data that is encrypted along with the main piece of data that is to be decrypted. When you go to decrypt the data the authenticator must also be specified to return the data in the correct unencrypted manner.

Note: you must have a database master key before doing this.

All our examples require a database called DBA set up on your local instance of SQL.

Example of simple symmetric encryption:

----------------

CREATE MASTER KEY ENCRYPTION BY   
PASSWORD = 'xyx123xtimeforsqltoburn';


--Create the tables and columns used
USE DBA;  
GO  

CREATE TABLE CC_Info (id INT, FullName VARCHAR(100), CreditCard VARCHAR(24) , Encrypted_CC VARBINARY (128));

INSERT INTO CC_Info
VALUES ( 1,'Rob McGeorge','1234-5678-8765-4321',null )
		,(2,'Don Perignon','5678-1234-4321-8765',null )

-- Check the data has loaded
SELECT * FROM CC_Info

--Create Certificate

CREATE CERTIFICATE CC2018  
   WITH SUBJECT = 'Cert Credit Card Nos';  
GO  

-- Create the Key
CREATE SYMMETRIC KEY CC_Key01  
    WITH ALGORITHM = AES_256  
    ENCRYPTION BY CERTIFICATE CC2018;  
GO  

-- Open the symmetric key with which to encrypt the data.  
OPEN SYMMETRIC KEY CC_Key01  
   DECRYPTION BY CERTIFICATE CC2018;  

-- Encrypt the value in column Encrypted_CC using the  
-- Symmetric key CC_Key01.  
-- Save the result in column Encrypted_CC.    
UPDATE CC_Info 
SET Encrypted_CC = EncryptByKey(Key_GUID('CC_Key01')  
    , CreditCard)
	 
GO  

--now let us see if we can Decrypt the data
		
--DELETE CC_Info

-- Verify the encryption.  
-- First, open the symmetric key with which to decrypt the data.  

OPEN SYMMETRIC KEY CC_Key01  
   DECRYPTION BY CERTIFICATE CC2018;  

-- Now list the table with the decrypted card number at the end. 

SELECT Encrypted_CC, CONVERT(VARCHAR(128),DECRYPTBYKEY(Encrypted_CC))  AS 'Decrypted Now'
     FROM CC_Info;  
GO  
 
 ---------------------

Example of simple symmetric encryption with Authenticator:

Now we will do a similar example as before but use an authenticator. This adds an increased amount of complexity to the encryption technique.

This is very similar to the last example except we add an additional column which is the authenticator that we will use to encrypt, and decrypt the card number.

----------------

CREATE MASTER KEY ENCRYPTION BY   
PASSWORD = 'xyx123xtimeforsqltoburn';



--Create the tables and columns used
USE DBA;  
GO  

CREATE TABLE CC_Info (id INT, FullName VARCHAR(100), CreditCard VARCHAR(24) , Authenticator CHAR(3), Encrypted_CC VARBINARY (128));

INSERT INTO CC_Info
VALUES ( 1,'Rob McGeorge','1234-5678-8765-4321','345',null )
		,(2,'Don Perignon','5678-1234-4321-8765','647',null )

-- Check the data has loaded
SELECT * FROM CC_Info

--Create Certificate

CREATE CERTIFICATE CC2018A  
   WITH SUBJECT = 'Cert Credit Card Nos Auth';  
GO  

-- Create the Key
CREATE SYMMETRIC KEY CC_Key01A  
    WITH ALGORITHM = AES_256  
    ENCRYPTION BY CERTIFICATE CC2018A;  
GO  

-- Open the symmetric key with which to encrypt the data.  
OPEN SYMMETRIC KEY CC_Key01A  
   DECRYPTION BY CERTIFICATE CC2018A;  

-- Encrypt the value in column Encrypted_CC using the  
-- Symmetric key CC_Key01.  
-- Save the result in column Encrypted_CC.    
UPDATE CC_Info 
SET Encrypted_CC = EncryptByKey(Key_GUID('CC_Key01A')  
    , CreditCard, 1, Authenticator );
	 
GO  

--now let us see if we can Decrypt the data
		
--DELETE CC_Info

-- Verify the encryption.  
-- First, open the symmetric key with which to decrypt the data.  

OPEN SYMMETRIC KEY CC_Key01A  
   DECRYPTION BY CERTIFICATE CC2018A;  

-- Now list the table with the decrypted card number at the end. 

SELECT Encrypted_CC, CONVERT(VARCHAR(128),DECRYPTBYKEY(Encrypted_CC, 1,Authenticator))  AS 'Decrypted Now'
     FROM CC_Info;  
GO  
 
 --try without the authenticator (returns NULL)
 SELECT Encrypted_CC, CONVERT(VARCHAR(128),DECRYPTBYKEY(Encrypted_CC))  AS 'Decrypted Now'
     FROM CC_Info;  
GO  
 

 ---------------------
 

Implement Always Encrypted

Another area you should study up on for the exam is implementing Always Encrypted.

This type of encryption is available from SQL 2016 (Enterprise and developer versions) and after the release of 2016 SP1, in all editions.

This type of encryption not only protects data at rest but also data in transit.

You could use this type of encryption if you wanted to protect the data against DBA’s or other IT Admin staff.

There is a lot of the work done on the application side in this scenario and the app must use an Always Encrypted-enabled driver to be able to intercept and encrypt/decrypt the data.

Let us use a similar example from before to show how this can be implemented.

This will set up the table for which we will add the column encryption to the yearly salary.

-------------------------------------
-- if you haven't already got one

CREATE MASTER KEY ENCRYPTION BY   
PASSWORD = 'xyx123xtimeforsqltoburn';



--Create the tables and columns used
USE DBA;  
GO  

CREATE TABLE Executive_Salaries (id INT, FullName VARCHAR(100), YearlySalary VARCHAR(24))

INSERT INTO Executive_Salaries
VALUES ( 1,'Rob McGeorge','2000500' )
		,(2,'Don Perignon','1800400')

-- Check the data has loaded
SELECT * FROM Executive_Salaries

-----------------------------------

Now right click on the table in SSMS and step through the encryption wizard.

You will need to select the column and choose a type of encryption.

Determinised – This will use a standard method for encrypting each value. This could make it easy to guess the value particularly if it was a boolean field or one with only a few possible values.

Random – Randomize the encryption meaning that it would be much more difficult to attempt to guess at the values.

You will need to save the key outside of the database. I just chose the certificate store on my laptop but note how you can also save in the Azure Key Vault.

Now if you query the table you will notice the data is encrypted.

To view it a client must connect and use the column master key to decrypt which is not stored in SQL Server.

To demonstrate open up a new SSMS Query window, connect to the SQL instance and select options.

Under Additional Connection Parameters enter the below.

Column Encryption Setting=Enabled

This will enable SSMS to use the Column Master Key stored in your users or computers certificate store.

Now do a select from the table and you will see the data has been decrypted.

-----------------------------------

SELECT * FROM Executive_Salaries

-----------------------------------

Ref: Microsoft Docs

Implement Backup Encryption

Starting with SQL 2014 Backup Encryption involves backing up a database using a certificate or asymmetric key to encrypt the data.

For the backup to be successfully restored the certificate or key must be available.

You must have a:

  • Database Master Key
  • Certificate

Backup Encryption Example:

-------------------------------------
-- if you haven't already got one create a master key
USE MASTER
CREATE MASTER KEY ENCRYPTION BY   
PASSWORD = 'xyx123xtimeforsqltoburn';

-- Create a certificate for the encrypted backup

USE Master  
GO  
CREATE CERTIFICATE BackupDBCert  
   WITH SUBJECT = 'DBA Backup Encryption Certificate';  
GO  


-- Encrypt the backup

BACKUP DATABASE [DBA]  
TO DISK = N'C:\Temp\EncryptedBackupDBA_DB.bak'  
WITH  
  COMPRESSION,  
  ENCRYPTION   
   (  
   ALGORITHM = AES_256,  
   SERVER CERTIFICATE = BackupDBCert  
   ),  
  STATS = 10  
GO  
-------------------------------------

How to restore an encrypted SQL server backup

Do show an example of this we will backup the certificate we just created.

-----------------------------------

BACKUP CERTIFICATE BackupDBCert

TO FILE = 'C:\Temp\BackupDBCert.cer'

WITH PRIVATE KEY

        (

                FILE = 'C:\Temp\Certkey.key'

                , ENCRYPTION BY PASSWORD = 'xyx123xtimefor3ncrypt'

        );

GO

-----------------------------------

Now we can either restore this on a different server. Or drop the certificate on the one we are currently working to simulate.

Dropping the Certificate (if required):

DROP CERTIFICATE BackupDBCert

If you try to open or restore the backup you will not be able to without the certficate.

To recreate the cert on the same or another server you will need to this:


CREATE CERTIFICATE BackupDBCert
FROM FILE = 'C:\Temp\BackupDBCert.cer'

WITH PRIVATE KEY 

        (

                FILE = 'C:\Temp\Certkey.key'

                , ENCRYPTION BY PASSWORD = 'xyx123xtimefor3ncrypt'

        );

Now you should be able to successfully restore the backup.

Ref: Microsoft Docs

Configure Transparent Data Encryption

Transparent Data Encryption, or TDE as it is known, is another method of “encrypting data at rest”

Came out in 2008. Prior to SQL 2016 you could not compress the backups if using TDE.

TDE applies to the entire database.

One of it’s great features is that you don’t need to change any applications to use it.

The way it works is by encrypting the pages as they are written to disk, and decrypting them as they are written to memory.

Here is a simple example of TDE in action:

-------------------------------------
-- if you haven't already got one create a master key
USE MASTER
CREATE MASTER KEY ENCRYPTION BY   
PASSWORD = 'xyx123xtimeforsqltoburn';

-- Create a certificate and then turn on encryption

CREATE CERTIFICATE TDECert WITH SUBJECT = 'My TDE Certificate';  
go  
USE DBA;  
GO  
CREATE DATABASE ENCRYPTION KEY  
WITH ALGORITHM = AES_128  
ENCRYPTION BY SERVER CERTIFICATE TDECert;  
GO  
ALTER DATABASE DBA  
SET ENCRYPTION ON;  
GO  

-------------------------------------

Seriously that is it. Your database is now encrypted if someone stole a backup or copied the underlying database files.

Remember if you plan on restoring the database onto another server you need to backup the certificate as well!

Ref: Microsoft TDE Doc

Configure encryption for connections

It is possible to encrypt the connections to and from a SQL Server.

SQL Server supports Secure Sockets Layer (SSL) and is compatible with Internet Protocol security (IPSec).

You will need:

  • A certificate provisioned on the database server
  • The client(s) need to be set up to trust the certificates root authority

This is set up using SQL Server Configuration Manager.

Ref: MS Libray Article How to: Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager) and Encrypting Connections to SQL Server.

Troubleshoot encryption errors

Encryption errors can be caused by a number of different issues.

Firstly look in the error logs (SQL and Windows) plus the main DMV tables related to encryption:

Here are a few of the main tables:

  • sys.key_encryptions
  • sys.dm_database_encryption_keys
  • sys.symmetric_keys
  • sys.column_master_keys

Here are some common areas of problems.

  • Certificate issues
  • Password problems
  • Key problems
  • Algorithm issues

Here are some good articles to have a read through and take further notes from:

How to troubleshoot SSL encryption issues in SQL Server
Recovering a SQL Server TDE Encrypted database Successfully

General Things to remember

** SQL Server supports AES_128, AES_192, and AES_256
** The stronger the encryption the slower it is too apply

Other Resources

If you are interested in purchasing a book for the exam check out Exam Ref 70-764 Administering a SQL Database Infrastructure by Victor Isakov:

It is not a large book and there are a couple of issues with it worth noting.

Does not come with online resources (you have to type in any examples yourself)
Some of the reviewers on Amazon found some typos and other errors. I haven’t myself but read a few reviews that mentioned that.

Despite those minor issues, I found it to be the best book available for this exam:

Exam Ref 70-764 Administering a SQL Database Infrastructure

If you are on Facebook come and join our Study Group here.

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.