How to encrypt a SQL Server database

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. 

Use Master 

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.

5 thoughts on “How to encrypt a SQL Server database

  1. Travis Alltop

    Great article Phillip, one question about encrypting a production database. Is this an online operation? Or does the database need to be in single_user mode to safely encrypt it? Thanks Travis.

    Like

  2. Ata

    Hello
    We have some Servers in our company . yesterday suddenly we faced to a problem with active Directory. No one could sign in even the Administrator . So I had to Setup a New Windows and Active Directory and create a new domain. then i joined my database server to the new domain . then i saw that one of my databases has been detached.i try to attach it but i couldn’t. I changed the permissions of the mdf file and grant full control to everyone. after it i tried to attach it again,but it gives me this error: “cannot find server certificate with thumbprint”
    I hadn’t encrypt any database by TDE .Would you please help me?
    thank you very much
    atafakhari@gmail.com

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.