Frustrated trying to get Cross DB Access by Signing SP with Certificate

Question

Post reply

Frustrated trying to get Cross DB Access by Signing SP with Certificate

Ken McKelvey

SSCoach

Points: 18825

March 2, 2024 at 5:54 pm

#4388335

Maybe it is lack of caffeine, but I cannot see why the following test code does not work. I keep getting:

Msg 916, Level 14, State 2, Procedure dbo.TestTrunc, Line 7 [Batch Start Line 92]
The server principal "XDBSignTest" is not able to access the database "db2" under the current security context.

Can anyone see what the problem is?

SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO
CREATE DATABASE db2;
GO
USE db2;
GO
CREATE TABLE dbo.Test
(
    TestId int NOT NULL PRIMARY KEY
    ,TestDesc varchar(20) NOT NULL
);
INSERT INTO dbo.Test
VALUES (1, 'Test1'),(2, 'Test2'),(3, 'Test3');
GO
CREATE DATABASE db1;
GO
USE db1;
GO
CREATE OR ALTER PROCEDURE dbo.TestTrunc
AS
SET NOCOUNT, XACT_ABORT ON;

SELECT * FROM db2.dbo.Test;

TRUNCATE TABLE db2.dbo.Test;

SELECT * FROM db2.dbo.Test;
GO
CREATE LOGIN XDBSignTest WITH PASSWORD = 'Password&1';
GO
CREATE USER XDBSignTest FROM LOGIN XDBSignTest;
GRANT EXECUTE ON dbo.TestTrunc TO XDBSignTest;
GO
DECLARE @password nchar(37) = convert(nchar(36), newid()) + 'a'
    ,@SQL nvarchar(MAX);

SET @SQL = CONCAT
(
N'CREATE CERTIFICATE dbo_TestTrunc$Cert
ENCRYPTION BY PASSWORD =  N''', @password, N'''
WITH SUBJECT = ''"Cert for dbo_TestTrunc"'', EXPIRY_DATE = ''99991231'';'
);
EXEC (@SQL);

SET @SQL = CONCAT
(
N'ADD SIGNATURE TO dbo.TestTrunc BY CERTIFICATE dbo_TestTrunc$Cert
WITH PASSWORD = N''', @password, N''';'
);
EXEC (@SQL);
GO
ALTER CERTIFICATE dbo_TestTrunc$Cert REMOVE PRIVATE KEY;
GO

DECLARE @public_key varbinary(MAX) = CERTENCODED(CERT_ID(N'dbo_TestTrunc$Cert'))
    ,@SQL nvarchar(MAX);
    
SET @SQL = CONCAT
(
N'CREATE CERTIFICATE db1_dbo_TestTrunc$Cert
FROM BINARY = ', CONVERT(nvarchar(MAX), @public_key, 1), N';'
);

USE db2;
EXEC (@SQL);
GO
CREATE USER db1_dbo_TestTrunc$CertUser
FROM CERTIFICATE db1_dbo_TestTrunc$Cert;

GRANT SELECT, ALTER ON dbo.Test TO db1_dbo_TestTrunc$CertUser;
GO
/* Now check everyting lines up on Thumbprint */
USE db1;
GO
SELECT P.thumbprint
FROM sys.crypt_properties P
    JOIN sys.certificates C
        ON P.thumbprint = P.thumbprint
WHERE OBJECT_SCHEMA_NAME(P.major_id) = N'dbo'
    AND OBJECT_NAME(P.major_id) = N'TestTrunc'
    AND C.[name] = N'dbo_TestTrunc$Cert';

SELECT thumbprint FROM db1.sys.certificates;

SELECT thumbprint FROM db2.sys.certificates;

/* test */
SELECT SUSER_NAME(), USER_NAME(), ORIGINAL_LOGIN();
EXECUTE AS LOGIN = 'XDBSignTest';
SELECT SUSER_NAME(), USER_NAME(), ORIGINAL_LOGIN();

USE db1;
GO
EXEC dbo.TestTrunc;
GO

REVERT;
SELECT SUSER_NAME(), USER_NAME(), ORIGINAL_LOGIN();

/* Remove Junk
USE [master];
GO
DROP LOGIN XDBSignTest;
GO
DROP DATABASE db1;
GO
DROP DATABASE db2;
GO
*/

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Ken McKelvey SSCoach Points: 18825 More actions · Answer 2

I have just managed to quickly look at this again. The problem is with the ALTER permission and TRUNCATE TABLE as it seems to work fine if the SP just does SELECT, INSERT, UPDATE, DELETE.

I will try and investigate further sometime but maybe cross database certificates just have problems with DDL.

Ken McKelvey SSCoach Points: 18825 More actions · Answer 3

As alter will work with a certificate in a single database, the way around this is to create a signed SP in db2 to truncate the table and then call the db2 SP from the db1 SP. The moral is to not just try and do something quickly!

CREATE DATABASE db2;
GO
USE db2;
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE TABLE dbo.Test
(
    TestId int NOT NULL PRIMARY KEY
    ,TestDesc varchar(20) NOT NULL
);
INSERT INTO dbo.Test
VALUES (1, 'Test1'),(2, 'Test2'),(3, 'Test3');
GO
CREATE OR ALTER PROCEDURE dbo.Trunc_dbo_Test
AS
SET NOCOUNT, XACT_ABORT ON;

TRUNCATE TABLE dbo.Test;
GO

IF EXISTS
(
    SELECT 1
    FROM sys.crypt_properties P
        JOIN sys.certificates C
            ON P.thumbprint = P.thumbprint
    WHERE OBJECT_SCHEMA_NAME(P.major_id) = N'dbo'
        AND OBJECT_NAME(P.major_id) = N'Trunc_dbo_Test'
        AND C.[name] = N'dbo_Trunc_dbo_Test$Cert'
)
DROP SIGNATURE FROM dbo.Trunc_dbo_Test BY CERTIFICATE dbo_Trunc_dbo_Test$Cert;

DROP USER IF EXISTS dbo_Trunc_dbo_Test$CertUser;

IF EXISTS
(
    SELECT 1
    FROM sys.certificates
    WHERE [name] = N'dbo_Trunc_dbo_Test$Cert'
)
DROP CERTIFICATE dbo_Trunc_dbo_Test$Cert;
GO

DECLARE @password nchar(37) = convert(nchar(36), newid()) + 'a'
    ,@SQL nvarchar(4000);

SET @SQL = CONCAT
(
N'CREATE CERTIFICATE dbo_Trunc_dbo_Test$Cert
ENCRYPTION BY PASSWORD =  N''', @password, N'''
WITH SUBJECT = ''"Cert for dbo_Trunc_dbo_Test"'';'
);
EXEC (@SQL);

SET @SQL = CONCAT
(
N'ADD SIGNATURE TO dbo.Trunc_dbo_Test BY CERTIFICATE dbo_Trunc_dbo_Test$Cert
WITH PASSWORD = N''', @password, N''';'
)
EXEC (@SQL);
GO

ALTER CERTIFICATE dbo_Trunc_dbo_Test$Cert REMOVE PRIVATE KEY;

CREATE USER dbo_Trunc_dbo_Test$CertUser FROM CERTIFICATE dbo_Trunc_dbo_Test$Cert;

GRANT ALTER ON dbo.Test TO dbo_Trunc_dbo_Test$CertUser;

USE [master];
GO
CREATE DATABASE db1;
GO
USE db1;
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE OR ALTER PROCEDURE dbo.TruncTest
AS
SET NOCOUNT, XACT_ABORT ON;

select * from db2.dbo.Test;
EXEC db2.dbo.Trunc_dbo_Test;
select * from db2.dbo.Test;
GO

USE db1;
GO
IF EXISTS
(
    SELECT 1
    FROM sys.crypt_properties P
        JOIN sys.certificates C
            ON P.thumbprint = P.thumbprint
    WHERE OBJECT_SCHEMA_NAME(P.major_id) = N'dbo'
        AND OBJECT_NAME(P.major_id) = N'TruncTest'
        AND C.[name] = N'dbo_TruncTest$Cert'
)
DROP SIGNATURE FROM dbo.TruncTest BY CERTIFICATE dbo_TruncTest$Cert;
GO

IF EXISTS
(
    SELECT 1
    FROM sys.certificates
    WHERE [name] = N'dbo_TruncTest$Cert'
)
DROP CERTIFICATE dbo_TruncTest$Cert;
GO

DECLARE @password nchar(37) = convert(nchar(36), newid()) + 'a'
    ,@SQL nvarchar(MAX);

SET @SQL = CONCAT
(
N'CREATE CERTIFICATE dbo_TruncTest$Cert
ENCRYPTION BY PASSWORD =  N''', @password, N'''
WITH SUBJECT = ''"dbo_TruncTest$Cert"'';'
);
EXEC (@SQL);

SET @SQL = CONCAT
(
N'ADD SIGNATURE TO dbo.TruncTest BY CERTIFICATE dbo_TruncTest$Cert
WITH PASSWORD = N''', @password, N''';'
);
EXEC (@SQL);

GO

ALTER CERTIFICATE dbo_TruncTest$Cert REMOVE PRIVATE KEY;

GO

USE db1;

DECLARE @public_key varbinary(MAX) = CERTENCODED(CERT_ID(N'dbo_TruncTest$Cert'))
    ,@SQL nvarchar(MAX);
    
SET @SQL = CONCAT
(
N'CREATE CERTIFICATE db1_dbo_TruncTest$Cert
FROM BINARY = ', CONVERT(nvarchar(MAX), @public_key, 1), N';'
);

USE db2;

DROP USER IF EXISTS db1_dbo_TruncTest$CertUser;

IF EXISTS
(
    SELECT 1
    FROM sys.certificates
    WHERE [name] = N'db1_dbo_TruncTest$Cert'
)
DROP CERTIFICATE db1_dbo_TruncTest$Cert;

EXEC (@SQL);
GO

USE db2;

CREATE USER db1_dbo_TruncTest$CertUser
FROM CERTIFICATE db1_dbo_TruncTest$Cert;

GRANT SELECT ON dbo.Test TO db1_dbo_TruncTest$CertUser;
GRANT EXEC ON dbo.Trunc_dbo_Test TO db1_dbo_TruncTest$CertUser;


USE db1;

SELECT P.thumbprint
FROM sys.crypt_properties P
    JOIN sys.certificates C
        ON P.thumbprint = P.thumbprint
WHERE OBJECT_SCHEMA_NAME(P.major_id) = N'dbo'
    AND OBJECT_NAME(P.major_id) = N'TruncTest'
    AND C.[name] = N'dbo_TruncTest$Cert';

SELECT thumbprint FROM db1.sys.certificates;

SELECT thumbprint FROM db2.sys.certificates;

GO

/*** Test ***/

CREATE LOGIN XDBSignTest WITH PASSWORD = 'Password&1'
GO
USE db1;
GO
CREATE USER XDBSignTest FROM LOGIN XDBSignTest
GRANT EXECUTE ON dbo.TruncTest TO XDBSignTest;

-- Login as XDBSignTest and try:
USE db1;
GO
SELECT SUSER_NAME(), USER_NAME(), ORIGINAL_LOGIN();
EXECUTE AS LOGIN = 'XDBSignTest';
SELECT SUSER_NAME(), USER_NAME(), ORIGINAL_LOGIN();

EXEC dbo.TruncTest;

REVERT;
SELECT SUSER_NAME(), USER_NAME(), ORIGINAL_LOGIN();
GO

/* Tidy Up
USE db1;
GO
DROP USER IF EXISTS XDBSignTest;
DROP LOGIN  XDBSignTest;
GO
USE [master];
GO
DROP DATABASE db2;
DROP DATABASE db1;
*/

Ken McKelvey SSCoach Points: 18825 More actions · Answer 4

Further reading:

https://straightforwardsql.com/posts/cross-db-access-with-module-signing/

https://sqlquantumleap.com/2017/12/30/please-please-please-stop-using-impersonation-execute-as/

This is detailed and probably requires you to place a bag of frozen peas on top of your head.

https://www.sommarskog.se/grantperm.html

Also, here are some outline SSMS templates (Ctrl Shift M) which have scope for being tidied up.

This reply was modified 6 months, 1 week ago by Ken McKelvey.

Ken McKelvey SSCoach Points: 18825 More actions · Answer 5

1. Sign a SP for permissions within the same database.

USE <Main DB, sysname,>;
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE OR ALTER PROCEDURE <schema_name, sysname, dbo>.<proc_name, sysname,>
AS
SET NOCOUNT, XACT_ABORT ON;

RETURN;
GO

GRANT [rights] ON [object] TO <schema_name, sysname, dbo>_<proc_name, sysname,>;

GO

IF EXISTS
(
    SELECT 1
    FROM sys.crypt_properties P
        JOIN sys.certificates C
            ON P.thumbprint = P.thumbprint
    WHERE OBJECT_SCHEMA_NAME(P.major_id) = N'<schema_name, sysname, dbo>'
        AND OBJECT_NAME(P.major_id) = N'<proc_name, sysname,>'
        AND C.[name] = N'<schema_name, sysname, dbo>_<proc_name, sysname,>$Cert'
)
DROP SIGNATURE FROM <schema_name, sysname, dbo>.<proc_name, sysname,> BY CERTIFICATE <schema_name, sysname, dbo>_<proc_name, sysname,>$Cert;

DROP USER IF EXISTS <schema_name, sysname, dbo>_<proc_name, sysname,>$CertUser;

IF EXISTS
(
    SELECT 1
    FROM sys.certificates
    WHERE [name] = N'<schema_name, sysname, dbo>_<proc_name, sysname,>$Cert'
)
DROP CERTIFICATE <schema_name, sysname, dbo>_<proc_name, sysname,>$Cert;
GO

DECLARE @password nchar(37) = convert(nchar(36), newid()) + 'a'
    ,@SQL nvarchar(4000);

SET @SQL = CONCAT
(
N'CREATE CERTIFICATE <schema_name, sysname, dbo>_<proc_name, sysname,>$Cert
ENCRYPTION BY PASSWORD =  N''', @password, N'''
WITH SUBJECT = ''"Cert for <schema_name, sysname, dbo>_<proc_name, sysname,>"'';'
);
EXEC (@SQL);

SET @SQL = CONCAT
(
N'ADD SIGNATURE TO <schema_name, sysname, dbo>.<proc_name, sysname,> BY CERTIFICATE <schema_name, sysname, dbo>_<proc_name, sysname,>$Cert
WITH PASSWORD = N''', @password, N''';'
)
EXEC (@SQL);
GO

ALTER CERTIFICATE <schema_name, sysname, dbo>_<proc_name, sysname,>$Cert REMOVE PRIVATE KEY;

CREATE USER <schema_name, sysname, dbo>_<proc_name, sysname,>$CertUser FROM CERTIFICATE <schema_name, sysname, dbo>_<proc_name, sysname,>$Cert;
GO

GRANT [rights] ON [object] TO <schema_name, sysname, dbo>_<proc_name, sysname,>$CertUser;

Ken McKelvey SSCoach Points: 18825 More actions · Answer 6

2. Sign a SP for permissions in another Database.

/* Only works with DML */
USE <main_db, sysname,>;
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE OR ALTER PROCEDURE <schema_name, sysname, dbo>.<proc_name, sysname,>
AS
SET NOCOUNT, XACT_ABORT ON;

RETURN;
GO

USE <main_db, sysname,>;
GO
IF EXISTS
(
    SELECT 1
    FROM sys.crypt_properties P
        JOIN sys.certificates C
            ON P.thumbprint = P.thumbprint
    WHERE OBJECT_SCHEMA_NAME(P.major_id) = N'<schema_name, sysname, dbo>'
        AND OBJECT_NAME(P.major_id) = N'<proc_name, sysname,>'
        AND C.[name] = N'<schema_name, sysname, dbo>_<proc_name, sysname,>$Cert'
)
DROP SIGNATURE FROM <schema_name, sysname, dbo>.<proc_name, sysname,> BY CERTIFICATE <schema_name, sysname, dbo>_<proc_name, sysname,>$Cert;
GO

IF EXISTS
(
    SELECT 1
    FROM sys.certificates
    WHERE [name] = N'<schema_name, sysname, dbo>_<proc_name, sysname,>$Cert'
)
DROP CERTIFICATE <schema_name, sysname, dbo>_<proc_name, sysname,>$Cert;
GO

DECLARE @password nchar(37) = convert(nchar(36), newid()) + 'a'
    ,@SQL nvarchar(MAX);

SET @SQL = CONCAT
(
N'CREATE CERTIFICATE <schema_name, sysname, dbo>_<proc_name, sysname,>$Cert
ENCRYPTION BY PASSWORD =  N''', @password, N'''
WITH SUBJECT = ''"<schema_name, sysname, dbo>_<proc_name, sysname,>$Cert"'';'
);
EXEC (@SQL);

SET @SQL = CONCAT
(
N'ADD SIGNATURE TO <schema_name, sysname, dbo>.<proc_name, sysname,> BY CERTIFICATE <schema_name, sysname, dbo>_<proc_name, sysname,>$Cert
WITH PASSWORD = N''', @password, N''';'
);
EXEC (@SQL);

GO

ALTER CERTIFICATE <schema_name, sysname, dbo>_<proc_name, sysname,>$Cert REMOVE PRIVATE KEY;

GO

USE <main_db, sysname,>;

DECLARE @public_key varbinary(MAX) = CERTENCODED(CERT_ID(N'<schema_name, sysname, dbo>_<proc_name, sysname,>$Cert'))
    ,@SQL nvarchar(MAX);
    
SET @SQL = CONCAT
(
N'CREATE CERTIFICATE <main_db, sysname,>_<schema_name, sysname, dbo>_<proc_name, sysname,>$Cert
FROM BINARY = ', CONVERT(nvarchar(MAX), @public_key, 1), N';'
);

USE <other_db, sysname,>;

DROP USER IF EXISTS <main_db, sysname,>_<schema_name, sysname, dbo>_<proc_name, sysname,>$CertUser;

IF EXISTS
(
    SELECT 1
    FROM sys.certificates
    WHERE [name] = N'<main_db, sysname,>_<schema_name, sysname, dbo>_<proc_name, sysname,>$Cert'
)
DROP CERTIFICATE <main_db, sysname,>_<schema_name, sysname, dbo>_<proc_name, sysname,>$Cert;

EXEC (@SQL);
GO

USE <other_db, sysname,>;

CREATE USER <main_db, sysname,>_<schema_name, sysname, dbo>_<proc_name, sysname,>$CertUser
FROM CERTIFICATE <main_db, sysname,>_<schema_name, sysname, dbo>_<proc_name, sysname,>$Cert;

GRANT [rights] ON [object] TO <main_db, sysname,>_<schema_name, sysname, dbo>_<proc_name, sysname,>$CertUser;

USE <main_db, sysname,>;

SELECT P.thumbprint
FROM sys.crypt_properties P
    JOIN sys.certificates C
        ON P.thumbprint = P.thumbprint
WHERE OBJECT_SCHEMA_NAME(P.major_id) = N'<schema_name, sysname, dbo>'
    AND OBJECT_NAME(P.major_id) = N'<proc_name, sysname,>'
    AND C.[name] = N'<schema_name, sysname, dbo>_<proc_name, sysname,>$Cert';

SELECT thumbprint FROM <main_db, sysname,>.sys.certificates;

SELECT thumbprint FROM <other_db, sysname,>.sys.certificates;

SELECT 
GO

/* Test - Cannot just impersonate a user

CREATE LOGIN XDBSignTest WITH PASSWORD = 'Password&1'
GO
USE <main_db, sysname,>;
GO
CREATE USER XDBSignTest FROM LOGIN XDBSignTest
GRANT EXECUTE ON <schema_name, sysname, dbo>.<proc_name, sysname,> TO XDBSignTest;

-- Login as XDBSignTest and try:
USE <main_db, sysname,>;
GO
SELECT SUSER_NAME(), USER_NAME(), ORIGINAL_LOGIN();
EXECUTE AS LOGIN = 'XDBSignTest';
SELECT SUSER_NAME(), USER_NAME(), ORIGINAL_LOGIN();

EXEC <schema_name, sysname, dbo>.<proc_name, sysname,>;

REVERT;
SELECT SUSER_NAME(), USER_NAME(), ORIGINAL_LOGIN();
GO


USE <main_db, sysname,>;
GO
DROP USER IF EXISTS XDBSignTest;
DROP LOGIN IF EXISTS XDBSignTest;
*/