0

I'm working on a project where I'm trying to connect a java spring application to a MS SQL 2008 R2 Server. The connection class is pretty straightforward here:

package databaseAccess;

import java.sql.Driver;

import org.skife.jdbi.v2.*;

public class DBConnection {

    DBI dbi;

    public DBConnection open() {
        String dbServerAddress =             "jdbc:sqlserver://<ipaddress>:1433;databaseName=<DBName>;integratedSecurity=true";
        String user = "<UserName>";
        String password = "<Password>";
        dbi = new DBI(dbServerAddress,user,password);
        return this;
    }

    public Handle getHandle() {
        return dbi.open();
    }

}

When I run this I'm getting the error:

Exception in thread "main"     org.skife.jdbi.v2.exceptions.UnableToObtainConnectionException:     com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the     host 129.21.208.42, port 1433 has failed. Error: "connect timed out. 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.".
    at org.skife.jdbi.v2.DBI.open(DBI.java:191)
    at databaseAccess.DBConnection.getHandle(DBConnection.java:20)
    at user.User.create(User.java:34)
    at main.Application.main(Application.java:20)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 129.21.208.42, port 1433 has failed. Error: "connect timed out. 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.".
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
    at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:241)
    at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2243)
    at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:491)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1309)
    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 org.skife.jdbi.v2.DBI$3.openConnection(DBI.java:125)
    at org.skife.jdbi.v2.DBI.open(DBI.java:180)
    ... 3 more

Now I've gone through a ton of other issues people have had and haven't been able to figure this out. The server's IPAII is active, enabled and the port is set to 1433. The firewall is open for that port on the server. I have JDBC setup and in the dll file is in my Java path, yet I'm still getting this error. Anyone have any ideas as to what I'm doing incorrectly?

Sildirc Stormwind
  • 13
  • 3

1 Answers1

1

I had such a scenario on different occasions. Some resolutions may apply:

  • The SQL Server is set to port randomization. This means, it will try to switch you to another port on connect.
    We have solved this by disabling the feature for our database. But we have heard, that using no port resolves this issue as well.

  • You are really on a DBInstance and did not give that name to your jdbc string.

  • Your virus scanner is blocking the java connection to that server, because virus scanner programmers are paranoid and all viruses are written in java and your java version is old/outdated.

  • Your windows user is not active on that database. But that should give you rather a access denied message.

  • UPDATE: Possibly duplicate of this question Can I connect to SQL Server using Windows Authentication from Java EE webapp?

Hope any of this helps.

Community
  • 1
  • 1
thst
  • 4,592
  • 1
  • 26
  • 40
  • I think it may have something to do with the dll file in my build path. Do you happen to know where I can find a guide on how to set that up in spring/eclipse so I can verify if I did that incorrectly? – Sildirc Stormwind Apr 30 '15 at 16:56
  • Hm, you mean the mssql-jdbc dll used for windows authentication? if it was that, you would rather receive an `UnsatisfiedLinkError`. As for an example to setup spring eclipse and jni, I am sure, if you enter this in SO or google, you will find plenty of pages. I have none at hand to suggest. – thst Apr 30 '15 at 17:01
  • Hmmm, I read that there is a dll file you need to have as an environment variable in order for 64 bit machines to be able to use the sqlijdbc4.jar. I think that maybe that's what I set up incorrectly, I've been googling around for a while now, but I haven't been able to find an example that will walk me through step by step on how to set that up :/ – Sildirc Stormwind Apr 30 '15 at 17:09
  • There is no such thing :-) sqljdbc4 works without any native part, but requires a dll for some special MS features. One is the integrated login, that extracts your windows runtime user to login to the database server. To use the dll, you need to either copy it to a default location (for example int JAVA_HOME\bin) or set the `java.library.path` property, to the location where your dlls reside. This can become pretty complex, but afair the sqljdbc documentation has a sample. If the java runtime is x64, your dlls need to be x64. – thst Apr 30 '15 at 17:13
  • I just tried without a port, no luck. I also recreated the DB on my local machine with MS SQL Server 2008 and pointed it to that, still no luck. :/ – Sildirc Stormwind Apr 30 '15 at 17:22
  • Have a look at the SO post I added, if the authentication and DLL are the issue, you may find some information there. – thst Apr 30 '15 at 17:26
  • I'll look at it again, but that's a link I've looked at quite a few times already, lol. Thank you though. – Sildirc Stormwind Apr 30 '15 at 17:56
  • If you really believe in the DLL problem :-) Then grab ProcessExplorer from Microsoft technet and trace the java.exe activity. Maybe it is missing another library like a msvcrt runtime. – thst Apr 30 '15 at 17:58