Certificate Signing Stored Procedures in Multiple Databases

Yesterday I received an email asking me a question related to my Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail on SQL Server Central that intrigued me enough that I had to put a couple of minutes into actually figuring out what the problem the being encountered was. In the article, I show how to create a stored procedure in a user database that can be used to call sp_send_dbmail without the calling user being in the DatabaseMailUserRole role in msdb, and there are actually a number of uses for this implementation beyond the example that is provided in the article. I use this same method to allow Service Broker activation procedures to send me emails through Database Mail all the time, and there are numerous other cases where you might need to create a stored procedure in one database that executes code in a separate database, where the calling user should not have access to the separate database.

I don’t know the exact use case for the problem that was posed in the email question, but the basic tenet of the question was that there was a stored procedure in one database that needed to be called from multiple databases and the intent was to use the same certificate to sign stored procedures in multiple databases to call this centralized stored procedure without giving the users access to the central database. Is that confusing to you? I confused me initially when I read the email, and it took a second pass for me to actually understand the problem being encountered and then build out a repro of the issue to begin looking at what the problem might be. To keep things simple and non-confusing if that is possible, lets first create two databases, two procedures and build out the basic framework from the article for two user stored procedures.

-- Create the calling database
CREATE DATABASE a;
GO
-- Create the target database
CREATE DATABASE b;
GO
-- Switch to the target database
USE b
GO
-- Create a table and insert 10 rows into the table
CREATE TABLE SomeTable (RowID int identity primary key)
GO
INSERT INTO SomeTable DEFAULT VALUES
GO 10
-- Create the target stored procedure that selects from the table
-- in the target database.
CREATE PROCEDURE Select_SomeTable
AS
SELECT * FROM SomeTable
GO
-- Switch to the calling database
USE a
GO
-- Create a stored procedure that calls the stored procedure
-- in the target database
CREATE PROCEDURE ExecuteDatabaseB_Select_SomeTable
WITH EXECUTE AS OWNER
AS
EXECUTE b.dbo.Select_SomeTable
GO
-- Create a certificate to sign the calling stored procedures with
CREATE CERTIFICATE [SigningCertificate]
ENCRYPTION BY PASSWORD = '$tr0ngp@$w0rd'
WITH SUBJECT = 'Certificate for signing a Stored Procedure';
GO
-- Backup certificate so it can be create in master database
BACKUP CERTIFICATE [SigningCertificate]
TO FILE = 'c:\SQLskills\SigningCertificate.cer';
GO
-- Add Certificate to Master Database
USE [master]
GO
CREATE CERTIFICATE [SigningCertificate]
FROM FILE = 'c:\SQLskills\SigningCertificate.cer';
GO
-- Create a login from the certificate
CREATE LOGIN [SigningLogin]
FROM CERTIFICATE [SigningCertificate];
GO
-- The Login must have Authenticate Sever access
-- per http://msdn.microsoft.com/en-us/library/ms190785.aspx
GRANT AUTHENTICATE SERVER TO [SigningLogin]
GO
-- Create a user in database b for the Login
USE b
GO
CREATE USER [SigningLogin] FROM LOGIN [SigningLogin]
GO
-- Grant EXECUTE on the target stored procedure to the
-- certificate based login
GRANT EXECUTE ON [dbo].[Select_SomeTable] TO [SigningLogin]
GO
-- Switch to the calling database
USE a
GO
-- Sign the procedure with the certificate's private key
ADD SIGNATURE TO OBJECT::[ExecuteDatabaseB_Select_SomeTable]
BY CERTIFICATE [SigningCertificate] WITH PASSWORD = '$tr0ngp@$w0rd'
GO

-- Create a test login to test that the certificate signed procedure
-- can properly execute the target procedure without the login having
-- access to the target database or target procedure directly
USE master;
GO
CREATE LOGIN [testuser] WITH PASSWORD=N't3stp@$', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
-- Create the test user for the test login in the calling database and
-- grant it EXECUTE privileges on the calling stored procedure
USE [a]
GO
CREATE USER [testuser] FOR LOGIN [testuser]GO
GRANT EXECUTE ON [dbo].[ExecuteDatabaseB_Select_SomeTable] TO [testuser]
GO
-- Switch to the test login context
EXECUTE AS LOGIN = 'testuser'
EXECUTE a.dbo.ExecuteDatabaseB_Select_SomeTable
GO
-- Revert the context back to our sysadmin login
REVERT
GO

In the above code, database “a” has a stored procedure that calls a stored procedure in database “b” and the “testuser” login only has access to database “a” and EXECUTE privileges on the stored procedure in database “a”. The certificate from database “a” is backed up and created in the “master” database so that a certificate login can be created from the certificate which has the EXECUTE privilege on the stored procedure in database “b”. The “testuser” login does not have access to database “b” or any of the objects inside of the database but because the stored procedure in database “a” is signed by the certificate and a login has been created in master from the certificate that has access to EXECUTE the stored procedure in database “b”, the “testuser” login can execute the stored procedure in database “a” and get access to the information in database “b” without having to have explicit access to database “b” or the stored procedure contained in that database.

At this point everything is essentially in line with the information contained in the SQL Server Central article I wrote, and it works exactly as shown in the article, even though it is all being applied to user databases and objects. Now let’s try to extend this functionality to a third database as a caller of the stored procedure in database “b” by creating a new database, the same stored procedure that was created in database “a”, and then creating the certificate in the new database from the previous backup from database “a” and signing the stored procedure using the same certificate.

-- Create a third database to create another calling database
-- and stored procedure to the target database and stored procedure
CREATE DATABASE c;
GO
-- Create the calling stored procedure in the third database
USE c
GO
CREATE PROCEDURE ExecuteDatabaseB_Select_SomeTable
WITH EXECUTE AS OWNER
AS
EXECUTE b.dbo.Select_SomeTable
GO
-- Create the certificate from in the third database from the
-- previous certificate backup to allow signing of the procedure
CREATE CERTIFICATE [SigningCertificate]
FROM FILE = 'c:\SQLskills\SigningCertificate.cer';
GO
-- Sign the procedure with the certificate's private key
ADD SIGNATURE TO OBJECT::[ExecuteDatabaseB_Select_SomeTable]
BY CERTIFICATE [SigningCertificate]
WITH PASSWORD = '$tr0ngp@$w0rd';
GO

This all works great, right up to the point that we try to sign the stored procedure using the certificate we created from our backup from database “a”. When we try to sign the stored procedure in database “c” we get the following error back from SQL Server:

Msg 15556, Level 16, State 1, Line 2
Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.

So what exactly happened here, the process worked the first time, but now we can’t duplicate it for additional databases that need to call this stored procedure? The problem is that when we backed up the certificate from database “a” we only backed up the certificate, we didn’t backup the certificate’s private key to a separate file, so we can’t use this same certificate in additional databases to sign stored procedures unless we go back and backup the private key from the certificate as well.

To fix the problem, we first have to drop the stored procedure in database “c” and then drop the certificate without its private key from the database as well. Then we will need to take a new backup of the certificate from database “a” and specify the WITH PRIVATE KEY clause to backup the private key for the certificate to a separate private key file so that we can recreate the certificate in database “c” with the correct private key to sign the stored procedure. Once we have done this, we can recreate our stored procedure and sign it with the certificate, then create the database user for the “testuser” login and grant the database user the EXECUTE privilege on the stored procedure in database “c”, and test the configuration by executing the stored procedure in database “c” to ensure that we get the results from database “b”.

-- Fix the problem
USE [c]
GO
-- Drop the calling procedure with the invalid signature
-- Create the calling stored procedure in the third database
DROP PROCEDURE ExecuteDatabaseB_Select_SomeTable
GO
-- Drop the certificate without the private key
DROP CERTIFICATE [SigningCertificate]
GO
-- Backup the certificate with its private key so it can be used in
-- additional databases to sign stored procedures calling the target
-- stored procedure.
USE a
GO
BACKUP CERTIFICATE [SigningCertificate]
TO FILE = 'c:\SQLskills\SigningCertificate_WithKey.cer'
WITH PRIVATE KEY (
FILE = 'c:\SQLskills\SigningCertificate_WithKey.pvk',
ENCRYPTION BY PASSWORD = '$tr0ngp@$w0rd',
DECRYPTION BY PASSWORD = '$tr0ngp@$w0rd');
GO
-- Create the certificate in our new database with the private key
-- from the new certificate backup with the private key.
USE c
GO
CREATE CERTIFICATE [SigningCertificate]
FROM FILE = 'c:\SQLskills\SigningCertificate_WithKey.cer'
WITH PRIVATE KEY (
FILE = 'c:\SQLskills\SigningCertificate_WithKey.pvk',
ENCRYPTION BY PASSWORD = '$tr0ngp@$w0rd',
DECRYPTION BY PASSWORD = '$tr0ngp@$w0rd');
GO
-- Recreate the calling stored procedure in the third database
CREATE PROCEDURE ExecuteDatabaseB_Select_SomeTable
WITH EXECUTE AS OWNER
AS
EXECUTE b.dbo.Select_SomeTable
GO
-- Sign the procedure with the certificate's private key
ADD SIGNATURE TO OBJECT::[ExecuteDatabaseB_Select_SomeTable]
BY CERTIFICATE [SigningCertificate]
WITH PASSWORD = '$tr0ngp@$w0rd';
GO
-- Create the database user for the testuser login and grant it
-- the EXECUTE privilege on the calling stored procedure
CREATE USER [testuser] FOR LOGIN [testuser]
GO
GRANT EXECUTE ON [dbo].[ExecuteDatabaseB_Select_SomeTable] TO [testuser]
GO
-- Switch to the test login context
EXECUTE AS LOGIN = 'testuser'
EXECUTE c.dbo.ExecuteDatabaseB_Select_SomeTable
GO
-- Revert the context back to our sysadmin login
REVERT
GO

This all works as expected and we’ve resolved our problem. This was a really interesting problem because I had never actually anticipated that someone would use the same certificate in multiple databases to sign multiple stored procedures that perform cross database operations without granting the calling users access to the target database. This repro shows that this is technically possible if you backup the private key for the certificate and restore the private key in the additional databases with the certificate.

See you on the playground!

4 thoughts on “Certificate Signing Stored Procedures in Multiple Databases

  1. This was confusing to me and I was the one with the original question!!! It definitely is a confusing situation, but I thought you covered it well.

    My case was that I have a stored procedure that I use to start a SQL Server agent job and loop until completion, basically making it a synchronous job execution. We have a couple different systems where a user application needs to be able to run the jobs (which are typically ssis packages, doing data migration), but I do not want to grant permissions on this stored procedure to the users.

    Thanks Jonathan. I was definitely very pleased by your promptness and willingness to look into this issue and even expand on it with another blog post.

    1. This was written and posted almost 10 years ago on our old blog platform and used the preformatting done by Live Writer. I went ahead and updated it to use our source code plugin for formatting and it should be easier to copy/paste now. Thanks for pointing this out.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.