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.

Source  spid26s

Message
The query notification dialog on conversation handle '{E6FC299F-8BFE-DC11-A4E1-000D5670268E}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8494</Code><Description>You do not have permission to access the service &apos;SqlQueryNotificationService-9ff8b39d-90a8-45bc-83a7-23837920774d&apos;.</Description></Error>'.

 

 

thanks

Answer 1

 

Did you grant send permission on the service to public?

Answer 2

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.

Answer 3

Woo! that is really something!

 

I start the SQLDependency with a higher level user, a user with create permission, while the normal user only has access/read/write permission.

 

I thought it is reason when I set like that. What a problem, just know it doesn't work this way.

 

Thanks!

Answer 4

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?

Answer 5

From this link, it seems you can have two users, one for Start, one for subscribe

 

http://www.codeproject.com/KB/database/SqlDependencyPermissions.aspx?msg=2487937#xx2487937xx

 

in my case, both users has dbo as default schema, I tried to make the subscribe user dbo of the db, and it seems that does work.

 

sounds like the start user create the query under dbo, but the subscribe user doesn't have pemission to access it. Not sure what permission it need

Answer 6

怡红公子 wrote:

Did you grant send permission on the service to public?

 

It seems what you said relates to this

GRANT SEND on service:qlQueryNotificationService to guest

 

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