August 17, 2012 at 8:31 am
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?
Thanks.
August 17, 2012 at 10:34 am
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply