By: Tim Cullen | Comments (12) | Related: > Encryption
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
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips