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

  • 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
    */
  • Thanks for posting your issue and hopefully someone will answer soon.

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

  • 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.

     

  • 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;
    */
  • 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 9 months, 1 week ago by  Ken McKelvey.
  • 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;
  • 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;
    */

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

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