“The SQL Guy” Post #19: Working with Encryption Keys in SQL Server

  • Article

Ever wonder how encryption keys are protected and managed in SQL Server?

 

Microsoft SQL Server makes use of a key hierarchy, which helps to protect keys that are to be used for encryption. This hierarchy can best be viewed as a series of layers, in which each layer encrypts the layer below it. This hierarchy provides a highly secure infrastructure for sensitive data.

 

Figure 1 illustrates the encryption key hierarchy:

 

clip_image001

Figure 1: SQL Server Encryption Key Hierarchy

At the core of the encryption hierarchy is data that needs to be encrypted. The arrows in the diagram illustrate the most common encryption configurations used to encrypt data. Data is first either encrypted with a symmetric key or a password. In symmetric key cryptography, the same key is used for encryption and decryption of the data. Symmetric key encryption is faster than asymmetric key cryptography or certificate base encryption. After encrypting data using a symmetric key, the symmetric key itself cannot be left un-encrypted and it is encrypted with another symmetric key or an asymmetric key. An asymmetric key consists of a key-pair – public key and private key. The private key is protected by a password or database master key (which is a symmetric key). There are 2 copies of the database master key in SQL Server, one protected with a password and another protected with the Service Master Key (SMK). The service master key resides at the root of the encryption hierarchy and is protected using Windows Data Protection API (DPAPI).

 

In summary, keep in mind the following concepts:

(1) For best performance, always encrypt data using symmetric keys instead of certificates or asymmetric keys.

(2) There are 2 copies of the Database Master Key (DMK) – protected with a password and the Service Master Key (SMK).

(3) The Service Master Key (SMK) is created by SQL Server setup and is encrypted using Windows Data Protection API. (DPAPI).

(4) The Extensible Key Management (EKM) module holds symmetric or asymmetric keys outside SQL Server.

(5) SMK and DMK are symmetric keys.

(6) Always use strong passwords for encryption.

(7) Always use stronger encryption algorithms.

 

QUERY SYMMETRIC KEYS

SELECT * FROM SYS.SYMMETRIC_KEYS;

GO

 

CREATING A SYMMETRIC KEY

CREATE SYMMETRIC KEY MySymKey

WITH ALGORITHM = AES_256

ENCRYPTION BY PASSWORD = '1Str0ngPassword’;

GO

 

QUERY CERTIFICATES

SELECT * FROM SYS. CERTIFICATES;

GO

 

CREATING A CERTIFICATE ENCRPTED WITH A PASSWORD

CREATE CERTIFICATE MySelfSignedCert

ENCRYPTION BY PASSWORD = ‘1Str0ngPassword'

WITH SUBJECT = 'Self Signed Certificate By Damir',

EXPIRY_DATE = '07/14/2016';

GO

 

CREATE DATABASE MASTER KEY IN TEST DATABASE

Use Test;

GO

 

CREATE MASTER KEY ENCRYPTION BY PASSWORD =

'1Str0ngPassword';

GO

 

QUERY ENCRYPTIONS OF SYMMETRIC KEYS

SELECT * FROM SYS. KEY_ENCRYPTIONS;

GO

 

QUERY ENCRYPTIONS OF SYMMETRIC KEYS

SELECT * FROM SYS. KEY_ENCRYPTIONS;

GO

 

QUERY CERTIFICATES

SELECT * FROM SYS. CERTIFICATES;

GO

 

CREATING A CERTIFICATE ENCRYPTED WITH A PASSWORD

CREATE CERTIFICATE MySelfSignedCert

ENCRYPTION BY PASSWORD = ‘1Str0ngPassword'

WITH SUBJECT = 'Self Signed Certificate By Damir',

EXPIRY_DATE = '07/14/2016';

GO

 

CREATE DATABASE MASTER KEY IN TEST DATABASE

Use Test;

GO

 

CREATE MASTER KEY ENCRYPTION BY PASSWORD =

'1Str0ngPassword';

GO

 

QUERY ENCRYPTIONS OF SYMMETRIC KEYS

SELECT * FROM SYS. KEY_ENCRYPTIONS;

GO

 

DamirB-BlogSignature