Can't use msdb.dbo.sp_send_dbmail when in service broker - executes as guest?

5 votes
2 answers
2805 views
sql-server sql-server-2008-r2 permissions service-broker signature
                          I have a procedure TheNotificationProcedure that makes a cross-database call to msdb.dbo.sp_send_dbmail.

It gets invoked (indirectly) from a service broker queue:

    CREATE QUEUE [Blah].[TheQueue]
    WITH ACTIVATION (STATUS = ON, PROCEDURE_NAME = [Blah].[TheQueueProcedure], 
    MAX_QUEUE_READERS = 1, EXECUTE AS N'TheUser');

TheQueueProcedure eventually calls TheNotificationProcedure

If I connect in SSMS as TheUser and execute TheNotificationProcedure, everything works and emails go out. 

However, when TheNotificationProcedure is invoked as a result of a message arriving on the queue, it fails as unable to access the msdb procedure.

I've tried everything I can think of, including creating my own procedure in msdb that wraps sp_send_dbmail and signing both my dbmail wrapper and TheNotificationProcedure with the same certiface, and ensure the certificate user in msdb is a member of "DatabaseMailUserRole".

Finally, after doing many more detailed traces, I eventually noticed the following:



That is even though the service broker is executing under the *login* of TheUser, for some reason, it is executing under the database *user* of guest, which I suspect at least partially explains my permissions issues.

The *login* TheUser is also mapped to a *user* in msdb called TheUser - it is certainly not mapped to guest.

So why is it being executed as guest in msdb when going through service broker?

I need to avoid marking the database as Trustworthy.  I  was hoping that by signing the procedures (e.g. http://www.sommarskog.se/grantperm.html)  I could get permissions to transfer across the database - does execute as negate any permissions that would typically be associated via the certificate user?

Here is a script to duplicate the above permissions issue *without* any of the module signing (which gives the same "guest" trace) when going through service broker:

Setup:

    --REPLACE EMAIL, and db_mail profile
    --@profile_name = 'Test db mail profile',
    --@recipients = 'test@test.test',

    use master;
    GO

    IF EXISTS(select * FROM sys.databases where name='http://dba.stackexchange.com/questions/166033 ')
    BEGIN
	    ALTER DATABASE [http://dba.stackexchange.com/questions/166033]  SET OFFLINE WITH ROLLBACK IMMEDIATE;
	    ALTER DATABASE [http://dba.stackexchange.com/questions/166033]  SET ONLINE WITH ROLLBACK IMMEDIATE;
	    DROP DATABASE [http://dba.stackexchange.com/questions/166033] ;
    END

    CREATE DATABASE [http://dba.stackexchange.com/questions/166033] ;
    GO

    IF EXISTS(select * FROM sys.server_principals WHERE name = 'TheUser' AND type_desc='SQL_LOGIN') DROP LOGIN TheUser;

    CREATE LOGIN [TheUser] WITH PASSWORD=N'jL839lIFKttcm3cNuk1WUazfk5lS76RKMscZ01UdFkI='
        , DEFAULT_DATABASE=[http://dba.stackexchange.com/questions/166033] 
        , DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;

    use [msdb];
    GO

    IF (NOT EXISTS(select * FROM sys.database_principals WHERE name = 'TheUser')) CREATE USER [TheUser] FOR LOGIN [TheUser] WITH DEFAULT_SCHEMA=[dbo];

    exec sp_addrolemember 'DatabaseMailUserRole', 'TheUser';
    GO
    use [http://dba.stackexchange.com/questions/166033] ;
    GO

    CREATE USER [TheUser] FOR LOGIN [TheUser] WITH DEFAULT_SCHEMA=[dbo]
    GO

    CREATE SCHEMA [Blah] AUTHORIZATION dbo;
    GO

    CREATE QUEUE [Blah].[SourceQueue];
    GO

    CREATE SERVICE [//FromService]
        AUTHORIZATION [dbo]
        ON QUEUE [Blah].[SourceQueue];
    GO

    CREATE MESSAGE TYPE [//TestMessage]
        AUTHORIZATION [dbo]
        VALIDATION = NONE;
    GO

    CREATE CONTRACT [//ServiceContract]
        AUTHORIZATION [dbo]
        ([//TestMessage] SENT BY INITIATOR);
    GO

    CREATE PROCEDURE [Blah].[SendMessage]
    AS
    DECLARE @message varchar(50),
            @conversationHandle UNIQUEIDENTIFIER

        SET    @message = 'Test Message Content';

        -- Begin the dialog.
        BEGIN DIALOG CONVERSATION @conversationHandle
            FROM SERVICE [//FromService]
            TO SERVICE '//ToService'
            ON CONTRACT [//ServiceContract] 
            WITH ENCRYPTION = OFF;

        -- Send the message on the dialog.
        SEND ON CONVERSATION @conversationHandle
          MESSAGE TYPE [//TestMessage]
          (@message) ;

        END CONVERSATION @conversationHandle ;
    GO


    CREATE  PROCEDURE [dbo].[TheNotificationProcedure]
    AS
        PRINT 'DEBUG - Entering [dbo].[TheNotificationProcedure]'

        -- Send notification
        PRINT 'DEBUG - [dbo].[TheNotificationProcedure] - PRIOR TO msdb.dbo.sp_send_dbmail'

        declare @log nvarchar(max) = ''; 
        select @log = @log + 'name: ' + name + ' ' + 'type: ' + type + ' usage: ' + usage + ' || ' FROM sys.login_token 
        print @log

        declare @mailitem_id int;

        --exec [msdb].[dbo].[WRAP__sp_send_dbmail]
        exec [msdb].[dbo].[sp_send_dbmail]
                    @profile_name = 'Test db mail profile',
                    @recipients = 'test@test.test', --@Recipient,
                    @subject = 'Testing sp_send_dbmail', --@NotificationSubject,
                    @body = 'Testing sp_sdend_dbmail from service broker', --@NotificationBody,
                    @exclude_query_output = 1,
                    @mailitem_id = @mailitem_id OUTPUT

        PRINT 'DEBUG - [dbo].[TheNotificationProcedure] - AFTER msdb.dbo.sp_send_dbmail'

    GO

    CREATE PROCEDURE [Blah].[TestMessageHandler]
    AS
        --has other logic that eventully calls notification
        EXECUTE [dbo].[TheNotificationProcedure]
    GO

    CREATE PROCEDURE [Blah].[TheQueueProcedure]
    AS
    --Service Broker variables
    DECLARE @conversation_handle UNIQUEIDENTIFIER,
            @conversation_group_id  UNIQUEIDENTIFIER,
            @message_body varchar(255),
            @message_type_name NVARCHAR(256),
            @dialog UNIQUEIDENTIFIER,
            @RowsReceived    int

    PRINT 'Start'
        WHILE (1 = 1)
        BEGIN

            -- Get next conversation group.

            WAITFOR(
               GET CONVERSATION GROUP @conversation_group_id FROM [Blah].[TheQueue]),
               TIMEOUT 500 ;

            -- If there are no more conversation groups, roll back the
            -- transaction and break out of the outermost WHILE loop.

            IF @conversation_group_id IS NULL
            BEGIN
                BREAK ;
            END ;

            WHILE (1 = 1)
            BEGIN
                BEGIN TRANSACTION
                PRINT 'Get Message'
                ;        RECEIVE TOP (1) 
                                @dialog = conversation_handle,
                                @message_type_name=message_type_name,
                                @message_body=message_body
                        FROM    [Blah].[TheQueue]
                        WHERE conversation_group_id = @conversation_group_id ;

                SET    @RowsReceived = @@ROWCOUNT
                PRINT 'Queue Read: ' + ISNULL(@message_body, '')
                PRINT '@RowsReceived: ' + CAST(@RowsReceived as varchar(200))
                IF (@RowsReceived = 0)
                    BEGIN
                        BREAK ;
                    END ;

                PRINT 'Deal with Message'

                IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog ')
                BEGIN
                    PRINT 'End Dialog received for dialog # ' + cast(@dialog as nvarchar(40)) ;
                    END CONVERSATION @dialog ;
                END ;

                IF (@message_type_name = '//TestMessage')
                BEGIN
                    print 'Have //TestMessage: ' + @message_body

                    exec [Blah].[TestMessageHandler];

                END

                COMMIT TRANSACTION;
            END

        END
        RETURN

    GO





    CREATE QUEUE [Blah].[TheQueue]
        WITH ACTIVATION (STATUS = ON, PROCEDURE_NAME = [Blah].[TheQueueProcedure], MAX_QUEUE_READERS = 1, EXECUTE AS N'TheUser');
    GO

    CREATE SERVICE [//ToService]
        AUTHORIZATION [dbo]
        ON QUEUE [Blah].[TheQueue]
        ([//ServiceContract]);
    GO


    GRANT EXECUTE ON [Blah].[TheQueueProcedure] TO [TheUser];
    GO

Then to kick everything off:

    --kick everything off
    EXEC [Blah].[SendMessage];

    GO

    --read results from error log
    --(might need to execute once or twice to get results - because service broker is asynchronous)
    declare @sqlErrorLog table (LogDate datetime, ProcessInfo nvarchar(max), Text nvarchar(max));
    INSERT INTO @sqlErrorLog EXEC xp_ReadErrorLog 

    SELECT * FROM @sqlErrorLog
    WHERE LogDate >= DATEADD(SECOND, -15, GETDATE()) AND Text NOT LIKE 'CHECKDB%' AND Text NOT LIKE 'Starting up database ''upgrade%' AND Text NOT LIKE '%upgrade%information%' AND TEXT  'Error: 9001, Severity: 21, State: 1.'
    ORDER BY LogDate
                        
Asked by Nathan (151 rep)
Mar 2, 2017, 10:47 PM
Last activity: Sep 25, 2017, 08:35 PM
Can't use msdb.dbo.sp_send_dbmail when in service broker - executes as guest?

Related Questions
