Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Login failed on database connection on IIS server

Posted on 2007-11-29
22
1,032 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:Starr Duskk
  • 13
  • 5
  • 2
  • +1
22 Comments
 
LVL 22

Expert Comment

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

Expert Comment

by:RedKelvin
ID: 20379051
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20379075
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 2

Author Comment

by:Starr Duskk
ID: 20380294
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
ID: 20380332
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 2

Author Comment

by:Starr Duskk
ID: 20380384
>>the difference is that ?a <> ?b

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

Expert Comment

by:imitchie
ID: 20380538
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 2

Author Comment

by:Starr Duskk
ID: 20382944
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 2

Author Comment

by:Starr Duskk
ID: 20407631
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
ID: 20408024
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 2

Author Comment

by:Starr Duskk
ID: 20494145
I will try that.

(when the dust settles on this other project. sorry so slow for getting back.)
0
 
LVL 2

Author Comment

by:Starr Duskk
ID: 20522887
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 2

Author Comment

by:Starr Duskk
ID: 20522888
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 2

Author Comment

by:Starr Duskk
ID: 20522895
Let me write this second one in regard to odbc up in another post.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20522922
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
ID: 20522996
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 2

Author Comment

by:Starr Duskk
ID: 20523051
>> 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 2

Author Comment

by:Starr Duskk
ID: 20523075
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 2

Author Comment

by:Starr Duskk
ID: 20525113
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 2

Author Closing Comment

by:Starr Duskk
ID: 31411845
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 2

Author Comment

by:Starr Duskk
ID: 20525189
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
ID: 20525646
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Check ALL SP in database make sure there are no errors 17 43
SQL Server 2008 R2, need a pivot/cross tab query... 4 27
sql, case when & top 1 14 26
RAISERROR WITH NOWAIT 2 15
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

840 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