Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Service Key clarification


Service Key clarification

Author
Message
Maddave
Maddave
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 1462
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.
sturner
sturner
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1475 Visits: 3254
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search