SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Master Key Closed After Database Copy
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

elracorey
Starting Member

United Kingdom
30 Posts

Posted - 07/01/2008 :  05:54:52  Show Profile  Reply with Quote
Hi,

A strange problem is occurring and I wonder if anyone has some information on it.

I have password encryption on a field in a database table which works fine using a Master Key. Each morning we perform a database copy (in addition to our standard overnight backup).

Each time the copy is performed we get an error on our web site as follows:

"Please create a master key in the database or open the master key in the session before performing this operation."

This is resolved with the following SQL:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'SQLAuthority'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

But why is this necessary? Is the action of copying causing the Master Key to close? Any comments would be appreciated.

We are using SQL Server 2005 Standard edition SP2 on a Windows Server 2003 platform.

Thanks and Regards.
Lee.



Just because something doesn't do what you planned it to do doesn't mean it's useless.

mcrowley
Aged Yak Warrior

771 Posts

Posted - 07/01/2008 :  12:01:49  Show Profile  Reply with Quote
There are several levels of encryption on SQL Server. First is the Service Master Key, which is a server key. All lower keys are encrypted with this key. Next is a Database Master Key. This key is stored encrypted in the sys.symmetric_keys table in each database that a Master Key has been created in. All Symmetric, and Asymmetric keys keys created in a database are further encrypted with this Master key.

Opening a key means the Database master key is opened, which in turn requires the Service Master Key to be opened to decrypt the Database Master Key, so the user's key can be used.

At a minimum, i would expect the two database servers do not share the same Service Master Key. This can be synchronized with the Backup Service Master Key command, but you should take great care before changing the keys on any server. If you have other data that is encrypted with an old Service Master Key, or any of its descendants, you could lose that data entirely.
Go to Top of Page

elracorey
Starting Member

United Kingdom
30 Posts

Posted - 07/02/2008 :  04:03:53  Show Profile  Reply with Quote
Thank you for this very useful explanation. My worry was that by performing a database copy, as opposed to a backup, this would mean that there would be multiple instances of the same key at server level. The SQL Server would detect this conflict and close the Master Key as part of a security measure, and would need to be re-opened with the OPEN MASTER KEY syntax.
I performed a copy this morning and all was fine, so it may be that my colleague did something quite different. I'll post back if this occurs again.
Thanks.

Just because something doesn't do what you planned it to do doesn't mean it's useless.
Go to Top of Page

mcrowley
Aged Yak Warrior

771 Posts

Posted - 07/02/2008 :  09:24:42  Show Profile  Reply with Quote
Any key called a Master Key will by definition be singular. Just like a backup carries user and role permissions, a database backup/restore will carry its (encrypted) Master Key, rendering the encrypted data unreadable, unless the Service Master Key has not changed.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 2.83 seconds. Powered By: Snitz Forums 2000