Monday, January 28, 2013 2:20 PM
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,
- Edited by OliveraEduardo Monday, January 28, 2013 2:22 PM
Monday, January 28, 2013 2:28 PM
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,
- Merged by Allen Li - MSFTModerator Tuesday, January 29, 2013 7:04 AM Same question
Monday, January 28, 2013 2:33 PMAnd what is the error message you're seeing?
Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu
Monday, January 28, 2013 3:00 PM
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.
Monday, January 28, 2013 10:43 PM
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, email@example.com
Tuesday, January 29, 2013 7:02 AMModerator
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:
TechNet Community Support
- Marked As Answer by Maggie LuoMicrosoft Contingent Staff, Moderator Thursday, February 07, 2013 7:43 AM