SQLUNINTERRUPTED

I am just a medium, SQL Server the Goal

Category Archives: Security

Error 15581 – Please create a master key in the database or open the master key in the session before performing this operation

3 Comments Posted by 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.

   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

Advertisement
Security, SQL Engine, SQL Server , ,

What’s new SQL Server 2012–Server Level Auditing For all Editions

Leave a comment Posted by on March 17, 2012

During my customer visits and over other offline interactions I have had with customer, one thing which they have always complained about is the fact that Server Level auditing in not available for other editions of SQL Server, except for the Enterprise, Developer and Evaluation Edition.

Now there is good news. With SQL Server 2012, Server level Auditing is now available for all editions of SQL Server. Database level auditing still remains limited to Enterprise, Developer and Evaluation Editions.

In addition to extending the Server level auditing to all editions, there are have been certain enhancements to the Auditing options.

  1. The logging is more resilient to failures. For example, if the target log is on a network share, and if the network connection fails, SQL Server audit would restart when the network connectivity is restored.
  2. A new Fail_Operations option has been added to On_Failure clause. With this new operations the operations which invoke the Audit, would be failed if the Audit cannot be written to be log. As mentioned above, if the write failure condition is resolved, the operations would continue to work without problems.
  3. A new MAX_Files options has been added to the Audits. Earlier either the no of log written were unlimited (limited by the max size option or the space on the destination directory) or the number of logs were regulated by the Max_Rollover_Files option. Now, we can define a limit on the maximum number of files to be created.
  4. A new option Predicate Expression has been added to the Create Server Audit command. These predicate expressions are used to determine if the Audit needs to be invoked or not. The Predicate Expressions can be specified in a where clause in the Create Server Audit command.
Security, SQL Engine, SQL Server , ,