SQL Server Query Notifications - Needed permissions in Active Directory if we can't grant the database SA role to the user running the web site?

Question

I've already configured the permissions in the DEV database for using query notifications in SQL Server 2005 with stored procedures.

The issue is that it works with my user account when running the web site from my local machine, and it does not work when the code was moved to the DEV web server.

The DBA verified my user has the 'sa' permissions in the DEV database, but the service account running the site from the DEV web server doesn't have these permissions, and it is not possible to assign this permission in QA or PROD. He also temporarily set the 'sa' permissions to the user, and the query notification works.

The error message the database is returning is:

An exception occurred while enqueueing a message in the target queue. Error: 15404, State: 19. Could not obtain information about Windows NT group/user 'DOMAIN\WebServiceAccount', error code 0x5.

Some things I've tried:

• Running xp_logininfo 'DOMAIN\WebServiceAccount' returns the same error:

  Could not obtain information about Windows NT group/usermessage

• Running xp_logininfo 'DOMAIN\MyUserAccount' returns the same error message for my user:

  Could not obtain information about Windows NT group/user

The possible solutions I've found so far are:

• Change the account running the SQL Server Service to one with admin permission. But which admin permissions?
• Give the "Authenticated Users", "Read Permissions" on the ADFS service account, or assign tokenGroupsGlobalAndUniversal to the user, and probably also add them to the group "Pre-Windows 2000 Compatible Access". Would this work for a single user, or it needs to be to a group? Is this secure?
• For a higher level of read access, add the "Windows Authorization Access Group" as well. Is this accurate?
• Grant the DOMAIN ADMIN privileges to the user. Is this configured in Active Directory?

Unfortunately, there's no way I can test all of this in the Active Directory we have, because of restrictions for developers, so I want to ask if anyone knows what exact permission we have to grant to the user running the SQL Server Service in Active Directory, for it to be able to query for other user's accounts?

Thank you very much in advance!

Answer 1

Our Systems guy did the following steps, which worked:

1. In Active Directory, search for the user DOMAIN\WebServiceAccount
2. In the "Security" tab, add the user running the Sql Server Service with Read permissions