Solved

Login failed on database connection on IIS server

Posted on 2007-11-29
22
1,027 Views
Last Modified: 2012-06-27
I have been testing my application the visual web developer using the local:host, and a remote database.

Now I have copied my project to my web server and IIS is all setup. The web pages are fine.

But when I hit the database, it gives me:

>>Exception Details: System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.


I have the IP address setup and everything and I'm not sure why it connects from my pc but not from the website.

Can you think of anything I need to check for? It has been a long day and I'm likely not thinking straight.

this works locally, but not from the IIS web site:

<add name="xxConnectionString" connectionString="Data Source=xx.xx.xx.xx;Initial Catalog=DbfName;Integrated Security=True" providerName="System.Data.SqlClient"/>
0
Comment
Question by:BobCSD
  • 13
  • 5
  • 2
  • +1
22 Comments
 
LVL 22

Expert Comment

by:RedKelvin
Comment Utility
Hi, take a look at this
http://support.microsoft.com/kb/889615
0
 
LVL 22

Expert Comment

by:RedKelvin
Comment Utility
0
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
Basically, when you connect from your PC (on the domain), it uses your (trusted) login details.  When the IIS app connets, it is using the IIS account for that web site, usually IUSR_<webmachinename> - this user is not valid on SQL Server.

One way to solve this is to go to IIS on the web server, configure the web folder account to use your domain account.
0
 
LVL 1

Author Comment

by:BobCSD
Comment Utility
I have the database setup with the IISUser.

I have about 6 database and several web apps using the databases. They are windows 2000 web server and sql server 2000. The classic asp uses odbc.

The .net is using sqlClient. Firewalls are open between boxes and it uses the local IPs between the two boxes to connect. It doesn't use a log in, it does use windows authentication.

Everything is setup the same. It is beyond me why I can't get it to read the sqlClient.

Is there something specific with SQLClient that is different from ODBC? Remember, I can get it to work on the remote database when I am using my local box. It's when I move it to the production web server.
0
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
it uses "windows authentication."
work on the remote database when I am using my local box - "windows authentication." user = ?a
production web server.  "windows authentication." user = ?b

the difference is that ?a <> ?b
0
 
LVL 1

Author Comment

by:BobCSD
Comment Utility
>>the difference is that ?a <> ?b

don't rightlyknow what you're getting at.
0
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
I mean that a Web Service belongs or is run by a "user".  To check which user is running a web app, go to IIS Manager, expand the tree to the Web Directory, right-click and view the 3rd or 4th tab - security and permissions. It will usually say at the top "Use this account" and the default is IUSR_ABCD (if ABCD is the machine name).  Now, when you use Windows authentication to SQL Server, it needs to validate the user "IUSR_ABCD" . On your machine, it could be MACHINENAME\IUSR_MYMACHINE which may have been added already. Or maybe in the "Use this account" setting, being a dev machine, it may have been set for you to something else, like your own login (MYDOMAIN\ME).  You need to make sure that the account for the production web server is known to SQL Server and has been granted permissions.
0
 
LVL 1

Author Comment

by:BobCSD
Comment Utility
Well as far as IIS goes, this ain't my first rodeo. LOL.

The only thing different about the mix is using ASP.net and sqlclient to access the database, as opposed to asp classic and odbc.

My database is and has always been setup for windows authentication, so setting up IISUSER is the first thing I do. What I'm saying is I can connect fine to the database using an asp classic application, but nto asp.net. It's the same database, same permissions, just classic versus .net. Same two servers, same IIS, same permissions.
0
 
LVL 1

Author Comment

by:BobCSD
Comment Utility
Oh, this is a sqlserver2000 database on a windows 2000 web server.

Does sqlquery work with windows 2000 webserver?

I know it works with my remote Visual Web Developer webserver, but maybe... who knows?

hmm...
0
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
Have you set the web folders up differently?
I mean, is it possible to put .NET and classic next to each other served from the same web folder and check whether one still works and the other doesn't?
0
 
LVL 1

Author Comment

by:BobCSD
Comment Utility
I will try that.

(when the dust settles on this other project. sorry so slow for getting back.)
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 1

Author Comment

by:BobCSD
Comment Utility
Okay, I took an existing classic website.

Added my aspx files files to it. The file that does not open a database connection displays fine:
/test1.aspx

So it is not directory permissions on the folders.
But the file that opens the database connection fails as per the code snippet.

However, if I run this same page locally vis visual web developer, it does connect remotely to the database on the database server (not a local database) and it does not fail.

The classic database uses this database as the aspx files are trying to use.

The only difference is that the classic is using an odbc connection and the aspx files are using a sqlclient connection.


Server Error in '/' Application.

--------------------------------------------------------------------------------
 

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. 

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
 

Exception Details: System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
 

Source Error: 
 

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  
 

Stack Trace: 
 
 

[SqlException (0x80131904): Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.]

   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739123

   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188

   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956

   System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33

   System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +170

   System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +349

   System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181

   System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170

   System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359

   System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28

   System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424

   System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66

   System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496

   System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82

   System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105

   System.Data.SqlClient.SqlConnection.Open() +111

   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121

   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137

   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83

   System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770

   System.Web.UI.WebControls.BaseDataList.GetData() +53

   System.Web.UI.WebControls.DataList.CreateControlHierarchy(Boolean useDataSource) +284

   System.Web.UI.WebControls.BaseDataList.OnDataBinding(EventArgs e) +56

   System.Web.UI.WebControls.BaseDataList.DataBind() +72

   System.Web.UI.WebControls.BaseDataList.EnsureDataBound() +55

   System.Web.UI.WebControls.BaseDataList.CreateChildControls() +63

   System.Web.UI.Control.EnsureChildControls() +87

   System.Web.UI.Control.PreRenderRecursiveInternal() +41

   System.Web.UI.Control.PreRenderRecursiveInternal() +161

   System.Web.UI.Control.PreRenderRecursiveInternal() +161

   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360
 

 
 
 

--------------------------------------------------------------------------------

Version Information: Microsoft .NET Framework Version:2.0.50727.832; ASP.NET Version:2.0.50727.832 

Open in new window

0
 
LVL 1

Author Comment

by:BobCSD
Comment Utility
If I change to an odbc connection instead of a sqlclient connection it still errors, but different.

>>The .Net Framework Odbc Data Provider requires Microsoft Data Access Components(MDAC) version 2.6 or later.
Server Error in '/' Application.

--------------------------------------------------------------------------------
 

The .Net Framework Odbc Data Provider requires Microsoft Data Access Components(MDAC) version 2.6 or later.  Version 2.53.6307.0 was found currently installed. 

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
 

Exception Details: System.InvalidOperationException: The .Net Framework Odbc Data Provider requires Microsoft Data Access Components(MDAC) version 2.6 or later.  Version 2.53.6307.0 was found currently installed.
 

Source Error: 
 

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  
 

Stack Trace: 
 
 

[InvalidOperationException: The .Net Framework Odbc Data Provider requires Microsoft Data Access Components(MDAC) version 2.6 or later.  Version 2.53.6307.0 was found currently installed.]

   System.Data.Common.ADP.CheckVersionMDAC(Boolean ifodbcelseoledb) +1465318

   System.Data.Odbc.OdbcEnvironment.GetGlobalEnvironmentHandle() +72

   System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) +69

   System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) +27

   System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +47

   System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105

   System.Data.Odbc.OdbcConnection.Open() +37

   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121

   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137

   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83

   System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770

   System.Web.UI.WebControls.BaseDataList.GetData() +53

   System.Web.UI.WebControls.DataList.CreateControlHierarchy(Boolean useDataSource) +284

   System.Web.UI.WebControls.BaseDataList.OnDataBinding(EventArgs e) +56

   System.Web.UI.WebControls.BaseDataList.DataBind() +72

   System.Web.UI.WebControls.BaseDataList.EnsureDataBound() +55

   System.Web.UI.WebControls.BaseDataList.CreateChildControls() +63

   System.Web.UI.Control.EnsureChildControls() +87

   System.Web.UI.Control.PreRenderRecursiveInternal() +41

   System.Web.UI.Control.PreRenderRecursiveInternal() +161

   System.Web.UI.Control.PreRenderRecursiveInternal() +161

   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360
 

 

Open in new window

0
 
LVL 1

Author Comment

by:BobCSD
Comment Utility
Let me write this second one in regard to odbc up in another post.
0
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
Are you sure you only have MDAC v2.5? That's a dinosaur... have you tried downloading MDAC 2.8 and installing it? Try adding this to your connection string as well

Network Library=DBMSSOCN;
0
 
LVL 27

Accepted Solution

by:
Sammy earned 500 total points
Comment Utility
ODBC itself is a Dinosaur ;-)
>>Basically, when you connect from your PC (on the domain), it uses your (trusted) login details.  When the IIS app connets, it is using the IIS account for that web site, usually IUSR_<webmachinename> - this user is not valid on SQL Server.

Now thats there is a major difference between asp and asp.net
sp uses the IUSER account while asp.net using uses AUTHORITY\Network Service  account under windows 2000.
you have 2 options to solve this issue

1- give the Network Service  account  permission to use the sql server databases
2-Create an account on sql server and use it  instead of the trusted connection by providing the username and password for the account.

Hope this helps
0
 
LVL 1

Author Comment

by:BobCSD
Comment Utility
>> give the Network Service  account  permission to use the sql server databases
Gonna write this next question up as a new one:
http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_23041049.html
How do I do that? On the sql server2000 box or the web server box?

If I go to the database on the sql server 2000 box, and right click and add a user, there is no account for Authority.

>>2-Create an account on sql server and use it  instead of the trusted connection by providing the username and password for the account.

I did add my personal admin account to the database as a user and gave it permissions and everything. But in VWD, when I go to Add Connection, and I put in *User SQL Server Authentication, and put in my name and password, it times out.

I'm going to write this next question up in a new post:
http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_23041047.html
How do I check in sql server 2000 to use both windows authentication and sql server authentication? I know I have windows authentication turned on, but don't know if I have sql server authentication turned on.

0
 
LVL 1

Author Comment

by:BobCSD
Comment Utility
I did check and I do have both: windows authentication and sql server authentication turned on. Yet remotely, I can't seem to add the sql server authentitcation to a new connection.

Maybe I'm not setting it up properly.
0
 
LVL 1

Author Comment

by:BobCSD
Comment Utility
sammy,

You gave me the specific answer that answered this in my next question...
http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=395

thanks so much! This has taken me forever! I got the sql server authentication to work, I'm so excited! Now I'm going to go get the windows authentication to work. thanks!
0
 
LVL 1

Author Closing Comment

by:BobCSD
Comment Utility
thanks so much! I've been working on this one for a long time! some of the answers I've had are just pathetic. You are truly a genius! thanks! Moon.
0
 
LVL 1

Author Comment

by:BobCSD
Comment Utility
Yay! All I needed to do to get windows authentication to work was add this one little measley line to my web.config file in the <system.web> section:

 <identity impersonate="true" />

thanks so much sammy!
0
 
LVL 27

Expert Comment

by:Sammy
Comment Utility
You welcome Bob,
Rule of thumb with Windows permission and authentication is Impersonation. whenever you need to use windows authentications you will have to impersonate the account in some way or another.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now