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.
1: USE master;
2: GO
3:
4: CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyStrongPassword';
5: go
6:
7: BACKUP MASTER KEY TO FILE = 'D:\MasterKey.dat' ENCRYPTION BY PASSWORD = 'MyStrongPassword';
8: go
9:
10: CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
11: go
12:
13: BACKUP CERTIFICATE MyServerCert TO FILE = 'D:\MyCert.dat'
14: WITH PRIVATE KEY ( FILE = 'D:\PrivKey.dat' ,
15: ENCRYPTION BY PASSWORD = 'MyStrongPassword' );
16: GO
17:
18: USE AdventureWorks2008R2;
19: GO
20:
21: CREATE DATABASE ENCRYPTION KEY
22: WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
23: GO
24:
25: ALTER DATABASE AdventureWorks2008R2
26: SET ENCRYPTION ON;
27: GO
So there is nothing wrong with the encryption scripts. I next checked for the script which was being used by the customer during restore.
1: Restore master key from File = '\\win2k3dc-alh16t\E$\MasterKey.dat'
2: decryption by password = 'MyStrongPassword'
3: encryption by password = 'MyStrongPassword'
4: go
5:
6: CREATE CERTIFICATE MYServCert
7: From File = '\\win2k3dc-alh16t\E$\MyCert.dat'
8: With private key (file ='\\win2k3dc-alh16t\E$\PrivKey.dat',
9: decryption by password = 'MyStrongPassword')
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…
1: restore database AdventureWorks2008R2
2: from disk = '\\win2k3dc-alh16t\E$\AdventureWorks2008.bak'
3: with move 'AdventureWorks2008R2_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Advworks2008.mdf',
4: move 'AdventureWorks2008R2_log' To 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Advworks2008_log.ldf',
5: move 'FileStreamDocuments2008R2' To 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FSDATA'
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.
1: OPEN MASTER KEY DECRYPTION BY PASSWORD = 'MyStrongPassword'
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
1: ALTER MASTER KEY
2: ADD ENCRYPTION BY SERVICE MASTER KEY
SQLUNINTERRUPTED 