MSSQL 2000 – ODBC connection SQL state 28000 error 18456

pts.
Tags:
Microsoft Windows
SQL Server
I am trying to make an ODBC connection to a MSSQL 2K server on a remote machine. I am using the SQL Server driver and can see the server in the drop down list when asked which server I would like to connect to. I am using SQL authentication over TCP/IP. The error occurs when ODBC tries to connect to SQL server to obtain the default settings. The error I receive is as follows:

Connection Failed; sqlstate '28000'; sql server error: 18456; [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'XXXX'


I have tried setting up alternate SQL Server users with varying security rights on the server but am not able to setup the ODBC connection. I am setting the ODBC connection up on a windows XP pro SP2 machine. The remote server is running Windows server 2003 (patched upto date) and MS SQL server 2K (SP3). The connection is over a LAN and does not pass through any firewalls (hardware or software). I can create the ODBC connection without issue locally on the server. This is my first time creating an ODBC connection to a Windows 2003 Server and I am wondering if there is some additional config I may have missed out. Many thanks.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Look at the SQL Log for additional information on the error. It appears to be a failed login.

Here is an msdn article about it.

You might try running a trace on the SQL box while you attempt to create the ODBC connection. Does the person creating the ODBC connection have rights to the SQL server?

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Seldom
    Hi Whittler, Thanks for the pointers. I have checked out the log on the server, but it only registers the DB and services starting. I checked that the DB is set to log everything correctly and it is. I tried putting a trace on the ODBC connection and that to logged nothing. I am confused as this points towards an issue well before a connection to the SQL server occurs, yet I can attach to other remote SQL servers fine. I have tried making the ODBC connection from several Win XP pro machines on the network without luck, so I am back to my theory that Win 2003 server is somehow involved in the issue. I have even tried removing the instance completely from the server and re-installing it and re-attaching the DB. I am now reduced to threatening it with a range of heavy objects! Any further pointers gratefully received.
    0 pointsBadges:
    report
  • Invisflare
    Were you using Windows authentication or SQL Server authentication? Try the other one if you only used one type. The result may shed some light on whether there's an issue with user rights/authentication mechanism setup...
    0 pointsBadges:
    report
  • Lsandhar
    Have you got mix mode authentication on?
    0 pointsBadges:
    report
  • Seldom
    I am using mixed mode but am only really using SQL authentication. I have tried windows auth but that gave a similar error.
    0 pointsBadges:
    report
  • Lsandhar
    Have you got a update version of mdac on your client pc?
    0 pointsBadges:
    report
  • Seldom
    I am running version 2.81 of the MDAC on all of the clients. I have checked and there are no newer versions I can install.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following