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 \u2014 or does not \u2014 inside SQL Server.<\/p>\n\n\n\n
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<\/a> for the baseline of a single-server IIS and SQL Server setup.<\/p>\n\n\n\n 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<\/strong>. The default is ApplicationPoolIdentity, a virtual account named after the pool \u2014 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 “Login failed for user ‘NT AUTHORITY\\SYSTEM'” means SQL Server received the connection and rejected the principal \u2014 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.<\/p>\n\n\n\n The fixes are completely different. Check the connection string first: The syntax looks odd because the name contains a backslash: the “server” is not the machine, it is literally Two mistakes follow. Creating the login but not the database user gives “The server principal is not able to access the database”. Granting Use the table as a filter, then confirm with a query. 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.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n 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 \u2014 uploads, logs, App_Data. The 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.<\/p>\n\n\n\n Work in one direction and stop at the first thing that disagrees with expectations. First, reread the connection string for leftover credentials. Second, run Most reports are resolved at step four. Change one thing at a time \u2014 a full 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<\/a> with enough RAM for IIS and SQL Server together.<\/p>\n\n","protected":false},"excerpt":{"rendered":"Which identity your application pool actually uses<\/h2>\n\n\n\n
IIS APPPOOL\\DefaultAppPool<\/code> \u2014 with no password and no interactive logon rights.<\/p>\n\n\n\nNT AUTHORITY\\NETWORK SERVICE<\/code>, LocalSystem as NT AUTHORITY\\SYSTEM<\/code> \u2014 frequently a sysadmin on an Express install. Stay with ApplicationPoolIdentity; if you need a custom account, use a dedicated low-privilege user.<\/p>\n\n\n\nReading the error message instead of guessing<\/h2>\n\n\n\n
Trusted_Connection=True;Integrated Security=SSPI;<\/code> with no User ID<\/code> or Password<\/code> keys. If a SQL credential is present, the Windows token is ignored entirely.<\/p>\n\n\n\nCreating the login for a virtual pool account<\/h2>\n\n\n\n
IIS APPPOOL<\/code>. Create the login in T-SQL rather than through the SSMS GUI, which mis-parses the name when you browse for it.<\/p>\n\n\n\n-- on the SQL Server instance, as a sysadmin\nCREATE LOGIN [IIS APPPOOL\\MyAppPool] FROM WINDOWS;\nUSE [MyDatabase];\nCREATE USER [IIS APPPOOL\\MyAppPool] FOR LOGIN [IIS APPPOOL\\MyAppPool];\nALTER ROLE [db_datareader] ADD MEMBER [IIS APPPOOL\\MyAppPool];\nALTER ROLE [db_datawriter] ADD MEMBER [IIS APPPOOL\\MyAppPool];\n\n-- custom local or domain account instead\nCREATE LOGIN [DOMAIN\\svc-webapp] FROM WINDOWS;\nGRANT CONNECT SQL TO [DOMAIN\\svc-webapp];<\/code><\/pre>\n\n\n\ndb_owner<\/code> because it removes the error is the other; a read-mostly app needs datareader, datawriter and execute, nothing more.<\/p>\n\n\n\nSymptom to cause: a quick lookup table<\/h2>\n\n\n\n
Symptom<\/th> Likely app pool identity<\/th> Fix<\/th><\/tr><\/thead> Login failed for ‘NT AUTHORITY\\SYSTEM’<\/td> Pool set to LocalSystem (often the default for old installers)<\/td> Create login for NT AUTHORITY\\SYSTEM<\/code> \u2014 or better, switch the pool back to ApplicationPoolIdentity<\/td><\/tr>Login failed for ‘NT AUTHORITY\\NETWORK SERVICE’<\/td> Pool set to NetworkService<\/td> Create login for that principal, or use ApplicationPoolIdentity and grant it instead<\/td><\/tr> Login failed for ‘IIS APPPOOL\\MyAppPool’<\/td> ApplicationPoolIdentity (correct default)<\/td> CREATE LOGIN FROM WINDOWS<\/code>, then create the database user and role<\/td><\/tr>Cannot open database “X” requested by the login<\/td> Correct identity, no database user<\/td> Create the user FOR LOGIN<\/code> inside the target database<\/td><\/tr>Login failed for ” \/ anonymous<\/td> None \u2014 connection string still has SQL creds<\/td> Remove User ID\/Password, add Integrated Security=SSPI<\/code><\/td><\/tr>Works in IIS Express, fails in IIS<\/td> Different identity in each host<\/td> Grant to the pool identity, not your developer account<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n SELECT SUSER_NAME()<\/code> from a working connection names the principal your code runs under; SELECT * FROM sys.server_principals<\/code> shows whether a matching login exists at all.<\/p>\n\n\n\nConfirming the identity from the server side<\/h2>\n\n\n\n
%windir%\\system32\\inetsrv\\appcmd list apppool \"MyAppPool\" \/text:processModel.identityType\n%windir%\\system32\\inetsrv\\appcmd list app \/text:applicationPool\nicacls \"C:\\inetpub\\wwwroot\\myapp\" \/grant \"IIS APPPOOL\\MyAppPool:(OI)(CI)RX\" \/T<\/code><\/pre>\n\n\n\nPermissions after authentication: files, temp and double hop<\/h2>\n\n\n\n
icacls<\/code> line above covers a read-only deployment; add (OI)(CI)M<\/code> only on writable folders.<\/p>\n\n\n\nA troubleshooting order that avoids wasted changes<\/h2>\n\n\n\n
appcmd list app<\/code> to confirm which pool serves the request. Third, run appcmd list apppool<\/code> to confirm that pool’s identity. Fourth, connect to SQL Server as a sysadmin and check sys.server_principals<\/code> for the exact name from step three. Fifth, check sys.database_principals<\/code> in the target database.<\/p>\n\n\n\niisreset<\/code> 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.<\/p>\n\n\n\n