locked
use ApplicationPoolIdentity to connect to SQL RRS feed

  • Question

  • User-864475932 posted

    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?

    Tuesday, August 23, 2011 11:27 PM

All replies

  • User-1672167363 posted

    Hi,

    Later in the post are suggestions.

    Martin

     

     

     

    Wednesday, August 24, 2011 12:05 AM
  • User-864475932 posted

    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?

     

     

    Wednesday, August 24, 2011 12:30 AM
  • User-1672167363 posted

    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

     

     

     

     

    Wednesday, August 24, 2011 1:00 AM
  • User-864475932 posted

    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?

    Wednesday, August 24, 2011 11:54 AM
  • User-1672167363 posted

    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

     

    Thursday, August 25, 2011 4:49 AM
  • User-864475932 posted
    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.
    Sunday, August 28, 2011 2:18 AM
  • User-2064283741 posted

    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? 

    Sunday, August 28, 2011 4:55 PM
  • User-864475932 posted
    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"
    Sunday, August 28, 2011 10:36 PM
  • User-340462011 posted
    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
    Friday, January 13, 2012 9:45 AM
  • User-1672167363 posted

    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

     

    Monday, January 16, 2012 3:57 AM
  • User-340462011 posted
    normally it works, but the problem is that it sometimes loses its impersonation with the machine account!
    Monday, January 16, 2012 7:26 AM
  • User-1672167363 posted

    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

     

    Monday, January 16, 2012 7:43 AM
  • User-340462011 posted
    i guess i have issues :) but why? the windows installation is a very basic "default" installation.
    Monday, January 16, 2012 8:12 AM
  • User-1672167363 posted

    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

     

    Monday, January 16, 2012 10:41 PM
  • User1200496557 posted

    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  

    Wednesday, May 2, 2012 2:54 AM
  • User-340462011 posted

    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.

    Wednesday, May 2, 2012 3:53 AM
  • User990644438 posted
    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.
    Monday, May 21, 2012 2:43 AM
  • User-340462011 posted
    @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.
    Monday, May 21, 2012 5:50 AM
  • User1145526800 posted

    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. 

    Friday, February 26, 2016 9:56 PM