Error 15581 – Please create a master key in the database or open the master key in the session before performing this operation
Posted by Sourabh Agarwal on March 26, 2012
Recently I was working with a client, when a unique issue was brought to my notice. As per the client, when they are tying to restore a TDE enabled database to another server, they cannot perform the restore using SSMS User interface. They were able to restore the database using T-SQL though.
Now this sounded a bit funny, but then the customer showed it to me. Since I did not have any answers at that time, I decided to try it out later and check and see if anything they are doing is wrong or not.
To begin with, I checked their scripts for enabling Transparent Database Encryption on the databases and also their backup scripts for the master key and certificates.
Sample code for the screen provided by the customer is provided below.
So there is nothing wrong with the encryption scripts. I next checked for the script which was being used by the customer during restore.
So the Master key and the certificate are in place. When we try to restore the database using the SSMS, it throws an error.
Please create a master key in the database or open the master key in the session before performing this operation.
RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 15581)
The same error would come from a T-SQL window, if we try to restore the database using the command below…
This also generates the same error as above.
This error simply means that we need to open the Master key in the same session (server process id) as the restore operation. If I run the below command, and then perform the restore, there is no error and the Database restore is successful.
When restoring using SSMS, since there is no provision for opening the master key, restore operation fails to decrypt the encrypted files and hence fails.
The same error happens, if you try to backup an encrypted database using SSMS. This error would also be generated, if we try to backup/restore the database from a T-SQL window, which does not have the OPEN MASTER KEY command.
In order for the Master key to be used by all the session, it needs to be encrypted by the Service Master Key. When the Database master key is encrypted by the Service Master Key, it can be automatically opened and closed, and we would not need to specify any OPEN MASTER KEY command.
In the above script for restoring the master key and the restoring the Certificate, we would need to add an additional command for