SqlException: Login failed for user 'NT AUTHOR

问题:

When I run web application via VS 2008, the application is able to log onto Sql server and check credentials ( user name and password ) entered by the user, but when I browse to this application via IIS 7 and try to postback user name and password, the application reports an exception:

System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\NETWORK SERVICE'

• With IIS 7 each process runs under Network Service account … so what rights need I to assign to this account for the application to be able to “contact” Sql server?

thanx

EDIT:

Hello,

It works now, though I don’t understand why machine account needed those rights. I understand machine account needs some rights to be able to “talk to” with specific program(Sql server), but why needs it rights to accesses database and its tables? Isn’t it up to the account specified in connection string

<add name="MyConnection" connectionString="data source=localhost; integrated security=sspi; initial catalog=aspnetdb;" />

to have appropriate access rights to database and its tables?

回答1:

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!

回答2:

Personally I would run the Web app under a custom service account. If you really want to run it under Network Service- see this MSDN document.

回答3:

As you specify "integrated security=sspi" in connection string than I guess you expect impersonating. But for this you should: 1. turn on integrated auth in IIS 2. turn on windows auth in asp.net: 3. turn on imerposation in asp.net:

Also consider that it's not enough if you web server and SQL Server machine are different machines. Then your users' account will be required to be trusted for delegation. It's special option in AD.

So, you have been said already it's better to have separated account for SQLSRV connections. Hope this helps.

回答4:

I recommend creating a service account and have your IIS 7 process run as that account. Make sure that account has proper access to the DB (if it just reads then DB-REader) (if it reads and updates then DB-Reader and DB-Writer).

回答5:

I encountered the same error with a fresh IIS setup. I used "integrated security=false;User Id=sa;Password=yourpassword" and everything worked just fine.

回答6:

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.

I will be going over a solution that works always and is very simple.

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.

回答7:

I encountered this problem in a SharePoint project. Integrated security was on and the database was already configured to allow access by the App Pool's user.

The problem was that impersonation was turned on when it needed to be off. This answer led me in the right direction:

WindowsImpersonationContext noImpersonateContext = null;

try
{
    noImpersonateContext = WindowsIdentity.Impersonate(IntPtr.Zero);
    using (SqlConnection conn = CreateConnection())
    {
        // database calls...
    }
}
finally
{
    if (noImpersonateContext != null) noImpersonateContext.Undo();
}