Click here to Skip to main content
15,868,340 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Comments
ZurdoDev 17-Oct-12 10:34am    
I believe you have to first setup of the keys on the other server as well.
AmitDey 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

Look into:
SQL
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.
 
Share this answer
 
Comments
AmitDey 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.
Corporal Agarn 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 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 ?
Corporal Agarn 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 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)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900