Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Decrypt issue Expand / Collapse
Author
Message
Posted Wednesday, May 6, 2009 7:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 1:54 PM
Points: 321, Visits: 760
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.
Post #711070
Posted Wednesday, May 6, 2009 8:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:42 AM
Points: 6,743, Visits: 8,515
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- 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
Post #711098
Posted Wednesday, May 6, 2009 8:17 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 6:15 AM
Points: 485, Visits: 11,021
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.

Post #711116
Posted Wednesday, May 6, 2009 9:23 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 1:54 PM
Points: 321, Visits: 760
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
Post #711215
Posted Wednesday, May 6, 2009 9:32 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:17 AM
Points: 31,278, Visits: 15,731
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #711226
Posted Wednesday, May 6, 2009 9:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 1:54 PM
Points: 321, Visits: 760
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
Post #711240
Posted Wednesday, May 6, 2009 9:53 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:17 AM
Points: 31,278, Visits: 15,731
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.








Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #711244
Posted Wednesday, May 6, 2009 9:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 1:54 PM
Points: 321, Visits: 760
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
Post #711245
Posted Wednesday, May 6, 2009 10:00 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:17 AM
Points: 31,278, Visits: 15,731
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?







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #711251
Posted Wednesday, May 6, 2009 10:01 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 1:54 PM
Points: 321, Visits: 760
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
Post #711253
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse