Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user

Posted on 2006-06-13
19
Medium Priority
?
8,261 Views
Last Modified: 2008-08-12
Hi Experts,

I have 2 servers using win 2003 server and IIS 6.0 and ASP.NET 2.0.50727
and I have this code for an ODBC Connection

Dim CN As New Data.Odbc.OdbcConnection
CN.ConnectionString = ProviderName=System.Data.ODBC;DSN=ODBCNAME;Database=DATABASENAME;Integrated Security=True;Persist Security Info=True;TRUSTED_CONNECTION=YES;
CN.Open()

It is working on a server and on the other it is gaving the following error:

ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
ERROR [01S00] [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute
ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
ERROR [01S00] [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute

And the ODBC on both servers is tested and works normaly and serveral applications using VB6 and VB.net and Crystal reports 7.

So, what is the problem?
      - ASP.NET configuration?
      - IIS Configuration?
      - Server Configuration?
      - Project configuration?
any Ideas?
0
Comment
Question by:LeDaouk
  • 10
  • 4
  • 3
  • +1
19 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 16894617
Hi LeDaouk,

A bunch of things really, ASP.NET firstly provides a built-in set of SQL specific objects which will perform better than using ODBC.

Secondly, the reason it fails is that the aspnet worker process is probably running under the default ASPNET local user account. This causes integrated security to fail. There are basically two ways around this. Either you need your asp.net application to impersonate a valid domain user or you need to use sql standard security to make the connection.

Using impersonation is effectively relaxing the security on your IIS server as an unauthorised user may be able to gain access using the credentials supplied by the application.
Using SQL authentication may expose the password for that sql user though this may be less of a risk and it can of course be obfuscated.

Tim Cottee
0
 
LVL 4

Author Comment

by:LeDaouk
ID: 16894687
thanx Tim,
I triet to use SQL Server connection It gave me the same problem.
So how to do it by code?
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 16894733
LeDaouk,

Ok, first start with a

Imports System.Data.SqlClient

Then instead of your odbc connection and related stuff use:

Dim CN as SqlConnection = New SqlConnection
CN.ConnectionString = "Data Source=<SERVERNAME>;Initial Catalog=<DATABASENAME>;User ID=<SQLUSERID>;Password=<PASSWORD>;"
CN.Open()

You can then use the SQL specific SqlCommand, SqlDataReader etc in place of your ODBCCommand and ODBCDataReader objects.

Tim
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 4

Author Comment

by:LeDaouk
ID: 16894792
I tried this befrore and It gave the same result.
(Sorry But I'm new in ASP.NET)
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 16894829
LeDaouk,

That's ok, I assume you are giving the correct sql server username and password here, not a domain user account. You could try for now with the sa user (if you know this) and then set up a specific user with the appropriate rights for your application when you know it works.

CN.ConnectionString = "Data Source=<SERVERNAME>;Initial Catalog=<DATABASENAME>;User ID=sa;Password=sapwd;"


Tim
0
 
LVL 4

Author Comment

by:LeDaouk
ID: 16895035
I tried now :
CN.ConnectionString = "Data Source=<SERVERNAME>;Initial Catalog=<DATABASENAME>;User ID=sa;Password=sapwd;Trusted_Connection = yes;"
and I had the following error:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
is there somthing that I should do in the sqlserver?
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 16895050
LeDaouk,

How do you have the server configured? Windows authentication only or mixed mode authentication? If windows only then you need to change that.

Tim
0
 
LVL 4

Author Comment

by:LeDaouk
ID: 16895085
mixed
and I told the application with the same ODBC is working on a server and it is not on another
0
 
LVL 4

Author Comment

by:LeDaouk
ID: 16895128
I had the sam problem when I tried to run the application on a Windows XP IIS.
is there something common between these 2 cases?
0
 
LVL 4

Expert Comment

by:jvn222
ID: 16899544
do you add account aspnet to SQLServer?
In SQLServer, Try set aspnet 's permisson to Sysadmin!
0
 
LVL 4

Author Comment

by:LeDaouk
ID: 16900245
do u mean to create a user called aspnet in sqlserver or I have to add an account called aspnet from somewhere to sqlserver???
0
 
LVL 4

Expert Comment

by:jvn222
ID: 16900262
Account aspnet is account built-in for execute asp.net apps
you have to add an account called aspnet  from your computer (not create new)
0
 
LVL 4

Author Comment

by:LeDaouk
ID: 16900516
I tried this now but nothing happen
and I tried to configure the ODBC to use a sql server account and I had the same problem
0
 
LVL 4

Author Comment

by:LeDaouk
ID: 16900523
note that the sql server is installed on a remote server
so A SERVER (IIS) and B SERVER (IIS) and C SERVER (SQL SERVER)
the application on the server A is working but on the Server B is not working
0
 
LVL 4

Expert Comment

by:jvn222
ID: 16900543
you try add an account called aspnet to SQLServer from Server B (ex: ServerB\Aspnet)
0
 
LVL 4

Author Comment

by:LeDaouk
ID: 16900608
yep but i Failed, and error popup: usernot found
I tried to add the aspnet one on the C, it was successfully added but the application still not working.
and note that there is no ASPNET user for the server A.
Is there something in web.config I should do???
0
 
LVL 4

Author Comment

by:LeDaouk
ID: 16900633
aaaaaaaaaaaaaaa
I found it
it was the internet Data connector not Allowed on the IIS on B server.
Thanx any way guys.
0
 

Accepted Solution

by:
CetusMOD earned 0 total points
ID: 17141487
PAQed with points refunded (100)

CetusMOD
Community Support Moderator
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

916 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