none
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

    Sunday, March 30, 2008 8:04 PM

All replies

  •  

    Did you grant send permission on the service to public?
    Monday, March 31, 2008 7:39 AM
  • 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.
    Monday, March 31, 2008 8:02 AM
    Moderator
  • 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!

    Monday, March 31, 2008 7:51 PM
  • 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?

    Monday, March 31, 2008 8:09 PM
  • 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

    Monday, March 31, 2008 11:25 PM
  •  怡红公子 wrote:

     

    Did you grant send permission on the service to public?

     

    It seems what you said relates to this


    GRANT SEND on service:Tongue TiedqlQueryNotificationService 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

    • Proposed as answer by Rafael Goodman Friday, August 1, 2014 10:16 PM
    • Unproposed as answer by Rafael Goodman Friday, August 1, 2014 10:16 PM
    Monday, March 31, 2008 11:57 PM
  • 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:

    private void CreateQueueAndService(bool restart)
    {
        // snip
        
        sqlCommand.CommandText = /* snip */ "IF (SELECT COUNT(*) FROM sys.database_principals WHERE name='sql_dependency_subscriber' AND type='R') <> 0 BEGIN GRANT SEND ON SERVICE::" + this._escapedQueueName + " TO sql_dependency_subscriber; END" /* snip */ ;
        
        // snip
    }

    As a result, if you add the SQL user doing the subscribing to a group called 'sql_dependency_subscriber', the error should go away and the subscriber should start getting the notifications.

    Hope this helps somebody else in the future.





    Friday, August 1, 2014 10:28 PM