SQL Server 2012 after mirroring failover, decryption of an encrypted table column no longer decryptable

Question

I'm wondering if anyone can help me?

We have sql server 2012 standard edition.

In one of the databases (all mirrored), we have a table with an encrypted column.

Decryption of this table works fine until, after mirroring failover, decryption of the encrypted table column is no longer decryptable?

I have researched this and the suggested solution seems to be to implement Transparent Data Encryption?

I came across

http://blogs.msdn.com/b/sqlserverfaq/archive/2009/03/31/setting-up-database-mirroring-in-sql-server-2008-using-t-sql-when-the-database-is-encrypted-using-transparent-data-encryption.aspx

However 2012 standard edition does not allow TDE.

Does anyone know if there is another way to resolve this issue other than TDE as I'm not very experienced with mirroring.

Current Principle is set up as:-

IF NOT EXISTS 
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY 
    PASSWORD = 'complex password'
GO

CREATE CERTIFICATE Data01
   WITH SUBJECT = 'Data01 Passwords';
GO

CREATE SYMMETRIC KEY SSN_Key_01
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Data01;
GO

Any suggestions greatfully appreciated.

Answer 1

Decryption of this table works fine until, after mirroring failover, decryption of the encrypted table column is no longer decryptable?

It would be nice if we had the exact error message so this is just an educated guess on my part. Chances are you're using the service master key to automatically decrypt the database master key, continuing further until the final key is decrypted and opened. Since, by default, the SMKs on each instance are unique (unless otherwise forced) the SMK will be different and unable to open the DMK on the mirrored instance.

TDE is to encrypt data at rest, it does nothing from stopping a user with read access from attaining the data like cell level encryption does.

To fix this, assuming my guess was correct, you'll either want to change your decryption access to remove the automatic decryption by the SMK, manually open the DMK and continue as usual or update the SMKs to match on both servers.