StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The database master key is encrypted using the server master key, which is specific to the machine where SQL Server is installed. When you move the database to another server, you lose the ability to automatically decrypt and open the database master key because the local server key will most likely be different. If you can't decrypt the database master key, you can't decrypt anything else that depends on it (certificates, symmetric keys, etc).</p> <p>Basically, you want to re-encrypt the database master key against the new server key, which can be done with this script (using admin privileges):</p> <pre><code>-- Reset database master key for server (if database was restored from backups on another server) OPEN MASTER KEY DECRYPTION BY PASSWORD = '---your database master key password---' ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY GO </code></pre> <p>Note that when you create a database master key, you should always provide a password as well so that you can open the key using the password in the scenario where the service master key cannot be used - hopefully you've got that password stored somewhere!</p> <p>Alternatively, you can restore a backup of the database master key - but you need one that was created for the target server, not the source server.</p> <p>If you haven't got either a backup or a password, then I'm not sure you will be able to recover the encrypted data on the new server, as you will have to drop and recreate the database master key with a new password, which will kill any dependent keys and data.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload