Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Restoring db's with certificates in another server Expand / Collapse
Author
Message
Posted Monday, January 28, 2013 4:59 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 7:54 PM
Points: 99, Visits: 315
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
Post #1412350
Posted Monday, January 28, 2013 6:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:42 PM
Points: 224, Visits: 1,732
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
Post #1412386
Posted Monday, January 28, 2013 6:23 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 7:54 PM
Points: 99, Visits: 315
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
Post #1412394
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse