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:
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
- Open master key decryption by password
- If database is write-able
- ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
- 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
- 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 - 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/