Post reply

Moving DB to new location -- encryption concerns

  • May 2, 2006 at 11:44 am

    #117418

    I am just wondering if there would be a problem in basically copying a database from one server to another (using a restore of a backup) if the database has some encrypted fields.

    I presume the symmetric keys, certificates, and master key will be restored correctly.  The thing I am worried about is the Service Master Key.  The SMK is used to encrypt the database master keys, so if it is different from the one on the old server, I anticipate there might be a problem.

    As I understand it, the SMK is created using the login credentials of the service that runs it.  Does that mean if SQL Server runs on the same service on both servers, the SMK will be the same?  On both servers the database seems to be running on LocalSystem.  As far as I know, that's the only credential -- there is no password.

    Any help anyone could provide here would be greatly appreciated!

  • May 2, 2006 at 2:00 pm

    #635452

    OK, I tried an experiment where, on the new server, I set up the same master key, certificate, and symmetric key (using backups or the same parameters) as on the old server.

    Then I tried decrypting something on the new server that I had encrypted on the old server, and it decrypted in correctly.

    So ... I don't completely understand it, but I guess the Service Master Key being different is not an issue. 

  • May 3, 2006 at 7:52 am

    #635602

    The service master key being different is not an issue. The database master key is generated from your user supplied password and the 3DES encryption algorithm. With respect to generating the key, the service master key doesn't come into play.

    Where the service master key comes in is that SQL Server by default stores an encrypted copy of the database master key in the master database. That encrypted copy is generated using the service master key. That's so the SQL Server has access to the database master key and can open it automatically. Since you recreated the keys, there wasn't an issue. You can disable this default behavior, but then you would have to issue OPEN MASTER KEY statements each time. In either case, you should still backup the master key to a file and store it in a secure location.

    K. Brian Kelley
    @kbriankelley

  • May 3, 2006 at 9:58 am

    #635695

    Oh OK, I get it now.  Thanks for that explanation!

Viewing 4 posts - 1 through 3 (of 3 total)

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