Problem setting SUBSCRIBE QUERY NOTIFICATIONS

Question

I'm currently implementing a cache mechanisem for our site. I want to use the SQL Cache dependancy feature. I am running the following command in management studio and it's not working.

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "my_server_name\ASPNET"

The error I'm getting is:

Cannot find the user 'my_server_name\ASPNET', because it does not exist or you do not have permission.

I tried signing in with the admin of the specific database I'm setting the notification for, sa, and windows authentication with the machine administrator. Also tried running management studio as administrator and still not joy. Can someone please point me in the right direction. Thank you!

Answer 1

First, it appears you are attempting to grant permissions to the account under which the site is running. In IIS 6 and IIS7 these are control by the account set on the Application Pool. That account used to be ASPNET but no longer by default. Instead, the default (starting with .NET 2.0 I believe) is NETWORK SERVICE. However, if you are using IIS 7, that has changed yet again. By default in IIS7 it uses something called the "ApplicationPoolIdentity" which is its own special credential created for each site. If SQL Server is on a different machine than the web server, you will run into another problem which is the credentials are all local to the machine.

My recommendation would be to do the following depending on your setup:

Both servers are on a domain and you want to use trusted connections:

1. Create a domain account and drop it into Domain Users.
2. On the web server, drop this account into the IIS_IUSRS group.
3. Go into the Application Pool for the site and change the account under which the site is running to this domain account. You will also want to ensure that this account has the proper NTFS permissions to the site files. If this site only writes to the database, you can given the account read-only access the folder(s) with the site files.
4. Ensure the connection string used by the site is formed to request a trusted connection. (See www.connectionstrings.com for the syntax)
5. On the database server execute your grant to this account:

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "domain name\MyIISAccount"

There may also be other Kerberos issues related to the fact that both servers on the domain and that might require creating a SPN (Service Principal Name).

Neither server is on a domain (i.e., both are member servers) and you want to use trusted connections:

1. Create a local account on both the web server and the database server with the same username and password. It is critical that they both have the same username and password. This technique involves using NTLM "pass-through" authentication which matches the hash created by the username and password to determine if the user is authenticated between the two desparate servers. On Windows 2008 R2, you may have to jump through a few local policy hoops to ensure that NTLM is enabled between the two servers.
2. Do steps #2 to #4 above with this account.
3. On the SQL Server, ensure that this local account has a Login and that this login maps to a User in the database. Then you would execute something like:

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO 'SQLServerMachineName\AccountUsedBySite'

You want to use SQL accounts instead of a trusted connection:

1. In this scenario, the connection string used by the site to connect to the database will include a username and password which map to a Login on the SQL Server database which maps to a User in the database (typically put in the db_owner role to make it dbo). This
2. Assuming the credentials are correct, you need only execute your grant against this user:

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO 'SQLUserAccountUsedBySite'

Both IIS and SQL Server are on the same machine and you want to use trusted connections

1. Create a local user account and drop it into the Users group.
2. Drop this account into the local IIS_IUSRS group.
3. Go into the Application Pool for the site and change the account under which the site is running to this local account. You will also want to ensure that this account has the proper NTFS permissions to the site files. If this site only writes to the database, you can given the account read-only access the folder(s) with the site files.
4. Ensure the connection string used by the site is formed to request a trusted connection. (See www.connectionstrings.com for the syntax)
5. In SQL Server, create a login for this account then create a user in the appropriate database for this account dropping it into the appropriate roles.
6. Now execute your grant to this account:

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO 'SQLServerMachineName\MyIISAccount'