Post reply

Restoring db's with certificates in another server

  • January 28, 2013 at 4:59 am

    #273251

    We will soon be converting our existing production databases to a a system where we use certificates on the actual data holding databases and procedures on a sp only database to limit access to the data by the application. We regularly obfuscate and backup production data to restore it in QA and Dev where we need to preserve the same arrangement. I would appreciate any assistance on how to perform this whether I need to drop it and recreate it on the destination server or if it is possible to move certificates across servers.

    Thanks in advance,

    Eduardo

  • January 28, 2013 at 6:10 am

    #1581583

    Eduardo Olivera (1/28/2013)

    We will soon be converting our existing production databases to a a system where we use certificates on the actual data holding databases and procedures on a sp only database to limit access to the data by the application. We regularly obfuscate and backup production data to restore it in QA and Dev where we need to preserve the same arrangement. I would appreciate any assistance on how to perform this whether I need to drop it and recreate it on the destination server or if it is possible to move certificates across servers.

    Thanks in advance,

    Eduardo

    You don't need to recreate the certificates, but what about security? This is not a good idea to use the same certificates and keys on prod and dev.

    After restore the backup on a new instance, you just alter DMK. And if you protect DMK only by password, you don't need to do anything.

    ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'pass';

    http://msdn.microsoft.com/en-us/library/ms186937.aspx

  • January 28, 2013 at 6:23 am

    #1581589

    Thanks, but I don't restore master and I am not planning to. Only the user databases will be restored. My guess is that would change the importing of the certificate. One more thing, I am not encrypting the databases. The certificates are used for the purpose of allowing the application login to access data outside the database that holds only procedures. It is only used to transfer cross-database permissions without granting them to the application login. It is based on the method described by Erland Sommarskog in his page:

    http://www.sommarskog.se/grantperm.html

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

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