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:

Service Broker Trace

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, '<NULL>')
            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
  • Off the top of my head I'm fairly certain service broker activated stored procedures and/or the queues for the services are impersonated using execute as user and due to the way execute as user works you are limited to impersonation in the scope of the database for that stored procedure and not inherit server level permissions. Because of this, I believe you'll need to set the database settings trustworthy to on so that the user context can extend across databases. TBH I hate seeing databases marked trustworthy. – Sean Gallardy Mar 3 '17 at 0:16
up vote 11 down vote
+150

I need to avoid marking the database as Trustworthy.

That is certainly the right attitude to have towards TRUSTWORTHY, and yes, this is possible.

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

I had originally thought that the issue was the typical cause of cross-Database problems when using Impersonation: by default, impersonating a Database-level principal (which is all that the EXECUTE AS clause, not statement, can do) will be quarantined to the local Database.

However, additional testing and discussion with the O.P. lead to the discovery that this scenario was slightly different. It seems that using Service Broker is one of the very few occasions that module signing does not solve all security problems. That is what I had thought upon setting up the typical implementation of module signing since it didn't work. So, I tried several things and found that only SQLCLR was able to accomplish this.

Then recently I found a related question, sa doesn't have permissions to other database through synonyms with the Service Broker, that referenced a post by @Remus Rusanu in which Remus says that this is indeed possible. Figuring that Remsus's example code worked, I concluded that I must have missed some minor detail. And, in reviewing the details, I found a counter-intuitive option being used:

alter the procedure to have an EXECUTE AS clause (otherwise the code signing infrastructure does not work)

Usually module signing allows you to remove EXECUTE AS clauses and statements, but here it is required. And it is required due to Service Broker working within a Database-only security context via an EXECUTE AS USER = statement. By adding the EXECUTE AS clause to the CREATE PROCEDURE statement, a new security context is created, and it can access the server-level and/or other Databases, which is what the rest of the module signing setup handles.

Sooooo, um, sorry about getting it right, then changing it to be something workable but not ideal due to missing that one piece ;-). BUT, I now have it working the way I originally said that it should work :-). The first set of example code below is the pure T-SQL, module signing approach that works with TRUSTWORTHY OFF (now that I added that missing WITH EXECUTE AS N'dbo'). I will keep the SQLCLR approach at the bottom since it did work and might could fit some other scenarios better.

I was hoping that by signing the procedures ... I could get permissions to transfer across the database

You can. I don't think I ever saw your module signing setup, but it could very well be that you missed the one small, atypical option that I had originally missed (that is the key to getting this whole thing working).

does execute as negate any permissions that would typically be associated via the certificate user?

Only if it is the EXECUTE AS USER statement (not the EXECUTE AS clause of a CREATE object statement, nor the EXECUTE AS LOGIN statement). In that one case, the security-context is, and can only be, Database-only, and cannot see the server-level or other databases, even with module signing in place. And, as luck would have it, that (i.e. the EXECUTE AS USER statement) is exactly what Service Broker is doing in order to execute the activation procedure. So, yes, that is what prevented your initial attempt at module signing from working. AND, the trick to fixing it is to simply add a WITH EXECUTE AS N'dbo' clause to the CREATE PROCEDURE statement for the proc that access another Database. It doesn't matter what User you use, but I found dbo to simplist as using OWNER gave a warning about needing to re-sign the Stored Procedure if that Owner ever changed. Of course, it is also possible to change the owner of a Database, so I would have expected a warning on my choice as well, but it didn't so I am choosing to ignore that potential nuance for the moment ;-).

Ideal Solution (T-SQL)

Main Setup

USE [master];
GO

IF (DB_ID(N'SendDbMailFromServiceBrokerQueue') IS NOT NULL)
BEGIN
  RAISERROR(N'Dropping DB: [SendDbMailFromServiceBrokerQueue]...', 10, 1) WITH NOWAIT;
  ALTER DATABASE [SendDbMailFromServiceBrokerQueue] SET OFFLINE WITH ROLLBACK IMMEDIATE;
  ALTER DATABASE [SendDbMailFromServiceBrokerQueue] SET ONLINE WITH ROLLBACK IMMEDIATE;
  DROP DATABASE [SendDbMailFromServiceBrokerQueue];
END


RAISERROR(N'Creating DB: [SendDbMailFromServiceBrokerQueue]...', 10, 1) WITH NOWAIT;
CREATE DATABASE [SendDbMailFromServiceBrokerQueue]
  COLLATE Latin1_General_100_CI_AS_KS_SC
  WITH DB_CHAINING OFF,
       TRUSTWORTHY OFF;

ALTER DATABASE [SendDbMailFromServiceBrokerQueue]
SET  RECOVERY SIMPLE,
     PAGE_VERIFY CHECKSUM,
     ENABLE_BROKER;
GO
-------------------------------------------------

USE [SendDbMailFromServiceBrokerQueue];
GO

CREATE SCHEMA [FunStuff] AUTHORIZATION [dbo];
GO

CREATE USER [BrokerUser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo];

CREATE QUEUE [FunStuff].[SendingQueue];

CREATE SERVICE [//SendingService]
    AUTHORIZATION [dbo]
    ON QUEUE [FunStuff].[SendingQueue];

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

CREATE CONTRACT [//AuditContract]
    AUTHORIZATION [dbo]
    ([//AuditMessage] SENT BY INITIATOR);
GO

CREATE PROCEDURE [FunStuff].[SendMessage]
(
  @Content NVARCHAR(MAX)
)
AS
SET NOCOUNT ON;
DECLARE @ConversationHandle UNIQUEIDENTIFIER;

  BEGIN DIALOG CONVERSATION @ConversationHandle
    FROM SERVICE [//SendingService]
    TO SERVICE '//ReceivingService'
    ON CONTRACT [//AuditContract] 
    WITH ENCRYPTION = OFF;

  SEND ON CONVERSATION @ConversationHandle
    MESSAGE TYPE [//AuditMessage]
    (@Content) ;

  END CONVERSATION @ConversationHandle ;
GO
---------------------------------------------------------------------------

GO
CREATE PROCEDURE [dbo].[EmailHandler]
(
  @EmailSubject VARCHAR(255),
  @EmailContent NVARCHAR(MAX)
)
WITH EXECUTE AS N'dbo' -- THIS IS REQUIRED (when used with Service Broker)!!!
AS
  DECLARE @Recipients NVARCHAR(4000) = N'recipient@place.tld';

  EXEC [msdb].[dbo].[sp_send_dbmail]
              @profile_name = N'{my_pofile_name}',
              @recipients = @Recipients,
              @subject = @EmailSubject,
              @body = @EmailContent,
              @exclude_query_output = 1;
GO

CREATE PROCEDURE [FunStuff].[AuditMessageHandler]
(
  @EmailSubject VARCHAR(255),
  @EmailContent NVARCHAR(MAX)
)
AS
  EXECUTE [dbo].[EmailHandler] @EmailSubject, @EmailContent;
GO

CREATE PROCEDURE [FunStuff].[AuditActivation]
AS
SET XACT_ABORT ON;

DECLARE @ConversationHandle UNIQUEIDENTIFIER,
        @ConversationGroupID UNIQUEIDENTIFIER,
        @MessageBody NVARCHAR(MAX),
        @MessageTypeName NVARCHAR(256),
        @RowsReceived INT;

WHILE (1 = 1)
BEGIN

   WAITFOR(
             GET CONVERSATION GROUP @ConversationGroupID
             FROM [FunStuff].[ReceivingQueue]
        ), TIMEOUT 500;

   IF (@ConversationGroupID IS NULL)
   BEGIN
        BREAK;
   END;

   WHILE (2 = 2)
   BEGIN
      BEGIN TRANSACTION;

      RECEIVE TOP (1) 
           @ConversationHandle = [conversation_handle],
           @MessageTypeName = [message_type_name],
           @MessageBody = [message_body]
      FROM    [FunStuff].[ReceivingQueue]
      WHERE   CONVERSATION_GROUP_ID = @ConversationGroupID;

      SET @RowsReceived = @@ROWCOUNT;

      IF (@RowsReceived = 0)
      BEGIN
           COMMIT;
           BREAK;
      END;

      IF (@MessageTypeName = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
      BEGIN
           END CONVERSATION @ConversationHandle;
      END;

      IF (@MessageTypeName = N'//AuditMessage')
      BEGIN
           EXEC [FunStuff].[AuditMessageHandler] N'Email From Broker test', @MessageBody;
      END;

      COMMIT TRANSACTION;
   END; -- WHILE (2 = 2)


END; -- WHILE (1 = 1)
GO

GRANT EXECUTE ON [FunStuff].[AuditActivation] TO [BrokerUser];
GO


CREATE QUEUE [FunStuff].[ReceivingQueue]
    WITH ACTIVATION (STATUS = ON,
        PROCEDURE_NAME = [FunStuff].[AuditActivation],
        MAX_QUEUE_READERS = 1,
        EXECUTE AS N'BrokerUser'
       );

CREATE SERVICE [//ReceivingService]
    AUTHORIZATION [dbo]
    ON QUEUE [FunStuff].[ReceivingQueue]
    ([//AuditContract]);
GO

Module Signing steps to make this work

USE [SendDbMailFromServiceBrokerQueue];

CREATE CERTIFICATE [Permission:SendDbMail]
  ENCRYPTION BY PASSWORD = N'MyCertificate!MineMineMine!'
  WITH SUBJECT = N'Grant permission to Send DB Mail',
  EXPIRY_DATE = '2099-12-31';

-- Sign the Stored Procedure that accesses another DB
ADD SIGNATURE
  TO [dbo].[EmailHandler]
  BY CERTIFICATE [Permission:SendDbMail]
  WITH PASSWORD = N'MyCertificate!MineMineMine!';

-- Copy the Certificate to [msdb]
DECLARE @PublicKey VARBINARY(MAX),
        @SQL NVARCHAR(MAX);

SET @PublicKey = CERTENCODED(CERT_ID(N'Permission:SendDbMail'));

SET @SQL = N'
CREATE CERTIFICATE [Permission:SendDbMail]
FROM BINARY = ' + CONVERT(NVARCHAR(MAX), @PublicKey, 1) + N';';
PRINT @SQL; -- DEBUG

EXEC [msdb].[sys].[sp_executesql] @SQL;


-- Create the Certificate-based User in [msdb]
EXEC [msdb].[sys].[sp_executesql] N'CREATE USER [Permission:SendDbMail]
FROM CERTIFICATE [Permission:SendDbMail];

GRANT AUTHENTICATE TO [Permission:SendDbMail];

PRINT ''Adding Certificate-based User to DB Role [DatabaseMailUserRole]...'';
EXEC sp_addrolemember N''DatabaseMailUserRole'', N''Permission:SendDbMail'';
';

TEST

USE [SendDbMailFromServiceBrokerQueue];

-- execute statement below if there is an error and the queue is disabled:
-- ALTER QUEUE [FunStuff].[ReceivingQueue] WITH STATUS = ON, ACTIVATION (STATUS = ON);

EXEC [FunStuff].[SendMessage] @Content = N'Woo hoo!';

Alternate Solution (SQLCLR)

I was also able to get this to work using SQLCLR (and yes, without enabling TRUSTWORTHY :-).

SQLCLR C# code

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class UserDefinedFunctions
{
    [SqlProcedure()]
    public static void ExecSendDbMail([SqlFacet(MaxSize = 255)] SqlString EmailSubject,
                                       SqlString EmailContent)
    {
        using (SqlConnection _Connection = new
                  SqlConnection("Server=(local); Trusted_Connection=true; Enlist=false;"))
        {
            using (SqlCommand _Command = _Connection.CreateCommand())
            {
                _Command.CommandType = CommandType.StoredProcedure;
                _Command.CommandText = @"dbo.sp_send_dbmail";

                SqlParameter _ParamProfileName =
                             new SqlParameter("profile_name", SqlDbType.NVarChar, 128);
                _ParamProfileName.Value = "{replace_with_your_profile_name}";
                _Command.Parameters.Add(_ParamProfileName);

                SqlParameter _ParamRecipients = new
                      SqlParameter("recipients", SqlDbType.VarChar, (int)SqlMetaData.Max);
                _ParamRecipients.Value = "{replace_with_your_recipients}";
                _Command.Parameters.Add(_ParamRecipients);

                SqlParameter _ParamSubject =
                          new SqlParameter("subject", SqlDbType.NVarChar, 255);
                _ParamSubject.Value = EmailSubject.Value;
                _Command.Parameters.Add(_ParamSubject);

                SqlParameter _ParamBody = new
                          SqlParameter("body", SqlDbType.NVarChar, (int)SqlMetaData.Max);
                _ParamBody.Value = EmailContent.Value;
                _Command.Parameters.Add(_ParamBody);

                SqlParameter _ParamExcludeQueryOutput =
                          new SqlParameter("exclude_query_output", SqlDbType.Bit);
                _ParamExcludeQueryOutput.Value = true;
                _Command.Parameters.Add(_ParamExcludeQueryOutput);


                _Connection.Open();
                _Connection.ChangeDatabase("msdb");

                _Command.ExecuteNonQuery();
            }
        }

        return;
    }
}

SETUP

USE [master];

CREATE DATABASE [SendDbMailFromServiceBrokerQueue]
  COLLATE Latin1_General_100_CI_AS_SC
  WITH DB_CHAINING OFF,
       TRUSTWORTHY OFF;

ALTER DATABASE [SendDbMailFromServiceBrokerQueue]
SET  RECOVERY SIMPLE,
     PAGE_VERIFY CHECKSUM,
     ENABLE_BROKER;
GO

-- Create objects needed to allow for EXTERNAL_ACCESS without TRUSTWORTHY ON:
CREATE ASYMMETRIC KEY [Permission:SendDbMail$Key]
    FROM EXECUTABLE FILE = N'C:\...\NoTrustworthy.dll';
CREATE LOGIN [Permission:SendDbMail$Login]
    FROM ASYMMETRIC KEY [Permission:SendDbMail$Key];
GRANT EXTERNAL ACCESS ASSEMBLY TO [Permission:SendDbMail$Login];
GO
-------------------------------------------------

USE [SendDbMailFromServiceBrokerQueue];
GO

CREATE ASSEMBLY [NoTrustworthy]
    AUTHORIZATION [dbo]
    FROM N'C:\...\NoTrustworthy.dll'
    WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO
CREATE PROCEDURE [dbo].[ExecSendDbMail]
(
  @EmailSubject NVARCHAR (255),
  @EmailContent NVARCHAR (MAX)
)
AS EXTERNAL NAME [NoTrustworthy].[UserDefinedFunctions].[ExecSendDbMail];
GO

CREATE SCHEMA [FunStuff] AUTHORIZATION [dbo];
GO

CREATE USER [BrokerUser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo];

CREATE QUEUE [FunStuff].[SendingQueue];

CREATE SERVICE [//SendingService]
    AUTHORIZATION [dbo]
    ON QUEUE [FunStuff].[SendingQueue];

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

CREATE CONTRACT [//AuditContract]
    AUTHORIZATION [dbo]
    ([//AuditMessage] SENT BY INITIATOR);
GO


CREATE PROCEDURE [FunStuff].[SendMessage]
(
  @Content NVARCHAR(MAX)
)
AS
SET NOCOUNT ON;
DECLARE @ConversationHandle UNIQUEIDENTIFIER;

    BEGIN DIALOG CONVERSATION @ConversationHandle
        FROM SERVICE [//SendingService]
        TO SERVICE '//ReceivingService'
        ON CONTRACT [//AuditContract] 
        WITH ENCRYPTION = OFF;

    SEND ON CONVERSATION @ConversationHandle
      MESSAGE TYPE [//AuditMessage]
      (@Content) ;

    END CONVERSATION @ConversationHandle ;
GO
---------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[EmailHandler]
(
  @EmailSubject VARCHAR(255),
  @EmailContent NVARCHAR(MAX)
)
AS
    -- other logic

    EXEC [dbo].[ExecSendDbMail] @EmailSubject, @EmailContent;
GO


GO
CREATE PROCEDURE [FunStuff].[AuditMessageHandler]
(
  @EmailSubject VARCHAR(255),
  @EmailContent NVARCHAR(MAX)
)
AS
    EXECUTE [dbo].[EmailHandler] @EmailSubject, @EmailContent;
GO

CREATE PROCEDURE [FunStuff].[AuditActivation]
AS
SET XACT_ABORT ON;

DECLARE @ConversationHandle UNIQUEIDENTIFIER,
        @ConversationGroupID UNIQUEIDENTIFIER,
        @MessageBody NVARCHAR(MAX),
        @MessageTypeName NVARCHAR(256),
        @RowsReceived INT;

WHILE (1 = 1)
BEGIN

     WAITFOR(
               GET CONVERSATION GROUP @ConversationGroupID
               FROM [FunStuff].[ReceivingQueue]
          ), TIMEOUT 500;

     IF (@ConversationGroupID IS NULL)
     BEGIN
          BREAK;
     END;

     WHILE (2 = 2)
     BEGIN
          BEGIN TRANSACTION;
          PRINT 'Get Message';

          RECEIVE TOP (1) 
               @ConversationHandle = [conversation_handle],
               @MessageTypeName = [message_type_name],
               @MessageBody = [message_body]
          FROM    [FunStuff].[ReceivingQueue]
          WHERE   CONVERSATION_GROUP_ID = @ConversationGroupID;

          SET @RowsReceived = @@ROWCOUNT;

          IF (@RowsReceived = 0)
          BEGIN
               COMMIT;
               BREAK;
          END;


          IF (@MessageTypeName = 
      N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
          BEGIN
               END CONVERSATION @ConversationHandle;
          END;

          IF (@MessageTypeName = N'//AuditMessage')
          BEGIN
               EXEC [FunStuff].[AuditMessageHandler]
                       N'Email From Broker test', @MessageBody;
          END;

          COMMIT TRANSACTION;
     END; -- WHILE (2 = 2)

END; -- WHILE (1 = 1)
GO

GRANT EXECUTE ON [FunStuff].[AuditActivation] TO [BrokerUser];
GO



CREATE QUEUE [FunStuff].[ReceivingQueue]
    WITH ACTIVATION (STATUS = ON,
        PROCEDURE_NAME = [FunStuff].[AuditActivation],
        MAX_QUEUE_READERS = 1,
        EXECUTE AS N'BrokerUser'
       );

CREATE SERVICE [//ReceivingService]
    AUTHORIZATION [dbo]
    ON QUEUE [FunStuff].[ReceivingQueue]
    ([//AuditContract]);
GO

---------------------------------------------------------------------------

TEST

USE [SendDbMailFromServiceBrokerQueue];

-- execute statement below if there is an error and the queue is disabled:
-- ALTER QUEUE [FunStuff].[ReceivingQueue] WITH STATUS = ON, ACTIVATION (STATUS = ON);

EXEC [FunStuff].[SendMessage] @Content = N'try me!';

CLEANUP

IF (DB_ID(N'SendDbMailFromServiceBrokerQueue') IS NOT NULL)
BEGIN
  RAISERROR(N'Dropping DB: [SendDbMailFromServiceBrokerQueue]...', 10, 1) WITH NOWAIT;
  ALTER DATABASE [SendDbMailFromServiceBrokerQueue] SET OFFLINE WITH ROLLBACK IMMEDIATE;
  ALTER DATABASE [SendDbMailFromServiceBrokerQueue] SET ONLINE WITH ROLLBACK IMMEDIATE;
  DROP DATABASE [SendDbMailFromServiceBrokerQueue];
END

DROP LOGIN [Permission:SendDbMail$Login];
DROP ASYMMETRIC KEY [Permission:SendDbMail$Key];
  • Following recommendations 1-4 didn't seem to change anything - still failing, still seeing guest in the trace. Though I'm wondering if there's perhaps some other subtle difference I'm missing between your example and what I have. I'm going to try taking your example and see if I can run it through service broker. – Nathan Mar 11 '17 at 4:19
  • Let us continue this discussion in chat. – Solomon Rutzky Mar 11 '17 at 5:15

I post here my founds around this problem. My first answer was this one:

  • create the certificate in master and create a login from it;
  • map this login to msdb and include the corrensponding user in DatabaseMailUserRole

  • export the certificate to your user database

  • sign your activation proc with this certificate

Even before I could try it, Solomon Rutzky gave me the link to this answer and I also suspected that I'll get DENY ONLY to DatabaseMailUserRole membership, and so it happened:

enter image description here

The picture above shows my proc and how it's executed by user test. It's important to understand that the procedure (activation proc in particular) will be always executed as user, not login, that is by design: Internal Activation Context

A queue configured for activation must also specify the user that the activation stored procedure runs as. SQL Server impersonates this user before starting the stored procedure.

So I execute it by user test that has no login, I created it this way:

create user test without login

Ok, I saw what was expected: my signed proc has recieved membership in DatabaseMailUserRole but with DENY ONLY.

Now I can return to the OP's question:

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?

The error is here: activation proc is NOT executed under the login. It always executed under the user.

That is why I even don't need to set up Service Broker, queue, activation proc...It's enough to make the test with a simple proc uxecuted by user

And now the finally part. To make my solution work I need the last condition to be true: user database must be TRUSTWORTHY with the owner that has AUTHENTICATE SERVER permission. This way the certificate user will be able to go out of his sandbox-database, it will not be a guest in msdb but it will be certificate user:

enter image description here

I don't think it's a good solution, I'm always against making user database TRUSTWORTHY.

It's safe only when that user database has no db_owner that is not a sysadmin at the same time

P.S. I upvote Solomon Rutzky solution that don't touch TRUSTWORTHY at all.

  • Hey there. I just updated my answer above with the actual, correct implementation of the module signing approach. I explain what I got wrong on my initial attempt. But the whole thing works without using TRUSTWORTHY ON and without using SQLCLR. This is what I had meant by "I think I found something that I need to test" in my comment on your now deleted answer to that recent duplicate question. – Solomon Rutzky Sep 25 '17 at 20:46

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.