Sql-server – Stored Procedure triggered by Service Broker can’t access another database in SQLServer 2017

impersonationservice-brokersql serversql-server-2017stored-procedures

I make an extensive use of SQL Server Service Broker to trigger/parallelize data science tasks (data imports with SSIS or direct queries to another database on the same server, then data management and machine learning with R and/or Python external scripts).

I wanted to upgrade from SQL Server 2016 to SQL Server 2017, exact same architecture and scripts, but now I get errors I don't understand, apparently caused by the service broker impersonations and privileges.

The activated proc '[StoredProcTriggerdByServiceBroker]' running on queue 'queuename' output the following: 'The server principal "sbuser" is not able to access the database "mainDB" under the current security context.'

To explain a bit a simplification of my process:

There are two DBs, mainDB and testDB. ServiceBroker and stored procedures are running on testDB.

We mainly use Windows logins to use SQL Server and that was less than ideal for some SSIS purposes, so I chose to create a user called sbuser for which connection is disabled but which we can impersonate, with sufficient privileges on both DBs to do everything required. The queues are triggering the stored procedure as sbuser.

The stored procedure fails on statement

SELECT * INTO testDB..targetTable from mainDB..sourceTable

Same select query, impersonating sbuser manually works fine.
Same select query, in a stored procedure, with a execute as sbuser / exec StoredProcedure works fine.
But it fails whenever the stored procedures are triggered by Service Broker.

I added to the stored procedure a logging of CURRENT_USER and results of

SELECT * into tmp_session FROM sys.dm_exec_sessions WHERE session_id = @@SPID;

CURRENT_USER give me sbuser, which is what I expect, but the content of dm_exec_sessions puzzles me as it's different from what I get when I log in as SA, then execute the procedure impersonating sbuser with an execute as.

security_id login_name context_info original_security_id original_login_name
0x01 sa 0x 0x01 sa

Nothing about sbuser… then again the error message in the log is about sbuser's security context…

So, what has changed between SQL Server 2016 and 2017 in the way the Service Broker works and impersonation of logins and their privileges, and how can I run my select into query now?

Best Answer

From comments:

I don't believe the behavior has changed between SQL 2016 and SQL 2017. It may be that the SQL 2016 database was TRUSTWORTHY whereas the SQL 2017 database is not or has a database owner sid mismatch. The proper solution is module signing as Rusanu describes. Also see the Activation Procedures for Service Broker topic topic in the appendex of Erland's module signing article. - Dan Guzman

I also supposed SQLServer 2016 hadn't CLR security activated as per the Microsoft Docs - Vincent Chalmel