Asked by:

Need help to figure out this SqlQueryNotificationService error.

Question

I found every several minutes, sometimes one or twice in an hour, I still get a dozen error like below logged in SQL Log. See the error is "You do not have permission to access the service". I found some articles on other errors, but nothing about this error. I want to get more information on this, and a way to trace what is the permission about and which user doesn't have the permission.

All replies

SqlDependency creates the temporary queue/service under the account used in the SqlDependency.Start call. But the individual notifications are created under the account used by the connection that executes the SqlCommand.Execute... If these two are different and from time to time the account calling SqlCommand.Execute... does not have the necessary permission you will get this error. An example of a scenario that exposes this problem could be a web app that starts the notifications using one fixed account (eg. the appdomain account, or a user/pwd connection string) but later when the notifications are subscribed it happens under an impersonated account. Some impersonated users may not have the necessary permission.

Unfortunately there is no solution for this. Since the temporary service name and creation /destruction are outside your control, you cannot grant SEND permissions individually on them. So, as with any problem with SqlDependency, the way to 'fix' it is to completely ditch the SqlDependency feature and go for the more basic SqlNotificationRequest Class (System.Data.Sql) that allows more granular control over what's going on. This is not something a dba can do, is an application change that has to be done by the developers.

One thing to confirm, you know I always use two users, ablesa and ableuser, ablesa has all permissions, I already use ablesa to start SqlDependency and use ableuser to do all other access.

I got those errors, what you said help me to understand what was wrong, but just want to know more, since the SQLdependency did work fine for me , at least I didn't find anything wrong. So that error happens understand what situation?

I found everyone have problem to find SqlQueryNotificationService . The permission is on SqlQueryNotificationService-GUID, I think if you can grand send on SqlQueryNotificationService-GUID, it could solve the problem, but the SqlQueryNotificationService-GUID is created by SQLDependency Start I guess. not sure if there is a way to set permission for all SqlQueryNotificationService

I know this is several years later, but I recently ran into this problem and discovered the solution; figured I'd write this for posterity sake.

After spelunking through the SqlDependency code, I discovered that by convention, if a SQL role called 'sql_dependency_subscriber' exists, SqlDependency.Start() will automatically grant send on the service it dyanmically creates. Per the code: