Solved

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

Posted on 2007-04-09
21
10,139 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
ID: 18874966
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
ID: 18875284
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
ID: 18875861
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18875865
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
ID: 18876591
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]
ID: 18876646
> " 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
ID: 18878440
password is blank
0
 
LVL 6

Expert Comment

by:chiragkhabaria
ID: 18878548
try out the connection string from the following link..

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

Expert Comment

by:rboyd56
ID: 18878572
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]
ID: 18878580
even if password is blank, you have to specify that part:

Server=192.168.0.10;database=ISSB;user id=sa;password=; "
0
 
LVL 3

Expert Comment

by:timeshell
ID: 18882182
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
ID: 18891073
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
ID: 18891223
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
ID: 18891297
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
ID: 18891512
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
ID: 18891515
Do this import on the Win98 computer.
0
 
LVL 16

Accepted Solution

by:
rboyd56 earned 200 total points
ID: 18891517
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
ID: 18896279
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
ID: 18897601
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
ID: 18944616
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
ID: 18946695
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

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…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

822 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