By: Tim Cullen | Comments (9) | Related: > Encryption
Problem
I recently accepted the challenge of designing a database for an employee application, and the first thought to cross my mind was," I will need to encrypt at least one piece of data". Along with SQL Server 2005 came the native ability to encrypt data in the database. Prior versions depended on third-party tools or encryption via the front end application. In this four-tip series we will cover the objects needed and steps required to encrypt data using SQL Server:
- Database Master Keys
- Certificates
- Symmetric and Asymmetric Keys
- Putting It All Together-Using Data Encryption in the Production Environment
Solution
SQL Server has an encryption hierarchy, as shown below, that needs to be followed in order to properly support the native encryption capabilities. The top-level resource in the SQL Server encryption hierarchy is the Service Master Key, which is encrypted by the Windows Data Protection API and created the first time a newly-created key needs to be encrypted. Second in line is the Database Master Key. This key can be used to create certificates and asymmetric keys. Next come certificates and asymmetric keys. Both can be used to create symmetric keys or encrypt data directly. Last in line are symmetric keys, which can also be used to encrypt data.
Behind the scenes, an encrypted copy of the Database Master Key is stored in the Master database, encrypted by the Service Master Key (this behavior can be changed using the ALTER MASTER KEY statement). Information on keys can be found in the sys.symmetric_keys dynamic management view:
How Do I Create A Database Master Key?
Before creating certificates (assuming the Database Master Key is used to create the certificate) or other keys can be generated, a Database Master Key must be created. Since there is no GUI method for creating one, the syntax for creating one is:
USE <DatabaseName> CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Password>'
How Do I Protect Database Master Keys?
Once a Database Master Key is created, you should back it up to a secure location, preferably offsite. To back up a Database Master Key, use the following syntax:
USE <DatabaseName> BACKUP MASTER KEY TO FILE = '<FolderLocation>' ENCRYPTION BY PASSWORD = '<Password>'
Can I Restore Database Master Keys?
A Database Master Key can be restored if the need arises. When the key is restored, all keys encrypted by the active Master Key are decrypted and re-encrypted using the new Master Key. If your infrastructure utilizes many keys this process can be resource-intensive, so restorations should take place during periods of low server activity when possible. To restore a Database Master Key, use the following syntax:
USE <DatabaseName> RESTORE MASTER KEY FROM FILE = '<FolderLocation>' DECRYPTION BY PASSWORD = '<Password>' ENCRYPTION BY PASSWORD = '<Password>'
Next Steps
- Consider using the encryption hierarchy built into SQL Server to either directly encrypt data or use in tandem with application-based encryption
- Read more about creating Database Master Keys, backing up Database Master Keys, and altering Database Master Keys.
- Read more security tips
About the author
Tim Cullen has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
