We are excited to announce that the IIS.NET Forums are moving to the new Microsoft Q&A experience. Learn more >

use ApplicationPoolIdentity to connect to SQLRSS

19 replies

Last post Feb 26, 2016 09:56 PM by Greyrat

  • use ApplicationPoolIdentity to connect to SQL

    Aug 23, 2011 11:27 PM|stephena55|LINK

    Hi All,

    I have a WCF web service hosted in IIS 7 (or maybe 7.5, whichever comes with Windows server 2008 R2) using DefaultAppPool running under ApplicationPoolIdentity per Microsoft's recommendation. The web service needs to call a stored procedure to insert data to a db. The web server is on a different VM than the database server. The db server is running SQL 2008 R2. Both VMs run Windows server 2008 R2. In addition, I created an AD group and add the web server VM as member of the group, and created a SQL login for the AD group.

    Here's the connection string in web.config:

    Application Name=somewebservice;Server=somewebserver;Integrated Security=SSPI;Database=somedatabase;Connection Timeout=60"

    When the web service tries to connect to db, it encounters this exception:

    Exception in InsertToDb()System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

    However, if I change the application pool identity to Network Service, it works fine. But since MS recommends ApplicationPoolIdentity account over Network Service, my boss wants us to look into how to make it work in our architecture.

    According to this article: http://learn.iis.net/page.aspx/624/application-pool-identities/, when accessing network resource, both Network Service and ApplicationPoolIdentity use machine account, so why in my case Network Service works but not ApplicationPoolIdentity?

    ApplicationPoolIdentity

  • Re: use ApplicationPoolIdentity to connect to SQL

    Aug 24, 2011 12:05 AM|HCamper|LINK

    Hi,

    Later in the post are suggestions.

    Martin

     

     

     

    Windows and Linux work Together IT-Pros
    Community Member Award 2011
  • Re: use ApplicationPoolIdentity to connect to SQL

    Aug 24, 2011 12:30 AM|stephena55|LINK

    Thanks for your reply Martin. Let me make sure I understand you correctly. You're proposing:

     (1) create a custom domain account called WebSvc

    (2) use this WebSvc account as application pool identity instead of using the built-in AppliationPoolIdentity account

    (3) creates a SQL login for the WebSvc account

    Correct?

     

     

  • Re: use ApplicationPoolIdentity to connect to SQL

    Aug 24, 2011 01:00 AM|HCamper|LINK

    Hi,

    Yes, Your understanding is correct that the existing "Accounts" "Application Pools" "logon Accounts"

    should not be considered as limitations.

     IIS Server with SQL Server allow you to design customize for your needs & requirements. 

    Your plan is even better using "Domain Custom Account" with granular control over permissions

          and you can also Audit Activities through Event logs.

    Since you will have SQL Server Audit logs for Access you could do a quick compare

        against Event logs for WebSvc.

    The IIS Net guides are just for getting started.

    For the "real world" cases you can customize IIS Server SQL Server for security and data isolation.

    Martin

     

     

     

     

    Windows and Linux work Together IT-Pros
    Community Member Award 2011
  • Re: use ApplicationPoolIdentity to connect to SQL

    Aug 24, 2011 11:54 AM|stephena55|LINK

    Understood Martin. Your points are perfectly reasonable. However, I still want to figure out why it works for Network Service but not ApplicationPoolIdentity. The link I sent out in my previous post specifically says ApplicationPoolIdentity uses machine account to access network resource, just like Network Service. But in my case, it's not true. I remove the AD group and any machine account from SQL server login, and this is the exception I get:

    When I use Network Service:

    Exception in MethodNamr() System.Data.SqlClient.SqlException (0x80131904): Cannot open database "someDb" requested by the login. The login failed.
    Login failed for user 'someDomain\someMachine$'

     

    When I use ApplicationPoolIdentity:

    Exception in MethodNamr() System.Data.SqlClient.SqlException (0x80131904): Cannot open database "someDb" requested by the login. The login failed.
    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'  

     

    So apparently in my case ApplicationPoolIdentity is not using machine account to connect to db. Why is that?

  • Re: use ApplicationPoolIdentity to connect to SQL

    Aug 25, 2011 04:49 AM|HCamper|LINK

    Hi,

    I do suggest you look at IIS Net guide for "Changes Between IIS 6.0 and IIS 7 Security"

     http://learn.iis.net/page.aspx/110/changes-between-iis-60-and-iis-7-security/ the "Network Service" is part of

    compatibily for Migration from IIS 6.0 to IIS 7 Servers.

    I hope this "Best Effort" view and description helps,

    Martin

     

    Windows and Linux work Together IT-Pros
    Community Member Award 2011
  • Re: use ApplicationPoolIdentity to connect to SQL

    Aug 28, 2011 02:18 AM|stephena55|LINK

    Hi Martin, Thanks for the links you provided. While they are have lots of information, I'm not sure they address the core questions, like why ApplicatiomPoolIdentity account doesn't use machine account when accessing network resource, or how to configure SQL to allow access to ApplicationPoolIdentity. If I'm missing something that address these questions, please point it to me. Thanks.
  • Rovastar Rovastar

    5495 Posts

    MVP

    Moderator

    Re: use ApplicationPoolIdentity to connect to SQL

    Aug 28, 2011 04:55 PM|Rovastar|LINK

    The app pool identity has a few undocumented features that is different to network service, I haven't seen a definitive list of these.

    But more interestingly is that fact that you cannot connect to SQL server with the app pool identity account. I am sure this is normal and ok to do. What is your connection string when connecting to this? 

    Troubleshoot IIS in style
    https://www.leansentry.com/
  • Re: use ApplicationPoolIdentity to connect to SQL

    Aug 28, 2011 10:36 PM|stephena55|LINK

    Yes I'm sure it's a common set up! :) Here's the connection string in web.config: Application Name=somewebservice;Server=somewebserver;Integrated Security=SSPI;Database=somedatabase;Connection Timeout=60"
  • Re: use ApplicationPoolIdentity to connect to SQL

    Jan 13, 2012 09:45 AM|atran1978|LINK

    i have exactly the same issue and it always worked again after a reboot!? ... very mysterious/unreliable!

    i still don't know why ApplicationPoolIdentity "sometimes" doesn't use the machine account to identify itself, resulting in 'NT AUTHORITY\ANONYMOUS LOGON' login failed.

    also falling back to "NETWORK SERVICE" always work.


    maybe related to "The Double-Hop Problem"? http://blogs.msdn.com/b/knowledgecast/archive/2007/01/31/the-double-hop-problem.aspx
  • Re: use ApplicationPoolIdentity to connect to SQL

    Jan 16, 2012 03:57 AM|HCamper|LINK

    Hello,

    Use  Process Monitor http://www.iislogs.com/articles/processmonitorw3wp/ 

    read this http://blogs.iis.net/davcox/archive/2009/08/12/what-is-my-iis-code-running-as.aspx "Who is my IIS application process identity?"

    The look at SQL Server.

    Martin

     

    Windows and Linux work Together IT-Pros
    Community Member Award 2011
  • Re: use ApplicationPoolIdentity to connect to SQL

    Jan 16, 2012 07:26 AM|atran1978|LINK

    normally it works, but the problem is that it sometimes loses its impersonation with the machine account!
  • Re: use ApplicationPoolIdentity to connect to SQL

    Jan 16, 2012 07:43 AM|HCamper|LINK

    Hello,

    You may have issues.   It may sometimes loses its impersonation with the machine account.

    Process Monitor and IIS Server information:

    http://learn.iis.net/page.aspx/202/application-pool-identity-as-anonymous-user/  "Application Pool Identity as Anonymous User"

    http://learn.iis.net/page.aspx/624/application-pool-identities/ Application Pools.

    http://learn.iis.net/page.aspx/139/iis7-and-above-security-improvements/ IIS Security and above.

    Regards,

    Martin

     

    Windows and Linux work Together IT-Pros
    Community Member Award 2011
  • Re: use ApplicationPoolIdentity to connect to SQL

    Jan 16, 2012 08:12 AM|atran1978|LINK

    i guess i have issues :) but why? the windows installation is a very basic "default" installation.
  • Re: use ApplicationPoolIdentity to connect to SQL

    Jan 16, 2012 10:41 PM|HCamper|LINK

    Hello,

    You might have issues with the Application Pool and Identity for IIS Server.

    The install for IIS server uses the "default" basic settings.

    The SQL Server products are not part of IIS Server.

    "to connect to SQL Server is the other part of the question.

    Martin

     

    Windows and Linux work Together IT-Pros
    Community Member Award 2011
  • Re: use ApplicationPoolIdentity to connect to SQL

    May 02, 2012 02:54 AM|Marka2020|LINK

    Hi,

    I've just run into a similar situation. I found the two folllowing links which provide quite a lot of detail around the virtual accounts (used to create the AppPoolIdentity) and managed service accounts (an alternate to using a normal account to run the service)

    http://technet.microsoft.com/en-us/library/dd548356.aspx

    This link details information about configuring service accounts and permissions.

    http://msdn.microsoft.com/en-us/library/ms143504.aspx

    The point I got from these two articles is that the AppPoolIdentity will use the computer account to authenticate when connecting to another network service, i.e the <computer name>$ however what is not pointed out is that to have a computer account the machine must be a member of an active directory domain.

    The reason for this is a computer account is the security principle a machines is assigned when it is joined to a domain, therefore if you want to use virtual accounts to connect to a remote service, such as SQL server, you need to be a domain member. Likewise is you want to use a Managed service account it looks as though you must also be a domain member as the account is managed by active directory (theres a number of requirements around domain functional, which leads me to beleive this).

     I'm not sure if this is relevant for other people on here having problems. From what I can see if you have a stand alone machine and you want IIS to connect to another service which requires authentication you need to create a user account on the local machine which will be used to run the App Pool.

     Regards,

    Mark  

  • Re: use ApplicationPoolIdentity to connect to SQL

    May 02, 2012 03:53 AM|atran1978|LINK

    the problem that i have is that is all works well (all server are in AD), until i do an "iisreset". After iisreset "ApplicationPoolIdentity" fails/looses it impersonation to the machine$ account.

    Maybe a bug?

    I currently have to use "Network Service" instead of "ApplicationPoolIdentity". Network Service allways impersonate well to the machine$ account.

  • Re: use ApplicationPoolIdentity to connect to SQL

    May 21, 2012 02:43 AM|Tasmey|LINK

    Application pool pros: You don't have to be a .Net programmer to understand what's going on. The security aspect leaves the domain of the programmer and falls under the remit of infrastructure Easy to change through IIS with proper saftey checks that the username is correct when setting up the app pool. I.e. It won't let you enter an incorrect username. Impersonation pros: Privileges can be documented and traced back through changes to configuration through source control history if configuration files are stored there. Impersonation cons: To change the user, you need to be familiar with .Net configuration rather than just setting up a website Not sure I can think of much else. My gut says to go with different application pools for each of the websites but it's your party.
  • Re: use ApplicationPoolIdentity to connect to SQL

    May 21, 2012 05:50 AM|atran1978|LINK

    @Tasmey I think there is a misunderstanding here. Please reread the posts above. We are talking about the automatic impersonation (by windows/IIS) of the "ApplicationPoolIdentity" or "Network service" account to the machine$ account.
  • Re: use ApplicationPoolIdentity to connect to SQL

    Feb 26, 2016 09:56 PM|Greyrat|LINK

    atran1978 - Just so you don't feel like you are bonkers, I've seen this same behavior on Server 2008 and 2012 with using the builtin applicationpoolidentity.  I've seen apps just stop working and simply recycling the app pool will "fix" the issue but the issue would come back.  I did not see stability here until I changed the app pool to run under Network service, for a perm fix.  I've also seen apps not able to right to remote MSMQ queues that have permissions allowed for the domain machine acct/Network Service (which applicationpoolidentity is supposed to use for remote calls) - this issue was also "fixed" by changing the apps to use Network Service or a domain acct.

    At this point I do not setup applications to use applicationpoolidentity.