Connecting to SQL Server from Java with Windows Authentication

Recently I was asked to troubleshoot an issue related to SQL Server connection string from a Java app which used Windows Authentication.

Since I did not have Java environment setup on my machine, my first task was to setup the environment. So first I installed, JDK (Java Development Kit) and JRE (Java Runtime Environment).

Since I am using vscode, I installed Java pack for vscode using the this link.

I also downloaded Microsoft JDBC driver here. It’s zip file and I extracted that to “Program Files” as stated in the “Install” instructions on the same file inside the zip file.

You need to add the specific JDBC driver to the Java Project library in vscode. Then only Java knows where to find the required libraries. This is stated in the screenshot below:

Below is the Java program I used to test the SQL Server connection using Windows Authentication. Just replace the with the actual one.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ConnectURL {
    public static void main(String[] args) {

        // Create a variable for the connection string.
        String connectionUrl = "jdbc:sqlserver://<SERVERNAME>;databaseName=tempdb;integratedSecurity=true";

        try (Connection con = DriverManager.getConnection(connectionUrl); Statement stmt = con.createStatement();) 
            {
            String SQL = "SELECT TOP 10 * FROM sys.databases";
            ResultSet rs = stmt.executeQuery(SQL);

            // Iterate through the data in the result set and display it.
            while (rs.next()) {
                System.out.println(rs.getString("name") + " " + rs.getString("database_id"));
            }
        }
        // Handle any errors that may have occurred.
        catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

There is Run button on upper-right conner of vscode and click on that. It will compile the program and executes.

At it’s first run, it gave me the error below:

com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication.

If you looked at the error stack, it also mentioned the root cause as below:

Caused by: java.lang.UnsatisfiedLinkError: no mssql-jdbc_auth-9.2.0.x64 in java.library.path

After doing some investigation, I found that, there is a .dll in JDBC driver folder (which we downloaded and extraced previously) which needs to be copied to the JDK bin location. In my case, the DLL was located on C:\Program Files\sqljdbc_9.2\enu\auth\x64

So copied the DLL to C:\Program Files\Java\jdk-15.0.2\bin folder, which is the location of the JDK I installed previously.

After copying the file, I just hit “Run” again. This time it gave me a different error which is stated below: At least, there is some progress. :)

WARNING: ConnectionID:1 ClientConnectionId: fc87a917-78ac-4c10-9c69-1e939a9ad0b4 Prelogin error: host SERVERNAME port 1433 reading prelogin response: A connection attempt failed because the connected party did not properly respond after a period o, or established connection failed because connected host has failed to respond ClientConnectionId:fc87a917-78ac-4c10-9c69-1e90b4
com.microsoft.sqlserver.jdbc.SQLServerException: A connection attempt failed because the connected party did not properly respter a period of time, or established connection failed because connected host has failed to respond ClientConnectionId:fc87a91-4c10-9c69-1e939a9ad0b4

Then I tried to establish a connection from SSMS to the same server using the same Windows account. After a bit of time, it established the connection. FYI, the target server I was testing resides on Azure.

Then, I tried again from vscode and it works this time. I’m still not sure there is connection between SSMS connection which I tried and then trying it on vscode. However, it worked on vscode with no issues afterwards. I tried several times, it worked.

Hope this helps!

About the post header picture: It was taken during the Winter Hike Jan/2021 at Mountain Lion Trail, near Golden, Colorado.