One of the hottest topics in the technology world is security. This topic is important because there are few things that can hurt a company more than stolen or misused data. So, it is easy to see why security should always be an area of highest concern to DBA’s. And one of the keys to having a secure environment is to understand the layers of security that can be involved with databases. For example, security can be applied at the server level, to each SQL Server instance, to individual databases, to individual objects within the database, backups, etc.
In a previous post, I addressed some server and database level security (https://sqlbadboy.wordpress.com/2013/03/22/is-it-safe-is-it-secure/). In this post, I am going to focus on how SQL Server uses encryption as a security measure. Encryption is the process of obfuscating data, or purposely scrambling or masking it, by the use of keys and/or passwords. Just to be clear, encryption does NOT solve access control issues, but rather adds another layer of security if those controls are bypassed.
*** NOTE: Please give thought as to when encryption needs to be used, since there is a performance overhead. It should not be blindly applied to everything. In some cases, it may be necessary to use encryption such as when data is moving over a public network. On the other hand, it may not be necessary for data on an internal network.
How does SQL Server use encryption?
Here are the basics of SQL Server encryption, and the diagram is taken from: (Encryption Hierarchy: https://msdn.microsoft.com/en-us/library/ms189586.aspx):
- The Windows Operating System has a Data Protection API (DPAPI) that protects the Service Master Key (DPAPI: https://msdn.microsoft.com/en-us/library/ms995355.aspx).
- The Service Master Key is created by the SQL Server setup program and is used to create the Database Master Key (Service Master Key: https://msdn.microsoft.com/en-us/library/ms189060.aspx).
- The Database Master Key can be used to create certificates and symmetric keys. A certificate is a digitally-signed security object that contains a public key (and optionally private key). A symmetric key is one key that is used for both encryption and decryption. An asymmetric key is made up of a private key and the corresponding public key. Each key can decrypt data encrypted by the other (SQL Server Certificates and Asymmetric Keys: https://msdn.microsoft.com/en-us/library/bb895327.aspx).
- The Extensible Key Management (EKM) module holds symmetric or asymmetric keys outside of SQL Server (EKM: https://msdn.microsoft.com/en-us/library/bb895340.aspx).
- Transparent Data Encryption (TDE) uses a symmetric key called the database encryption key, which is protected by either a certificate protected by the database master key, or by an asymmetric key stored in an EKM (TDE: https://msdn.microsoft.com/en-us/library/bb934049.aspx).
How to setup Transparent Data Encryption (TDE)?
Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. The encryption of the database files is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory. Also, using TDE does not increase the size of the encrypted database.
Steps to enable TDE on a database:
- Create a master key (https://msdn.microsoft.com/en-us/library/aa337551.aspx)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'
- Create or obtain a certificate protected by the master key (https://msdn.microsoft.com/en-us/library/ms187798.aspx)
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate'
- Create a database encryption key and protect it by the certificate (https://msdn.microsoft.com/en-us/library/bb677241.aspx)
USE DatabaseName GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDECert
- Set the database to use encryption (https://msdn.microsoft.com/en-us/library/bb522682.aspx)
ALTER DATABASE DatabaseName SET ENCRYPTION ON
*** NOTE: Make sure that when you enable TDE that you should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.
Backup the database master key (https://msdn.microsoft.com/en-us/library/aa337546.aspx):
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'P@ssw0rd' BACKUP MASTER KEY TO FILE = 'c:\temp\exportedmasterkey' ENCRYPTION BY PASSWORD = 'P@ssw0rd'
Backup the certificate (https://msdn.microsoft.com/en-us/library/ms178578.aspx):
BACKUP CERTIFICATE TDECert TO FILE = 'C:\temp\dbcert.cer' WITH PRIVATE KEY (FILE = 'C:\dbcert_Key.pvk', ENCRYPTION BY PASSWORD = 'P@ssw0rd')
Moving a TDE enabled database (https://msdn.microsoft.com/en-us/library/ff773063.aspx):
If a database with TDE enabled needs to be moved to another server, you will have to use code like the following to restore the database master key and certificate.
RESTORE MASTER KEY FROM FILE = 'c:\temp\exportedmasterkey' DECRYPTION BY PASSWORD = 'P@ssw0rd' ENCRYPTION BY PASSWORD = 'P@ssw0rd' GO OPEN MASTER KEY DECRYPTION BY PASSWORD = 'P@ssw0rd'; CREATE CERTIFICATE TDECert FROM FILE = 'D:\BACKUP\dbCerts\dbcert.cer' WITH PRIVATE KEY (FILE = 'D:\BACKUP\dbCerts\dbcert_Key.pvk', DECRYPTION BY PASSWORD = 'P@ssw0rd')
*** NOTE: At times, I have seen an issue with databases being restored with the following error, “Please create a master key in the database or open the master key in the session before performing this operation.” In order to the master key to be used with all sessions, it must be encrypted by the service master key as follows:
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Useful DMV’s to use with encryption:
- sys.certificates (https://msdn.microsoft.com/en-us/library/ms189774.aspx)
- sys.dm_database_encryption_keys (https://msdn.microsoft.com/en-us/library/bb677274.aspx)
Hopefully, this post gave enough information for a DBA to have a basic understanding of encryption in SQL Server and how to use it.