SQL Server encryption options start with backup encryption. This encryption was introduced in version 2014. You can encrypt data while creating a backup. You need to specify an encryptor, which can be either a certificate or an asymmetric key, and define which algorithm to use for the encryption. The supported algorithms are AES-128, AES-192, AES-256, and Triple DES. Of course, you also need to back up the encryptor, and store it in a different, probably even off-site, location from the backup files. Without the encryptor, you can't restore an encrypted backup. You can also use EKM providers to store your encryptor safely outside SQL Server. Actually, if you are using an asymmetric key as an encryptor instead of a certificate, then this key must reside in an EKM provider.
The restore process for an encrypted backup is completely transparent. You don't need to specify any particular encryption options. However, the encryptor must be available on the instance of SQL Server you are restoring to. In addition to the regular restore permissions, you also need to have at least the VIEW DEFINITION permission on the encryptor.
In the following code showing the start of the backup encryption process, first a master database DMK is created. This key is used to protect a self-issued certificate, also created in the master database:
USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd'; CREATE CERTIFICATE DemoBackupEncryptCert WITH SUBJECT = 'SQLDevGuideDemoDb Backup Certificate'; GO
The master DMK is encrypted using the SMK created during the setup. You can check both keys with the following query:
SELECT name, key_length, algorithm_desc FROM sys.symmetric_keys;
The query returns the following result set:
Name key_length algorithm_desc
------------------------ ----------- --------------
##MS_DatabaseMasterKey## 256 AES_256
##MS_ServiceMasterKey## 256 AES_256
For a test, the following code creates an unencrypted backup in the C:SQL2017DevGuide folder, which should be created in advance:
BACKUP DATABASE SQLDevGuideDemoDb TO DISK = N'C:SQL2017DevGuideSQLDevGuideDemoDb_Backup.bak' WITH INIT;
Next, you can create an encrypted backup, as shown in the following code:
BACKUP DATABASE SQLDevGuideDemoDb TO DISK = N'C:SQL2017DevGuideSQLDevGuideDemoDb_BackupEncrypted.bak' WITH INIT, ENCRYPTION ( ALGORITHM = AES_256, SERVER CERTIFICATE = DemoBackupEncryptCert );
Note that this time you get a warning telling you that the certificate used for encrypting the database encryption key has not been backed up. Therefore, you should back up the certificate used for the backup encryption and, in addition, the master DMK used to protect the certificate and the SQL Server SMK used to protect the master DMK, as the following code shows:
-- Backup SMK BACKUP SERVICE MASTER KEY TO FILE = N'C:SQL2017DevGuideSMK.key' ENCRYPTION BY PASSWORD = 'Pa$$w0rd'; -- Backup master DMK BACKUP MASTER KEY TO FILE = N'C:SQL2017DevGuidemasterDMK.key' ENCRYPTION BY PASSWORD = 'Pa$$w0rd'; -- Backup certificate BACKUP CERTIFICATE DemoBackupEncryptCert TO FILE = N'C:SQL2017DevGuideDemoBackupEncryptCert.cer' WITH PRIVATE KEY ( FILE = N'C:SQL2017DevGuideDemoBackupEncryptCert.key', ENCRYPTION BY PASSWORD = 'Pa$$w0rd' ); GO
Now you are ready to simulate a failure. Drop the demo database, the certificate used for the encryption, and the master DMK:
DROP DATABASE SQLDevGuideDemoDb; DROP CERTIFICATE DemoBackupEncryptCert; DROP MASTER KEY;
Try to restore the encrypted backup. You should get error 33111, telling you that SQL Server cannot find the server certificate. The following code is used to restore the encrypted backup:
RESTORE DATABASE SQLDevGuideDemoDb FROM DISK = N'C:SQL2017DevGuideSQLDevGuideDemoDb_BackupEncrypted.bak' WITH FILE = 1;
You have to start the restore process by restoring the master DMK, as shown in the following code:
RESTORE MASTER KEY FROM FILE = N'C:SQL2017DevGuidemasterDMK.key' DECRYPTION BY PASSWORD = 'Pa$$w0rd' ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
Next, you open the master DMK and restore the certificate, as shown in the following code:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Pa$$w0rd'; CREATE CERTIFICATE DemoBackupEncryptCert FROM FILE = N'C:SQL2017DevGuideDemoBackupEncryptCert.cer' WITH PRIVATE KEY (FILE = N'C:SQL2017DevGuideDemoBackupEncryptCert.key', DECRYPTION BY PASSWORD = 'Pa$$w0rd');
Now you are ready to restore the encrypted backup. The following code should restore the demo database successfully:
RESTORE DATABASE SQLDevGuideDemoDb FROM DISK = N'C:SQL2017DevGuideSQLDevGuideDemoDb_BackupEncrypted.bak' WITH FILE = 1, RECOVERY;
Finally, you can check which backups are encrypted by querying the msdb.dbo.backupset table as shown in the following code:
SELECT b.database_name, c.name, b.encryptor_type, b.encryptor_thumbprint FROM sys.certificates AS c INNER JOIN msdb.dbo.backupset AS b ON c.thumbprint = b.encryptor_thumbprint;
Backup encryption encrypts backups only. It does not encrypt data in data files. You can encrypt data in tables with T-SQL using column-level encryption. Column-level encryption is present in SQL Server from version 2008 onwards. You encrypt the data in a specific column by using a symmetric key. You protect the symmetric key with an asymmetric key or a certificate. The keys and the certificate are stored inside your database where the tables with the encrypted columns are. You protect the asymmetric key or the certificate with the database master key. The following code, which created the DMK in the demo database, issues a SQL Server certificate and then creates the symmetric key used for column encryption:
USE SQLDevGuideDemoDb; -- Create the SQLDevGuideDemoDb database DMK CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd'; -- Create the column certificate in SQLDevGuideDemoDb CREATE CERTIFICATE DemoColumnEncryptCert WITH SUBJECT = 'SQLDevGuideDemoDb Column Certificate'; -- Create the symmetric key CREATE SYMMETRIC KEY DemoColumnEncryptSimKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE DemoColumnEncryptCert; GO
Next, you can prepare an additional column to store the encrypted data. The dbo.Table1 should already exist from the demo code earlier in this chapter. The following code adds an additional column to store the encrypted data:
ALTER TABLE dbo.Table1 ADD tableContainer_Encrypted VARBINARY(128); GO
Now you are ready to encrypt data in the new column. You need to open the symmetric key and decrypt it with the certificate used for the encryption. The following code opens the symmetric key and then updates the new column in the table with the values from an unencrypted column. The code uses the ENCRYPTBYKEY() T-SQL function to encrypt the data with a symmetric key:
OPEN SYMMETRIC KEY DemoColumnEncryptSimKey
DECRYPTION BY CERTIFICATE DemoColumnEncryptCert;
UPDATE dbo.Table1
SET tableContainer_Encrypted =
ENCRYPTBYKEY(Key_GUID('DemoColumnEncryptSimKey'), tableContainer);
GO
You can check the data with the following query, which uses the DECRYPTBYKEY() T-SQL function for decryption:
OPEN SYMMETRIC KEY DemoColumnEncryptSimKey DECRYPTION BY CERTIFICATE DemoColumnEncryptCert; -- All columns SELECT id, tableContainer, tableContainer_Encrypted, CAST(DECRYPTBYKEY(tableContainer_Encrypted) AS CHAR(5)) AS tableContainer_Decrypted FROM dbo.Table1; GO
Here are the results, with the encrypted value abbreviated for simpler reading:
Id tableContainer tableContainer_Encrypted tableContainer_Decrypted ----- -------------- ------------------------ ------------------------ 1 dbo 0x003D10428AE86248A44F70 dbo 2 dbo 0x003D10428AE86248A44F70 dbo
You can use the following code to clean up your SQL Server instance. The code also deletes backups in the demo folder. You need to run SSMS as administrator and turn on the SQLCMD mode in SSMS to successfully execute the clean-up code (go to the Query menu and select the SQLCMD mode option):
USE master; !!del C:SQL2017DevGuideDemoBackupEncryptCert.cer !!del C:SQL2017DevGuideDemoBackupEncryptCert.key !!del C:SQL2017DevGuidemasterDMK.key !!del C:SQL2017DevGuideSMK.key !!del C:SQL2017DevGuideSQLDevGuideDemoDb_Backup.bak !!del C:SQL2017DevGuideSQLDevGuideDemoDb_BackupEncrypted.bak GO IF DB_ID(N'SQLDevGuideDemoDb') IS NOT NULL DROP DATABASE SQLDevGuideDemoDb; DROP LOGIN LoginA; DROP LOGIN [BuiltinPower Users]; DROP LOGIN LoginB; DROP CERTIFICATE DemoBackupEncryptCert; DROP MASTER KEY; GO
Column-level encryption protects the data in the database, not just backups. However, it protects data at rest only. When data is used by an application, the data is decrypted. If you don't use network encryption, the data travels over the network in an unencrypted way. All the keys are in a SQL Server database, and therefore a DBA can always decrypt the data. End users who don't have access to the certificates and keys can't decrypt the encrypted data. In addition, the implementation of column-level encryption might be quite complex because you might need to modify a lot of T-SQL code. Column-level encryption is available in all editions of SQL Server.
Another option to protect data at rest is Transparent Data Encryption (TDE). You can use the TDE for real-time encryption and decryption of data and log files. You encrypt data with the database encryption key (DEK), which is a symmetric key. It is stored in the database boot record and is therefore already available during the database recovery process. You protect the DEK with a certificate in the master database. You can also use an asymmetric key instead of the certificate; however, the asymmetric key must be stored in an EKM module. TDE uses the AES and Triple DES encryptions only. TDE was first implemented in SQL Server with version 2012.
You can use TDE on user databases only. You cannot export the database encryption key. This key is used by the SQL Server Database Engine only. End users never use it. Even if you change the database owner, you don't need to regenerate the DEK.
TDE encrypts data on a page level. In addition, it also encrypts the transaction log. You should back up the certificate used to protect the DEK and the private key used to protect the certificate immediately after you enable TDE. If you need to restore or attach the encrypted database to another SQL Server instance, you need to restore both the certificate and the private key, or you are not able to open the database. Note again that you don't export the DEK as it is a part of the database itself. You need to keep and maintain the certificate used to protect the DEK even after you disable the TDE on the database. This is because parts of the transaction log might still be encrypted. The certificate is needed until you perform a full database backup.
The following code starts the process of enabling the TDE by creating a DMK in the master database:
USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd'; GO
You can check whether the master DMK was created successfully with the following code:
SELECT name, key_length, algorithm_desc FROM sys.symmetric_keys;
Let's back up the SMK and the master DMK immediately, as the next part of the code shows:
BACKUP SERVICE MASTER KEY TO FILE = N'C:SQL2017DevGuideSMK.key' ENCRYPTION BY PASSWORD = 'Pa$$w0rd'; -- Backup master DMK BACKUP MASTER KEY TO FILE = N'C:SQL2017DevGuidemasterDMK.key' ENCRYPTION BY PASSWORD = 'Pa$$w0rd'; GO
The next portion of the code creates a demo database:
IF DB_ID(N'TDEDemo') IS NULL CREATE DATABASE TDEDemo; GO
While still in the context of the master database, use the following code to create the certificate you will use to protect the DEK:
CREATE CERTIFICATE DemoTDEEncryptCert WITH SUBJECT = 'TDEDemo TDE Certificate'; GO
Of course, you need to back up this certificate immediately, as shown in the following code:
BACKUP CERTIFICATE DemoTDEEncryptCert TO FILE = N'C:SQL2017DevGuideDemoTDEEncryptCert.cer' WITH PRIVATE KEY ( FILE = N'C:SQL2017DevGuideDemoTDEEncryptCert.key', ENCRYPTION BY PASSWORD = 'Pa$$w0rd' ); GO
You create the database encryption key in the demo user database, as shown in the following code:
USE TDEDemo; CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE DemoTDEEncryptCert; GO
The final step of this process is to actually turn the TDE on, as shown in the following code:
ALTER DATABASE TDEDemo SET ENCRYPTION ON; GO
You can check which databases are encrypted by querying the sys. dm_database_encryption_keys dynamic management view. This view exposes the information about the encryption keys and the state of encryption of a database, as shown in the following code:
SELECT DB_NAME(database_id) AS DatabaseName,
key_algorithm AS [Algorithm],
key_length AS KeyLength,
encryption_state AS EncryptionState,
CASE encryption_state
WHEN 0 THEN 'No database encryption key present, no encryption'
WHEN 1 THEN 'Unencrypted'
WHEN 2 THEN 'Encryption in progress'
WHEN 3 THEN 'Encrypted'
WHEN 4 THEN 'Key change in progress'
WHEN 5 THEN 'Decryption in progress'
END AS EncryptionStateDesc,
percent_complete AS PercentComplete
FROM sys.dm_database_encryption_keys;
The results of this query are as follows:
DatabaseName Algorithm KeyLength EncryptionState EncryptionStateDesc PercentComplete ------------ --------- --------- --------------- ------------------- ------------ Tempdb AES 256 3 Encrypted 0 TDEDemo AES 128 3 Encrypted 0
Note that the Tempdb system database also inherited the encryption. The demo database is empty and thus very small. The encryption process on such a small database is very fast. However, in a production database, you would be able to monitor the percentage complete rising from 0 to 100, while the encryption state would be Encryption in progress. SQL Server needs to scan all of the data files and log files to finish the encryption.
Now let's turn encryption off for the demo database:
ALTER DATABASE TDEDemo SET ENCRYPTION OFF; GO
Using the same query, you can check the encryption status again:
SELECT DB_NAME(database_id) AS DatabaseName,
key_algorithm AS [Algorithm],
key_length AS KeyLength,
encryption_state AS EncryptionState,
CASE encryption_state
WHEN 0 THEN 'No database encryption key present, no encryption'
WHEN 1 THEN 'Unencrypted'
WHEN 2 THEN 'Encryption in progress'
WHEN 3 THEN 'Encrypted'
WHEN 4 THEN 'Key change in progress'
WHEN 5 THEN 'Decryption in progress'
END AS EncryptionStateDesc,
percent_complete AS PercentComplete
FROM sys.dm_database_encryption_keys;
Please note the result. The tempdb system database is still encrypted:
DatabaseName Algorithm KeyLength EncryptionState EncryptionStateDesc PercentComplete ---------- --------- --------- --------------- ------------------- -------------- Tempdb AES 256 3 Encrypted 0 TDEDemo AES 128 1 Unencrypted 0
Restart your SQL Server instance and execute the previous query again. This time, the tempdb system database is unencrypted.
You can use the following code to clean up your SQL Server instance. Again, use SQLCMD mode to execute it:
USE master; !!del C:SQL2017DevGuideDemoTDEEncryptCert.cer !!del C:SQL2017DevGuideDemoTDEEncryptCert.key !!del C:SQL2017DevGuidemasterDMK.key !!del C:SQL2017DevGuideSMK.key IF DB_ID(N'TDEDemo') IS NOT NULL DROP DATABASE TDEDemo; DROP CERTIFICATE DemoTDEEncryptCert; DROP MASTER KEY; GO