Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Restoring a database created with cross database certificates on another server

Answered Restoring a database created with cross database certificates on another server

  • Monday, January 28, 2013 2:20 PM
     
     
    Sign In to Vote
    0

    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. 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. Unfortunately, I can't post link because it doesn't allow me but the title of his article is Giving Permissions through Stored Procedures.



    Thanks in advance,

    Eduardo


     

All Replies

  • Monday, January 28, 2013 2:28 PM
     
     
    Sign In to Vote
    0

    We have set up Erland Sommaskog's method for assigning permissions to stored procedures through certificates (as described in his article Giving Permissions through Stored Procedures) and we are now experiencing issues with inserts on tables that have identity columns. Is it possible that the certificates are causing this issue?  I can't post the link to the page properly so I will try it this way www(.)sommarskog(.)se(/)grantperm(.)html. To the best of my knowledge, this is the only thing htat has changed but it is a dev server.

    Thanks in advance,

    Eduardo

     
  • Monday, January 28, 2013 2:33 PM
     
     
    Sign In to Vote
    0
    And what is the error message you're seeing?

    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

     
  • Monday, January 28, 2013 3:00 PM
     
     
    Sign In to Vote
    0

    My apologies, this was not the issue that I was led to believe. I should have checked with the programmer first but things were not as explained and the issue has been solved. The error was that it could not insert into the table but he was not in the database that he told me he was and there was a legitimate permissions issue because the certificate granted the login permission from the procedure on the remote database but the user had none on the actual database.

    Thanks

     
  • Monday, January 28, 2013 10:43 PM
     
     
    Sign In to Vote
    0

    If you only use certificate to sign procedures within the database, that will work even if you restore a copy, as everything is in the database.

    The same applies if you use the certificates for cross-database access, and you restore all databases.

    If you use certitificate signing to encapsulate server-level permissions, you will need to import the certificates in the master database of the receiving server, as well as recreating logins and permissions.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Tuesday, January 29, 2013 7:02 AM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0

    Hi Eduardo,

    Please backup the certificate with primary key, and then recreate the certificate from the files. For example:

    USE AdventureWorks2012;
CREATE CERTIFICATE Shipping11 
    FROM FILE = 'c:\Shipping\Certs\Shipping11.cer' 
    WITH PRIVATE KEY (FILE = 'c:\Shipping\Certs\Shipping11.pvk', 
    DECRYPTION BY PASSWORD = 'sldkflk34et6gs%53#v00');
GO

    You can also refer to the following documents:
    http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/9bcb4a32-af88-4df7-8649-b6c34c130ba0

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

    Allen Li
    TechNet Community Support