Service Broker's SEND statement causes security context switch
1
vote
0
answers
144
views
We have triggers in multiple tables that will send messages to a Service Broker service upon INSERT, UPDATE and DELETE operations. These operations may be nested as parts of bigger, transactional operations in various different processes, with additional operations coming up after sending the messages but before transactions actually gets committed.
Our users authenticate using SSPI (Windows Active Directory). Some legacy stored procedure have been setup with
From this point on, it's as if the
WITH EXECUTE AS OWNER
clause so we don't have to worry about possible permission issues for whatever these procedures will do - including writing in tables described above.
Yesterday, following up an update to one of our apps, it started logging a strange permission error on a very basic SELECT statement. I looked up the procedure (it happens to be a SQLCLR
procedure) and it should indeed be running as its owning schema (dbo
in this case, which happens to be mapped to sa
user) and therefore have every possibly required permissions.
I ran a Sql Profiler
session while trying to reproduce the issue and noticed something strange: the SEND
command caused LoginName
to switch back to the domain user.

EXECUTE AS OWNER
condition in the head procedure was being reverted.
Just to make sure, I disabled Service Broker
on the database and did the exact same action. Sure enough, there was no more security context switch.
What could possibly be the cause for this? Is this by design? Is it smh because it's invoked from within SQLCLR?
As a reference, here is the declaring code for the head procedure:
CREATE PROCEDURE [dbo].[ps_SQLCLR_RunTicket]
@ticket [nvarchar](128)
WITH EXECUTE AS OWNER
AS
EXTERNAL NAME [SomeAssembly].[SomeStaticClass].[SomeMethod]
GO
And here is a slimmed down version of the nested code that's calling out SEND
:
-- This code normally is in a stored procedure that's invoked from a trigger
DECLARE @msg VARCHAR(MAX) = ''
SET @handle = NEWID();
BEGIN DIALOG CONVERSATION @handle
FROM SERVICE auditService
TO SERVICE 'receivingService'
ON CONTRACT auditServiceContract
WITH ENCRYPTION = OFF, LIFETIME = 600;
SEND ON CONVERSATION @handle
MESSAGE TYPE auditMessageType (@msg);
END CONVERSATION @handle;
Asked by Crono
(111 rep)
Aug 12, 2021, 08:44 PM