1

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.

1
  • When looking for the database user, make sure the context of the query is the user database, not master.
    – Jon Seigel
    Nov 13, 2014 at 19:11

1 Answer 1

3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.