• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 580
  • Last Modified:

Connecing MSSQl with Java

Hi,

I am working on java after a long time

I need to connect MSSQl with java version "1.6.0_11"

I am making standalone application . I t is not web application

Can you please help me what jar file I need.

Also I need connection string to connect  MSSQL 2005 with windows authentication and without windows authentication

Thanks
0
tia_kamakshi
Asked:
tia_kamakshi
  • 19
  • 14
  • 2
  • +1
2 Solutions
 
CEHJCommented:
You should probably try to use the JDBC driver that comes with the DB.
0
 
CEHJCommented:
As for authentication, you can use a Trusted Connection for Win auth. See

http://www.datadirect.com/developer/jdbc/topics/winauth/index.ssp
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
tia_kamakshiAuthor Commented:
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());
        }
    }
}

Open in new window

0
 
CEHJCommented:
Try

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


Connection conn = DriverManager.getConnection(url,"sa","sa");
0
 
tia_kamakshiAuthor Commented:
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
0
 
CEHJCommented:
>>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
0
 
CEHJCommented:
>>Its on 172.17.1.65

Doesn't ABCD-PBHA23 resolve to that?
0
 
tia_kamakshiAuthor Commented:
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());
        }
    }
}

Open in new window

0
 
tia_kamakshiAuthor Commented:
No
>>Its on 172.17.1.65

It doesn't ABCD-PBHA23 resolve to 172.17.1.65
0
 
CEHJCommented:
Try

jdbc:odbc:127.17.1.65/Workflow

in that case
0
 
tia_kamakshiAuthor Commented:
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
0
 
CEHJCommented:
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";
0
 
CEHJCommented:
For Windows authentication, that should be

final String CONNECT_STRING = "dbc:sqlserver://172.17.1.65\\Workflow;integratedSecurity=true";
0
 
tia_kamakshiAuthor Commented:
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.
0
 
CEHJCommented:
This should get you the latest driver
http://www.microsoft.com/downloads/details.aspx?FamilyId=C47053EB-3B64-4794-950D-81E1EC91C1BA&displaylang=en

The connection string should appear just before you call getConnection
0
 
tia_kamakshiAuthor Commented:
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());
        }
    }
}

Open in new window

0
 
CEHJCommented:
>>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";
0
 
mandar_udg80Commented:
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
}

0
 
CEHJCommented:
Merging those, try


String CONNECT_STRING = "jdbc:sqlserver://172.17.1.65:1433;DatabaseName=Workflow;integratedSecurity=true";
0
 
tia_kamakshiAuthor Commented:
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());
		}
 
    }
}

Open in new window

0
 
tia_kamakshiAuthor Commented:
Also, we have not added dbserver name

DBServer = ABCD-PBHA23

I think we require that as well some where

Thanks for your co-operation
0
 
CEHJCommented:
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
0
 
CEHJCommented:
>>DBServer = ABCD-PBHA23

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


ping ABCD-PBHA23

Open in new window

0
 
tia_kamakshiAuthor Commented:
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
0
 
CEHJCommented:
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";
0
 
tia_kamakshiAuthor Commented:
Error message changed to

Got an exception!
Invalid integratedSecurity property value:true
Press any key to continue . . .
0
 
tia_kamakshiAuthor Commented:
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 . . .
0
 
CEHJCommented:
You must ascertain what port it's running on, or try leaving the port out of the string
0
 
tia_kamakshiAuthor Commented:
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
0
 
CEHJCommented:
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
0
 
CEHJCommented:
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
0
 
objectsCommented:
> Do I need to register this??

no

> Also here is the current error

try:

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

0
 
tia_kamakshiAuthor Commented:
Many Many Thanks.

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

It works
0
 
CEHJCommented:
:-)
0
 
objectsCommented:
let me know if you have any more questions, I should be able to save you having to guess what the right answer is.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 19
  • 14
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now