Jdbc mssql windows authentication

Let’s see more on how to connect SQL Server-JDBC Driver using Integrated Authentication in this article. At Bobcares, with our Server Management Services, we can handle your SQL Server issues.

SQL Server-JDBC Driver Integrated Authentication

Microsoft provides a Java Database Connectivity (JDBC) driver for SQL Server. The driver is free and offers Java database connectivity from any Java program, application server, or Java-enabled applet. The Microsoft JDBC Driver for SQL Server has been validated against popular application servers like IBM WebSphere and SAP NetWeaver.

Creation of the connection URL

The connection URL’s general form is as follows:

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

The components on the URL are as follows:

• jdbc:sqlserver://: The subprotocol. It is constant.
• serverName: It is the server address to which we want to connect. This address can be a DNS or IP address, or it can be localhost or 127.0.0.1 for the local machine. If it is not in the connection URL, we must specify the server name in the properties collection.
• instanceName: It is the instance to which we should connect on serverName. If we don’t supply an instance, then it establishes a connection to the default instance.
• portNumber: the port to use while connecting to serverName. The default value is 1433. If we use the default, we do not need to include the port or the preceding ‘:’ in the URL.
• property: It involves one or more option connection properties.

Using a username and password, access the local computer’s default database. An example of the connection URL is as below:

jdbc:sqlserver://localhost;user=MyUserName;password=*****;

How to connect SQL Server-JDBC Driver using Integrated Authentication?

The connection string that follows demonstrates how to connect to a SQL Server database from an application running on any supported operating system by the Microsoft JDBC Driver for SQL Server utilizing integrated authentication and Kerberos:

jdbc:sqlserver://;servername=server_name;integratedSecurity=true;authenticationScheme=JavaKerberos

Use integrated authentication to establish a connection to the local computer’s default database:

jdbc:sqlserver://localhost;integratedSecurity=true;
Establish a connection to a named database on a remote server:
jdbc:sqlserver://localhost;databaseName=AdventureWorks;integratedSecurity=true;
Connect to the remote server using the default port:
jdbc:sqlserver://localhost:1433;databaseName=AdventureWorks;integratedSecurity=true;
Choose a unique application name to connect with:
jdbc:sqlserver://localhost;databaseName=AdventureWorks;integratedSecurity=true;applicationName=MyApp;
We can install multiple database instances on one SQL Server server. There is a unique name assigned to each instance. We can either specify the port number of the identified instance of SQL Server to connect to it or we can specify the instance name as a JDBC URL property or a datasource property. We can use the following format to indicate a port number:
jdbc:sqlserver://localhost:1433;integratedSecurity=true;<more properties as required>;
Use this format to use a JDBC URL property:
jdbc:sqlserver://localhost;instanceName=instance1;integratedSecurity=true;<more properties as required>;
How to connect SQL Server-JDBC driver on Windows using Integrated Authentication?
Using the integratedSecurity connection string property, the JDBC driver enables Type 2 integrated authentication on Windows-based operating systems. Copy the mssql-jdbc_auth–.dll file to a directory on the Windows system path on the computer where the JDBC driver is installed in order to use integrated authentication. The mssql-jdbc_auth–.dll files will install on the following location:
<installation directory>\sqljdbc_<version>\<language>\auth\
[Looking for a solution to another query? We are just a click away.]
Conclusion
In this article, we have provided the steps from our Tech team to connect SQL Server-JDBC Driver using Integrated Authentication. We also included the steps to connect SQL Server-JDBC driver on Windows using Integrated Authentication Windows.

PREVENT YOUR SERVER FROM CRASHING!
Never again lose customers to poor server speed! Let us help you.
Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.
GET STARTED

Goal

This document describes how to use Windows Authentication to access SQL Server using a JDBC data source.

These steps are only needed when we do not want to use a login and a password in our SQL Server JDBC data source and we want to obtain those credentials from the operating system. When using this approach, the credentials from the user that started the Denodo VDP service will be the ones used to access the SQL Server data source.

The configuration with the Microsoft  and jTDS drivers will be explained.

NOTE: The jTDS driver is no longer maintained and its use should be avoided.

Content

Using the Microsoft JDBC driver

MS JDBC Drivers

There are a different methods to authenticate into SQLServer using Windows Credentials:

• Reading OS Credentials without the need of adding login/password in Denodo’s JDBC Data Source dialog. A dll is needed and the steps can be found below

• Using Windows credentials (writing the credentials in the data source dialog):

• Drivers < 10.x Not supported
• Version 10.x.

a.The url needs to include the domain and integratedSecurity parameters. E.g. :

    jdbc:sqlserver://localhost:1433;database=denodo_db;

integratedSecurity=true;domain=TEST

b. In the Design Studio navigate to the Advanced Tab > Driver properties of the data source use authenticationScheme=ntlm like below

Картинка с сайта: community.denodo.com

c.In the login password, use your Windows credentials without the domain

Картинка с сайта: community.denodo.com

Steps using dll

To access MS SQL Server from VDP using the Microsoft driver and Windows Authentication:

1. Download the driver from Microsoft JDBC Driver for SQL Server.

1. Make sure that you are downloading the driver that matches the driver used by Denodo in the data source, this depends on the database adapter and classpath selected.

For instance, if the classpath in the data source is mssql-jdbc-10.x go to <DENODO_HOME>/lib/extensions/jdbc-drivers/mssql-jdbc-10.x, check the driver version (i.e. mssql-jdbc-10.2.0.jre8.jar) and make sure you download the same version from the Microsoft download site.

1. Unzip the distribution file and copy sqljdbc_xx./enu/auth/x86|x64/msssqljdbc_auth-xx.x.x.x64.dll under <DENODO_HOME>/extensions/thirdparty/dll.

1. Database URI:

jdbc:sqlserver://<host_name>:<port>;IntegratedSecurity=true;DatabaseName=database

    Note: If the SQL server is not SSL enabled,then select the latest driver version for            the SQL Server datasource and add encrypt=false in the Database URI if you are using MS Driver for the Test connection.

1. No login or password is needed. Virtual DataPort will use credentials of the user that started the VDP server.

1. As the Microsoft driver is provided in Denodo 7 and newer versions, you could skip step 2 while connecting MS SQL Server in recent Denodo versions.

Картинка с сайта: community.denodo.com

When using the Microsoft Diver, the URI parameter IntegratedSecurity=true indicates that Windows credentials will be used by SQL Server to authenticate the user of the application.

If «true» the JDBC driver searches the local computer credential cache for credentials that have already been provided at the computer or network logon. If «false» the username and password must be supplied.

This means that the credentials used to authenticate to SQL Server will be the credentials used to log in to the machine where the VDP Server is installed or the credentials used to run the VDP Server as a Windows Service.

Using the jTDS JDBC driver

NOTE: The jTDS driver is no longer maintained and its use should be avoided.

Before creating a data source to a MS SQL Server database from Virtual DataPort using the jTDS driver and Windows Authentication follow these steps:

1. Download the latest jTDS driver version from JTDS Document.
2. Extract from the distribution jtds-xxx-dist.zip the file ntlmauth.dll. Extract the file that matches the target architecture. For instance, for a 64-bit operating system, extract the file under x64/SSO.
3. Copy the file under <DENODO_HOME>/extensions/thirdparty/dll.

Note: Please restart the Virtual DataPort server for the changes to take effect.

1. Create a new JDBC data source in the Virtual DataPort Administration Tool or the Web Design Studio.
2. Select DB Adapter: MS SQL Server 2000/2005/2008/2008R2/2012 (JTDS).
3. Database URI:

jdbc:jtds:sqlserver://host:port/database;useNTLMv2=true;domain=domain

1. No login or password is needed. Virtual DataPort will use the credentials of the user that started the VDP server.

Картинка с сайта: community.denodo.com

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.

Картинка с сайта: kontext.tech

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"


Картинка с сайта: kontext.tech

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.

JDBC TUTORIAL

Updated: 06 Aug 2024

Introduction

Choosing a Windows Authentication Mechanism

Configuring for Pure Java Windows Authentication

Configuring for Windows-Specific Authentication

Using Windows Authentication with a Security Manager

Future Enhancements and Conclusion

Steps:-

1. We will need to first obtain the «mssql-jdbc_auth-9.4.0.x64.dll» file needed for integrated authentication.
   Since Joget DX 7.0.24, MSSQL JDBC driver version 9.4.0.jre8 is shipped by default, therefore it is advisable to use the dll file of the same version.
   Download from the direct link https://github.com/microsoft/mssql-jdbc/releases/download/v9.4.0/mssql-jdbc_auth.zip
2. Unzip, and find «x64\mssql-jdbc_auth-9.4.0.x64.dll». Place it into a location accessible by Joget, i.e. «C:\Joget-DX8-Enterprise\wflow\library»

3. Edit «joget-start.bat» and append «-Djava.library.path=./wflow/library» into JAVA_OPTS.

   Your joget-start.bat may look like this now. (Line 12)
   Please take note that line «-javaagent:./wflow/glowroot/glowroot.jar» must be placed at the end too.

   @ECHO OFF
   
   REM Start MariaDB
   ECHO == Starting MariaDB ==
   ECHO.
   CALL .\mariadb-start.bat
   ECHO.
   
   REM Start Tomcat
   set JAVA_HOME=.\jre11.0.2
   set CATALINA_HOME=.\apache-tomcat-8.5.65
   set JAVA_OPTS=-Xmx768M -Dwflow.home=./wflow/ -javaagent:./wflow/aspectjweaver-1.8.5.jar -Djava.library.path=./wflow/library -javaagent:./wflow/glowroot/glowroot.jar
   REM set JAVA_OPTS=-XX:MaxPermSize=128m -Xmx1024M -Xdebug -Xnoagent -Djava.compiler=NONE -Xrunjdwp:transport=dt_socket,suspend=n,server=y,address=5115 -Dwflow.home=./wflow/ -javaagent:./wflow/aspectjweaver-1.8.5.jar -javaagent:./wflow/glowroot/glowroot.jar
   ECHO == Starting Tomcat from %CATALINA_HOME% ==
   ECHO.
   %CATALINA_HOME%\bin\catalina.bat run

   Note: If your Joget server runs as Windows service, then you will need to launch tomcat8w.exe to configure JAVA_OPTS variable accordingly.

4. Initialize your mssql database instance with Joget’s base tables.
   Navigate and import the file in the specified directory below into your database, using your database client of choice: 

   <Your Joget folder>\apache-tomcat-<version number>\webapps\jw\WEB-INF\classes\setup\sql\jwdb-mssql.sql

5. Create a new datasource profile file inside the «wflow» folder.
   Here’s sample content.

   workflowDriver=com.microsoft.sqlserver.jdbc.SQLServerDriver
   workflowUrl=jdbc\:sqlserver\://localhost\:1433;SelectMethod\=direct;DatabaseName\=jwdb;integratedSecurity\=true
   workflowPassword=
   workflowUser=sa

   Note: The value in «workflowUser» is not being used but it is a required field. Simply key in any value here.

6. Change your datasource to point to your new profile. 
   inside the same folder, open «app_datasource.properties» and edit the line «currentProfile=default» to «currentProfile=mssql» or «currentProfile=<name>» where <name> is the name you used for your newly created datasource profile above.
7. Start Joget server and test.