Need help to figure out this SqlQueryNotificationService error.

Sun, 30 Mar 2008 20:04:45 Z

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 'SqlQueryNotificationService-9ff8b39d-90a8-45bc-83a7-23837920774d'.</Description></Error>'.

thanks

Need help to figure out this SqlQueryNotificationService error.

Mon, 31 Mar 2008 07:39:52 Z

Did you grant send permission on the service to public?

Need help to figure out this SqlQueryNotificationService error.

Mon, 31 Mar 2008 08:02:20 Z

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.

Need help to figure out this SqlQueryNotificationService error.

Mon, 31 Mar 2008 19:51:05 Z

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!

Need help to figure out this SqlQueryNotificationService error.

Mon, 31 Mar 2008 20:09:14 Z

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?

Need help to figure out this SqlQueryNotificationService error.

Mon, 31 Mar 2008 23:25:48 Z

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

Need help to figure out this SqlQueryNotificationService error.

Mon, 31 Mar 2008 23:57:35 Z

怡红公子 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