source: http://www.MSSQLTips.com/tip.asp?id=1507 -- printed: 5/5/2016 10:16:18 AM
Implementing Transparent Data Encryption in SQL Server 2008Written By: Edwin Sarmiento -- 5/28/2008
In this tip we are going to look at how to implement transparent data encryption to protect database backups. Let us first demonstrate how prone database backups are to security threats. By default, SQL Server backups are not encrypted. Let's start by creating a full backup of the Northwind database on my default instance.
Let's query the Northwind database for a specific record. We will use this record later on to view the contents of our database backup
Open the database backup in Notepad and search for 'A r i a C r u z' (note the spaces between the letters and three spaces between "Aria" and "Cruz" as this is Unicode text.) Observe that the text data is readable.
Since your native backups are readable they are prone to security risks since they can be read in clear text. This enables someone to just look at your database backups and retrieve data from the files. What's worse is that anybody can restore your database backups on another SQL Server instance. We will demonstrate this procedure shorlty.
Let's implement transparent data encryption on our Northwind database by first setting up encryption on the server. To do that, we create the database master key on the master database
Then, we create the server-based certificate which will be used to encrypt the database
We will then set the encryption for the Northwind database by creating a database encryption key and password using the certificate we just created.
The encryption algorithm choice is yours, since a few different options exist. For guidance on selecting an encryption algorithm on SQL Server, check out this Microsoft TechNet article. After creating the database encryption key, we'll enable encryption on the database level.
Let's backup the encrypted Northwind database this time but using a different filename.
The encrypted backup would take a bit longer than the unencrypted backup. If you open the encrypted database backup and search for the string 'A r i a C r u z' which we searched for earlier, you will no longer find it. You can scroll thru the text and notice the encrypted data which is unlike the one you'll saw in the unencrypted backup.
Restoring the encrypted database backup on another SQL Server instance
It is easy to restore the unencrypted backup on another SQL Server instance. In fact, this is what makes it prone to security risks. You can try restoring the encrypted database backup as Northwind_encrypted and you'll encounter an error. I am doing this on my TEST instance. The error message will tell you that the server certificate cannot be found. Even though the restore failed, you will still see the Northwind_encrypted database on the Object Explorer on a restoring state.
In order to restore the encrypted database backup on another SQL Server instance we need to first export the certificate we created on the instance on which the encrypted database backup was created. To export the certificate to a file, I'll connect on my default instance and run this query.
Then, we'll need to copy the certificate and the private key file to the other SQL Server instance. Since I am running my default and my TEST instances on the same server, I'll just refer to those files when I do the import process. Now, on the TEST instance, import the certificate by first creating a master key.
The password provided here is different from the one you used in the default instance as you are creating a new master key for this instance. After a master key has been created, you can create a certificate by importing the certificate we created earlier.
Notice that the password specified here in the DECRYPTION BY PASSWORD parameter is the same as that which we used to export the certificate to a file. This is because we will use the same certificate on this new instance to access the encrypted database. After the certificate has been created, we can now restore the encrypted database backup on this instance. You might want to drop the initial encrypted database which you attempted to restore before proceeding. You will now be able to restore the encrypted database backup successfully.