Managing SQL Server 2005 Master Keys for Encryption

By: Tim Cullen   |   Read Comments (7)   |   Related Tips: More > 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 2005:

• Database Master Keys
• Certificates
• Symmetric and Asymmetric Keys
• Putting It All Together-Using Data Encryption in the Production Environment

Solution
SQL Server 2005 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.

Source - SQL Server 2005 Books Online

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 2005 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
• Read an IT showcase paper on improving data security using SQL Server 2005
• Stay tuned for more Data Encryption tips on MSSQLTIPS.com

Last Update: 8/20/2007

About the author

Tim has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer.

View all my tips

We Recommend

• More Database Developer Tips...

Comments and Feedback:

Tuesday, October 28, 2008 - 8:59:58 AM - lacorrigan

Read The Tip

I pretty much follow these concepts.  I am having an additional problem in that we have 4 environments, Dev, QA, UAT and Production. I am not real clear as to what happens or what should happen to the keys and encryption when an encrypted database is moved from one environment to another.  I have initial tried this and once moved to a different server I can't properly decrypted the data.  I don't really know whether I should use the same keys in all environments or different keys and if so how to make that work moving these databases around.  I have learned it is very easy to loose your encrypted data.  Any help would be greatly appreciated.  Cheers!

Wednesday, April 18, 2012 - 6:59:34 AM - EC	Read The Tip
My company uses moble team to enroll  client in the remote villages. i want to encrypt the database on the labtop.(TDE) i follow the neccessay steps but when i try to backup the master key. i received an error: You do not have permission or master key don't exit. After create the maskey and the ceritificates i check the encryption state it was:3

Wednesday, April 18, 2012 - 8:52:09 AM - Tim Cullen	Read The Tip
Good morning, EC:   A couple of questions:   1) Is the connection pointing to the correct database (I do that all the time)? 2) Are you a member of the sysadmin group in the SQL instance? 3) Are you a member of the db_owner group in the database?

Wednesday, April 18, 2012 - 11:38:58 AM - EC	Read The Tip
The database is locally on my labtop. and i login with sa account.

Thursday, April 26, 2012 - 3:30:29 PM - Charles	Read The Tip
is it possible via the database to retrieve the creation date of the service master key and the database master key? thank you in advance

Thursday, April 26, 2012 - 4:29:01 PM - Tim Cullen	Read The Tip
You can query the sys.symmetric_keys:   SELECT [name] AS KeyName , symmetric_key_id AS KeyID , key_algorithm AS KeyAlgorithm , create_date AS DateCreated , modify_date AS LastModified FROM sys.symmetric_keys WITH (NOLOCK)

Monday, February 25, 2013 - 11:53:17 AM - CPR

Read The Tip

Hi Tim, I have question regarding encryption key that i have already in my production server.. Right now if I want to reset password for user with  sql authentication and I’m using 5 character encrypted password and it's giving me error for windows policy.. my question is that can i make new encrypted password with 8 character? I have right now Symmetric keys on my master DB. Name: ##MS_ServiceMasterKey##, Principal _id:1, Symmetric_key_id:102,Key_lenght:128,Key_algorithm:D3,algorithm_desc:TRPLE_DEC then it's create date and modify date..

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Find and fix SQL Server problems before they happen - SQL diagnostic manager now with predictive analysis! SQL Developer Bundle: Cut out dull work with 12 tools for simpler, faster database development. Free trial What grade do you think your SQL Servers get? Find out with a SQL Server Health Check consultant in the USA. In two mouse clicks view SQL bottlenecks. With ZERO impact pinpoint all poor performing SQL with 100% accuracy. Free Webinar - Making the most out of SQL Server Agent with SQL Server MVP Jeremy Kadlec