Unable to connect to DB using ODBC

[ziycon]

The ODBC connection is setup and testing grand. I can log into the DB via the management console no problem. Also the windows firewall is disabled.

But if I try to run the java file it errors out with:

error java.sql.SQLExc eption: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa'.

The connection string is below and its connecting to a SQL 2005 DB.

String surl="jdbc:odbc:testconnect";
Connection con=DriverManager.getConnection(surl,"sa","password");

I'm going crazy, can anyone point me in the right direction?

P.S. - I didn't set up the SA user in the java, it was done previously and can't be changed.

[wizardry]

do you have the current java odbc? also i would try to login to the database with that user it should connect.

[ziycon]

I'm unsure how to change the ODBC driver or how to add a new one as I think it may be the SQL 2000 driver thats installed its version is 2000.85.1132.00 ?!

Also the ODBC currently connects to the database with the ODBC driver.

[wizardry]

here is the link for jdbc driver:

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=99b21b65-e98f-4a61-b811-19912601fdc9&displaylang=en

you also need the jdk which is here:

http://www.oracle.com/technetwork/java/javase/downloads/index-jdk5-jsp-142662.html

also have you patched the sql server 2000 database? that should have the latest drivers with the patches.

http://support.microsoft.com/kb/821277

just follow the instructions.

also have you checked the error logs? it should tell you what is going on other then unable to access.

[ziycon]

This is the error in the event logs. It doesn't make sense as the ODBC connection test works grand and it can connect via the 'sa' user!?

Login failed for user 'sa'. [CLIENT: xx.xx.xx.xx]

[ziycon]

Think its sorted, the SA user wasn't part of the security/logins for the database in question and I was unable to add it but the ODBC connection would still connect as it was connecting to the master DB.

I created a new user with the required privileges and assigned it to the DB I needed it to access, created a new ODBC connection using the new user and changed the java code to use this new ODBC connection and hey presto, all working.

Thanks for all your help.

P.S. - Also had to enable remote connections and start the SQL Browser.