Sharing of thoughts and information is what blogging is all about. This way we can learn from each other. Post A Comment!These postings are provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use.
Anthony Bartolo Twitter | LinkedIn
Pierre Roman Twitter | LinkedIn
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:
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;
CREATING A SYMMETRIC KEY
CREATE SYMMETRIC KEY MySymKey
WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = '1Str0ngPassword’;
SELECT * FROM SYS. CERTIFICATES;
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';
CREATE DATABASE MASTER KEY IN TEST DATABASE
CREATE MASTER KEY ENCRYPTION BY PASSWORD =
QUERY ENCRYPTIONS OF SYMMETRIC KEYS
SELECT * FROM SYS. KEY_ENCRYPTIONS;
CREATING A CERTIFICATE ENCRYPTED WITH A PASSWORD