Click here to Skip to main content
11,933,037 members (56,834 online)
Rate this:
Please Sign up or sign in to vote.
See more: SQL Encryption Server
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
ryanb31 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

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

Solution 1

Look into:

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.
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.
djj55 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 ?
djj55 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)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web03 | 2.8.151126.1 | Last Updated 17 Oct 2012
Copyright © CodeProject, 1999-2015
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