Unable to establish database connection to SQL Server 2008 using java in Eclipse IDE

Question

I am trying to connect to HP Operations Manager Database using Java code in Eclipse IDE. I am able to connect successfully through Microsoft SQL Server Management Studio 2008 but it fails through code. I have installed "Microsoft JDBC Driver 4.0 for SQL Server"

Code:

import java.sql.*;

public class ConnectDatabase {

    Connection dbConnection = null;

    String dbName = "openview";
    String serverip="10.105.219.102";
    String serverport="1433";
    String url = "jdbc:sqlserver://"+serverip+"\\OVOPS;databaseName="+dbName+"";
    String userName = "HPOM-QA-WIN\\Administrator"; 
    String password = "Nbv12345";
    final String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    Statement statement = null;
    ResultSet rs = null;
    int updateQuery = 0;

    public Connection getConnection() {

        System.out.println(url);
        try{
             Class.forName(driverName).newInstance();

             dbConnection = DriverManager.getConnection(url,userName,password);
             System.out.println(DriverManager.getDrivers());

             statement = dbConnection.createStatement();

             String QueryString = "select Id from openview.dbo.OV_MS_Message where OriginalServiceId like '{FaultDn[1]}'";

             updateQuery = statement.executeUpdate(QueryString);

             if(updateQuery!=0){
                System.out.println("success" + updateQuery);
             }
             statement.close();
             dbConnection.close();
          }catch (Exception e){
              e.printStackTrace();
         }
         return dbConnection;

     }

     public static void main(String[] args) {

        ConnectDatabase cDB = new  ConnectDatabase();
        cDB.getConnection();

    }

 }

I get the following error when I execute this code:

jdbc:sqlserver://10.105.219.102\OVOPS;databaseName=openview com.microsoft.sqlserver.jdbc.SQLServerException: The connection to the host 10.105.219.102, named instance ovops failed. Error: "java.net.SocketTimeoutException: Receive timed out". Verify the server and instance names and check that no firewall is blocking UDP traffic to port 1434.  For SQL Server 2005 or later, verify that the SQL Server Browser Service is running on the host.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.getInstancePort(SQLServerConnection.java:3589)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.primaryPermissionCheck(SQLServerConnection.java:1225)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:972)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at com.ucs.test.ConnectDatabase.getConnection(ConnectDatabase.java:27)
    at com.ucs.test.ConnectDatabase.main(ConnectDatabase.java:51)

When I change the url to

String url = "jdbc:sqlserver://"+serverip+"\\OVOPS:"+serverport+";databaseName="+dbName+"";

I get the below error:

jdbc:sqlserver://10.105.219.102\OVOPS:1433;databaseName=openview com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'HPOM-QA-WIN\Administrator'. ClientConnectionId:f1d323b7-9998-418c-b2a2-f2a7bd7b9b04
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
    at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:254)
    at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:84)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:2908)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:2234)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(SQLServerConnection.java:41)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:2220)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1326)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at com.ucs.test.ConnectDatabase.getConnection(ConnectDatabase.java:27)
    at com.ucs.test.ConnectDatabase.main(ConnectDatabase.java:51)

I have explicitly added an inbound rule in windows firewall to allow UPD traffic on 1434 port, then disabled the firewall. But I still get this error. The credentials provided here are used for connection using Microsoft SQL Server Management Studio and it works perfectly fine. But it fails through the code.

I am not sure where I am going wrong. I am unable to establish a successful connection through the code. Please help me.

I have changed the url string to String url = "jdbc:sqlserver://"+serverip+"//OVOPS:"+serverport+";databaseName="+dbName+""; But his gives a new error message: The TCP/IP connection to the host 10.105.219.102//OVOPS, port 1433 has failed. Error: "null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.". — Diya, Sep 05 '12 at 14:02
So, does your sql server is running? And why are you creating statement and result set when you want to return a connection. — Branislav Lazic, Sep 05 '12 at 14:04
yes, sql server is running and I am able to execute queries from the sql server management studio. — Diya, Sep 05 '12 at 14:08
Just replace database parameter's from my answer with parameters from grachol's answer and it should work. — Branislav Lazic, Sep 05 '12 at 14:10

score 3 · Accepted Answer · answered Sep 07 '12 at 05:53

3

Hey Thanks all for your responses. Finally I was able to resolve the issue. The problem was with the url and auth dll. Changed the url to

"jdbc:sqlserver://10.105.219.102:1433;instance=OVOPS;DatabaseName=openview;integratedSecurity=true"

and added the location of "sqljdbc_auth.dll" in java.library.path. It worked!

Thanks again for your efforts to help me :)

answered Sep 07 '12 at 05:53

Diya

143
1
3
13

@Diya- Sorry, I did not get u...where exactly did u put "sqljdbc_auth.dll" file...?? – Name is Nilay Nov 08 '12 at 12:51
Add -Djava.library.path="C:\Program Files\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\auth\x64" (which is location of sqljdbc_auth.dll) to Run configurations -> Arguments -> VM Arguments – Diya Nov 09 '12 at 11:52

score 2 · Answer 2 · answered Oct 20 '14 at 20:14

It took me a while to sort this issue out, but you have to head into the SQL Server Configuration Manager application. When that's loaded, expand the [SQL Native Client 11.0 Configuration] > Client Protocols.

Enable all three (Shared Memory, TCP/IP, and Named Pipes), if they aren't already. Then click on TCP/IP and ensure that the default port is 1433.

If you have a 32bit system or version of SQL Server installed, do the same in the SQL Native Client 11.0 Configuration (32it) menu, enabling Shared Memory, TCP/IP, and Named Pipes and setting the default port to 1433.

Then click open the [SQL Server Network Configuration] or (32bit if applicable), and select the [Protocols for "YOURSERVERNAME"]. Ensure again that Shared Memory, TCP/IP, and Named Pipes are all enabled.

Then click on the [TCP/IP] Protocol Name, then select the [IP Addresses] tab at the top of the new popup window. For IP1 ensure that Active is YES; Enabled is YES (this is No by default, even if it is Active); and set the TCP Port to 1433 (tbh I don't know if you have to do this step, but I did and it worked!!); my TCP Dynamic Ports are set to 0 and I didn't change any of the IP addresses;

I did the same thing for IP10, which has IP: 127.0.0.1, which is the local machine. I also scrolled down to the bottom of the page, and set IPAll TCP Ports to 1433, (dynamic ports is 49163). Then you need to Apply all changes, close the properties window, and click on SQL Server Services in SQL Server Configuration Manager, and restart all running Servers. This should do it :D

In my case, I realized that the SQL Server Browser service was not running — Dani, Nov 13 '18 at 08:30

score 1 · Answer 3 · answered Sep 05 '12 at 14:04

1

Catch example:

    String url = "jdbc:sqlserver://localhost:1433/databaseName";
    String username = "user";
    String password = "pass";

Connection connection = DriverManager.getConnection(url, username, password);

1433 is default port. Dont use '\' in url.

answered Sep 05 '12 at 14:04

grachol

82
1
7

I have changed the url string to String url = "jdbc:sqlserver://localhost/openview"; But it still gives this error: The TCP/IP connection to the host localhost/openview, port 1433 has failed. Error: "null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.". – Diya Sep 05 '12 at 14:10
@Diya You forgot to add port number (1433). It should be: String url = "jdbc:sqlserver://localhost:1433/openview" – Branislav Lazic Sep 05 '12 at 14:13
When I added port number explicitly, it says "The port number 1433/openview is not valid." – Diya Sep 05 '12 at 14:23
@Diya Sorry Diya I gave my best. – Branislav Lazic Sep 06 '12 at 11:48
1

@Diya Maybe you have wrong driver? I mean, url is wrong for this driver: url = "jdbc:microsoft:sqlserver://localhost:1433"; http://support.microsoft.com/kb/313100 – grachol Sep 06 '12 at 11:59
1

Hey Thanks all for your responses. Finally I was able to resolve the issue. The problem was with the url and auth dll. Changed the url to ""jdbc:sqlserver://10.105.219.102:1433;instance=OVOPS;DatabaseName=openview;integratedSecurity=true" and added the location of sqljdbc_auth.dll in java.library.path. It worked! Thanks again for your efforts to help me :) – Diya Sep 07 '12 at 05:52

Branislav Lazic · Answer 4 · 2012-09-05T14:19:33.067

0

This is probably not an answer to your question, but I hope this will help you. Here is the way how to properly return connection (mysql):

import java.sql.*;

public class ConnectToDatabase{


    public Connection getConn(){

        final String DBPATH="jdbc:mysql://localhost:3306/mydb";
        final String DBUSER="root";
        final  String DBPASS="";
        Connection conn=null;
        try {
             conn = DriverManager.getConnection(DBPATH,DBUSER,DBPASS);

        } catch (SQLException e) {

            e.printStackTrace();
        }
    return conn;
    }

}

edited Sep 05 '12 at 14:19

answered Sep 05 '12 at 14:06

Branislav Lazic

14,388
8
60
85

I used your code but I still get this error: The TCP/IP connection to the host localhost/openview, port 1433 has failed. Error: "null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.". The instance name is "OVOPS". Should that not be specified explicitly in the url? I tried with string url final String DBPATH="jdbc:sqlserver://localhost/openview;instance=OVOPS;"; in your code. But that too doesnt help :( – Diya Sep 05 '12 at 14:21

score 0 · Answer 5 · answered Sep 07 '13 at 03:42

Try changing your database user password. Make sure new password doesn't have special characters. I faced same problem and it got resolved after changing the db password. Steps of my workaround:

I was getting sqlserverexception.java 190 error stating unable to connect to host port: 4500 which was my localhost port. Opened Sql Server Configuration Manager -> SQL Server Network Configuration -> Protocols for MSSQLSERVER -> Enabled TCP/IP. Checked TCP port number. It was 1433. So I changed 4500 to 1433 in my registry file.

After following above steps, I got a different error this time startingpassword for my db user(sql-test) is incorrect. I changed the password to sqltest123 and it worked.

This question is a year old. This is not very constructive at this point. Your experience could certainly be useful on some newer questions. You might even browse around to find some good answers to help you improve your formatting. — Doug Morrow, Sep 07 '13 at 04:04

Unable to establish database connection to SQL Server 2008 using java in Eclipse IDE

5 Answers5

Linked