SQLStarters

I recently read an article which stating that since the GDPR came in force, there has been 59,000 data breaches reported in the EU. I must admit, that while I did anticipate that we’d see a surge in these numbers, due to reporting requirements in the legislation. I really did not expect the numbers to look that terrifying.

From the point of view of a SQL Server DBA, there is a number of different ways to protect your data. Some of them are even quite easy to setup, such as Transparent Data Encryption (TDE). So let’s have a look at how to set that up!

Transparent Data Encryption

TDE has been available for quite a while, but there is on thing to notice. It’s an Enterprise Edition only feature, and this remains true also in the SQL Server 2017 version. Microsoft did make a whole lot of Enterprise Edition features available for Standard Edition with SQL Server 2016, but the goal there was to ultimately allow developers to make use of the new features in preparation of taking their workloads to Enterprise Edition.

However TDE, which is transparent to users, does not require anything to change from the application perspective and was therefor not included. You can still encrypt backups with Standard Edition, so you can get halfway there if you’re limited to using Standard Edition.

Note: TDE only protects data at rest, so your backups, data- and log files will be encrypted. However once those pages are read into memory and send over to network they are decrypted.

Enabling Transparent Data Encryption

Enabling TDE is actually quite straightforward, the steps you need to take are:

1. Create Master Key
2. Create Certificate
3. Create Database Encryption Key
4. Backup the Master Key and Certificate with the Private Key
5. Enable Database Encryption

Create Master Key

Creating the Database Master Key can be done with the following T-SQL command, you just need to define the password to be used, which should probably be quite a lot of better than mine in the example.

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '5uperSekritPassword!'

The Database Master Key is used for protecting the private keys of certificates and asymmetric keys that are in the database. You can use the following query to find any symmetric keys in the server.

SELECT * 
FROM sys.symmetric_keys AS SK

You’ll notice that there are two symmetric keys, one that was created by us (Database Master Key) and another one that was created during the server setup (Service Master Key). And now we’re ready to move to the next step.

Create Certificate

As a next step we need to create the certificate, in this case a self-signed one with the private key being protected by a password. Again this is as simple as executing the following code.

USE master;
GO
CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'Certificate for TDE',
     EXPIRY_DATE = '2021-01-01 23:59:00.000';

It’s usually a good idea to add a subject so you can identify what the certificate is used for, also adding an expiry data is a good practice. Again you can query the SQL Server to find what certificates exists.

SELECT *
FROM sys.certificates AS C;

Now we’re pretty much done with setting up the required infrastructure to enable TDE, and can move to work with the actual user database.

Create database encryption key

The database encryption key is what is used to actually perform the encryption of the database. We create it and protect the private key with the certificate we created in the previous step.

USE StackOverflow2013;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyCertificate;

Now we have added the database encryption key, however the database is still not encrypted, but you need to enable that separately. Before doing that however, it’s time to take some backups.

Backup Master Key and Certificate with the Private Key

Once the database is encrypted the certificate becomes critical piece of the infrastructure. If you want to restore the database anywhere and access it, you need to have the certificate available. There are services you can use to store certificates safely, but at minimum you must back it up into a file and store it somewhere safe (outside the server).

BACKUP MASTER KEY
TO FILE = 'F:\Backup\master_key_backup'
ENCRYPTION BY PASSWORD = '5uperSekritPassword';

While it’s not actually necessary to use it, f.ex when restoring database to another server (you can create a new master key), it’s still a good administrative policy to have it in a safe location for other purposes (such as Disaster Recovery).

Where as the master key is not critical to our Transparent Data Encryption setup, the certificate very much is. You need to back this up with the private key for it to be useful in restoring our database.

-- And the certificate with the private key
BACKUP CERTIFICATE MyCertificate TO FILE = 'F:\Backup\certificate_backup'
WITH PRIVATE KEY
(
    FILE = 'F:\backup\privatekey',
    ENCRYPTION BY PASSWORD = '5uperSekritPassword'
);

Remember to store this in a safe place, preferably a dedicated service if you are lucky enough to have such. And if you don’t, take a look at Azure Key Vault. And finally we’re ready to enable the encryption!

Enabling TDE

If you were expecting something miraculously complex here, I have to disappoint you. To enable the Transparent Date Encryption you just, well, enable it.

ALTER DATABASE StackOverflow2013 SET ENCRYPTION ON;

The ALTER DATABASE command will start the process of encrypting the database, however to fully complete it might take time, which is depending on how large database and how beefy your SQL Server is. There is a way to monitor this.

SELECT DB_NAME(DDEK.database_id) AS [Database Name],
       DDEK.encryption_state,
       DDEK.percent_complete,
       DDEK.create_date
FROM sys.dm_database_encryption_keys AS DDEK;

Ah, right. So when you start to encrypt any of the databases in your instance, it will also automatically encrypt the TEMPDB due to security reason. The status codes indicate that if the database is already encrypted (encryption_state = 3) or is the encryption still in progress (encryption_state = 2). I don’t have a beefy test server and the StackOverflow database I am using is on the larger side, so it’ll take awhile.

To wrap things up

Setting up Transparent Data Encryption is truly not that difficult, I would say that it is pretty much the easiest first step into securing your database files, including the backups, properly. You might want to have proper certificates instead of self-signed ones at some point, but doing even this little is better than doing nothing.

Remember: If you’re a DBA making sure that the databases are secure is also part of your responsibility, not just the security team. Work together to secure your servers/instances/databases as far as you’re able.