SQL SERVER – FIX : ERROR : Cannot open database requested by the login. The login failed. Login failed for user ‘NT AUTHORITY\NETWORK SERVICE’.

This error is quite common and I have received it few times while I was working on a recent consultation project. The message reads Cannot open database requested by the login, and the fix is simple.

Cannot open database requested by the login. The login failed.
Login failed for user ‘NT AUTHORITY\NETWORK SERVICE’.

This error occurs when you have configured your application with IIS, and IIS goes to SQL Server and tries to login with credentials that do not have proper permissions. This error can also occur when replication or mirroring is set up.

If you search online, there are many different solutions provided to solve this error, and many of these solutions work fine. However, I will be going over a solution that works always and is very simple.

Fix, Workaround, Solution

Go to SQL Server >> Security >> Logins and right click on NT AUTHORITY\NETWORK SERVICE and select Properties

In newly opened screen of Login Properties, go to the “User Mapping” tab. Then, on the “User Mapping” tab, select the desired database, especially the database for which this error message is displayed. On the lower screen, check the role db_owner. Click OK.

In almost all such cases, this should fix your problem.

The same fix as a script

Clicking works once. If you are setting up a second server, or writing this into a deployment step, here it is as T-SQL.

-- create the login at server level, if it is not there yet
CREATE LOGIN [NT AUTHORITY\NETWORK SERVICE] FROM WINDOWS;
GO

-- create the user inside the database and give it access
USE YourDatabase;
GO
CREATE USER [NT AUTHORITY\NETWORK SERVICE] FOR LOGIN [NT AUTHORITY\NETWORK SERVICE];
GO
ALTER ROLE db_datareader ADD MEMBER [NT AUTHORITY\NETWORK SERVICE];
ALTER ROLE db_datawriter ADD MEMBER [NT AUTHORITY\NETWORK SERVICE];
GO

Notice I have used db_datareader and db_datawriter rather than db_owner. See the note at the end for why.

Why your website appears as NETWORK SERVICE

This is the part that confuses people, and once it clicks the error stops being mysterious.

SQL Server never sees the person using your website. It sees the Windows account that your application is running as. With a Windows Authentication connection string, IIS connects as whatever identity the application pool is set to, and the visitor’s identity never leaves the web server.

So the account you need to grant is the application pool identity, not the developer, and not the person who reported the problem.

On a current version of IIS it is probably not NETWORK SERVICE

Since IIS 7.5, application pools run under ApplicationPoolIdentity by default, which is a separate invented account per pool rather than the shared NETWORK SERVICE. That is a good change, because one website can no longer reach another website’s database by accident.

It does mean the name you grant looks unusual. For a pool called MyAppPool on a machine called WEBSERVER01, it is:

CREATE LOGIN [IIS APPPOOL\MyAppPool] FROM WINDOWS;

And if SQL Server is on a different machine from IIS, that local identity does not exist over there. The connection then arrives as the computer account instead, which looks like this, trailing dollar sign and all:

CREATE LOGIN [DOMAIN\WEBSERVER01$] FROM WINDOWS;

That dollar sign is not a typo, and forgetting it is the reason half an afternoon disappears.

Find out exactly who is connecting

Rather than guessing, ask. Open the application pool in IIS Manager, Advanced Settings, and read the Identity line. Or let SQL Server tell you what it received, by running this from the application itself:

SELECT SUSER_SNAME() AS ConnectedAs,
       ORIGINAL_LOGIN() AS OriginalLogin,
       DB_NAME() AS CurrentDatabase;

Whatever ConnectedAs returns is the name you grant. No guessing needed.

A word about db_owner

The steps above tick db_owner because it always works, and when your site is down you want the thing that always works. I would not leave it there.

db_owner can drop your tables. A website almost never needs to. Once the site is running again, go back and swap it for db_datareader and db_datawriter, then add execute permission if it calls stored procedures:

GRANT EXECUTE ON SCHEMA::dbo TO [NT AUTHORITY\NETWORK SERVICE];

Then if your site is ever compromised, the attacker gets your data rather than the ability to delete it. That difference is worth the five minutes.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.