Connecting to SQL Server from Java

Almost every time I try to connect to SQL Server from Java I end up hacking something together, like copy-pasting the connection string from an old project; then again, more often than not I end up getting some sort of error and spending quite sometime Googling. So I spent sometime trying to understand the different options and thought I’d put together a few things I have learnt. I’m using eclipse, so the examples are based on that, but I’m sure you could easily find the equivalent if you’re using a different editor.

There seem to be primarily two ways to connect to SQL Server – using Windows Authentication or SQL Authentication (with a username/password). Also, we have two commonly used JDBC Type IV drivers – MS-JDBC and jTDS. This gives us four combinations and here are the connection strings/settings you need to get things up and running (you may need to adjust the string according to your PC name, SQL Server instance and DB name):

1. Windows authentication + JDBC

This is the connection string we have to use:

String url ="jdbc:sqlserver://MYPC\\SQLEXPRESS;databaseName=MYDB;integratedSecurity=true";
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection conn = DriverManager.getConnection(url);

For this to work, you’ll need to have sqljdbc driver included in the build path:

Note that you need to point to the sqljdbc4.jar (shown below) in ‘Microsoft JDBC Driver 4.0 for SQL Server’ folder. Make sure you have all other files that go along with it as well as simply having only the .jar won’t do.

You would think this would be enough, but if you just run it now you’re going to get the following error:

Jan 22, 2012 1:41:43 PM com.microsoft.sqlserver.jdbc.AuthenticationJNI <clinit>
WARNING: Failed to load the sqljdbc_auth.dll cause :- no sqljdbc_auth in java.library.path

So you also need to add the path to sqljdbc_auth.dll as a VM argument:

Now at one stage I was having the 3.0 JDBC driver and was getting the same error above and after wasting a decent amount of time found out that SQL Server 2012 requires the JDBC 4.0 driver. So if you are working with SQL Server 2008 you might need 3.0, although I’m guessing 4.0 should be backward compatible.

2.  SQL authentication + JDBC

This is probably the easiest scenario. You need to add sqljdbc4.jar similar to (1), but don’t need the VM arguments. The connection string to use in this case is:

String userName ="username";
String password ="password";

String url ="jdbc:sqlserver://MYPC\\SQLEXPRESS;databaseName=MYDB";

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection conn = DriverManager.getConnection(url, userName, password);

Some details related to setting up a new user can be found here.

3.  Windows authentication + jTDS

jTDS uses Windows authentication by default, so this is the connection string we need to use (don’t need to explicitly mention ‘integrated security’).

String url ="jdbc:jtds:sqlserver://MYPC/MYDB;instance=SQLEXPRESS";
Class.forName("net.sourceforge.jtds.jdbc.Driver");
Connection conn = DriverManager.getConnection(url);

You also need jtds.jar in the Java build path:

Now at this point if you just run it you are going to get the following error:

java.sql.SQLException: I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.

Took me a while to figure out how to resolve this, but what you need to do is something a bit similar to (1). Simply have the following as a VM argument:

’Path’ above is where your SSO folder is after installing the jTDS driver. Seems like for Windows authentication to work, Java needs to know where this ntlmaauth.dll is.

4.  SQL authentication + jTDS

Very similar to case (2). No VM arguments needed, but still need jtds.jar in the build path. Connection string to use is:

String userName ="username";
String password ="password";
String url ="jdbc:jtds:sqlserver://MYPC/MYDB;instance=SQLEXPRESS";
Class.forName("net.sourceforge.jtds.jdbc.Driver");
Connection conn = DriverManager.getConnection(url, userName, password);

Once you have the Connection, you can run a query/stored procedure as follows:

Query/UDF

try{           

    PreparedStatement ps = conn.prepareStatement("select * from yourTable/UDF/query");
    /* if UDF, and need to pass params, can do something like:
    ...prepareStatement("select * from UDF('" + UDFinputVal + "')" */

    ResultSet rs = ps.executeQuery();

    while (rs.next()) {

        String relatedConcept = rs.getString(1);
        int shortestDistance = rs.getInt(2);
        float weightedDistance = rs.getFloat(3);

        // do stuff    }  

    rs.close();
    ps.close();            
}
catch (Exception e) {
    e.printStackTrace();
}

Stored procedure

try{           

    // execute SP that requires two string arguments    PreparedStatement ps = conn.prepareStatement("exec storedProcedure ?,?");
    ps.setEscapeProcessing(true);

    ps.setString(1, "str1");
    ps.setString(2, "str2");

    ResultSet rs = ps.executeQuery();

    while (rs.next()) {
        String strVal = rs.getString(1);
        // do stuff    }

    /* If you get multiple results back from you SP, you can iterate through them using:

    ps.getMoreResults();
    rs = ps.getResultSet();

    while (rs.next()) {
        String strVal = rs.getString(1);
        ... 
    }  */

    rs.close();
    ps.close();            
}
catch (Exception e) {
    e.printStackTrace();
}

Keywords: com.microsoft.sqlserver.jdbc.AuthenticationJNI, connecting to SQL Server from Java, eclipse connect to SQL Server, Failed to load the sqljdbc_auth.dll cause :- no sqljdbc_auth in java.library.path, java.sql.SQLException: I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property, jdbc, jtds, SQL Server Windows authentication, executing SQL Server stored procedure from Java, calling SQL Server user-defined-function (UDF) from Java, SQL Server multiple tables ResultSets