marcingminski
Module being executed is not trusted on AWS RDS instance using service broker
Did you check DOCS to make sure there is no workaround? Yes
Describe the bug "The module being executed is not trusted. Either the owner of the database of the module needs to be granted authenticate permission, or the module needs to be digitally signed." error when attempting to execute 'usp_sqlwatch_logger_performance' from the service broker activation procedure (usp_sqlwatch_internal_exec_activated).
To Reproduce Steps to reproduce the behavior:
- Create RDS instance with SQL Server 2016 Express Version
- Create SQLWatch database on RDS
- Download code
- Open with VS, publish to script.
- Remove references to extended events (not supported in express on RDS)
- Change assembly from UNSAFE to SAFE (not really sure what this affects)
- Execute script
- Execute ' dbo.usp_sqlwatch_internal_migrate_jobs_to_queues' to migrate to queues
- Review log and errors should be displayed
SQL Server (please complete the following information):
- SQL Version: SQL Server 2016
- SQL Edition: Express
Additional context I realize this is most likely an RDS issue but I have been struggling with it for a few days and looking for some additional help if possible. Basically what is happening is that RDS will not allow the SP (usp_sqlwatch_logger_performance) to access a database outside of SQLWatch database. I also verified that SP can be manually executed without error but will not function when called from the queue activation process.
Do you know of a way to either modify the SP or the Queue activation process to allow access to other databases?
Another alternative I was thinking, if maybe this is a restriction of RDS, would be to create a NodeJS version of the collection process and possibly packaging that in docker. I would be interested in giving this shot but I would like to verify the steps first. I think this would mean an easier installation of SQLWatch on the database side, also allow one 'process' to trigger multiple servers and allow for external monitoring if the database becomes inaccessible/unresponsive. Let me know what you think.
Thanks for the quick response! I did try that but it appears like that is not possible on RDS.. quote from a search "Turning on the Trustworthy Database Property requires membership in the sysadmin fixed server role, which is not available to users in RDS for Microsoft SQL Server. "
https://forums.aws.amazon.com/thread.jspa?threadID=240654
Not sure, that is not something I have used before. Do you have any examples or resource you can point me to?
Making database trustworthy is just a lazy way of telling sql server that the code is, well, trusted. Since sqlwatch is open source there is nothing to hide so do not see a reason to have it not trusted. It easier this way, not necessarily the best way.
A better and more secure way of handling broker authentication would be to create a certificate-based login instead of making the whole database as trustworthy. Certificate based logins have their own certificate and whilst I could do this in the sqlwatch project, everyone would then get the same certificate out of the box so arguably this would be less secure approach. Ideally, everyone should create new cert-based logins for sqlwatch. I have not worked out the logistics of that yet hence I just set trustworthy.
It's a bit of a pain to set it up but here is a good article about it.
- http://rusanu.com/2008/10/23/how-does-certificate-based-authentication-work/
- https://social.msdn.microsoft.com/Forums/SqlServer/en-US/f5a71000-5f37-4cd4-921e-e04dbd3a3bbd/service-broker-and-quotthe-module-being-executed-is-not-trustedquot?forum=sqlservicebroker
I would not be surprised if RDS did not allow cert-based logins though.
I should add - trusted code means that it can access resources outside of its own database. which is what we need for SQLWATCH
Ok, thanks! I will dig in this weekend and see if I can get it working.