April 7, 2009 at 4:33 pm
I have a log shipping set up between Server A(Primary) and B(Secondary standby/read only mode)
Set up Details:
Database master key is encrypted with password and a certificate is available in the database.
Symmetric key is encrypted using certificate. Data is encrypted using this Symmetric key.
The whole process happens in log shipping primary and the logs are shipped to secondary which is in read-only/standby mode.
Problem:
I am able to decrypt the data using decryptbyautocert without opening the master key/symmetric key in the log shipping primary.
Whereas in the log shipping secondary I am not able to decrypt the data withour opening the master key and symmetric key. Even after opening the master key and symmetric key, I receive an error ' the database is in read only cannot update' the first time and subsequent runs goes through fine.
Only option I found so far was to backup the service master key from primary and restore it on secondary using force option. After doing this, I was able to decrypt the values in log shipping secondary without opening master/symmetric keys.
Please let me know if there are any other options available to decrypt the data without opening the master key/symmetric key everytime.
Thanks
Prem
April 8, 2009 at 1:33 am
Please don't cross post. It just wastes peoples time and fragments replies.
No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic692609-1291-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply