backup and resore encrypted data

I have two databases, one production and other a production copy.

One column of the production table is encrypted by the following syntax.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password!'

CREATE CERTIFICATE cert_demo

WITH SUBJECT = 'encryption demo'

CREATE SYMMETRIC KEY symkey_demo

WITH ALGORITHM = TRIPLE_DES

ENCRYPTION BY CERTIFICATE cert_demo

--

I have stored procedure to add records to a production table like this.

ALTER Procedure AddRecord

@.id int,

@.var1 varchar(100)

AS

BEGIN

DECLARE @.var2 varbinary(256)

OPEN SYMMETRIC KEY symkey_demo

DECRYPTION BY CERTIFICATE cert_demo

SET @.var2 = EncryptByKey(

key_guid( 'symkey_demo' ),

@.var1 )

insert into dbo.Test(id,Data,EData)

values(@.id,@.var1,@.var2)

CLOSE SYMMETRIC KEY symkey_demo

END

Now I need to decript the data in the production copy database. So I had export production data to the production copy database and also generates the same key and cerificate. When i tried to decrypt the data, no results has returned.

Create procedure ViewData

@.id int

AS

BEGIN

DECLARE @.var1 varbinary(256)

DECLARE @.var2 varchar(100)

OPEN SYMMETRIC KEY symkey_demo

DECRYPTION BY CERTIFICATE cert_demo

select @.var1=EData from Test where id=@.id

Set @.var2 = convert( varchar(100), DecryptByKey( @.var1))

print @.var2

END

It doesn't print the enctypted data. Please suggest what goes wrong.

The certificate protecting the key encrypting your data is protected by the DB master key (DBMK). In the server where the DBMK was created, the DBMK is protected by the password and by the service master kit (SMK). The SMK protection allows to use the DBMK automatically (i.e. without the need to open the DBMK explicitly).

When you move the DB to a different server, the protection by the SMK is no longer valid, and you need to manually open the DBMK and encrypt it with the current SMK. This is a one-time operation.

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<password>>'

Go

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Go

For more detailed information go to BOL:

http://msdn2.microsoft.com/en-us/library/ms174433.aspx

http://msdn2.microsoft.com/en-us/library/ms186937.aspx

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks Raul.

I had done two things.

1.The service master key doesn't working. So I have to manually open the DBMK and encrypt with the current SMK.

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password!'

Go

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

2. Again the symmetric key doesn't work.

Because keys cannot be individually backed up and restored, there is no direct way of moving a key from one database to another. However, by specifying the same values for the ALGORITHM, KEY_SOURCE, and IDENTITY_VALUE clauses of CREATE SYMMETRIC KEY, the same key can be generated on different databases.

so I have to change the symmetric key generation code to,

CREATE SYMMETRIC KEY symkey_demo

WITH ALGORITHM = TRIPLE_DES,

identity_value = 'Test Data encryption key',

key_source = 'test key'

ENCRYPTION BY CERTIFICATE cert_demo

Now I backed up the production database and restore it in a different server. I followed the stpe 1 and it works. Now I am able to decrypt the data in the production copy database.

Thanks

Amit

|||

I see, when you described a copy of the datbase I assumed (incorrectly) that you were referring to a copy based on a backup.

Thanks a lot for letting us know, and let us know if you have further questions or comments.

-Raul Garcia
SDE/T
SQL Server Engine