I'm trying to set on IIS 7 an application pool identity to ApplicationPoolIdentity.
SO is Windows 2008 R2. On IIS is hosted a web application that connects to a SQL Server 2008 R2 database
and uses Broker service through SqlDependency.
I've having an error when trying to create a new SqlDepemdency object:
Cannot connect to SQL Server instance. An entity of type service cannot be owned by a role, a group, or by principals mapped to certificates or asymmetric keys.
Invalid object name 'SqlQueryNotificationService-af105e34-a0f8-492f-99c8-55fbba091c13'.
If using NETWORK_SERVICE instead everything is fine. Using ApplicationPoolIdentity from a remote IIS works fine too.
I've tried granting db_owner role to a group to which both NETWORK_SERVICE and IIS AppPool\<PoolName> belong or by
granting db_owner directly to IIS AppPool\<PoolName> but nothing changed.
In Global.asax SqlDependency is started through SqlDependency.Start(Globals.ConnectionString) and then
SqlDependency dependency = new SqlDependency(command) is used.
Based on the error message, this issue can occur if the Windows Virtual Account doesn’t have permission to login SQL Server, we can try the following commands to create a login for the Windows Virtual Account.
CREATE LOGIN [IIS APPPOOL\YourAppPoolName] FROM WINDOWS WITH DEFAULT_DATABASE=[YourDatabase]
CREATE USER [IIS APPPOOL\YourAppPoolName] FOR LOGIN [IIS APPPOOL\YourAppPoolName]
For more detail information, you can refer to the following link:
I've already tried your solution ("I've tried granting db_owner role to a group to which both NETWORK_SERVICE and IIS AppPool\<PoolName> belong or
by granting db_owner directly to IIS AppPool\<PoolName> but nothing changed").
Your script grant access to IIS AppPool\<PoolName> to my database.