none
Service Broker and Service Broker and is_trustworthy_on RRS feed

  • Question

  • I use the Service Broker to execute a stored procedure asynchronously simultaneously, using such a queue:

    CREATE QUEUE MyQueue WITH STATUS=ON , RETENTION=Off , ACTIVATION (STATUS=ON, PROCEDURE_NAME=MyProcedure, MAX_QUEUE_READERS=3, EXECUTE AS Self);

    When I insert a new item into the queue (using a Dialog/Conversation), the stored procedure is not initiated unless the database is_trustworthy_on definition is set to on (=1).

    There are some risks in changing the is_trustworthy_on definition from its default value (Off=0) to On,
    and I wonder if there is a way to avoid it.

    Thanks in advance.


    El castellano no es mi lengua materna. Discúlpenme por los errores gramaticales, y, si pueden, corríjanme en los comentarios, o por correo electrónico. ¡Muchas gracias! Blog: http://about.me/GeriReshef

    Wednesday, August 5, 2015 5:08 AM

Answers

  • A linked server is a server-level object, and to access it you need to go outside the database, but when you impersonate a database user, you are sandboxed into the current database. An activation procedure always runs in impersonated context, as you have this EXECUTE in the activation part of the queue.

    You need to make two changes:

    1) Change the activation procedure to have:
       WITH EXECUTE AS OWNER AS

    2a) Create a certificate in master,
    2b) Copy this certificate to the user database.
    2c) Sign the procedure with this certificate.
    2d) Create a login from the certificate. (This login is only a placeholder for permissions, not a login that can log on.)
    2e) Grant this login AUTHENTICATE SERVER.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Geri_Reshef Wednesday, August 5, 2015 12:32 PM
    Wednesday, August 5, 2015 11:19 AM

All replies

  • So what is the stored procedure doing?

    Generally, you don't have to deal with permissions or trustworthy with an activation procedure. But if it is doing something outside the database, you need to.

    You are perfectly right in that you should not set the database to trustworthy - that is a dangerous setting. The correct method is certificate signing. But I would need to know a little more what's in the procedure to give exact advice.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, August 5, 2015 8:35 AM
  • Hi,

    The system handles all the backups in the domain: both the local and the remote backup (through Linked Servers), and take care of the backup files (old backup files are deleted after some time).

    We have a central storage system, and the procedure executes dynamic commands such as

    Exec('Backup .... ;') At [MyRemoteServer];

    Thanks!


    El castellano no es mi lengua materna. Discúlpenme por los errores gramaticales, y, si pueden, corríjanme en los comentarios, o por correo electrónico. ¡Muchas gracias! Blog: http://about.me/GeriReshef

    Wednesday, August 5, 2015 8:50 AM
  • A linked server is a server-level object, and to access it you need to go outside the database, but when you impersonate a database user, you are sandboxed into the current database. An activation procedure always runs in impersonated context, as you have this EXECUTE in the activation part of the queue.

    You need to make two changes:

    1) Change the activation procedure to have:
       WITH EXECUTE AS OWNER AS

    2a) Create a certificate in master,
    2b) Copy this certificate to the user database.
    2c) Sign the procedure with this certificate.
    2d) Create a login from the certificate. (This login is only a placeholder for permissions, not a login that can log on.)
    2e) Grant this login AUTHENTICATE SERVER.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Geri_Reshef Wednesday, August 5, 2015 12:32 PM
    Wednesday, August 5, 2015 11:19 AM
  • Thank you very much Erland!

    El castellano no es mi lengua materna. Discúlpenme por los errores gramaticales, y, si pueden, corríjanme en los comentarios, o por correo electrónico. ¡Muchas gracias! Blog: http://about.me/GeriReshef

    Wednesday, August 5, 2015 12:33 PM
  • Hello, I came across this post looking for answers and it seems pretty close.

    My activation stored procedure is sending mail via EXEC msdb.dbo.sp_send_dbmail

    Funny thing, I can write a simple procedure to send a sample email and it works. But when the same bit of code is activated by a Service Broker receive queue stored procedure, it doesn't work. 

    How do I get msdb to trust Service Broker stored procedure?

    Thanks for your help.


    Todd C - MCSE SQL Server Business Intelligence MCITP BI Dev 2008 MSCTS SQL Server 2005 Please mark posts as answered where appropriate.

    Wednesday, May 22, 2019 9:19 PM
  • TRUSTWORTHY is not a setting you should use lightly, as it can incur a security risk.

    The correct way to handle this is through certificate signing. This is a bit complicated when Service Broker is involved, and in fact, I don't have a complete recipe for doing what you are asking for. However, on my web site you can find the article http://www.sommarskog.se/grantperm.html, where I explain the general technique for certicate signing (and why TRUSTWORTHY is a security risk). This article has an appendix, http://www.sommarskog.se/grantperm-appendix.html which covers how to use signing with activation procedure. There is also a chapter on how to let users start jobs which is simimlar in kind to send mail.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, May 23, 2019 9:47 PM