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 12:17 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 1:54 PM
Points: 321, Visits: 760
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
Post #711397
Posted Wednesday, May 6, 2009 2:57 PM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 7:12 AM
Points: 31,284, Visits: 15,746
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #711592
Posted Wednesday, May 6, 2009 6:12 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 1:54 PM
Points: 321, Visits: 760
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.
Post #711699
Posted Thursday, May 7, 2009 12:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:46 PM
Points: 6,743, Visits: 8,516
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


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 #711789
Posted Thursday, May 7, 2009 12:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:46 PM
Points: 6,743, Visits: 8,516
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


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 #711800
Posted Thursday, May 7, 2009 9:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 1:54 PM
Points: 321, Visits: 760
Alzdba, Thanks for your response.

Here is the output:

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
;

No rows returned.

Select @@version

Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)


When I restore the db, the decryption works fine. But when I drop the Master key and recreate the key it doesnt work, returns NULL. Incase a data corruption occurs or the certificates/symmetric keys/master key gets dropped by mistake, we want to provide our client with an approach for the decryption. I'm trying to reproduce the scenario on our SQl 2005 database, after dropping symmetric keys, certificates, master key on the restored db, I restored the master key from the Prod db then created certificates and symmetric keys. I opened the symmetric key and the master key in the session and the decrypt output returns NULL for the existing values. The application team wants to know if it is possible to decrypt the existing data when the keys are dropped & restored.

Please give me your suggestions. Sorry if I'm confusing this.
Post #712156
Posted Thursday, May 7, 2009 3:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:46 PM
Points: 6,743, Visits: 8,516
As shown in my previous reply a password encrypted master key can only be modified using the path of:
 
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

If someone drops this master key, it has to be intentionally because the system will state it is in use, so the using certificates will have to be removed !

As shown above, it is fairly easy to modify the master key, so you cannot rely on an known password.

So using BACKUP MASTER KEY will be a life saver
(from BOL)
BACKUP MASTER KEY TO FILE = 'path_to_file' 
ENCRYPTION BY PASSWORD = 'password'


This should also be done for all certificates of the db using

(from BOL)
BACKUP CERTIFICATE certname TO FILE = 'path_to_file'
[ WITH PRIVATE KEY
(
FILE = 'path_to_private_key_file' ,
ENCRYPTION BY PASSWORD = 'encryption_password'
[ , DECRYPTION BY PASSWORD = 'decryption_password' ]
)
]

Off course, you can always rely on the regular backup scenarios !
Advise full recovery model with a sequence of full and log backups, so you can perform a PIT (point in time) recovery !

You can make if fool proof, but they will come up with an even better fool !


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 #712468
Posted Friday, May 8, 2009 9:15 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 1:54 PM
Points: 321, Visits: 760
Thanks for all your help. I appreciate it.
Post #713046
Posted Wednesday, May 13, 2009 8:07 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 1:54 PM
Points: 321, Visits: 760
HI all,

I just have this one question in the backup command if the password should be the same as used in the master key creation or can it be different?

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

BACKUP MASTER KEY TO FILE = ''
ENCRYPTION BY PASSWORD = ' '

I'm thinking the master key creation password will be different from the backup command? I know we need to use the same password used in the backup command for the restore of the master key. I'm just confused with the password in the create and backup command.

Thanks for your help.




Post #716605
Posted Wednesday, May 13, 2009 8:51 PM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 7:12 AM
Points: 31,284, Visits: 15,746
The backup/restore pwd shouldn't need to be the same as the creation password. The creation password is needed to open the key.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #716612
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse