Encrypting SQL Server databases couldn’t be easier and is something you definitely want to consider doing from a security point of view. 

If we take a backup of a standard SQL Server database (here I am using the AdventureWorks sample database downloaded from Codeplex) then it’s a simple matter to restore it to another server. This makes it incredibly easy to obtain, for example, credit card details stored in a SQL database. 

One way to protect against this is to encrypt the database either at the cell level (encrypting individual items of data) or by encrypting the whole database. Encrypting the data cell by cell involves using code to program the encryption and leads to very slow searches for data as the data needs to be decoded for each search. 

Encrypting at the database level requires no code in your final application and provides transparent access to the data. You should still be aware that data flowing from the server to the end user is still un-encrypted and can be read in the clear and so you will still need to encrypt data transfer to the end point using IPSec or similar. Transparent Database Encryption (TDE) is only supported in the SQL Server 2008 Enterprise and Developer editions. 

Microsoft has a jolly good article on encrypting at the database level which can be found here and from which the majority of this article is taken. The steps to enable database encryption are described in the following flowchart. 

 

In simple terms this means that a Service Master Key is created at the time of installation of SQL. This key is used to create a Database Master Key which in turn is used to create a certificate which we use to create the Database Encryption Key for any database of our choice. 

In order to recover from a failure of any database or server it is critical that this certificate and its associated private key are backed up. The whole process above is implemented by use of the following commands. 

USE master;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘myStrongPassword679!!!‘;
GO
 

CREATE CERTIFICATE NameForMyServerCert WITH SUBJECT = ‘DEK Certificate‘;
GO
 

USE NameOfDatabaseToBeEncrypted;
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE NameForMyServerCert;
GO

ALTER DATABASE NameOfDatabaseToBeEncrypted
SET ENCRYPTION ON;
GO
 

–To backup the Certificate use the following code
 

BACKUP CERTIFICATE NameForMyServerCert
TO FILE = ‘C:BackupLocationCertificate’
WITH PRIVATE KEY (file=’C:BackupLocationPrivateKey’,
ENCRYPTION BY PASSWORD=’PasswordToEncryptBackup‘);
GO
 

I’ve coloured the bits you need to amend in the code above in red. 

Running the above in the scripting window in SQL Server Management Studio does all of the work for you. Below is the script amended to encrypt the AdventureWorks database and export the certificate and private key. 

 

In a similar fashion you should also backup your Master Key – the instructions for that are here. 

Once the database has been encrypted it cannot be restored to a different server without first importing the certificate. If an attempt is made then it is met with the “Cannot find server certificate with thumbprint” error below. 

 

You will also need to restore the Master Key backed up previously. 

To backup the Master Key use T-SQL similar to the below. 

 

To restore the Master Key use the RESTORE MASTER KEY command 

 

Note, if you are backing up from one instance and restoring to another you may need to change the NTFS permissions to the exported key files as they are restricted by the process to the instance that backs them up. 

 

To re-import the certificate use code similar to the below. 

 

Once the master certificate and the certificate used to encrypt the database have been re-created you can then restore your database. However, because the database has been encrypted you will not be able to use the GUI to restore it, instead you will need to use T-SQL. 

An example of code to use is below. First we open the Master Key to use it and then we just perform a standard restore. The one below restores to a database with a different name and moves the data into different files as I was restoring to an alternative instance to the same server. 

OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘Password1’; 

RESTORE DATABASE [AVWorks] FROM DISK = N’E:BackupAdventureWorks.bak’ WITH FILE = 2, MOVE N’AdventureWorks_Data’ TO N’E:DATAAVWorks.mdf’, MOVE N’AdventureWorks_log’ TO N’F:DataAVWorks_log.ldf’, NOUNLOAD, REPLACE, STATS = 10

GO  

Once restored your data will still be encrypted but will be accessible once more to authorised users.