Connecing MSSQl with Java

You should probably try to use the JDBC driver that comes with the DB.

As for authentication, you can use a Trusted Connection for Win auth. See

http://www.datadirect.com/developer/jdbc/topics/winauth/index.ssp

MS recommends

http://www.microsoft.com/downloads/details.aspx?familyid=e22bc83b-32ff-4474-a44a-22b6ae2c4e17&displaylang=en

Hi,

I copied sqljdbc.jar file in directory C:\Program Files\Java\jdk1.6.0_11\lib

and in classpath I wrote

%classpath%;C:\Program Files\Java\jdk1.6.0_11\lib\sqljdbc.jar

In connection string I wrote

Class.forName("com.microsoft.sqlserver.jdbc");

I got an exception

Got an exception!
com.microsoft.sqlserver.jdbc
Press any key to continue . . .


When I write

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

I get an error

Got an exception!
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Press any key to continue . . .

My servers details are below

host = 172.17.1.65
DBServer = ABCD-PBHA23
database = Workflow
user = "sa"
password = "sa"

I need windows authentication connection string

Please guide

Thanks

//  Establish a connection to an Interbase database using JDBC.
import java.sql.*;
 
class JdbcTest1 {
 
    public static void main (String[] args) {
        try {
 
            // Step 1: Load the JDBC driver.
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            //Class.forName("com.microsoft.sqlserver.jdbc");
 
            // Step 2: Establish the connection to the database.
            String url = "jdbc:odbc:contact_mgr";
            //jdbc:msql://www.myserver.com:1114/contact_mgr
            Connection conn = DriverManager.getConnection(url,"user1","password");
 
        } catch (Exception e) {
            System.err.println("Got an exception! ");
            System.err.println(e.getMessage());
        }
    }
}

Select allOpen in new window

Try

String url = "jdbc:odbc:ABCD-PBHA23/Workflow";
...


Connection conn = DriverManager.getConnection(url,"sa","sa");

But i need to give server address where sql server is running

Its on 172.17.1.65

And I need to use windows authentication.

I dont want to use sql authetication connection

Thanks

>>I copied sqljdbc.jar file in directory C:\Program Files\Java\jdk1.6.0_11\lib


Put that in

C:\Program Files\Java\jdk1.6.0_11\jre\lib\ext

>>Its on 172.17.1.65

Doesn't ABCD-PBHA23 resolve to that?

Getting the same exception

Got an exception!
[Microsoft][ODBC Driver Manager] Data source name not found and no default drive
r specified
Press any key to continue . . .


My host is at 172.17.1.65

//  Establish a connection to an Interbase database using JDBC.
import java.sql.*;
 
class JdbcTest1 {
 
    public static void main (String[] args) {
        try {
 
            // Step 1: Load the JDBC driver.
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            //Class.forName("com.microsoft.sqlserver.jdbc");
 
            // Step 2: Establish the connection to the database.
            String url = "jdbc:odbc:ABCD-PBHA23/Workflow";
            //jdbc:msql://www.myserver.com:1114/contact_mgr
            Connection conn = DriverManager.getConnection(url,"user1","password");
 
        } catch (Exception e) {
            System.err.println("Got an exception! ");
            System.err.println(e.getMessage());
        }
    }
}

Select allOpen in new window

No
>>Its on 172.17.1.65

It doesn't ABCD-PBHA23 resolve to 172.17.1.65

Try

jdbc:odbc:127.17.1.65/Workflow

in that case

Same error

Connection conn = DriverManager.getConnection(url,"user1","password");

Also, it is using windows authentication, so there will be no username and password

Anyway, I am still getting the same error

Thanks for your co-operation

But you should really be using the JDBC driver, so the connection string should be

final String CONNECT_STRING = "dbc:sqlserver://172.17.1.65\\Workflow";

For Windows authentication, that should be

final String CONNECT_STRING = "dbc:sqlserver://172.17.1.65\\Workflow;integratedSecurity=true";

Thanks then where should I use CONNECT_STRING in my above code

Also, I think I need JDBC jar file to do so.

If yes, I need to download that as well.

I have downloaded file sqljdbc_2.0.1607.1_enu.exe from the given URL

When I extracted the files, the jar file has the following packages

com.microsoft.sqlserver.jdbc


Classpath added
C:\me\Pers\Java\JDBC\Windows\sqljdbc_2.0.1607.1_enu\sqljdbc_2.0\enu.jar;C:\me\Pers\Java\JDBC\Windows\sqljdbc_2.0.1607.1_enu\sqljdbc_2.0\enu\sqljdbc4.jar

In the
dbc:sqlserver://172.17.1.65\\Workflow;integratedSecurity=true

Where is the DBServer name PBMS-PBHA23. This is the DB server which has the database in it

Below code gives same error

Got an exception!
com.microsoft.sqlserver.jdbc
Press any key to continue . . .

import java.sql.*;
 
class JdbcTest1 {
 
    public static void main (String[] args) {
        try {
 
            // Step 1: Load the JDBC driver.
            //Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Class.forName("com.microsoft.sqlserver.jdbc");
 
            // Step 2: Establish the connection to the database.
            //String url = "jdbc:odbc:PBMS-PBHA23/DocumationWorkflow";
            //String url = "jdbc:odbc:172.17.1.65/DocumationWorkflow";
            String CONNECT_STRING = "dbc:sqlserver://172.17.1.65\\Workflow;integratedSecurity=true";
 
            //jdbc:msql://www.myserver.com:1114/contact_mgr
            //Connection conn = DriverManager.getConnection(url,"user1","password");
            Connection conn = DriverManager.getConnection(CONNECT_STRING);
 
        } catch (Exception e) {
            System.err.println("Got an exception! ");
            System.err.println(e.getMessage());
        }
    }
}

Select allOpen in new window

>>String CONNECT_STRING = "dbc:sqlserver://172.17.1.65\\Workflow;integratedSecurity=true";

should be

String CONNECT_STRING = "jdbc:sqlserver://172.17.1.65\\Workflow;integratedSecurity=true";

Assuming that you dont require windows authentication, use the following code,-
You havent mentioned any port in the connection url.

try following.

try{
java.lang.Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection c = java.sql.DriverManager.getConnection("jdbc:sqlserver://172.17.1.65:1433;DatabaseName=mydb;user=testadmin;password=admin");
}catch(Exception ex){
//exception here
}

Merging those, try


String CONNECT_STRING = "jdbc:sqlserver://172.17.1.65:1433;DatabaseName=Workflow;integratedSecurity=true";

Got an exception!
The TCP/IP connection to the host  has failed. java.net.ConnectException: Connec
tion refused: connect
Press any key to continue . . .

If I ping 172.17.1.65

Pinging 172.17.1.65 with 32 bytes of data:

Reply from 172.17.1.65: bytes=32 time=20ms TTL=128
Reply from 172.17.1.65: bytes=32 time=23ms TTL=128
Reply from 172.17.1.65: bytes=32 time=19ms TTL=128
Reply from 172.17.1.65: bytes=32 time=19ms TTL=128

Ping statistics for 172.17.1.65:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 19ms, Maximum = 23ms, Average = 20ms

//  Establish a connection to an Interbase database using JDBC.
import java.sql.*;
import com.microsoft.sqlserver.jdbc.*;
import com.microsoft.sqlserver.*;
 
class JdbcTest2 {
 
    public static void main (String[] args) {
 
 
		try{
		java.lang.Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		//Connection c = java.sql.DriverManager.getConnection("jdbc:sqlserver://172.17.1.65:1433;DatabaseName=DocumationWorkflow;integratedSecurity=true");
		Connection c = java.sql.DriverManager.getConnection("jdbc:sqlserver://172.17.1.65:1433;DatabaseName=DocumationWorkflow;user=test;password=test");
		}catch(Exception e){
			System.err.println("Got an exception! ");
            System.err.println(e.getMessage());
		}
 
    }
}

Select allOpen in new window

Also, we have not added dbserver name

DBServer = ABCD-PBHA23

I think we require that as well some where

Thanks for your co-operation

Pinging it won't tell you much. You need to know:

a. that the MSSQL server is running on that host
b. what port it's running on

Check your server config

>>DBServer = ABCD-PBHA23

Are you sure that's not the hostname? What does the following do?

ping ABCD-PBHA23

Select allOpen in new window

IP: 172.17.1.65 --> It is the computer IP where MSSQL server is hosted

DBServer = ABCD-PBHA23 --> When you click on SQL management studio, MSSQl first asks for screen where you put your user name password or do windows authenitication. This is that screen. Please see screen attached



dbscreen.bmp

That could be the 'instance name' then, so try

String CONNECT_STRING = "jdbc:sqlserver://172.17.1.65:1433\\ABCD-PBHA23;DatabaseName=Workflow;integratedSecurity=true";

Error message changed to

Got an exception!
Invalid integratedSecurity property value:true
Press any key to continue . . .

When I use my connection string like this

Connection c = java.sql.DriverManager.getConnection("jdbc:sqlserver://172.17.1.65:1433\\ABCD-PBHA23;DatabaseName=DocumationWorkflow;user=test;password=test");

I get the error

Got an exception!
The port number 1433\PBMS-PBHA23 is not valid.
Press any key to continue . . .

You must ascertain what port it's running on, or try leaving the port out of the string

I just came to know that PBMS-PBHA23 is the 172.17.1.65 itself

Also, I have not registered sqljdbc_xa.dll file which come with sqljdbc_1.0.809.102_enu.exe

Do I need to register this??

Also here is the current error


Connection c = java.sql.DriverManager.getConnection("jdbc:sqlserver://172.17.1.65:1433;DatabaseName=DocumationWorkflow;integratedSecurity=true");

Got an exception!
Invalid integratedSecurity property value:true
Press any key to continue . . .


Connection c = java.sql.DriverManager.getConnection("jdbc:sqlserver://172.17.1.65:1433;DatabaseName=DocumationWorkflow;user=test;password=test");

Got an exception!
The TCP/IP connection to the host  has failed. java.net.ConnectException: Connec
tion refused: connect
Press any key to continue . . .


Thanks for all your co-operation

You need to make sure you've installed the driver properly. Read it's help files and see the following:

http://www.devx.com/dbzone/Article/33681

Once you're sure you've got it installed properly, the following shows how to make the connection string:

http://msdn.microsoft.com/en-us/library/ms378428.aspx

Many Many Thanks.

Connection c = java.sql.DriverManager.getConnection("jdbc:sqlserver://172.17.1.65:1433;DatabaseName=DocumationWorkflow;integratedSecurity=true;");

It works

:-)

let me know if you have any more questions, I should be able to save you having to guess what the right answer is.