JDBC Integrated Security, NTLM and Kerberos Authentication for SQL Server

access_time 2 years ago visibility3858 comment 0

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.

image

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"

image

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.

info Last modified by Raymond at 8 months ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Want to publish your article on Kontext?

Learn more

Kontext Column

Created for everyone to publish data, programming and cloud related articles.
Follow three steps to create your columns.


Learn more arrow_forward

More from Kontext

local_offer SQL Server local_offer python local_offer spark local_offer pyspark local_offer spark-database-connect

visibility 20586
thumb_up 4
access_time 2 years ago

Spark is an analytics engine for big data processing. There are various ways to connect to a database in Spark. This page summarizes some of common approaches to connect to SQL Server using Python as programming language. For each method, both Windows Authentication and SQL Server ...

local_offer SQL Server

visibility 897
thumb_up 0
access_time 11 years ago

我们都知道在一个表中当需要2列以上才能确定记录的唯一性的时候,就需要用到联合主键,当建立联合主键以后,在查询数据的时候性能就会有很大的提升,不过并不是对联合主键的任何列单独查询的时候性能都会提升,但我们依然可以通过对联合主键中的首列除外的其他列建立非聚集索引来提高性能。关于索引,可以参考我的这篇文章:http://hi.baidu.com/1987raymond/blog/item/a0f7b11062e1330a213f2e26.html

local_offer SQL Server

visibility 378
thumb_up 0
access_time 12 years ago

个人感觉这个存储过程写得比较的好:转载自:http://www.cnblogs.com/eme/archive/2006/12/19/597275.html

About column