SQL Server – Log Shipping – Error Message – “Please Create a master key”

Background

Assessing our database recover-ability and noted that encrypted data is not accessible on our Log Shipping secondary database.

Let us see if there are steps that we can take to re-mediate.

 

Code – Open Symmetric Key


use [DBLab]
go

set nocount on;
set ansi_padding on;
go 

/*
   select * from sys.certificates tblSC
   select * from sys.symmetric_keys tblSK
*/

OPEN SYMMETRIC KEY [smKey]
   DECRYPTION BY CERTIFICATE  [encCert];


if exists
	(
		select *
		from   sys.openkeys	tblSOK
		where  tblSOK.key_name = 'smKey'
	)
begin

	CLOSE SYMMETRIC KEY [smKey];

end

Error Message

Here is the actual error message.

Image:

errorMessage

Text:

Msg 15581, Level 16, State 3, Line 14
Please create a master key in the database or open the master key in the session before 
performing this operation

Remediation

Outline Steps

  1. Open master key decryption by password
  2. If database is write-able
    • ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
  3. Else, if database is read-only (Log Shipping – Secondary / DB Mirroring – Snapshot
    • Apply exec master.dbo.sp_control_dbmasterkey_password

 

Code

Snippet

Read / Write

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'masterkeypassword'; 
 
    if (@@Error = 0)
    begin
 
	ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY; 

        CLOSE MASTER KEY;

    end

 

Read Only


 declare @dbname   sysname
 declare @password sysname
 declare @type     sysname
 
 set @dbName = db_name();
 set @password = 'masterkeypassword';
 set @type = 'add';
 
 OPEN MASTER KEY DECRYPTION BY PASSWORD = 'masterkeypassword'; 
 
    if (@@Error = 0)
    begin
		
	 exec master.dbo.sp_control_dbmasterkey_password 
 
                      @db_name = @dbname
                    , @password = @password
                    , @type = @type

	CLOSE MASTER KEY;
		
   end

Programmatic



set noexec off;
set nocount on;
set ansi_padding on;
go
 
 
use [HRDB]
go
 
if (db_name() != 'HRDB')
begin
 
    set noexec on
 
end
 
while (@@trancount > 0)
begin
 
    print 'rollback ';
    rollback;
 
end
go
 
 
declare @dbname                 sysname
declare @passwordNew            sysname
declare @passwordPrevious		sysname
declare @typeAdd                sysname
declare @typeDrop               sysname
declare @iDBMasterKeyPasswords  int
declare @bCommit                bit
declare @bOverwriteDBMasterKey  bit
declare @sqlFormatOpenMasterKey varchar(60)
declare @sql                    varchar(600)

declare @CHAR_TAB				varchar(30)

set @iDBMasterKeyPasswords = -1
set @dbname = db_name()
set @passwordNew = 'complexPassword'
set @passwordPrevious = 'masterkeypassword';
set @typeAdd     = 'add'
set @typeDrop     = 'drop'
set @bCommit  = 1
set @bOverwriteDBMasterKey = 0
set @sqlFormatOpenMasterKey = 'OPEN MASTER KEY DECRYPTION BY PASSWORD = ''%s'' ' 
 
set @CHAR_TAB = char(9)

begin tran
 
    /*
        If wrong password entered, you will get the message listed below:
            Msg 15313, Level 16, State 1, Line 27
            The key is not encrypted using the specified decryptor.
    */
    -- temp fix
    --set @sqlFormatOpenMasterKey = 'OPEN MASTER KEY DECRYPTION BY PASSWORD = ' + ''' + @password + '''
	exec master.dbo.xp_sprintf @sql output, @sqlFormatOpenMasterKey, @passwordNew

	print @sql

	exec (@sql)

    if (@@Error = 0)
    begin
 
        /*
            permanent fix for writable db:
 
            If applied to read-only db
                Msg 3906, Level 16, State 1, Line 38
                Failed to update database "DBLab" because the database is read-only.
 
        */ 
        if exists
            (
                select *
         
                from   sys.databases tblSD
 
                where  tblSD.[name] = db_name()
 
                and    (
 
                            -- Is not Readonly
                            (tblSD.is_read_only = 0)
                         
                            -- StandBy
                            --         Log Shipping 
                        and   (tblSD.is_in_standby != 1)
 
                        -- Not Shapshot
                        and (tblSD.source_database_id is null)
 
                     )
            )
        begin
 
            print 'ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY ....'
 
                ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY; 
 
            print 'ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY'
 
        end
        else if exists
            (
                select *
         
                from   sys.databases tblSD
 
                where  tblSD.[name] = db_name()
 
                and    (
 
                         -- Is not Readonly
                         (tblSD.is_read_only = 0)
 
                        -- StandBy
                        -- Log Shipping 
                        --or   (tblSD.is_in_standby != 1)
 
                        -- Not Shapshot
                        or (tblSD.source_database_id is null)
 
                    )
            )
        begin
 
 
            select
                  source = 'Before - master.sys.master_key_passwords'
                , [sqlInstance] = cast(serverproperty('servername') as sysname)
                , [database] = db_name()
                , [credentialName]
                    = tblSC.name
                , [credentialIdentity]
                    = tblSC.credential_identity
                , [credentialDateCreatedDate]
                        = tblSC.[create_date]
 
            from   master.sys.master_key_passwords tblSMKP
 
                inner join sys.credentials tblSC
 
                   on tblSMKP.credential_id = tblSC.credential_id
 
                inner join master.sys.database_recovery_status tblSDRS
 
                   ON tblSMKP.family_guid = tblSDRS.family_guid
 
                inner join master.sys.databases tblSD
 
                  ON tblSDRS.database_id = tblSD.database_id
 
            set @iDBMasterKeyPasswords = @@rowcount


            if (@iDBMasterKeyPasswords > 0)
            begin

				if (@bOverwriteDBMasterKey =1 )
				begin

					print 'master.dbo.sp_control_dbmasterkey_password  ....Dropping '

			            exec master.dbo.sp_control_dbmasterkey_password 
	 
						  @db_name = @dbname
						, @password = @passwordPrevious
						, @type = @typeDrop

					print 'master.dbo.sp_control_dbmasterkey_password Dropped '

					/*
						Go back and revise Master Key Count
					*/
					select
						  source = 'Before - master.sys.master_key_passwords'
						, [sqlInstance] = cast(serverproperty('servername') as sysname)
						, [database] = db_name()
						, [credentialName]
							= tblSC.name
						, [credentialIdentity]
							= tblSC.credential_identity
						, [credentialDateCreatedDate]
								= tblSC.[create_date]
		 
					from   master.sys.master_key_passwords tblSMKP
		 
						inner join sys.credentials tblSC
		 
						   on tblSMKP.credential_id = tblSC.credential_id
		 
						inner join master.sys.database_recovery_status tblSDRS
		 
						   ON tblSMKP.family_guid = tblSDRS.family_guid
		 
						inner join master.sys.databases tblSD
		 
						  ON tblSDRS.database_id = tblSD.database_id
		 
					set @iDBMasterKeyPasswords = @@rowcount

				end

			end 
 
            if (@iDBMasterKeyPasswords = 0)
            begin
 
                print 'master.dbo.sp_control_dbmasterkey_password  - Add'
 
                exec master.dbo.sp_control_dbmasterkey_password 
 
                      @db_name = @dbname
                    , @password = @passwordNew
                    , @type = @typeAdd
 
                 print 'master.dbo.sp_control_dbmasterkey_password  - Added'
 
                select
                    source = 'After - master.sys.master_key_passwords'
                  , [sqlInstance] = cast(serverproperty('servername') as sysname)
                  , [database] = db_name()
                  , [credentialName]
                    = tblSC.name
                  , [credentialIdentity]
                        = tblSC.credential_identity
                  , [credentialDateCreatedDate]
                    = tblSC.[create_date]
 
                from   master.sys.master_key_passwords tblSMKP
 
                    inner join sys.credentials tblSC
 
                       on tblSMKP.credential_id = tblSC.credential_id
 
                    inner join master.sys.database_recovery_status tblSDRS
 
                       ON tblSMKP.family_guid = tblSDRS.family_guid
 
                    inner join master.sys.databases tblSD
 
                      ON tblSDRS.database_id = tblSD.database_id
 
                print 'master.dbo.sp_control_dbmasterkey_password'
 
            end -- master key password ( count )

            else if (@iDBMasterKeyPasswords > 0)
            begin
 
                print @CHAR_TAB + 'Master Key already exists!  '
				print @CHAR_TAB + 'Please drop Database Master Key if you will like to replace'

			end
 
        end
        else
        begin
 
            print 'Permanent Fix can not be applied'
 
        end
 
        print 'Open Master Key succeded'
 
		OPEN SYMMETRIC KEY [keyLab]
			DECRYPTION BY CERTIFICATE [certLAB];
 
 
        if exists
            (
                select *
                from   sys.openkeys tblSOK
                where  tblSOK.key_name = 'keyLab'
            )
        begin
 
           	CLOSE SYMMETRIC KEY [keyLab]
 
        end
 
    end -- if (@@Error = 0)
    else
    begin
 
        print 'Open Master Key failed!'
 
    end
 
if (isNull(@bCommit, 0) = 0)
begin
 
    print 'Rollback Tran'

    rollback tran;
     
end
else
begin
 
    print 'Commit Tran'

    commit tran;
     
end
go
 
set noexec off;



Advisory

Please treat encryption key management with utmost care.

Save passwords and scripts.

It is not quite easy to recover from bad key management as the original and applied keys are tantamount when applying to new environments and replacing existing keys.

 

References

  1. MSDN Blogs > Laurentiu Cristofor’s blog @microsoft.com > SQL Server 2005: using symmetric keys to encrypt data ( By Laurentiu Cristofor )
    SQL Server 2005: using symmetric keys to encrypt data
    http://blogs.msdn.com/b/lcris/archive/2005/10/14/sql-server-2005-using-symmetric-keys-to-encrypt-data.aspx
  2. HOW TO: DECRYPT SQL 2005/2008 DATABASE MASTER KEYS ON OTHER SERVERS ( By Tatham Oddie )
    http://blog.tatham.oddie.com.au/2008/10/28/how-to-decrypt-sql-20052008-database-master-keys-on-other-servers/

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s