Problem
The company I work for has decided to use the encryption technology available in SQL Server 2005. We have almost everything ironed out except for which keys to use in the development, testing, and production environments. Should we use one key for all three environments or different keys for each?
Solution
There are a number of theories on how to deal with encryption in multiple environments, so I will offer mine based on past experience and what is known about the logistics of using SQL Server 2005 encryption.
If I had my druthers I would use one key for all environments. Using one key allows for retrieval of data even when the database is moved from one server to the other. The method of encryption I usually use is to create the database master key, certificate, and the necessary symmetric keys. Once the database you are moving has been restored on the new SQL instance, perform the following procedure:
- Drop all symmetric keys used. Keep track of the key names so that they can be re-created later. The syntax for dropping a symmetric key is:
USE <Database_Name>; DROP SYMMETRIC KEY <Key_Name>; - Drop all certificates used. Again, keep track of the certificate names so that they be re-created later. The syntax for dropping a certificate is:
USE <Database_Name>; DROP CERTIFICATE <Certificate_Name>; - Drop the Database Master Key. The syntax for dropping the Database Master Key is:
USE <Database_Name>; DROP MASTER KEY;
In short, what we have done is deleted everything in the Encryption Hierarchy from the lowest level to the top. If you try to drop objects higher in the hierarchy that are used to encrypt objects lower in the hierarchy, you will receive an error:
We can now start re-creating the objects for encryption, starting at the top of the hierarchy and working our way down:
- Re-create the Database Master Key. The syntax for creating a Database Master Key is:
USE <Database_Name>; CREATE MASTER KEY ENCRYPTION BY PASSWORD = <Password>; - Re-create the certificates using the same names as the ones that were dropped earlier. The syntax for creating a certificate is:
USE <Database_Name>; CREATE CERTIFICATE <Certificate_Name> AUTHORIZATION dbo WITH SUBJECT = '<Certificate subject or message indicating its purpose>' , EXPIRY_DATE = '<Expiration_Date>; - Re-create all symmetric keys using the same names as the ones that were dropped earlier. The syntax for creating symmetric keys is:
USE <Database_Name>; CREATE SYMMETRIC KEY <Key_Name> AUTHORIZATION dbo WITH ALGORITHM = <Algorithm_Name> ENCRYPTION BY CERTIFICATE <Certificate_Name>; - Reassign permissions to the users or groups that need to encrypt or decrypt the data. The two permission grants required for encryption and decryption are VIEW DEFINITION and CONTROL on each object involved in the encryption process, other than the Database Master Key.
Next Steps
- Keep in mind that there are a number of ways to encrypt and decrypt data using the encryption hierarchy provided by SQL Server 2005 and 2008. There are advantages and disadvantages to using each method. Thorough evaluation of the resources available and the client’s needs should be performed before deciding on an encryption method
- When moving databases in which encryption is used, it is best to write the above procedure as a T-SQL, including any other necessary cleanup, like changing the database owner, removing unnecessary logins, or removing test data. This will ensure that all steps have been included and are performed in proper sequence
- Read about granting permissions on certificates and symmetric keys
Tim has been working in the IT industry since 2003. After spending a few years as an “IT Generalist”, his career focus turned to SQL Server and application development. He currently works as an IT Specialist for a Federal agency in Maryland, where he focuses on SQL Server administration and providing guidance to teams on SQL Server performance issues. When not working, he enjoys playing golf, playing bass guitar, and exercising.
- MSSQLTips Awards: Trendsetter (25+ tips)
