JDBC Integrated Security, NTLM and Kerberos Authentication for SQL Server

With Microsoft SQL Server JDBC driver, you can connect to the database through SQL Server Authentication or Kerberos Authentication. This post summarizes the configurations required for each authentication method with coding examples.

*NTLM block in the following diagram represents pure Java version of authentication without native library.

Prerequisites

• JDK 1.8
• SQL Server instance (local or remote)
• Eclipse/Maven (or other IDEs)

Please install the above software/frameworks accordingly.

SQL Server JDBC Driver

Download SQL Server JDBC driver from the following website:

https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server

For the code examples in the following sections, I’m using version 7.2 (mssql-jdbc-7.2.1.jre8.jar).

You can just create a Maven project in your IDE and then add a package dependency to the JDBC driver:

<dependencies>
         <dependency>
             <groupId>com.microsoft.sqlserver</groupId>
             <artifactId>mssql-jdbc</artifactId>
             <version>7.2.1.jre8</version>
         </dependency>
     </dependencies>

However, the package from Maven central (or other repos) doesn’t include the native binaries for sqljdbc_auth.dll which is required by native authentication. 

In the project, create a class named JDBCToSQLServer with a main function.

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

SQL Server authentication

SQL Server authentication is very simple as you can directly specify the user name and password.

static void SQLAuthentication() {
         try {
             Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            
             String userName = "zeppelin";
             String password = "zeppelin";
             String url = "jdbc:sqlserver://localhost;databaseName=master;";
             Connection con = DriverManager.getConnection(url, userName, password);
             Statement statement = con.createStatement();
             String sql = "select count(*) from sys.all_objects where type='U'";
             statement.execute(sql);
             ResultSet rs = statement.getResultSet();
             if (rs.next()) {
                 int count = rs.getInt(1);
                 System.out.println("Table count:" + count);
             }
             rs.close();
             statement.close();
             con.close();
        } catch (Exception e) {
             e.printStackTrace();
         }
     }

The above code establishes a connection to local SQL Server instance using the following connection string pattern:

jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]

Replace the properties accordingly. The username and password are both zeppelin for this case.

For more details about the connection string, refer to the following page:

https://docs.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url

Add the function call to the main method and you will be able to connect to the server through SQL Server authentication.

Table count:6

Native integrated security authentication

Now, we can change the code slightly to use integrated security.

*Please note native integrated security authentication scheme only works on Windows machines.

static void NativeAuthentication() {
         try {
             Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
             String url = "jdbc:sqlserver://localhost;databaseName=master;integratedSecurity=true;authenticationScheme=nativeAuthentication;";
             Connection con = DriverManager.getConnection(url);
             Statement statement = con.createStatement();
             String sql = "select count(*) from sys.all_objects where type='U'";
             statement.execute(sql);
             ResultSet rs = statement.getResultSet();
             if (rs.next()) {
                 int count = rs.getInt(1);
                 System.out.println("Table count:" + count);
             }
             rs.close();
             statement.close();
             con.close();
        } catch (Exception e) {
             e.printStackTrace();
         }
     }

As shown in the above code, we have changed the connection string slightly to:

jdbc:sqlserver://localhost;databaseName=master;integratedSecurity=true;authenticationScheme=nativeAuthentication;

The connection will use Integrated Security and the authentication scheme is configured to use native authentication. By default, JDBC uses native authentication when integratedSecurity property is enabled.

AUTHENTICATION_SCHEME("authenticationScheme", AuthenticationScheme.nativeAuthentication.toString())

Thus, you can remove the authenticationScheme property from the connection string.

Caused by: java.lang.UnsatisfiedLinkError: no sqljdbc_auth in java.library.path

You may encounter the following errors when running with native authentication:

com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication. ClientConnectionId:
Caused by: java.lang.UnsatisfiedLinkError: no sqljdbc_auth in java.library.path
     at java.lang.ClassLoader.loadLibrary(ClassLoader.java:1867)
     at java.lang.Runtime.loadLibrary0(Runtime.java:870)
     at java.lang.System.loadLibrary(System.java:1122)
     at com.microsoft.sqlserver.jdbc.AuthenticationJNI.<clinit>(AuthenticationJNI.java:52)
     at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:3627)
     ... 13 more

The exception is self-explaining. As this authentication method relies on native library sqljdbc_auth.dll, you need to make sure that:

• The DDL exist either in your Java JDK bin folder
• or  java.library.path VM argument includes the path to the folder of the library.

In my case, I am specifying it through VM arguments:

-Djava.library.path="E:\Downloads\Microsoft JDBC Driver 7.2 for SQL Server\sqljdbc_7.2\enu\auth\x64"


Kerberos integrated security authentication

Kerberos authentication will be slightly more difficult to use as you need to configure first. Your SQL Server instance needs to the in the same domain as your machine. This usually

Refer to my following post to learn how to configure them properly in your environment:

Java Kerberos Authentication Configuration Sample & SQL Server Connection Practice

Once it is configured, you can change the authenticationScheme to JavaKerberos.

static void KerberosAuthentication() {
         try {
             Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
             String url = "jdbc:sqlserver://localhost;databaseName=master;integratedSecurity=true;authenticationScheme=JavaKerberos;";
             Connection con = DriverManager.getConnection(url);
             Statement statement = con.createStatement();
             String sql = "select count(*) from sys.all_objects where type='U'";
             statement.execute(sql);
             ResultSet rs = statement.getResultSet();
             if (rs.next()) {
                 int count = rs.getInt(1);
                 System.out.println("Table count:" + count);
             }
             rs.close();
             statement.close();
             con.close();
        } catch (Exception e) {
             e.printStackTrace();
         }
     }

NTLM authentication

Well, if your machines are not in a domain and you want to connect to your SQL Server database in a Windows machine through Windows Authentication, what should you do? You can use NTLM authentication. Unfortunately this is not directly supported by Microsoft SQL Server JDBC driver but we can use jDTS JDBC driver.

Add the following dependency in your Maven project:

<!-- https://mvnrepository.com/artifact/net.sourceforge.jtds/jtds -->
<dependency>
     <groupId>net.sourceforge.jtds</groupId>
     <artifactId>jtds</artifactId>
     <version>1.3.1</version>
</dependency>

Refer the following page for more details about this driver:

http://jtds.sourceforge.net/faq.html

The code looks like:

static void NTLMAuthentication() {
         try {
             Class.forName("net.sourceforge.jtds.jdbc.Driver");
             String windowsUsername="user";
             String windowsUserPwd="password";
             String url = "jdbc:jtds:sqlserver://192.168.1.106/master;domain=WORKGROUP;useNTLMv2=true";
             Connection con = DriverManager.getConnection(url, windowsUsername, windowsUserPwd);
             Statement statement = con.createStatement();
             String sql = "select count(*) from sys.all_objects where type='U'";
             statement.execute(sql);
             ResultSet rs = statement.getResultSet();
             if (rs.next()) {
                 int count = rs.getInt(1);
                 System.out.println("Table count:" + count);
             }
             rs.close();
             statement.close();
             con.close();
        } catch (Exception e) {
             e.printStackTrace();
         }
     }

There are several places changed:

• Windows user name and password are provided.
• jDTS driver connection string is used.

Remember to replace user name and password and IP address accordingly.

domain=WORKGROUP is important as it tells jDTS to use Windows Authentication. However the value can be anything since your machine is not in any domain.

Error: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication

If you encounter this error, please make sure your windows account has access to the SQL Server instance.