Post reply

Database Mirroring and the New Hierarchical Encryption Key Management

  • December 17, 2007 at 10:45 am

    #154022

    Question for the group. I’m investigating the use of the new database mirroring functionality. Our application also uses the new SQL Server 2005 encryption key management infrastructure of service and database master keys. When I perform a failover to the mirrored copy of my DB should I also plan to restore the service master key? Testing is needed here but I’m just in the planning phase now.

    Thanks in advance.

  • December 17, 2007 at 10:52 am

    #760767

    Mirroring is at the database level, so you'd need the Database Master Key, not the Service Master.

  • December 17, 2007 at 11:00 am

    #760770

    I was thininking the DbMK will be in the mirror DB already.

    I was under the impression that the only case when you don't need to do the backup-restore of the SMK is if the service account on the source and destination server is the same domain account.

  • December 17, 2007 at 12:58 pm

    #760798

    The DBMK will be encrypted with the SMK, but you can unencrypt it and back it up, then open it on the new server, or after the restore and encrypt it again with the SMK on the second server.

  • March 25, 2010 at 9:37 am

    #1139740

    We are facing this exact same issue. We are in the development process of using column-level encryption in SQL Server 2005 SP3. We have a production server on server1 with a mirror database on server2 (using a witness database on server3).

    - In the case of a failure, if the mirror becomes primary, what steps would need to be taken to ensure the encrypted column's data can be decrypted on the newly principaled database?

    - Since the databases are, in theory, identical, do they share the same service master and database master keys? That doesn't seem possible to me since it was my understanding the service master key is at the server level.

    I'm not sure I understand how this would work or what steps to add to our DR process to cover the encryption issue. My biggest fear is that we will encrypt data that we can never recover.

  • March 26, 2010 at 3:23 pm

    #1140793

    Nice step by step illustration at-->http://blogs.msdn.com/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

    MJ

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply