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.