This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
We use SQL 2017 enterprise to setup high availability group with one AG contains 28 databases. Recently, on primary server, column level encryption is implemented on two databases. Symmetric key is created at DB1 as
CREATE SYMMETRIC KEY JanainaKey09 WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE Shipping04;
Symmetric Key is created at DB2 with more option as
CREATE SYMMETRIC KEY #MarketingXXV WITH ALGORITHM = AES_128, KEY_SOURCE = 'The square of the hypotenuse is equal to the sum of the squares of the sides', IDENTITY_VALUE = 'Pythagoras' ENCRYPTION BY CERTIFICATE Marketing25;
After failover to the Secondary server, data can't be decrypted. It returns NULL instead of plain value. Restore the Service Master Key from Primary Server. The data still can't be decrypted. How to solve this issue?
Additional SQL Server features and topics not covered by specific categories
If JanainaKey09 is protected by Shipping04, where and how is Shipping04 defined? It sounds like something is missing in the trust chain.
Similar question for #MarketingXXV and Marketing25.
The other slightly odd question is about the name of the #MarketingXXV certificate. I've never tried having a # prefix for a cert and referencing it without quoting it, but in general, objects that start with # are temporary in SQL Server. No idea if it's possible to create a temporary cert but it might be. Not sure I'd chance it. Does the same happen with a name that's not starting with #.
Thanks for the response. I didn't provide all the information. The names are faked. The keys are created in DB1 and DB2 as
On DB1, the Symmetric Key is created as
3)CREATE SYMMETRIC KEY JanainaKey09 WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE Shipping04;
On DB2, the Symmetric Key is created with more defined parameters
3)REATE SYMMETRIC KEY JanainaKey09
WITH ALGORITHM = AES_128, KEY_SOURCE = 'The square of the hypotenuse is equal to the sum of the squares of the sides', IDENTITY_VALUE = 'Pythagoras' ENCRYPTION BY CERTIFICATE Shipping04;
So, the symmetric key is created differently for DB1 and DB2. Once databases are failover to the secondary, The data can't be decrypted. Even the Service Master Key from Primary has been restored to the secondary. How to fix this issue?
So didn't I post an example to get this working in response to a previous questions of yours a month or two ago? I would suggest that you find that post, and set up an AG and implement encryption as I suggested in your post, so that you learn the process.
I would guess that you have managed to mess something up, but what is hard to say on a distance.