We wanted to store some encrypted data in some fields. So, went ahead with this approach.
DBA creates a MASTER KEY encrypted with a password known only to him.
DBA creates a SERVER CERTIFICATE encrypted with this MASTER KEY.
DBA creates a SYMMETRIC KEY encrypted with this SERVER CERTIFICATE.
And then, we just have to open the SYMMETRIC KEY and provide it the name of this SERVER CERTIFICATE before executing any
OPEN SYMMETRIC KEY KeyName DECRYPTION BY CERTIFICATE CertName
-- Include this in a SELECT command
-- CONVERT(varchar(128),DECRYPTBYKEY(@encrypted_value))
CLOSE SYMMETRIC KEY KeyName
This worked well in dev & test environments. But, in prod, we would have a mirrored server as failover to improve availability. And hence when we tried this out before we went live, in perf/staging environment, we got these errors when we attempted to manually failover the primary db server.
Please create a master key in the database or open the master key in the session before performing this operation.
The key 'KeyName' is not open. Please open the key before using it.
When the use the DBA's master key, the queries work fine. But, we wouldnt like our queries to use the master key, would we?
So, it seems the DBA had created a certificate on each server with the same name. But, that means having 2 different certificates, one on each server, with just the name being common.
To get this in place properly, the DBA has to create a certificate on one server, take a backup, copy it to the other server, and then restore it on the failover partner. This way, both the servers would actually use the same certificate.
Some of these best practices are also mentioned here.
http://technet.microsoft.com/en-us/library/cc917681.aspx
DBA creates a MASTER KEY encrypted with a password known only to him.
DBA creates a SERVER CERTIFICATE encrypted with this MASTER KEY.
DBA creates a SYMMETRIC KEY encrypted with this SERVER CERTIFICATE.
And then, we just have to open the SYMMETRIC KEY and provide it the name of this SERVER CERTIFICATE before executing any
OPEN SYMMETRIC KEY KeyName DECRYPTION BY CERTIFICATE CertName
-- Include this in a SELECT command
-- CONVERT(varchar(128),DECRYPTBYKEY(@encrypted_value))
CLOSE SYMMETRIC KEY KeyName
This worked well in dev & test environments. But, in prod, we would have a mirrored server as failover to improve availability. And hence when we tried this out before we went live, in perf/staging environment, we got these errors when we attempted to manually failover the primary db server.
Please create a master key in the database or open the master key in the session before performing this operation.
The key 'KeyName' is not open. Please open the key before using it.
When the use the DBA's master key, the queries work fine. But, we wouldnt like our queries to use the master key, would we?
So, it seems the DBA had created a certificate on each server with the same name. But, that means having 2 different certificates, one on each server, with just the name being common.
To get this in place properly, the DBA has to create a certificate on one server, take a backup, copy it to the other server, and then restore it on the failover partner. This way, both the servers would actually use the same certificate.
Some of these best practices are also mentioned here.
http://technet.microsoft.com/en-us/library/cc917681.aspx
No comments:
Post a Comment