Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Encryption Server on
Hello All,
 
We are trying to setup encryption for a single column ( not the entire database ) using symmetric key on database where we also want to setup always on.
 
We created master key, certificate and symmetric key on the user databse and we are able to encrypt.
 
When the primary database server is up, the decryption works fine.
 
But in case of a failover, when the secondary is promoted primary. Decryption fails with following error.
 
Msg 15581, Level 16, State 7, Line 18
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 23
The key 'CoolKeyName' is not open. Please open the key before using it.

 
Can any one help in resolving this ?
Posted 17-Oct-12 4:25am
AmitDey17.6K
Comments
ryanb31 at 17-Oct-12 10:34am
   
I believe you have to first setup of the keys on the other server as well.
AmitDey at 17-Oct-12 11:47am
   
Hi Ryan, thanks for replying.
 
That will be negated by "Always on". As "Always On" requires that the databases are in exact same state.
 
Please correct me if I am wrong.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Look into:
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY 
GO
 
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'your password';
GO
 
Just went through this and had a tough time finding information on how to fail over.
 
We are using a symmetric key and creating that on a new server was a problem until I found that both "KEY_SOURCE = " and "IDENTITY_VALUE = " are needed in my case to recreate the key.
  Permalink  
Comments
AmitDey at 17-Oct-12 11:43am
   
Hi djj55, Thanks for answering.
 
The decryption I am doing is inside a SP. By your method, I will have to store 'my password' as plain text within the SP Text. This is something I would like to avoid.
 
Please correct me If I am wrong.
djj55 at 17-Oct-12 11:45am
   
Sorry what I meant was a one time execute of the commands by a sysadmin. This sets up the database to use the key. Look at BOL for a complete explanation of the commands
AmitDey at 17-Oct-12 11:49am
   
Ok even if this is a one-time executing of command. I still think this might have to be done every time failover happens. Is that correct ?
djj55 at 17-Oct-12 11:56am
   
I am not sure. I was hoping to give a couple of pointers that would get you going as I am new to this myself.
Our shop does not use automated fail-over and have not needed fail-over for a couple of years. I guess I will find out on the 22nd as we are conducting a disaster recovery exercise which will be the first since the encryption started last week.
AmitDey at 17-Oct-12 12:03pm
   
I did do some analysis, but looks like this needs to be done every time a failover happens. Thanks for your pointers.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 7,903
1 Sergey Alexandrovich Kryukov 7,127
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,820


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 17 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100