I recently went to the local SQL Server user group meeting were there was a presentation on database encryption. The main focus of the presentation was an overview of how encryption works and how to configure encryption to secure one or more columns of data in your database. I’m going to provide a brief overview of encryption, how to encrypt one column in a table, and how you can encrypt your entire database.
One of the most difficult-to-understand options in SQL Server is the ability to encrypt data. This is mainly because of all of the different encryption capabilities and options offered.
Encryption brings data into a state that cannot be easily interpreted by someone who does not have access to the decryption key, password, or certificates. Though encryption does not restrict the actual access to the data, it ensures that if data loss does occur, the data is useless to the person who does not have the ability to decrypt the data. Though when you use encryption, there should be a maintenance strategy for passwords, keys, and certificates.
To meet the demands of regulatory compliance and corporate data security standards, SQL Server allows you to enable encryption at column-level or on the database level.
Column-level encryption (aka cell-level encryption) was introduced in SQL Server 2005 and is available in all editions of SQL Server, including the free SQL Server Express edition. To use cell-level encryption, the schema must be changed to varbinary, then reconverted to the desired data type. This means the application must be changed to support the encryption-decryption operation; in addition, it can impact database performance. Encryption of the database occurs at the page level, but when those pages are read to buffer pool, they’re decrypted. Data can be encrypted using a passphrase, an asymmetric key, a symmetric key, or a certificate. The supported algorithms for column-level encryption are AES with 128,196,256 bit keys and 3DES. You can read more about these algorithms here.
This level of encryption is the preferred method of encryption. Only columns containing important data should be encrypted; this will result in less CPU load than the whole database level encryption. If a column is used as primary key, or used in comparison clauses (WHERE clauses, JOIN conditions) the database will have to decrypt the whole column to do operations involving those columns.
Here are some scripts to help your analysis:
-- Create Database USE master GO CREATE DATABASE EncryptionTest ON PRIMARY (NAME = N'EncryptionTest', FILENAME = N'C:\Encrypt.mdf') LOG ON (NAME = N'Encryption_log', FILENAME = N'C:\Encrypt_log.ldf') GO
Let’s create a sample table and populate it with sample data. We will later encrypt one of the two columns of the table.
--Create table and insert data in the table USE EncryptionTest GO CREATE TABLE TestTable (FirstCol INT, SecondCol VARCHAR(50)) GO INSERT INTO TestTable (FirstCol, SecondCol) SELECT 1,'First' UNION ALL SELECT 2,'Second' UNION ALL SELECT 3,'Third' UNION ALL SELECT 4,'Fourth' UNION ALL SELECT 5,'Fifth' GO --Check the content of the TestTable USE EncryptionTest GO SELECT * FROM TestTable GO
Every database can have one master key. The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys present in the database. It uses Triple DES algorithm along with user provided password to encrypt the keys.
--Create Database Master Key USE EncryptionTest GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'AVeryStongPassword' GO
Certificates are used to protect encryption keys, which are used to encrypt data in the database. SQL Server 2005 and above has the ability to generate self-signed X.509 certificates.
--Create Encryption Certificate USE EncryptionTest GO CREATE CERTIFICATE EncryptionTestCert WITH SUBJECT = 'MyCertificatePassword' GO
The symmetric key can be encrypted by using any of the certificate, password, and symmetric key, asymmetric key options. We can use many different algorithms for the encrypting key. Supported algorithms are DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192, and AES_256.
--Create Symmetric Key USE EncryptionTest GO CREATE SYMMETRIC KEY TestTableKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE EncryptionTestCert GO
Now add a column of type varbinary to original table, which will store the encrypted value for the SecondCol.
--Encrypt Data using Key and Certificate --Add Columns to hold the encrypted data in binary USE EncryptionTest GO ALTER TABLE TestTable ADD EncryptedSecondCol VARBINARY(256) GO
Before using the key, it needs to be decrypted by the same method with which it was encrypted. In our example we had used a certificate for encrypting the key. Due to the same reason, we are using the same certificate for opening the key and make it available for use. After it is open and available to use, we can use the encryptkey function and store the encrypted values in the database, in the EncryptSecondCol column.
--Update binary column with encrypted --data created by certificate and key USE EncryptionTest GO OPEN SYMMETRIC KEY TestTableKey DECRYPTION BY CERTIFICATE EncryptionTestCert UPDATE TestTable SET EncryptSecondCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SecondCol) GO
We can now drop the original SecondCol column, because we have now encrypted the data in the EncryptSecondCol column. If you don’t want to drop the column, you can keep it for future comparison of the data as we decrypt the column. In production, you would not want to keep this data unencrypted.
--DROP original column which was --encrypted to protect the data USE EncryptionTest GO ALTER TABLE TestTable DROP COLUMN SecondCol GO
You can run a SELECT query on your database to verify that your data in the table is now protected, and unauthorized users will have no understanding of the encrypted data if they manage to reach the data.
--Check the content of the TestTable USE EncryptionTest GO SELECT * FROM TestTable GO
Authorized users can use the decryptbykey function to retrieve the original data from the encrypted column. If Symmetric key is not open for decryption, it has to be decrypted using same certificate which was used for the encryption. One thing to keep in mind here is that the original column and the decrypted column should have the same data types. If they are of different data types, incorrect values could be reproduced. In our case, we have used a VARCHAR data type for SecondCol and EncryptSecondCol.
--Decrypt the data of the SecondCol USE EncryptionTest GO OPEN SYMMETRIC KEY TestTableKey DECRYPTION BY CERTIFICATE EncryptionTestCert SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptSecondCol)) AS DecryptSecondCol FROM TestTable CLOSE SYMMETRIC KEY TestTableKey GO
Transparent Data Encryption
Transparent Data Encryption (TDE) is the primary SQL Server encryption option. It was first available in SQL Server 2008, and with the more recent versions of SQL Server it’s available only in the SQL Server Enterprise edition. TDE enables you to encrypt an entire database. Backups for databases that use TDE are also encrypted. TDE protects the data at rest, which means that the database’s data and log files are encrypted using the AES and 3DES encryption algorithms. TDE is completely transparent to the application and requires no coding changes to implement.
The performance overhead for using TDE ranges from about 3 percent to 30 percent, depending on the type of workload. SQL Server instances with low I/O and low CPU usage will have the least performance impact. Servers with high CPU usage will have the most performance impact.
When you enable or disable TDE, the encryption and decryption operations are scheduled on background threads (called the encryption scan or scan – that scans all database files for encryption when enabling or decryption when disabling TDE) by SQL Server. You can view the status of these operations using the catalog views and dynamic management views as demonstrated next.
Please note, TDE works on data at rest for the entire database by encrypting data when writing to disk and decrypting it when reading from disk at I/O level through the buffer pool. This means, data in the buffer pool remains there in clear text format. Hence, if you want to protect data in the buffer pool with encryption, you need to employ a different technique.
These are the steps you need to perform to enable TDE for a database, assuming you have the required permissions for creating a database master key and certificates in the master database and CONTROL permissions on the user database.
- Create a master key – A master key is a symmetric key that is used to create certificates and asymmetric keys.
- Create or obtain a certificate protected by the master key – Certificates can be used to create symmetric keys for data encryption or to encrypt the data directly.
- Create a database encryption key and protect it by the certificate .
- Set the database to use encryption – When you enable TDE on any user database, encryption is also automatically enabled for the tempdb database. This prevents temporary objects that are used by the user database from leaking to disk unencrypted via tempdb database. System databases other than tempdb cannot currently be encrypted by using TDE. You can read more about the supported algorithms here.
Let’s run through some scripts to see some basic examples:
USE master; --create a database master key (DMK) --for the master database CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'AVeryStrongPassword'; --create a certificate for use as the database encryption --key (DEK) protector and is protected by the DMK. CREATE CERTIFICATE Cert4TDE WITH SUBJECT = 'Certificate for TDE'; GO Use AdventureWorks2012 --Create the database encryption key (DEK) --encrypted with the certificate created CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 --Supported encryption algorithms are --AES (128-bit, 192‑bit, or 256‑bit keys) --or 3 Key Triple DES ENCRYPTION BY SERVER CERTIFICATE Cert4TDE; GO Use Master --Enable TDE for your database. --This command starts a background --thread which runs asynchronously. ALTER DATABASE AdventureWorks2012 SET ENCRYPTION ON; GO --To monitor encryption progress --you can use this query SELECT db_name(database_id), encryption_state, percent_complete, key_algorithm, key_length FROM sys.dm_database_encryption_keys;
You can also disable this encryption by turning the process back off:
Use Master --Disable TDE for your database. --As mentioned, this command --starts a background thread --which runs asynchronously. ALTER DATABASE AdventureWorks2012 SET ENCRYPTION OFF;
It’s very important (and essential for disaster recovery) to take backup of the keys and certificates in order to restore or attach the encrypted database on another SQL Server instance after restoring these keys and certificates there.
When you use a certificate without taking a backup of it, SQL Server gives this warning:
“Warning: The certificate used for encrypting the database encryption key has not been backed up. 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.”
Please note, enabling encryption does not necessarily change the storage requirements but it does require additional CPU cycles to encrypt and decrypt the data. Additionally, you need to create and use a maintenance strategy in place to store and protect your keys. I recommended you first evaluate the need and then plan for implementation in a test environment before you move anything into production.
Encryption is a very important security feature of SQL Server 2005 and later. Long keys as well as asymmetric keys create strong encryption and stronger encryption uses lots of CPU cycles to encrypt and decrypt data. Stronger encryption is slower to process, but you will need to weigh performance against the level of required security. You can read more on this subject here and here.