IIS AppPool to SQL Server permissions (adding NT AUTHORITY\IUSR)

Question

I've got a new server build running Windows Server 2012 R2, IIS 8.5 (inc. ClassicASP feature) and SQL Server 2014 Express. I want to use Application Pool Identity to connect to the database. The database is set to "Windows Authentication Mode".

My Application Pool Identity is called activbase.net. I've set up a Security Login in SQL Server called IIS AppPool\activbase.net and user mapped it my database with db_datareader and db_datawriter access.

However when I try to access the database from the website, I get:

Cannot open database "ActivbaseLive" requested by the login. The login failed.

I thought this was enough to get the connection working. The Application Log (Event Viewer) shows:

Login failed for user 'NT AUTHORITY\IUSR'. Reason: Failed to open the explicitly specified database 'ActivbaseLive'. [CLIENT: ]

So I have added NT AUTHORITY\IUSR likewise to the SQL Server>Security>Logins and Databases>[ActivbaseLive]>Security>Users and this fixes the problem.

My questions are as follows:

1. Should I be needing to add NT AUTHORITY\IUSR login/user in addition to IIS AppPool\activbase.net login/user to my SQL Server Instance and database?
2. Is there a security issue with doing this? (NOTE: this will be a production environment)

Thanks, Chris

Answer 1

No. You don't need to add an SQL Server login for the NT AUTHORITY\IUSR identity in addition to the IIS AppPool\activbase.net identity. A login for the IIS AppPool\activbase.net application pool identity alone is adequate for connecting to SQL Server using Windows Authentication.

NT AUTHORITY\IUSR is a built-in Windows account that is the default identity used when Anonymous Authentication is enabled for your application. This page describes the rationale for the account.

To connect to your database with the IIS AppPool\activbase.net identity, you need to change the account set up for anonymous users from NT AUTHORITY\IUSR to your IIS AppPool\activbase.net application pool identity. Proceed as follows to make this change:

1. Open Internet Information Services (IIS) Manager.
2. In the Connections panel, locate and click to select the website hosting your application e.g. Default Web Site. (If you want to configure a specific application under your website, you can select the application.)
3. In the Features View in the center panel, double-click Authentication.
4. Anonymous Authentication will most likely be enabled in your setup. Right-click Anonymous Authentication, and select Edit.
5. In the Edit Anonymous Authentication Credentials dialog box, click the Application pool identity option, and then click OK.

The question in the link below (and its answer) addresses the same issue:

Login failed for user NT AUTHORITY\IUSR

With regard to your second question, "Is there a security issue with doing this?", the answer is, "Yes". You preferably don't want the NT AUTHORITY\IUSR built-in account having access to your SQL Server database since it is used as the default anonymous account on any other websites (and their applications) hosted on your IIS web server. This means those other websites and applications would be able to connect to your database. If they are compromised in an attack, they could potentially be used to access your data. So it's best not to have an SQL Server login for NT AUTHORITY\IUSR. Instead, limit database access to your website's (or application's) application pool identity.