August 31, 2011 at 8:27 am
I have a test server with 1 database on it. I've created a master key, certificate and symmetric key to encrypt data as follows:
USE MyDatabase
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password_for_master_key';
GO
CREATE CERTIFICATE [My_certificate] WITH SUBJECT = 'Key Protection';
GO
CREATE SYMMETRIC KEY [My_symmetric_Key] WITH
KEY_SOURCE = 'Key generation for multiple servers',
ALGORITHM = AES_256,
IDENTITY_VALUE = 'Key Identity generation bits for more than 1 server'
ENCRYPTION BY CERTIFICATE [My_certificate];
GO
I then encrypt a field in my table like this:
USE MyDatabase
GO
OPEN SYMMETRIC KEY [My_Symmetric_Key]
DECRYPTION BY CERTIFICATE My_certificate;
UPDATE ATable SET SecureField = encryptbykey(key_guid('My_Symmetric_Key'), UnsecureField)
CLOSE SYMMETRIC KEY [My_Symmetric_Key]
All of which I am quite happy with.
I now create a new database, called MyDatabaseTest, and import the table with the encrypted field in. As expected, I cannot decrypt this field, I get an error about the missing certificate. However, if I create a new Master key, certificate and symmetric key, like this:
USE MyDatabaseTest
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'a_different_Password_for_Testing';
GO
CREATE CERTIFICATE [My_certificate_Test] WITH SUBJECT = 'Key Protection';
GO
CREATE SYMMETRIC KEY [My_symmetric_Key_Test] WITH
KEY_SOURCE = 'Key generation for multiple servers',
ALGORITHM = AES_256,
IDENTITY_VALUE = 'Key Identity generation bits for more than 1 server'
ENCRYPTION BY CERTIFICATE [My_certificate_Test];
GO
then I can view the encrypted data. I was expecting to get an error - which would have made me a happy chappy.
I'm very new to using encryption, in fact, I'm new to SQL 2005! I'm fairly sure I'm missing something obvious and simple, but I cannot see it.
Any advice or pointers would be much appreciated
August 31, 2011 at 8:48 am
I think that this is because you are using the option KEY_SOURCE. If you use this option, then you get the same key. The certificates on each database encrypts the key, but don’t change the way that the key encrypts the data.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 31, 2011 at 9:21 am
Adi Cohn-120898 (8/31/2011)
I think that this is because you are using the option KEY_SOURCE. If you use this option, then you get the same key. The certificates on each database encrypts the key, but don’t change the way that the key encrypts the data.Adi
Hi Adi, Thank you for the reply, removing the KEY_SOURCE has cured the problem.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply