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/
