Certificate signed procedure cannot access data on another database in same instance

Question

The scenario is simply to run a stored procedure, sign it with a certificate, and run it as an unprivileged user to get data from another database. The other database happens to be in the same instance as the database containing this stored procedure.

I have tried all variations of certificates, starting with just using password protected certificates which are backed up and restored on the other database for use there. The latest revision I provide below uses the certificate hierarchy as suggested at SQLSmarts.

I'm using SQL Server 2017.

Here is a simplified script I used to try and figure out what was happening.

USE master

SELECT *
FROM sys.symmetric_keys

--Create server master key
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterDatabaseKey123!';
GO

USE BuildSchedule;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'CertificatesBuildScheduleDatabaseKey123!';
GO

USE BuildSchedule
SELECT *
FROM sys.symmetric_keys

USE master;
CREATE CERTIFICATE BuildSchedule_Certificate
    WITH SUBJECT = 'Certificate for Build Schedule stored procedures using dynamic SQL',
EXPIRY_DATE = '2100-01-01'

--Save BuildSchedule cert keys from master to file
USE master

BACKUP CERTIFICATE BuildSchedule_Certificate TO FILE = 'D:\SQL Certificates\BuildSchedule_Certificate.cer'
WITH PRIVATE KEY (FILE = 'D:\SQL Certificates\BuildSchedule_Certificate.pvk'
    ,ENCRYPTION BY PASSWORD = 'Backup Certificate Password 123456789!'
    );
 
--Create certificate in BuildSchedule DB from file.
USE BuildSchedule
CREATE CERTIFICATE BuildSchedule_Certificate FROM FILE = 'D:\SQL Certificates\BuildSchedule_Certificate.cer'
WITH PRIVATE KEY (FILE = 'D:\SQL Certificates\BuildSchedule_Certificate.pvk'
    ,DECRYPTION BY PASSWORD = 'Backup Certificate Password 123456789!'
    );

-- Create a login with limited permissions for testing
USE master
CREATE LOGIN TestBuild WITH PASSWORD = 'P@ssw0rd123!P@ssw0rd123!';

-- Create a login based on the certificate
CREATE LOGIN BuildSchedule_Certificate_Login
    FROM CERTIFICATE BuildSchedule_Certificate;
 
EXEC sp_addsrvrolemember @loginame = 'TestCertLogin',
                         @rolename = 'bulkadmin';
 
 
-- Create a database user from the login for testing 
USE BuildSchedule
CREATE USER TestBuild FOR LOGIN TestBuild;

-- Create a database user from the certificate login 
USE BuildSchedule
CREATE USER BuildSchedule_Certificate_User FOR LOGIN BuildSchedule_Certificate_Login;

--Create test procedure in application database
USE BuildSchedule
CREATE PROCEDURE BuildSchedule.TestCertPermissions
AS
BEGIN
    SELECT USER_NAME(),
           SUSER_SNAME(),
           ORIGINAL_LOGIN();

    SELECT *
    FROM fn_my_permissions (NULL, 'SERVER')
    UNION ALL
    SELECT *
    FROM fn_my_permissions (NULL, 'DATABASE');

    SELECT TOP 100 *
    FROM Reporting.dbo.StockCodes

END
GRANT EXECUTE ON BuildSchedule.TestCertPermissions TO TestBuild

-- Execute it using my login
EXEC BuildSchedule.TestCertPermissions;

-- Execute it using the restricted login
EXECUTE AS USER = 'TestBuild';
EXEC BuildSchedule.TestCertPermissions;
REVERT;

-- Add signature to stored procedure
ADD SIGNATURE TO BuildSchedule.TestCertPermissions
    BY CERTIFICATE BuildSchedule_Certificate;

-- Execute it using my login
EXEC BuildSchedule.TestCertPermissions;

-- Execute it using the restricted login
EXECUTE AS USER = 'TestBuild';
EXEC BuildSchedule.TestCertPermissions;
REVERT;

USE Reporting;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'CertificatesReportingDatabaseKey123!';

USE Reporting
SELECT *
FROM sys.symmetric_keys

--Create certificate in Reporting DB from file.
USE Reporting
CREATE CERTIFICATE BuildSchedule_Certificate FROM FILE = 'D:\SQL Certificates\BuildSchedule_Certificate.cer'
WITH PRIVATE KEY (FILE = 'D:\SQL Certificates\BuildSchedule_Certificate.pvk'
    ,DECRYPTION BY PASSWORD = 'Backup Certificate Password 123456789!'
    );

-- Create a database user from the certificate login 
USE Reporting
CREATE USER BuildSchedule_Certificate_User FOR LOGIN BuildSchedule_Certificate_Login;

USE master
GRANT AUTHENTICATE SERVER TO BuildSchedule_Certificate_Login

USE Reporting
GRANT
GRANT SELECT ON dbo.StockCodes TO BuildSchedule_Certificate_User

--Execute it using the restricted login
USE BuildSchedule
EXECUTE AS USER = 'TestBuild';
EXEC BuildSchedule.TestCertPermissions;
REVERT;

And this is the error I get.

Msg 916, Level 14, State 1, Procedure BuildSchedule.TestCertPermissions, Line 14 [Batch Start Line 95]
The server principal "TestBuild" is not able to access the database "Reporting" under the current security context.

I have confirmed that the thumbprints on all certificates are the same across both databases, that the certificate user has select permission on the table being queried, as well as public on the database. I'm not sure what else to try.

Answer 1

After a few days of pulling my hair out, this post at http://rusanu.com/2006/03/07/call-a-procedure-in-another-database-from-an-activated-procedure/ gave me a clue into what was happening. The code below solved my problem. I think the key take-away was that the permissions of the security certificate must be explicit, even to the extent of accessing data on the same server. Essentially, the public certificate must be granted AUTHORIZATION on the local server, as well as CONNECT on the database being accessed.

In this new rendition, The certificate with private key was created in the application database, the procedures were signed, the private key dropped, then the certificate was backed up, and used to create a server level certificate, which is used to create a login, which would then be inherited by users created on databases on the server. Permissions are then granted as required.

I am not sure that this fully answers my original question, as it still is not clear why the script I originally posted does not work. It does essentially to follow the same line of logic. The only difference I can see is that the procedure was signed without a password, but this is presumably because it is not required because the private key is protected by the server level master key.

Here is the working script:

USE master
CREATE LOGIN TestBuild WITH PASSWORD = 'P@ssw0rd123!P@ssw0rd123!';

USE BuildSchedule
CREATE USER TestBuild FOR LOGIN TestBuild;

CREATE PROCEDURE BuildSchedule.TestCertPermissions
WITH EXECUTE AS OWNER
AS
BEGIN
    SELECT USER_NAME(),
           SUSER_SNAME(),
           ORIGINAL_LOGIN();

    SELECT *
    FROM fn_my_permissions (NULL, 'SERVER')
    UNION ALL
    SELECT *
    FROM fn_my_permissions (NULL, 'DATABASE');

    SELECT TOP 100 *
    FROM SysproReporting.dbo.StockCodes;

END

GRANT EXECUTE ON BuildSchedule.TestCertPermissions TO TestBuild

CREATE CERTIFICATE BuildSchedule_Certificate
    ENCRYPTION BY PASSWORD = 'BuildSchedulePassword123!'
    WITH SUBJECT = 'Certificate for Build Schedule stored procedures using dynamic SQL',
    EXPIRY_DATE = '2100-01-01';

--Sign procedure with Private Key
ADD SIGNATURE TO BuildSchedule.TestCertPermissions 
    BY CERTIFICATE BuildSchedule_Certificate 
    WITH PASSWORD = 'BuildSchedulePassword123!';

--Remove private key to prevent further use for signing
ALTER CERTIFICATE BuildSchedule_Certificate
    REMOVE PRIVATE KEY;

--Backup certificate to file
BACKUP CERTIFICATE BuildSchedule_Certificate 
    TO FILE = 'D:\SQL Certificates\BuildSchedule_Certificate.cer';

USE master;
CREATE CERTIFICATE BuildSchedule_Certificate 
    FROM FILE = 'D:\SQL Certificates\BuildSchedule_Certificate.cer'

CREATE LOGIN BuildSchedule_Certificate_User
    FROM CERTIFICATE BuildSchedule_Certificate

GRANT AUTHENTICATE SERVER TO BuildSchedule_Certificate_User

USE Reporting;
CREATE USER BuildSchedule_Certificate_User
    FROM LOGIN BuildSchedule_Certificate_User

GRANT SELECT ON dbo.StockCodes TO BuildSchedule_Certificate_User

USE BuildSchedule
EXECUTE AS USER = 'TestBuild';
EXEC BuildSchedule.TestCertPermissions;
REVERT;