October 17, 2012 at 6:00 am
We have SQL 2012 ‘Always on’ setup on 2 SQL servers hosting some 6 Databases.
On one of the Database, we are trying to implement Column Level Encryption on two columns in 1 table.
We have used the below scripts to configure Encryption.
1. Setup Script – These scripts were run in User Database on Primary Replica
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'abcd1234!@#$'
CREATE CERTIFICATE SSSCredentialsCert WITH SUBJECT = 'SSSCertificate'
CREATE SYMMETRIC KEY SSSCredentialsKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE SSSCredentialsCert
2. Encryption Key Script - These scripts were run in User Database on Primary Replica
DELETE FROM SSSCredentials -- This is the table to store the encrypted values.
OPEN SYMMETRIC KEY SSSCredentialsKey DECRYPTION
BY CERTIFICATE SSSCredentialsCert
INSERT INTO SSSCredentials
Values(ENCRYPTBYKEY(KEY_GUID('SSSCredentialsKey'),@userName),ENCRYPTBYKEY(KEY_GUID('SSSCredentialsKey'),@password))
CLOSE SYMMETRIC KEY SSSCredentialsKey
3. Decryption Key Script used to test the decryption.
OPEN SYMMETRIC KEY SSSCredentialsKey DECRYPTION
BY CERTIFICATE SSSCredentialsCert
SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(Username)) AS Username,
CONVERT(VARCHAR(50),DECRYPTBYKEY(Password)) AS Password
FROM SSSCredentials
CLOSE SYMMETRIC KEY SSSCredentialsKey
Decryption test was working fine on Primary Replica. But when we failed over to Secondary Server [ new Primary instance ], its giving error :
Msg 15581, Level 16, State 7, Line 5
Please create a master key in the database or open the master key in the session before performing this operation.
Msg 15315, Level 16, State 1, Line 10
The key 'SSSCredentialsKey' is not open. Please open the key before using it
Please suggest.
October 17, 2012 at 4:32 pm
I am not an expert with column-level encryption, but I think you are going to have to import the certificate you created on the primary instance over to your secondary instance before it can be used to decrypt the column contents. This sounds similar to how TDE uses certificates in its encryption and it is very clear that you need to have the certificate available wherever an encrypted data file or database restore is going.
Joie Andrew
"Since 1982"
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply