none
Service Broker with ApplicationPoolIdentity user

    Question

  • Hi,

    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?

    Thank You

    Claudio Mellina

    Thursday, July 11, 2013 12:05 PM

All replies

  • Hi Claudio,

    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
    http://www.xdan.com/2010/03/iis-7-applicationpoolidentity-and-sql-server/

    If you have any feedback on our support, please click here.



    Allen Li
    TechNet Community Support

    Tuesday, July 16, 2013 9:14 AM
    Moderator
  • Hi,

    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.

    Claudio

    Wednesday, July 17, 2013 10:57 AM