May 6, 2009 at 7:43 am
Hi all,
We are facing this critical issue, I need some help from you experts. I ran the encryption and decryption procedures on prod db and created a new master key on prod db in March 2009. It all worked fine. Our client refreshed their test db with prod data last week. Now, after the refresh to the test db last week, we are unable to decrypt the data on test db. I tried dropping and recreating the master key on test, still the decrypt value returns NULL. Both test db and prod db are SQL Server 2005 running on different servers. I'm confident that I'm using the same master key for both. Could there be any other possible issues here??? We are able to decrypt fine on the PROD db. Just the test db, the decrypt returns NULL.
Please give me your suggestions. Thank you very much.
May 6, 2009 at 8:05 am
psangeetha (5/6/2009)
Hi all,We are facing this critical issue, I need some help from you experts. I ran the encryption and decryption procedures on prod db and created a new master key on prod db in March 2009. It all worked fine. Our client refreshed their test db with prod data last week. Now, after the refresh to the test db last week, we are unable to decrypt the data on test db. I tried dropping and recreating the master key on test, still the decrypt value returns NULL. Both test db and prod db are SQL Server 2005 running on different servers. I'm confident that I'm using the same master key for both. Could there be any other possible issues here??? We are able to decrypt fine on the PROD db. Just the test db, the decrypt returns NULL.
Please give me your suggestions. Thank you very much.
I suppose you refreshed the testdb by using Restore database from production backup.
You will need to add the prod db key to your test server
(maybe have a look at my SSC article on Service Broker http://www.sqlservercentral.com/articles/Service+Broker/2897/)
cfr
USE Airline
GO
select *
from sys.certificates
;
select *
from sys.master_key_passwords
;
if not exists ( select MKP.*
, D.name as DbName
from sys.master_key_passwords MKP
inner join sys.credentials C
on MKP.credential_id = C.credential_id
inner join sys.database_recovery_status DRS
on MKP.family_guid = DRS.family_guid
inner join sys.databases D
on DRS.database_id = D.Database_id
Where D.name = 'Airline'
)
begin
Print 'Adding dbmasterkey for Airline';
-- using the same password that has been used the first time !
EXEC sp_control_dbmasterkey_password @db_name = N'Airline',
@password = N'P@ssword', @action = N'add';
end
GO
select MKP.*
, D.name as DbName
from sys.master_key_passwords MKP
inner join sys.credentials C
on MKP.credential_id = C.credential_id
inner join sys.database_recovery_status DRS
on MKP.family_guid = DRS.family_guid
inner join sys.databases D
on DRS.database_id = D.Database_id
;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 6, 2009 at 8:17 am
If you have dropped the master key for that database, and recreated it. Meaning created the same one again, you should take you database online and offline to enable the encryption agian.
May 6, 2009 at 9:23 am
Thanks for the info.
I created the same Master key in test db that I used for the Prod db in March. I bounced the test db after recreating the master key, still returns NULL for the existing encrypted data. This is on my client's server.
I just tried to reproduce the scenario on our local server, we only have one SQL server 2005, so I restored our db to test db and it decrypted fine. But when I drop and recreate the same master key, the decrypt returns NULL for the existing encrypted values even after bouncing test db. I know incase production db crashes and we restore it to a different server and recreate the master key it should work. Am I missing something here?
Also, in our SQL 2005 server, we have different client's test databases and is it possible for these databases to have different master key for every database? I'm thinking the master key will be only one for all the databases in a SQL server.?
Please help. Thanks a lot again
May 6, 2009 at 9:32 am
Which key are you talking about? You need to get your semantics straight.
there is a Service Master Key (SMK) for the instance. Each database has a Database Master Key (DMK), which is different for each databases.
That is what you need to move from production to test. The DMK.
May 6, 2009 at 9:48 am
Steve,
I'm talking about the key below..
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = '*************************************************'
GO
Is this different from SMK? Do I need to find the SMK for Prod db and run it on test db server?
Thanks a lot
May 6, 2009 at 9:53 am
That is the database master key. It needs to be loaded into the server to open that database's keys for access.
So you need to make a backup of this key, and then a restore on the test server.
May 6, 2009 at 9:54 am
Steve,
This is the database master key I ran on the Prod db in March and after the test db restore last week I'm running the same master key on the test db. But the decrypt returns NULL for the existing encrypted data. Could there be any other issues here?
Thanks much
May 6, 2009 at 10:00 am
Is it the same code?
I'd double check that you have the same key, maybe restore it again. Someone could have regenerated something. Or you might need to restore it each time you restore the db.
If you are getting NULL, that sounds strange. Can you open the encryption keys with no issue? Can you encrypt something and decrypt something new on the test db?
May 6, 2009 at 10:01 am
Yes, we are able to encrypt & decrypt new data without any issue. Just the existing encrypted values we are unable to decrypt and it returns NULL.
I just tried backup master key of the prod db:
USE prod
BACKUP MASTER KEY TO FILE = 'c:\bkup'
ENCRYPTION BY PASSWORD = ''
After dropping the certificates and symmetric keys, restored the master key on the test db:
USE test
RESTORE MASTER KEY FROM FILE = 'c:\bkup'
DECRYPTION BY PASSWORD = ''
ENCRYPTION BY PASSWORD = ''
when I run SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101 on both db's I get the same key_GUID.
But the decrypt still returns NULL. I tried bouncing the db too.
Please help thanks a lot
May 6, 2009 at 12:17 pm
Any ideas/suggestions, please?
It is very strange that the decrypt is not working after restoring the same master key and bouncing the db. I would think this is a very basic scenario when there is a db crash when we restore the db on a different server and try to decrypt, it will fail too..
Please give me your suggestions. Thanks all
May 6, 2009 at 2:57 pm
You can't drop the certificates and symmetric keys.
You need to slow down and carefully understand what is happening. When you create encryption, you
1. Create DMK
2. Create certificates/asymm key protected by DMK
3. create symmetric keys protected by cert/asymm key
4. Encrypt data with keys in #3.
when you restore this database to another instance.
1. restore db
2. restore DMK to this instance, protect by that instances SMK
3. use your normal code to decrypt data by opening DMK, opening asymm key, opening symm key.
From what you're describing, it's hard to tell which keys you are moving, restoring, deleting, etc. This stuff gets tricky, so you need to be careful about not only what you do, but how you talk about it.
You should be able to restore again to your client, then ONLY restore the DMK.
May 6, 2009 at 6:12 pm
Steve,
Thanks for the reply. Below is what I did on the Prod db:
1. Created Master Key encryption by password.
2. Create Certificates
3. Create Symmetric keys encryption by certificates.
Client restored the test db with the Prod data last week. For some reason, developer was getting error 'No master key found' when tried to encrypt the data. So, first tried to drop master key, but was unable to drop unless we drop the symmetric keys and the certificates. Then followed the same 3 steps above and created the master key, certificates, symmetric keys on the test db with the same password that was used in the prod db. As you said, I'll try to just restore the master key and see if it will work.
But, is there a reason why it wont work if we re-create the certificates and symmetric keys as well?? Because, when the database crashes and we restore the database, dont we have to re-create all of them?
Thanks a lot for your help. I really appreciate it.
May 7, 2009 at 12:33 am
In your OP you state you are running SQL 2005.
Can you post the version info?
Select @@version
Can you also post the result of this ?
select MKP.*
, D.name as DbName
from sys.master_key_passwords MKP
inner join sys.credentials C
on MKP.credential_id = C.credential_id
inner join sys.database_recovery_status DRS
on MKP.family_guid = DRS.family_guid
inner join sys.databases D
on DRS.database_id = D.Database_id
;
I'll be using this art.to make a repro of your situation.
Laurentiu Cristofor's blog http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx
For the moment I'm at the point whe I restored the db on a second server and get the execution error:
Msg 15581, Level 16, State 3, Line 8
Please create a master key in the database or open the master key in the session before performing this operation.
I'll be back 😉
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 7, 2009 at 12:59 am
Please test this scenario (based on my previous post).
In my test it works on the second server.
edited this warning from BOL topic "ALTER MASTER KEY"
The REGENERATE option re-creates the database master key and all the keys it protects. The keys are first decrypted with the old master key, and then encrypted with the new master key. This resource-intensive operation should be scheduled during a period of low demand, unless the master key has been compromised.
use master
go
RESTORE DATABASE [TestEncryption3]
FROM DISK = N'X:\MSSQL.1\MSSQL\Backup\TestEncryption3.bak'
WITH FILE = 1
, MOVE N'TestEncryption3' TO N'X:\MSSQL.1\MSSQL\Data\TestEncryption3.mdf'
, MOVE N'TestEncryption3_log' TO N'X:\MSSQL.1\MSSQL\Data\TestEncryption3_1.LDF'
, NOUNLOAD, STATS = 10
GO
-- Create two logins that will be used for this demo
-- They are logins for two doctors in a clinic
--
create login Doc1 with password = 'Yukon90!'
create login Doc2 with password = 'Yukon90!'
go
-- Switch to the clinic database
--
use TestEncryption3
-- Create users for the doctors
--
sp_change_users_login @Action = 'Update_One',@UserNamePattern = 'Doc1', @LoginName = 'Doc1'
go
--*** opgelet : exec stmt staat in commentaar !!! ***
sp_change_users_login @Action = 'Update_One',@UserNamePattern = 'Doc2', @LoginName = 'Doc2'
go
---------------------------------
-- Simulate connecting as Doctor1
--
execute as login = 'Doc1'
-- Open the relevant key for usage
--
open symmetric key Doc1Key
DECRYPTION BY certificate Doc1Cert
-- Select from the table including decrypting from
-- encrypted columns
--
select
Id,
name,
Docname,
convert (varchar, decryptbykey(SSN)) as SSN,
convert (varchar, decryptbykey(Problem)) as Ailment
from PatientTable
/*
Msg 15581, Level 16, State 3, Line 8
Please create a master key in the database or open the master key in the session before performing this operation.
*/
-- Simulate logging out
--
close all symmetric keys
go
revert
go
print 'Is this you sitiation you are in now ?'
go
create master key
ENCRYPTION BY password = 'TestEncryption31'
go
/*
Msg 15578, Level 16, State 1, Line 1
There is already a master key in the database. Please drop it before performing this statement.
*/
Alter master key
ADD ENCRYPTION BY PASSWORD = 'TestEncryption31_TestServerDB'
/*
Msg 15581, Level 16, State 3, Line 1
Please create a master key in the database or open the master key in the session before performing this operation.
*/
go
ALTER MASTER KEY
REGENERATE WITH ENCRYPTION BY PASSWORD = 'TestEncryption31TEST';
/*
Msg 15329, Level 16, State 20, Line 1
The current master key cannot be decrypted. If this is a database master key, you should attempt to open it in the session before performing this operation. The FORCE option can be used to ignore this error and continue the operation but the data encrypted by the old master key will be lost.
*/
GO
Print 'Do not execute the following commented code because then you will not get to see the encrypted data !! '
--Alter master key
--FORCE REGENERATE WITH ENCRYPTION BY PASSWORD = 'TestEncryption31'
--/*
--The current master key cannot be decrypted. The error was ignored because the FORCE option was specified.
--*/
--go
-----------------------------------
---- Simulate connecting as Doctor1
----
--execute as login = 'Doc1'
---- Open the relevant key for usage
----
--open symmetric key Doc1Key
--DECRYPTION BY certificate Doc1Cert
---- Select from the table including decrypting from
---- encrypted columns
----
--select
-- Id,
-- name,
-- Docname,
-- convert (varchar, decryptbykey(SSN)) as SSN,
-- convert (varchar, decryptbykey(Problem)) as Ailment
--from PatientTable
--/*
--Msg 15581, Level 16, State 3, Line 8
--Please create a master key in the database or open the master key in the session before performing this operation.
--*/
---- Simulate logging out
----
--close all symmetric keys
--revert
--go
--/*
--Msg 15466, Level 16, State 1, Line 8
--An error occurred during decryption.
--*/
--print 'All encrypted data lost'
go
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'TestEncryption31' ; -- old db master key
ALTER MASTER KEY
REGENERATE WITH ENCRYPTION BY PASSWORD = 'TestEncryption31TEST'; -- renew db master key
go
---------------------------------
-- Simulate connecting as Doctor1
--
execute as login = 'Doc1'
-- Open the relevant key for usage
--
open symmetric key Doc1Key
DECRYPTION BY certificate Doc1Cert
-- Select from the table including decrypting from
-- encrypted columns
--
select
Id,
name,
Docname,
convert (varchar, decryptbykey(SSN)) as SSN,
convert (varchar, decryptbykey(Problem)) as Ailment
from PatientTable
/*
Id name Docname SSN Ailment
----------- ------------------------------ -------------------- ------------------------------ ------------------------------
1 Alice Doc1 111-11-1111 Migraine
2 Bob Doc1 222-22-2222 Stomach Ache
3 Charles Doc1 333-33-3333 Sore throat
4 Dave Doc2 NULL NULL
5 Emily Doc2 NULL NULL
6 Frank Doc2 NULL NULL
7 George Doc1 or Doc2 NULL NULL
(7 row(s) affected)
*/
-- Simulate logging out
--
close all symmetric keys
go
revert
go
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply