Error Opening SQL Server Connection on Web Server

0
votes

Here is my Error:

ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Here is my connection string at application start up: 

MyConnection = New Odbc.OdbcConnection("DRIVER={SQL Server};SERVER=MySqlServer;Trusted_Connection=True;DATABASE=MyDatabase")

Here is part of Web.Config:

<authentication mode="Windows"/> 
<identity impersonate="false"/>

Here is my IIS(7) settings:

Authentication

  • Anonymous Authentication: Disabled
  • ASP.NET Impersonation: Disabled
  • Basic Authentaction: Enabled
  • Digest Authentication: Disabled
  • Forms Authentication: Disabled
  • Windows Authentication: Enabled

Connection Strings

  • MyDatabaseConnection: Data Source=MySqlServer;Initial Catalog=MyDatabase;Integrated Security=True
  • LocalSqlServer: data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true
4
asp.netiiswebserverconnection-stringsqlconnection
There is something here which doesn't make sense: If you had truly disabled ANONYMOUS authentication it seems improbable that you would get past the authentication mechanisms of IIS (without a 401 Access Denied) to the point where the database is being queried - Simen S
One thing I forgot to mention is that when I run the App using local host from (Visual Studio 2010) the sql connection works fine. So in other words I'm sure the connection string must be ok. - Troy Mitchel

4 Answers

0
votes

I believe you may have to use ASP.Net Impersonation in this case.

http://weblogs.asp.net/achang/archive/2004/04/15/113866.aspx

0
votes

As Zach says, you need to change the credentials of the user accessing the database.

So you either need to turn on impersonation as recommended or you should change the user id and password used by the application pool that is used to run your web site. This MSDN article, while a bit old, still applies and should help you do that.

0
votes

What user account should be used to connect to the database? Your user account, an account on the SQL server, the IIS app pool account?

By default, if you don't give the connection string a username and password, .NET will use the AppPool's credentials, which as Simen says doesn't have sufficient privileges to the SQL server.

If you want to use the credentials of the user accessing the website (probably a bad idea), you need to use impersonation as Thyamine says, but if you want to use a SQL account that is set up for the database, then you need to put the username and password of that user into the connection string. (http://www.sqlstrings.com/SQL-Server-connection-strings.htm)

0
votes

if your web application is running without Integrated Security. remove the "Allow Anonymous connections" security option. When authenticated by the web server as ANONYMOUS the request will be executed using an identity does not have sufficient privileges to your SQL Server DB.

Edit: If you are running the web server on a separate machine than the client machine you might want to investigate whether the server actually recognizes the account used on the client machine. If the machines are not part of the same security domain, you might even have to go through the questionable procedure of modifying the server's Local Policies --> Security Options --> Network access: Sharing and security model for local accounts to the setting : Classic - local users authenticate as themselves and creating a user account with the same name and password on the server.

You might also want to investigate http logs on the server