2

Basic Problem:

I have a web application that accesses a SQL Server database on the same machine. The web app runs under its own app pool - let us call it MyAppPool. If I goto advanced settings in IIS Manager, I can see that MyAppPool runs under ApplicationPoolIdentity. When I make requests to the web app, I can open task manager and verify that the username of w3wp.exe is MyAppPool. In SQL Server, I have added a Windows User IIS AppPool\MyAppPool and given it necessary permissions to read from db. The problem is that I am getting a login failed for DOMAIN\MACHINE$ when a logon is attempted to SQL Server. Its beyond me. Why is the app not logging on as IIS AppPool\MyAppPool?

Details:

I know variants of this question have been asked elsewhere, but I am really stuck without a solution. I experimented adding a <identity impersonate="true" /> to the web.config. If I do this, I get a login failed for NT AUTHORITY\IUSR. I have tried accessing the web app from the machine on which it is hosted and get same login error. I am running IIS8, Windows Server 2012, and SQL Server 2012.

Closest question I could find is Why is my MVC app trying to log into my DB as my machine, and not as the App Pool identity?, and the solutions provided do not work. I cannot change Integrated Security to be false (I had this thing running in the past). Quoting https://stackoverflow.com/a/15145488/147530:

ApplicationPoolIdentity uses IIS AppPool\ApplicationPool for local access, but DOMAIN\MACHINE-NAME$ for remote access

sounds reasonable. Question is why is ApplicationPoolIdentity not using IIS AppPool\MyAppPool identity when db is hosted on same machine??

Quoting another SO post, IIS application using application pool identity loses primary token?:

This application also connects to a SQL Server database using Integrated Security=true in the connection string. If the database is local, then we see that IIS APPPOOL\OurAppPoolName is used to connect to the database; if the database is remote, then the machine account OURDOMAIN\ourwebserver$ is used.

This is the behavior I want, but not getting it, and that is what I am asking in this question - I don't want to give permissions to DOMAIN\MACHINE-NAME$ to log onto SQL Server. Quoting https://stackoverflow.com/a/15445280/147530:

I think that's a bad idea, however, because it authorizes any program running as NetworkService to access the database - not just your web applications.

I tried one more thing, which was to enable Windows Authentication on IIS8 using this link http://www.iis.net/learn/install/installing-iis-85/installing-iis-85-on-windows-server-2012-r2#ModulesinIIS85 but this has also not solved the problem.

Community
  • 1
  • 1
morpheus
  • 18,676
  • 24
  • 96
  • 159
  • 1) Which IIS? 2) In "MyAppPool", have you set the app pool to run under a specific identity? By default, it runs under LocalSystem. See this SO answer: http://stackoverflow.com/questions/7698286/login-failed-for-user-iis-apppool-asp-net-v4-0/8853386#8853386 – tgolisch Nov 27 '13 at 22:03

1 Answers1

3

Fixed this problem. In SSMS, there is a path machine -> security -> logins which contains users who can log onto the machine. I had not added the apppool to this list. I had only added the apppool to machine -> databases -> my database -> security -> users

morpheus
  • 18,676
  • 24
  • 96
  • 159