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: