SQL Server – Service Broker User Permission to Send to the Service

service-brokersql server

I am using .NET SqlDependency with a named queue (not dynamically created).

My Service Broker Configuration Is

CREATE QUEUE dbo.SqlDependencyQueue;
GO
CREATE SERVICE SqlDependencyService AUTHORIZATION [dbo]
ON QUEUE SqlDependencyQueue
([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);
GO
CREATE ROUTE SqlDependencyRoute WITH SERVICE_NAME = 'SqlDependencyService', ADDRESS = 'LOCAL';
GO
GRANT SELECT to [ApplicationRole]
GO
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [ApplicationRole] 
GO
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [ApplicationRole]
GO
GRANT REFERENCES on 
CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
  to [ApplicationRole]
GO

GRANT RECEIVE ON SqlDependencyQueue TO [ApplicationRole]
GO

When I update a table related to the SqlDependency SqlCommand, I see in Sql Server Profiler the events

Broker:Conversation
Broker:Message Classify
Broker:Message Undeliverable
Broker:Remote Message Ackknowledgement

The error message in the Message Undeliverable event is:

This message could not be delivered because the user with ID 6 in database ID 54 does not have permission to send to the service. Service name: 'SqlDependencyService'.

User Id number 6 is ##MS_AgentSigningCertificate##

I have not seen any documentation that mentions this user id in the context of SqlDependency or Service Broker so I am not quite sure why the error is occurring.

I have tried granting this user id access to the service but the id is not recognized.

GRANT SEND ON SERVICE::SqlDependencyService TO ##MS_AgentSigningCertificate##

Msg 15151, Level 16, State 1, Line 1
Cannot find the user '##MS_AgentSigningCertificate##', because it does not exist or you do not have permission.

I thought maybe I needed to create a login for that user but the user is not browseable when creating a login.

Best Answer

I wound up having to grant send on the service to the role that the application was in. I'm not sure why that is necessary given that the application is only supposed to be receiving notification of changes. The complete SQL is

CREATE QUEUE dbo.SqlDependencyQueue;
GO
CREATE SERVICE SqlDependencyService AUTHORIZATION [dbo]
ON QUEUE SqlDependencyQueue
([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);
GO
CREATE ROUTE SqlDependencyRoute WITH SERVICE_NAME = 'SqlDependencyService', ADDRESS = 'LOCAL';
GO
GRANT SELECT to [ApplicationRole]
GO
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [ApplicationRole] 
GO
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [ApplicationRole]
GO
GRANT REFERENCES on 
CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
  to [ApplicationRole]
GO

GRANT RECEIVE ON SqlDependencyQueue TO [ApplicationRole]
GO
GRANT SEND ON SERVICE::SqlDependencyService TO [ApplicationRole]
GO
Related Question