Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
elracorey
Starting Member
30 Posts |
 Posted - 2008-07-01 : 05:54:52
|
| 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 KEYBut 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 - 2008-07-01 : 12:01:49
|
| 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. |
 |
|
|
elracorey
Starting Member
30 Posts |
Posted - 2008-07-02 : 04:03:53
|
| 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. |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2008-07-02 : 09:24:42
|
| 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. |
|
|
|
|
|
|
|
|
Master Key Closed After Database Copy