April 9, 2010 at 1:42 pm
We have a database with column-level encryption. We need to migrate data (using the "Export" functionality) from this database to a target database. In order to use the same key structure, we created backups of the Database Master Key and Certificate, and restored those on the target database. There seems to be no restore functionality for the Symmetric Key, so we used the same CREATE script we used on the source database.
All keys restored normally and the symmetric key created sucessfully. However, in trying to decrypt one of the tables, we are receiving the following error message:
Msg 15556, Level 16, State 1, Line 2
Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.
(139979 row(s) affected)
Msg 15315, Level 16, State 1, Line 2
The key '<KeyName' is not open. Please open the key before using it.
The syntax we are using for the decryption is:
OPEN SYMMETRIC KEY <KeyName>
DECRYPTION BY CERTIFICATE <CertName>;
GO
selectCONVERT(varchar, DecryptByKey(Field1)),
CONVERT(varchar, DecryptByKey(Field2)),
CONVERT(varchar, DecryptByKey(field3))
from dbo.TableName
GO
CLOSE SYMMETRIC KEY <KeyName>
Any suggestions? Many thanks--this is a critical issue with a looming deadline.
April 9, 2010 at 2:28 pm
Two thoughts:
1. Check out the article "How to: Create Identical Symmetric Keys on Two Servers"
Here: http://msdn.microsoft.com/en-us/library/ms366281(SQL.90).aspx
2. In your sample script, you are not opening your database master key before opening the user key (I realize you may be doing that and simply didn't post that step). The database master key must be opened before you can open any other keys. If the database master key has been encrypted using the Service Master Key, then it will be opened automatically for you when you attempt to open other keys. Otherwise, it must be specifically opened first using one of the passwords under which it has been encrypted. The database master key may be encrypted many times with many passwords, and may additionally be encrypted using the Service Master key. Compare the results of the following query when run in both source and target databases:
SELECT * FROM sys.key_encryptions
...specifically, key 101 (the database master key). If there there is a row in that result for the database master key where crypt_type_desc = ENCRYPTION BY MASTER KEY, but not in the target, you simply need to encrypt the database master key in the target using the Service Master Key:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'your master key password goes here...'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY
Those are the first two things I'd check if this were the case in my servers.
-Eddie
Eddie Wuerch
MCM: SQL
April 14, 2010 at 7:37 am
Hi Eddie,
First, thank you for the link. I was not using the KEY_SOURCE or IDENTITY_VALUE values when creating my Symmetric Key. Once I utilized those keywords I was able to create the same keys on all of our databases, regardless of server. That was a tremendous help.
I'm a little confused by the Database Master Key behavior. In all of my testing, I never had to open the Database Master Key. I tested the following scenarios:
1) Entering data into the encrypted field from a business application attached to the database. The code that reads/writes to the database opens the Symmetric Key and uses encrypt/decrypt when accessing the encrypted values. This works perfectly.
2) I encrypted two databases, then exported a table with encrypted data to the second database, and was able to decrypt the data through SSMS just by opening the Symmetric Key and using the decrypt function. This worked fine as well.
3) I restored a database from a backup of another database (both had encryption applied) and was able to decrypt data by opening the Symmetric Key on the newly restored database.
In all three scenarios, I was able to decrypt, either through the business application or through SSMS. However, when I tested restoring a database on one server from a backup of a database from a different server, two things happened:
- When bringing up the record in the business application, the values in the decrypted field were displayed as NULLs.
- When running the decryption function in SSMS, I received this error:
Msg 15581, Level 16, State 3, Line 2
Please create a master key in the database or open the master key in the session before performing this operation.
(1 row(s) affected)
Msg 15315, Level 16, State 1, Line 2
The key '<key name>' is not open. Please open the key before using it.
My question then is under what circumstances does the Database Master Key need to be opened? In testing this through the business application, the DMK open statement was not included in the code, and it worked fine. It appears that by crossing servers I'm generating this DMK error, but I can't be sure that is the only issue.
I hope this makes sense. If not, I'm happy to clarify. And I sure hope someone can help with this because the code is about to be promoted to full testing and I'd hate to have to pull it back if this can be resolved this quickly.
April 14, 2010 at 8:21 am
This sounds a lot more complicated than using something like xp_crypt or other tool to encrypt columns. What's the benefit ?
April 14, 2010 at 2:41 pm
My question then is under what circumstances does the Database Master Key need to be opened?
It must always be opened. However, if that key has been encrypted by the Service Master Key, then it will be opened automatically, and you won't need to issue an OPEN MASTER KEY statement. Look in the original location to see of the Database Master Key has been encrypted using the Service Master Key (see query in my previous post).
The Database Master Key may be encrypted multiple times, and may be opened using any of the methods used to encrypt it.
The Service Master Key is unique to each SQL Server instance. If you restore a database to the same instance, the Service Master key will work in the newly-restored database because it does not change, and resides outside of the databases.
So, if the Database Master Key has been encrypted using the Service Master (instance) Key in one location, and then you copy that database somewhere else, the Service Master Key of the new instance will be unable to open the Database Master Key, until you re-encrypt the Database Master Key using the new location's Service Master Key (the second script from my previous post, run it in the database in the new location).
-Eddie
Eddie Wuerch
MCM: SQL
April 15, 2010 at 8:16 am
Thanks Eddie. Your explanation makes perfect sense, because we didn't see this error until our test scenario of restoring a database on one server with a backup file from another server. All our other testing worked fine without running the script to open the master key, and once we ran the script the restored database worked fine as well.
Thank you so much for all your assistance. This morning I deployed encryption over our entire testing base, and <me knocking on wood> so far, so good. So your quick responses allowed me to hit my deadline. I hope I can return the favor some day.
April 15, 2010 at 8:37 am
I have a follow up question related to certificate effective and expiration dates.
When we created the certificates on all the test databases, we did not enter those dates, so they would be using the defaults of today for effective date and one year later for expiration.
What is the implication of not setting the expiration date? When we move this to production, is it advisable that we use an expiration date?
Thanks to anyone who can help with this.
April 19, 2010 at 12:36 pm
Re: Certificate expiration dates
From BOL (CREATE CERTIFICATE):
Built-in functions for encryption and signing do not check the expiration dates of certificates. Users of these functions must decide when to check certificate expiration.
Eddie Wuerch
MCM: SQL
April 19, 2010 at 12:43 pm
Hi Eddie,
Thank you again. You've been a tremendous help.
May 6, 2010 at 3:06 pm
Eddie,
Please help me also.
I have an encrypted column on a table on one Database and I want to decrypt it from another database.
USE DB2
GO
OPEN SYMMETRIC KEY SSNFieldSymmetricKey
DECRYPTION BY CERTIFICATE SSNFieldCertificate;
Select a.ID, (CONVERT(varchar, DecryptByKey(b.ssn)) as SSN from DB2.dbo.table1 a Inner Join DB1.dbo.Table2 b on a.ID=b.ID
I am not able to decrypt it. It works when I decrypt it in the same database, see below
USE DB1
GO
OPEN SYMMETRIC KEY SSNFieldSymmetricKey
DECRYPTION BY CERTIFICATE SSNFieldCertificate;
SELECT (CONVERT(varchar, DecryptByKey(ssn)) from dbo.table2
Any Suggestions? Appreciate your help.
Thanks,
Ashwani
May 7, 2010 at 7:14 am
I had the same issue with SQL 2005 so when you move from one server to another i had to decrypt my value and then encrypt it on the new server.
May 7, 2010 at 7:42 am
Thanks for the response. But actually I am calling the encrypted column from another database in my query and it is not working. I am getting NULL's. If I am in the same databse, it works fine. My question is how do I decrypt the encrypted column if I am calling the encrypted column in my query from another database on the same instance.
May 10, 2010 at 12:02 pm
This Link helped me.
http://technet.microsoft.com/en-us/library/ms366281(SQL.90).aspx
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply