locked
Problem With Decryption on DR Server RRS feed

  • Question

  • We recently broke mirroring between our production and DR servers and are unable to decrypt the data on one of our nodes. Here is our setup for both our DR and production environments:

    Production (Active\Active)
    Cluster1 - Node1A and Node1B
    Cluster2 - Node2A and Node2B

    DR (Active\Active)
    Cluster1 - Node1A and Node1B
    Cluster2 - Node2A and Node2B

    The cluster setup is identical (except for the server names) for both production and DR. Mirroring has been established as follows:

    Mirroring one database from production Node1A to DR Node1A.
    Mirroring one database from production Node2A to DR Node2A.
    Mirroring one database from production Node2B to DR Node2B.

    We have broken mirroring and confirmed that the data is encrypted (unreadable or NULL) when we query the encrypted data on the DR servers.

    Next, we tried to decrypt the encrypted data on all three databases (nodes) with the following script:

    USE [Database Name]
    GO
    OPEN MASTER KEY DECRYPTION BY PASSWORD = '**********'
    ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = '**********'
    GO

    This has worked for Node2A and Node2B but we are having problems with Node1A. We got no error message when we ran the above script against Node2A and Node2B and was able to decrypt the data. However, we got the message 'An error occurred during decryption.' when we ran the script against Node1A yet, we were able to decrypt the data nevertheless. When we disconnected from Node1A and then reconnected to Node1A, the data was decrypted again. We ran the script again, got the same error message but could read the data again. Everytime we disconnected and reconnected, we get the same behavior. This does not happen on the other two nodes.

    Any Ideas?

    Monday, June 8, 2009 3:27 PM