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.
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?
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?
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.
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?
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"
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
stephena55
7 Posts
use ApplicationPoolIdentity to connect to SQL
Aug 24, 2011 03:27 AM|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
HCamper
8048 Posts
Re: use ApplicationPoolIdentity to connect to SQL
Aug 24, 2011 04:05 AM|LINK
Hi,
Later in the post are suggestions.
Martin
Community Member Award 2011
stephena55
7 Posts
Re: use ApplicationPoolIdentity to connect to SQL
Aug 24, 2011 04:30 AM|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?
HCamper
8048 Posts
Re: use ApplicationPoolIdentity to connect to SQL
Aug 24, 2011 05:00 AM|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
Community Member Award 2011
stephena55
7 Posts
Re: use ApplicationPoolIdentity to connect to SQL
Aug 24, 2011 03:54 PM|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?
HCamper
8048 Posts
Re: use ApplicationPoolIdentity to connect to SQL
Aug 25, 2011 08:49 AM|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
Community Member Award 2011
stephena55
7 Posts
Re: use ApplicationPoolIdentity to connect to SQL
Aug 28, 2011 06:18 AM|LINK
Rovastar
3321 Posts
MVP
Moderator
Re: use ApplicationPoolIdentity to connect to SQL
Aug 28, 2011 08:55 PM|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?
stephena55
7 Posts
Re: use ApplicationPoolIdentity to connect to SQL
Aug 29, 2011 02:36 AM|LINK
atran1978
8 Posts
Re: use ApplicationPoolIdentity to connect to SQL
Jan 13, 2012 01:45 PM|LINK
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