SQL Server Service Broker with Signed Procedure - Permissions Issues

Question

We have the Service Broker running with internal activation enabled. The stored procedure is signed. How is this permissions issue occurring? I thought that the signature user takes over when the procedure is called. Why isn't the permissions issue related to the signature user?

916:The server principal "{userName}" is not able to access the database "{sisterDatabaseName}" under the current security context. - Line: 1

We have multiple databases on the same server. These databases are grouped in pairs, with the databases occasionally needing to send information back and forth. Typically, this is done simply by fully qualifying the database name when performing select or inserts between databases.

For example:

SELECT *
FROM dbname.dbo.tablename

However, we have a process that would greatly benefit from asynchronous SQL execution.

Enter the Service Broker! We ran the following script:

ALTER AUTHORIZATION ON database::databasename TO someuser
ALTER DATABASE databasename SET NEW_BROKER WITH ROLLBACK IMMEDIATE
DECLARE @Password VARCHAR(20) = ''
DECLARE @char CHAR = ''
DECLARE @charI INT = 0
DECLARE @len INT = 20 -- Length of Password
WHILE LEN(@Password) < @len
BEGIN
    SET @charI = ROUND(RAND()*73,0) + 49
    SET @char = CHAR(@charI)
    SET @Password += @char
END

SET @SQL = '
CREATE CERTIFICATE {certName}
ENCRYPTION BY PASSWORD = ''' + @Password + '''
WITH SUBJECT = ''Certificate to sign Internal Activation Procedure''

CREATE USER {certificateUserName} FROM CERTIFICATE {certName};

GRANT CONNECT TO PullConfirmRequestQueueProcessorUser;

ADD SIGNATURE TO [dbo].[{procedureName}]
BY CERTIFICATE {certName} WITH PASSWORD = ''' + @Password + '''
'

EXEC(@SQL)

Essentially, we are creating a certificate that can only be assigned to this one specific stored procedure. It is fine for now. This is in the testing phase. If we need a more secure solution that can be extended to multiple procedures, we can come up with one later. I am not worried about it. But, I would expect this test to work.

Here is the stored procedure:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{procedureName}]') AND type in (N'P', N'PC'))
BEGIN
    EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[{procedureName}] AS' 
    GRANT EXECUTE ON {procedureName} TO {username}
END
GO

ALTER PROCEDURE [dbo].[{procedureName}]
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON

    BEGIN TRY
        DECLARE @SSBSTargetDialogHandle UNIQUEIDENTIFIER;
        DECLARE @RecvdRequestMessage XML;
        DECLARE @RecvdRequestMessageTypeName sysname;
        WHILE (1=1)
        BEGIN
            BEGIN TRANSACTION;
            WAITFOR
            ( RECEIVE TOP(1)
            @SSBSTargetDialogHandle = conversation_handle,
            @RecvdRequestMessage = CONVERT(XML, message_body),
            @RecvdRequestMessageTypeName = message_type_name
            FROM dbo.{queueName}
            ), TIMEOUT 1000;

            IF (@@ROWCOUNT = 0)
            BEGIN
                IF (@@TRANCOUNT > 0 ) ROLLBACK TRANSACTION;
                BREAK;
            END

            IF @RecvdRequestMessageTypeName = N'{messageName}'
            BEGIN
                --Statements to process message in this database and "sister" database

                END CONVERSATION @SSBSTargetDialogHandle;
            END
            ELSE IF @RecvdRequestMessageTypeName IN (N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog',N'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
            BEGIN
                END CONVERSATION @SSBSTargetDialogHandle;
            END

            COMMIT TRANSACTION;
        END
    END TRY
    BEGIN CATCH
        IF (@@TRANCOUNT > 0 ) ROLLBACK TRANSACTION;
        INSERT INTO ServiceBrokerErrors(
            MessageXml,
            DialogHandle,
            TimeLoggedUTC,
            ErrorNumber,
            ErrorSeverity,
            ErrorState,
            ErrorProcedure,
            ErrorLine,
            ErrorMessage)
        VALUES(@RecvdRequestMessage,@SSBSTargetDialogHandle,GETUTCDATE(),ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE(),ERROR_LINE(),ERROR_MESSAGE())
    END CATCH
END
GO

The following four queries return the same value:

SELECT sid FROM {mainDatabase}.sys.database_principals WHERE name = 'dbo'
SELECT sid FROM {sisterDatabase}.sys.database_principals WHERE name = 'dbo'
SELECT owner_sid FROM sys.databases WHERE name = '{mainDatabase}'
SELECT owner_sid FROM sys.databases WHERE name = '{sisterDatabase}'

Answer 1

For cross-database access, you need to copy the certificate to each database accessed by the signed proc and create a certificate user in order to provide a security context.

Sample script to copy the cert to the other database and create the cert user:

USE {mainDatabase};
DECLARE @cert_id int = cert_id('{certName}')
DECLARE @public_key  varbinary(MAX) = certencoded(@cert_id),
        @private_key varbinary(MAX) =
           certprivatekey(@cert_id,
              '{password}',
              '{password}');

DECLARE @sql nvarchar(MAX) =
      'CREATE CERTIFICATE {certName}
       FROM  BINARY = ' + convert(varchar(MAX), @public_key, 1) + '
       WITH PRIVATE KEY (BINARY = ' +
          convert(varchar(MAX), @private_key, 1) + ',
          DECRYPTION BY PASSWORD = ''{password}'',
          ENCRYPTION BY PASSWORD = ''{password}'')';

EXEC {sisterDatabase}.sys.sp_executesql @sql;
GO

USE {sisterDatabase];
CREATE USER {certificateUserName} FROM CERTIFICATE {certName};