IIS Application Pool Identities and SQL Server Integrated Auth: Fixing the Login Failed Errors

You add integrated authentication to a connection string, and the site that worked with SQL logins on your workstation returns “Login failed for user ‘NT AUTHORITY\SYSTEM'” on IIS. The mismatch is almost never in the connection string. It is between the Windows account the application pool runs as and the login that exists — or does not — inside SQL Server.

Three different identities can be involved, and the one in the error is often not the one you expected. This guide checks each from the server and covers the permission problems that appear after authentication succeeds. See the Windows VPS plans overview for the baseline of a single-server IIS and SQL Server setup.

Which identity your application pool actually uses

An application pool runs under an identity defined on the pool, not the site. In IIS Manager, select the pool, open Advanced Settings, and look at Identity. The default is ApplicationPoolIdentity, a virtual account named after the pool — IIS APPPOOL\DefaultAppPool — with no password and no interactive logon rights.

Older guides say to switch the pool to NetworkService or LocalSystem. Both work and both are far more privileged than necessary. NetworkService reaches SQL Server as NT AUTHORITY\NETWORK SERVICE, LocalSystem as NT AUTHORITY\SYSTEM — frequently a sysadmin on an Express install. Stay with ApplicationPoolIdentity; if you need a custom account, use a dedicated low-privilege user.

Reading the error message instead of guessing

“Login failed for user ‘NT AUTHORITY\SYSTEM'” means SQL Server received the connection and rejected the principal — no such login, or no user mapped in the target database. “Login failed for user ”. Reason: An attempt to log on using SQL Server Authentication failed” means no Windows token was sent, so the connection string still carries SQL credentials.

The fixes are completely different. Check the connection string first: Trusted_Connection=True;Integrated Security=SSPI; with no User ID or Password keys. If a SQL credential is present, the Windows token is ignored entirely.

Creating the login for a virtual pool account

The syntax looks odd because the name contains a backslash: the “server” is not the machine, it is literally IIS APPPOOL. Create the login in T-SQL rather than through the SSMS GUI, which mis-parses the name when you browse for it.

-- on the SQL Server instance, as a sysadmin
CREATE LOGIN [IIS APPPOOL\MyAppPool] FROM WINDOWS;
USE [MyDatabase];
CREATE USER [IIS APPPOOL\MyAppPool] FOR LOGIN [IIS APPPOOL\MyAppPool];
ALTER ROLE [db_datareader] ADD MEMBER [IIS APPPOOL\MyAppPool];
ALTER ROLE [db_datawriter] ADD MEMBER [IIS APPPOOL\MyAppPool];

-- custom local or domain account instead
CREATE LOGIN [DOMAIN\svc-webapp] FROM WINDOWS;
GRANT CONNECT SQL TO [DOMAIN\svc-webapp];

Two mistakes follow. Creating the login but not the database user gives “The server principal is not able to access the database”. Granting db_owner because it removes the error is the other; a read-mostly app needs datareader, datawriter and execute, nothing more.

Symptom to cause: a quick lookup table

Symptom	Likely app pool identity	Fix
Login failed for ‘NT AUTHORITY\SYSTEM’	Pool set to LocalSystem (often the default for old installers)	Create login for NT AUTHORITY\SYSTEM — or better, switch the pool back to ApplicationPoolIdentity
Login failed for ‘NT AUTHORITY\NETWORK SERVICE’	Pool set to NetworkService	Create login for that principal, or use ApplicationPoolIdentity and grant it instead
Login failed for ‘IIS APPPOOL\MyAppPool’	ApplicationPoolIdentity (correct default)	CREATE LOGIN FROM WINDOWS, then create the database user and role
Cannot open database “X” requested by the login	Correct identity, no database user	Create the user FOR LOGIN inside the target database
Login failed for ” / anonymous	None — connection string still has SQL creds	Remove User ID/Password, add Integrated Security=SSPI
Works in IIS Express, fails in IIS	Different identity in each host	Grant to the pool identity, not your developer account

Use the table as a filter, then confirm with a query. SELECT SUSER_NAME() from a working connection names the principal your code runs under; SELECT * FROM sys.server_principals shows whether a matching login exists at all.

Confirming the identity from the server side

Do not trust IIS Manager alone; the effective identity can be overridden in applicationHost.config. List the pool’s identity from an elevated prompt and compare. If someone edited the file by hand, the two disagree and the file wins after the next restart.

%windir%\system32\inetsrv\appcmd list apppool "MyAppPool" /text:processModel.identityType
%windir%\system32\inetsrv\appcmd list app /text:applicationPool
icacls "C:\inetpub\wwwroot\myapp" /grant "IIS APPPOOL\MyAppPool:(OI)(CI)RX" /T

The second command is the one people forget. A site can have a perfectly configured pool that is not the pool serving the request, and no SQL Server change will fix that. Confirm the mapping first.

Permissions after authentication: files, temp and double hop

Once the login works, failures turn filesystem-bound. The pool identity needs read and execute on the app folder and modify rights on directories the app writes to — uploads, logs, App_Data. The icacls line above covers a read-only deployment; add (OI)(CI)M only on writable folders.

If the app reaches SQL Server on another machine, you hit the double hop: the pool identity cannot be passed onward without Kerberos delegation. The symptom is a login failure for ANONYMOUS LOGON or a switch to the machine account. Register an SPN and configure constrained delegation, or use a SQL login for that one link.

A troubleshooting order that avoids wasted changes

Work in one direction and stop at the first thing that disagrees with expectations. First, reread the connection string for leftover credentials. Second, run appcmd list app to confirm which pool serves the request. Third, run appcmd list apppool to confirm that pool’s identity. Fourth, connect to SQL Server as a sysadmin and check sys.server_principals for the exact name from step three. Fifth, check sys.database_principals in the target database.

Most reports are resolved at step four. Change one thing at a time — a full iisreset after every edit makes it impossible to tell which change fixed it, and a pool recycle is enough since identity is read when the worker starts.

Integrated authentication is only mysterious when you skip the question of which identity is on the wire. Find the pool, confirm the mapping, create the login and user for that exact name, grant minimum roles, verify filesystem rights. That covers nearly every report. If you need a clean host to test the chain, compare Windows VPS plans with enough RAM for IIS and SQL Server together.