Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Service Key clarification Expand / Collapse
Posted Friday, August 17, 2012 8:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, November 17, 2016 2:12 AM
Points: 361, Visits: 1,450
Hi, I am just after some clarification regarding keys. I understand that the Service Master Key is created on installation of a SQL instance, but I am unsure exactly what would happen if you moved a database to another SQL Server without taking a copy and restoring this master key onto the new server first.

Am I correct in thinking that if the database on the first server does not use any encryption, then all will be ok, the new instance will not care about the database and vice versa? I.e you would just be able to restore the database to create it and view the data.
If, however, the database had a table that was encrypted using a key secured by a password, even if you were trying to decrypt this key using the password, because the service key wasn’t the original key that was used to create the key for that table, you wouldn’t be able to view the data. In this case you would have to restore the service key and this would encrypt all existing keys and you could see the data?

Im just confused a bit as I have moved databases around servers quite happily without ever having run into problems. For example, the AdventureWorks samples can be restored easily onto any server without having to use the original service master key first. I take it is because nothing is encrypted in these databases?

Post #1346577
Posted Friday, August 17, 2012 10:34 AM

UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, October 19, 2016 12:41 PM
Points: 1,447, Visits: 3,254
Basically, yes. If you are using encryption or service broker it would make things easier if you had a backup of the service master key to be restored to a new server if you needed to move everything there (or a standby server).

You can re-recreate all of your database master keys and certificates later as well, but the service master key backup will save a lot of time.

The probability of survival is inversely proportional to the angle of arrival.
Post #1346658
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse