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?