Decrypting returns a NULL value

I find it weird when decrypting a column from a baked up database and restoring it to another database. Here's the scenario:

Server1 has Database1.
Database1 has Table1 with two columns encyrpted -- Card Number and SS Number
Encryption and decryption in this Database1 is perfectly fine. Records are encrypted and can be decrypted too.
Now, I tried to backup this Database1 and restore it to another server with SQL 2005 instance called Server2. Of course the columns Card and SS Numbers were encrypted. I tried decrypting the columns using the same command to decrypt in Database1, however, it returns a NULL value

Here's exactly what I did to create the encyprtion and decryption keys on the restored database:
-- Create the master key encryption
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'myMasterPassword'

-- Create a symetric key
CREATE SYMMETRIC KEY myKey WITH ALGORITHM = DES
ENCRYPTION BY Password='myPassword';
Go

-- Create Card Certificate
CREATE CERTIFICATE myCert WITH SUBJECT = 'My Certificate on this Server';
GO

-- Change symmetric key
OPEN SYMMETRIC KEY myKey DECRYPTION BY PASSWORD = 'myPassword';

-- I then verified if the key is opened
SELECT * FROM sys.openkeys

If I create a new database, say Database2 from that Server2, create table, master key, certificate, and symmetric key. Encrpytion and decryption on Database2 will work!

Any suggestions gurus? I tried all searches and help for almost 2 weeks regarding this issue but nobody could resolve this.

Thanks in advance!

faiga16

3 Posts

I wish I could be more helpful, but all I have to offer is that I think it has to do with your Server master key being different. I was just at a conference where this exact scenario was mentioned and there is a way to use the password (or whatever it is called) to re-encrypt the restored database with the master key so that you can decrypt it.

[ed] sorry it looks like I missed the end of your post. It looks like you're already headed down the path I suggested.

|||

If the two servers are the same OS then I think all you will need to do is run:

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

to the new server.

hth,

-Steven Gott

S/DET

SQL Server

|||

Hi Steve,

Yes they have the same OS and SQL version. But executing

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

doesnt work too... it prompt an error saying "Create a master key in the database or open the master key in the session before performing this operation"

Is my procedure correct base on what you suggest?

1) restored the Database1

2) run ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

|||

I am getting to know my problem.

Before the alter master key command I opened the master key by open master key decryption by password command. Now it prompts an error that the decryption password I supplied was incorrect. Is there a way that I could know the master key encryption/decryption password? I just got here with all the encryption/decryption set up. Nobody remember the master key password duh!

|||

They are quite sure the master key they gave me was the one they used before.

"The key is not decrypted using the specified decryptor"

|||

From the error you are getting back, it seems like the password to decrypt the master key (DBMK) is not correct. The passwords are case sensitive; make sure it is typed exactly as it was originally created.

If you have a backup of the DBMK, you can try restoring the backup on top of the current copy (it should be the same DBMK) using the RESTORE MASTER KEY … FORCE statement.

NOTE: As you are using the force option, make sure you have a copy of the original DB in case the DBMK from the backup doesn’t match the one in your DB.

If the original server still exists, there may be one possibility: you can try to restore the DB on the original server and see if you can still access the DBMK based on the original service master key encryption. From there you can try to regenerate the DBMK (ALTER MASTER KEY REGENERATE) to establish a new DBMK password. I am not sure if this one will work, but it may be worth trying it.

I hope this helps.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks! Will test if this would work...