Sql-server – The error “Login failed for user ‘NT AUTHORITY\IUSR'” in ASP.NET and SQL Server 2008

asp.netdatabase-permissionsiis-7sql server

My ASP.NET v3.5 web application is throwing the following exception when it attempts to open a connection to a SQL Server 2008 database:

System.Data.SqlClient.SqlException:
Cannot open database "MyDbName"
requested by the login. The login
failed. Login failed for user 'NT
AUTHORITY\IUSR'.

The thing is, I've added NT AUTHORITY\IUSR to the server's list of logins, and to the database's list of users. For the server, I've granted the user the Public role, and for the database I've granted db_datareader permissions.

I've also granted the same for NT AUTHORITY\NETWORK SERVICE, which is the identity that the application pool is running under.

The web application is hosted by IIS7, if that makes a difference. The problem repros when the DB and IIS are on the same physical machine as well.

Best Answer

The trick here is that NT AUTHORITY\NETWORK SERVICE actually appears to the database as DOMAINNAME\MACHINENAME$ (note the $ sign!). That is, when you cross the machine boundary from your web server to the SQL Server, SQL Server sees the machine account if you use the NETWORK SERVICE or LOCAL SYSTEM accounts. If you use any other non-domain account, SQL Server will not receive your credentials.

I'm a bit puzzled by your error message. Truth be told, I don't think that when the DB is on another box, you'll see anything other than Login Failed for NT AUTHORITY\ANONYMOUS LOGON.

IUSR is used for anonymous websites, and can't pass over the wire to SQL Server. You may find a way for it to work if you're doing everything on the same machine, but I'd never know because I'd never do it that way... ;-)

Related Solutions

Sql – How to return only the Date from a SQL Server DateTime datatype

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

for example

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

gives me

2008-09-22 00:00:00.000

Pros:

No varchar<->datetime conversions required
No need to think about locale

Sql-server – SqlException: Login failed for user ‘NT AUTHORITY\NETWORK SERVICE

You will need to actually create an account in SQL Server for the Network Service account. Then you will grant it access to your database, the specific permissions that you grant the account depend on the nature of the tasks that your database needs to do.

You can do this all in SSMS via the "Security" section, right click on "Logins" and select add. You will be adding a Windows Account, you can then lookup and validate the name "NETWORK SERVICE". Then switch to the "User Mapping" section and grant permission to view your database. The permissions as I said are up to you, or you can assign it dbowner permissions for full control.

After doing that you will be fine. I do caution against giving the application more permissions than needed!

Best Answer

Related Solutions

Sql – How to return only the Date from a SQL Server DateTime datatype

Sql-server – SqlException: Login failed for user ‘NT AUTHORITY\NETWORK SERVICE

Related Topic