Connecting from iis5 ASP.net2.0 to sql2005

I'm trying to move an application I wrote from sql2000 to sql2005. The application is in ASP.NET and works fine using SQL2000 but when I moved the database and change the connect string the page display the message:

Cannot open database "EventTracker" requested by the login. The login failed.
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

If I add the identify tag to the web.config file

<identity impersonate="true" username="myname" password="mypasword"/>

then page comes up and everything works. The problem is I need to have the user's credentials from internet explorer to be used against the SQL database to control access.

I added some lines of code to see if the web page was acting with the web users credientials and it is for things other than the SQL2005 connections.

The following error message shows up in the web server's application log:

Event code: 3005
Event message: An unhandled exception has occurred.
Event time: 7/6/2007 6:50:10 PM
Event time (UTC): 7/7/2007 12:50:10 AM
Event ID: c3d90a99419f49ad8d307930acb5bb71
Event sequence: 13
Event occurrence: 2
Event detail code: 0
Application information:
Application domain: /LM/W3SVC/1/Root/ComputerServices/TruckerViewer-9-128282421386484375
Trust level: Full
Application Virtual Path: /ComputerServices/TruckerViewer
Application Path: c:\inetpub\wwwroot\ComputerServices\TruckerViewer\
Machine name: webserver
Process information:
Process ID: 960
Process name: aspnet_wp.exe
Account name: webserver\ASPNET
Exception information:
Exception type: SqlException
Exception message: Cannot open database "EventTracker" requested by the login. The login failed.
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Request information:
Request URL: http://serverws/ComputerServices/TruckerViewer/Counters.aspx
Request path: /ComputerServices/TruckerViewer/Counters.aspx
User host address: 10.10.17.187
User: $PCA\User4
Is authenticated: True
Authentication Type: NTLM
Thread account name: webserver\ASPNET
Thread information:
Thread ID: 1
Thread account name: webserver\ASPNET
Is impersonating: False
Stack trace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at TruckerViewer.Counter1.Page_PreRender(Object sender, EventArgs e) in \\webserver\c$\Inetpub\wwwroot\ComputerServices\TruckerViewer\Counters.aspx.vb:line 61
at System.Web.UI.Control.OnPreRender(EventArgs e)
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

The SQlserver's application log records two messages;

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 10.10.94.136]

and

Login succeeded for user 'NT AUTHORITY\ANONYMOUS LOGON'. Connection: trusted. [CLIENT: 10.10.94.136]

This exact same code works with SQL2000 but not with SQL2005. What am I missing? Any help would be appreciated.

If you want to use the credentials supplied by the user (through internet explorer), make sure the application is setup to use Windows Authentication.

Authentication Settings:

http://samples.gotdotnet.com/quickstart/aspplus/doc/authandauth.aspx

Also make sure you are setup for Impersonation:

http://samples.gotdotnet.com/quickstart/aspplus/doc/impersonation.aspx

The fact that the login failed for the Anonymous Login account tells me your configuration isn't quite correct (for what you are trying to do).

Evan

|||As you already turned on impersonation, you will have to configure your website NOT to use anonymous connection as this is always prefered before using authenticated connections.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
--|||

That's not working... The idea is that the web "internet explorer" user's domain\account are to be used by the web server to gain access to the SQL database on another server. This isn't happeining. If I add code on the web server to reference a file on the web server that on the "internet explorer" client's account has access to, it works. If I deny access to that file for that web user they don't get access. So I think "impersonation" is working. However, when that file is stored on another computer (via a network share) the other computer complains that the "anonymous user" doesn't have access. If I grant the anonymous account access it works but I don't want anonymous users to have access, only those users who have access to the file (or in reality the SQL database). Is there something I need to do to enable the web user's account to pass through the web server to the SQL server? Can this be done at all?

|||

As Jens mentioned, you will want to disable anonymous connection for the web site/page in IIS that's hosting your ASP.Net code. This is on top of configuring your ASP.NET application to support Integrated authentication and impersonation. You can change the web site configuration using the IIS manager tool.

HTH,

Jimmy