Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Encryption Key Management between Dev, Test and Prod


By:   |   Last Updated: 2009-02-17   |   Comments (12)   |   Related Tips: More > 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:

Error message encountered when dropping an object used for encryption

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


Last Updated: 2009-02-17


get scripts

next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Sunday, January 05, 2014 - 7:25:13 AM - Menaka Back To Top

I tested this and try to decrypt the data using recreated Symmetric key. But I can't.

My Steps:

USE database

GO

 

Create Symmetric Key symA

WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE PAN_CertificateA

GO

 

GRANT VIEW DEFINITION ON SYMMETRIC KEY::symA TO roleEncrypt;
GRANT VIEW DEFINITION ON SYMMETRIC KEY::symA TO roleDecrypt;
GO

-- Grant roles to users

--

 

--Insert the data using symA

--

 

-- Decrypt the data using SymA

--

 

--Create 2nd 'Symmetric Key : symB

-- Grant permission

-- Insert data using 2nd Symmetric Key

--Decrypt the data using 2nd Symmetric Key

 

-- Now I dropped the 2nd Symmetric key : symB

--

DROP SYMMETRIC KEY symB;
GO

 

-- recreate the 2nd Symmetric key with same name + same Certificate

--

Create Symmetric Key symA

WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE PAN_CertificateA

GO

 

-- grant permission

-- then tried to Decrypt the data. BUT its not decrypting. Could you please lte me know what I have wrong?

 

Many thanks

Menaka

 

 

 

 


Thursday, April 11, 2013 - 12:33:02 PM - Patti Back To Top

In essence did you company decide to keep 3 different keys or 1 key that is shared among prod, test and dev? 


Wednesday, December 14, 2011 - 2:46:33 PM - dbaduck Back To Top

The master key password as well as the Private Key backup of the certificate are key pieces of the pie when dealing with TDE.  These are just as important as sa passwords and any other secrets that you keep from everyone in the world.  When you protect them you don't have to recreate the hierarchy and you can move the databases from one instance to another and gain access to them by keeping the Security Hierarchy intact.


Wednesday, December 14, 2011 - 1:45:22 PM - Edward Back To Top

Oh and I read a blurb somewhere, I believe in an MSDN forum thread, that the approach that dbaDuck recommends to restoring a database using encryption is the MS recommended method.  I found out the hard way when I restored a database to  a different server and tried to give a demo to a manager.  I found out you have to open the Database Master Key using the password to resync it with the Service Master key. Unfortunately I had long before forgotten the password since it was not used to carry out encryption, so I was required to implement everything from scratch like you outlined in your article.


Wednesday, December 14, 2011 - 1:39:16 PM - Edward Back To Top

"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."

We had that question this morning.  We found through experimentation that the minimum rights we needed to grant were "Control" rights on the certificate and "References" rights on the symmetric key.  We did not need "Control" on the symmetric key.  Why would a user require "View Definition"  We had no desire for thge users to be able to view the definition and they were able to decrypt the data just fine.  We felt no need for users to encrypt data as that was handled by the user in the db_owner role who had all the rights needed to create and use theencryption tools. 


Saturday, August 13, 2011 - 11:16:55 AM - Tim Cullen Back To Top

Sudheer:

Let me do some testing this weekend and I'll get an answer for you.

Tim


Friday, August 12, 2011 - 12:21:39 PM - Sudheer Back To Top

I have a basic question. Once the database is restored and we drop and recreate the the keys, certificatates and db master keys or even for that matter as per the new suggestion just rebuild the keys using the new Server Master key, will it try to encrypt all the underlysing data. WIll it affect the performance of the system or will it take longer to bring a table online if it has millions of records?


Tuesday, March 03, 2009 - 7:01:55 PM - dbaduck Back To Top

The Service Master key encrypts the Database Master Key, so when you open it with the password and close it the current Service Master key of the new instance will re-encrypt it.


Tuesday, March 03, 2009 - 5:45:26 PM - timothyrcullen Back To Top

I'll have to give a try.  My understanding was that the Service Master Key was specific to each SQL instance.  I was moving the databases from one instance to another.  Thanks for the heads up-will let you know what I find,


Tuesday, March 03, 2009 - 5:30:56 PM - dbaduck Back To Top

That is how I handle it in our environment.  This is especially handy when I restore a database.  Because the password should be pretty strong, even outrageous and secured, this keeps you from having to recreate all the heirarchy.


Tuesday, March 03, 2009 - 4:59:52 PM - timothyrcullen Back To Top

I'll have to give that a try and will report back.  Is this something you've had success with in your environment? 


Tuesday, March 03, 2009 - 7:27:35 AM - dbaduck Back To Top

Why wouldn't you just Open the database master key with the password and then close it?  This would allow the Service Master Key to encrypt all the keys below the Master key and you would not have to reassign permissions or drop any keys.

Otherwise, this could get pretty complex fast with a complex heirarchy.


Learn more about SQL Server tools