Solved

" Login failed for user sa. Reason: Not associated with a trusted SQL Server connection."

Posted on 2007-04-09
21
10,129 Views
Last Modified: 2012-05-05
Hello
I have 2 computers one has server has windows xp and the client has win 98 installed on them.
these computer are connected through dialup (direct telephone dialing).
Sql server 2000 is installed on the windows xp computer and the authentication mode is mixed.
from the client vb.net application is used to connect, when it tries to connect the message appears
" Login failed for user sa. Reason: Not associated with a trusted SQL Server connection."
there shouldnt be such error because the authentication mode is mixed.
is there any other reason? is it MSDE problem?
Solution please
thanks
0
Comment
Question by:jawadashraf78
  • 7
  • 5
  • 3
  • +3
21 Comments
 
LVL 6

Expert Comment

by:chiragkhabaria
Comment Utility
Are you able to connect via Query Analyser??

what connection string you are using?? check out the this site

http://www.connectionstrings.com/
0
 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
It looks like there may be a problem with SqlClient in some cases. You might want to try using ODBC objects in place of the SqlClient objects.

Using the sa account is a really bad idea. It has full permission to do anything. In addition, the default password for sa is an empty string, which kind of leaves a HUGE security hole.

You'll also want to check that SP4 is installed, since it looks like there may be some issues with mixed-mode security in an unpatched version of SQL Server 2000: http://blog.benday.com/archive/2005/06/16/637.aspx
0
 
LVL 16

Expert Comment

by:rboyd56
Comment Utility
chiragkhabaria's idea is the best way to trouble shoot this. Get the application out of the way and see if you can connect using Query Analyzer. If you cannot put Query Analyzer on the client machine, use the ODBC applet in Control Panel to set up a DSN. Then test the connection using sa. If that works then you know it is the application. If you get the same error the SQL server 2000 (MSDE) is not configured correctly).

You said that the authentication mode was set to mixed. Did you restart SQL Server after making this change.

Check the LoginMode entry undeer the following registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer (default instance)

or

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance>\MSSQLServer (named instance)

The value should be 2.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
please post your connection string here.
if you did not specify a sql login, windows based-authentication is tried instead. however, if your windows account is not defined directly or indirectly (via a windows group), you will get that error.
0
 

Author Comment

by:jawadashraf78
Comment Utility
thnx

I want to add further that the same application is installed on another computer, xp is installed on it and it gets connected easily. it is the third computer.

1: main computer having sql server on it.
2: client accessing using vb.net application OS win 98
3: client accessing using vb.net application OS win xp

connection string is
 " Server=192.168.0.10;database=ISSB;user id=sa; "

thnx
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
> " Server=192.168.0.10;database=ISSB;user id=sa; "

and where is the password in the connection string? you need that, when using SQL authentication.
0
 

Author Comment

by:jawadashraf78
Comment Utility
password is blank
0
 
LVL 6

Expert Comment

by:chiragkhabaria
Comment Utility
try out the connection string from the following link..

http://www.connectionstrings.com/?carrier=sqlserver
0
 
LVL 16

Expert Comment

by:rboyd56
Comment Utility
Just as a word of warning and you may have heard this befor or read it before as teh case may be:

Having a blank password for the sa login is not very safe. If someone were to log into your SQL Server then they would have access to all your data and the ability to delete anything they wanted to within SQL Server and possibly on the box itself depending on what permissions the SQL Server service account has on the mahcine
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
even if password is blank, you have to specify that part:

Server=192.168.0.10;database=ISSB;user id=sa;password=; "
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 3

Expert Comment

by:timeshell
Comment Utility
This line should be a hint:
"Not associated with a trusted SQL Server connection"

This error occurs when the connection is using Windows or NT authentication instead of MSSQL server authentication.  If you are connecting via a User or System DSN, go into the DSN configuration and make sure that where it verifies authenticity that it is set to "With SQL Server authentication using a login ID and password entered by the user."

If this setting is not correct, you can enter anything you want in your connection string, but it will not use it if it is expecting to authenticate with your Windows login ID.

I 95% guarantee that this is your issue.
0
 

Author Comment

by:jawadashraf78
Comment Utility
thanks u all

blank password is not an issue at the moment i ll change it later on.
But as i told earlier authentication mode is mixed, its not window authentication.
and it is working from one computer and not from other.
the one for which this connection string is working has xp installed on it and the one for which its not working has 98 on it.
again keep in mind both connects remotely through telephone line.
so is there any way to change dialup connection setting in 98 that may solve the problem.

thnx
0
 
LVL 3

Expert Comment

by:timeshell
Comment Utility
The point isn't whether the authentication mode is mixed on the server.  I presume that you are using an ODBC DSN for your connection.  It's in the ODBC configuration on the client that you define which authentication method you are going to use to connect to your data source at the server.  When you try to connect from the client side, using Windows authentication instead of SQL authentication, to the server side, you will get this error.  

In short, your client side ODBC DSN configuration is your answer.  If it's set to Windows, it will not accept your SQL Server login parameters.  It will use your Windows login ID.  Check your ODBC settings.  You will find the ODBC Data Sources applet in the Control Panel.  Your DSN will be under User DSN or System DSN.
0
 
LVL 3

Expert Comment

by:timeshell
Comment Utility
Incidentally, it's not possible to define mixed mode authentication on the client.  Your only options are Windows or SQL.  You must choose SQL to use an SQL login.
0
 
LVL 3

Expert Comment

by:timeshell
Comment Utility
Copy the following text between the [BLOCK] markings (but not including the [BLOCK] markings) to a text file and name it fix_odbc.reg.  Then, go to your regedit.exe and import that file.  That should fix it for you.

[BLOCK]
REGEDIT4

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ISSB]
"Trusted_Connection"="No"

[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ISSB]
"Trusted_Connection"="No"

[BLOCK]
0
 
LVL 3

Expert Comment

by:timeshell
Comment Utility
Do this import on the Win98 computer.
0
 
LVL 16

Accepted Solution

by:
rboyd56 earned 200 total points
Comment Utility
This could be related to the version of MDAC installed on the Windows 98 computer. Do you know what version is installed? If not then download the componet checcker from here:

http://www.microsoft.com/downloads/details.aspx?FamilyID=8f0a8df6-4a21-4b43-bf53-14332ef092c9&DisplayLang=en

and run it on the Windows 98 machine.

If it is an older version then I would upgrade to at least MDAC 2.7 or MDAC 2.8.

MDAC 2.6 had an issue where NT authentiaton was attempted even if you were attempting to use a SQL authenticated login. You may be running into this.
0
 

Author Comment

by:jawadashraf78
Comment Utility
thnx rboyd56

this could b the issue, i was thinking of it too and now as u said i m sure it will be the problem.
0
 
LVL 3

Assisted Solution

by:timeshell
timeshell earned 200 total points
Comment Utility
Please reference the following link for rboyd56' comment.  Please note that it also makes reference to the client authentication using SQL rather than Windows.

http://www.thescripts.com/forum/thread195807.html
0
 

Author Comment

by:jawadashraf78
Comment Utility
Thanks u all
Can any one tell me that
same application is used on xp client and it is able to connect and 98 is not.
I m not using DSN but connection string in application.
One thing more that it worked a month ago on 98 and now its not, may be some changes have been made on 98 which i dont know, what could it be? that caused it to stop working.

Another thing that is it possible that  MSDE settings can be changed, if yes then how to fix it.

Please answer as early as possible
0
 
LVL 3

Expert Comment

by:timeshell
Comment Utility
I will still suggest that your answer lies in your error message.  That error message specifically occurs when it is using Windows authentication.  A trusted SQL Server connection is specifically associated with that.  If you are not using ODBC, then there must be some other setting in play somewhere that is telling it to authenticate using MS Windows rather than MS SQL.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

762 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

12 Experts available now in Live!

Get 1:1 Help Now