Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ProEdge
Yak Posting Veteran
64 Posts |
 Posted - 2009-03-03 : 19:16:26
|
| Hi all, when using EncryptByKey with an authenticator (let's say a simple Identity value), how come if I move the encrypted data from one SQL Server instance to another which has a) the exact same master key, certificate and symmetric keys and b) same table schema and values, does then data not decrypt correctly? In Server B, the data server I copied the data to, I am using the exact same authenticator value. I understand that the authenticator is used so as to prevent moving of the data, but from what I've read in BOL, it seems that this would still work because the authenticator value is the same. Here's the info. from BOL:If an authenticator parameter is specified when data is encrypted, the same authenticator is required to decrypt the data by using DecryptByKey. At encryption time, a hash of the authenticator is encrypted together with the plaintext. At decryption time, the same authenticator must be passed to DecryptByKey. If the two do not match, the decryption will fail. This indicates that the value has been moved since it was encrypted. We recommend using a column containing a unique and unchanging value as the authenticator. If the authenticator value changes, you might lose access to the data.I've tried doing the same thing but without using the authenticator. So on Server A, I encrypt the data and move it to Server B. I then am able to DecryptByKey with no problem. But I would like to be able to use the authenticator value. Am I missing something? |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2009-03-04 : 01:22:38
|
| Before moving data you must create a script which can create same symmetric key and certificate with same authenticator BUT while creating symmetric key never forgetidentity_value & key_source-------------------------------------------------------------CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'goCREATE CERTIFICATE [Usercert] WITH SUBJECT = 'UserKey protection'goCREATE SYMMETRIC KEY [UserKey] WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE [Usercert] CREATE SYMMETRIC KEY [UserKey] WITH ALGORITHM = TRIPLE_DES, identity_value = 'Data encryption key 01/01/2009', key_source = 'my algo' ENCRYPTION BY CERTIFICATE [Usercert];----------------------------------------------------------------After moving your data to server B,1- Delete existing Symmetric Key and Certificate2- Run your already created script, to create symmetric key and certificate with same authenticator |
 |
|
|
|
|
|
|
|
EncryptByKey and Authenticator Question