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.
Am I missing something?
- Changed type Allen Li - MSFTMicrosoft contingent staff, Moderator Tuesday, July 16, 2013 9:14 AM Question
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] USE [YourDatabase] CREATE USER [IIS APPPOOL\YourAppPoolName] FOR LOGIN [IIS APPPOOL\YourAppPoolName]
For more detail information, you can refer to the following link:
IIS 7 ApplicationPoolIdentity and SQL Server
TechNet Community Support
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.