Kerberos authentication when connecting from Java to MSSQL
I present to the respected community a guide to setting up domain authentication when working from Java with MSSQL.
This guide is written for DBAs and can be used to demonstrate the use of Domain Authentication for Java developers and DevOps.
Faced with one fairly large infrastructure that makes extensive use of Java development, I was surprised that all connections are made with SQL authentication, which is not only not secure, but also inconvenient. The last straw was the proposal to distribute a built-in account to a large number of SQL servers
and create a mechanism for auditing and scheduled password changes. I decided to see if it is possible to work with the built-in windows authentication from Java. My experience as a developer was mainly related to development in C# under windows and, accordingly, Java, especially under linux, was new to me. But this obstacle was also overcome.
So, the conclusion is immediately: Java using the MS JDBC driver with MSSQL can work, the setup is simple, if Windows machines are in a domain, then nothing needs to be done at all, if Windows is not in a domain or it is a Linux machine, then you need to configure DNS and set the parameters in one krb5.conf/ini file.
I will not bore readers with stories about problems and searches for solutions, but I will immediately give an example of a working code, a command to install the necessary packages and a launch command indicating the path to JDBC.
Test stand:
- Domain p4.local, two controllers, with DNS, Win Server 2019. No special Kerberos settings were made;
- SQLCore1 Win Server 2019, machine in the domain, standalone SQL Server 2019 (running under the local SPN service account by default);
- ws1-test Win10 LTSC, not a domain machine;
- CentOSJava1 7.9.2009 (Core) Java 20;
- CentOSJava2 7.9.2009 (Core) Java 11;
- Domain account p4\javaWin with password !Qaz123wsx .
Setting up CentOS
- It is necessary to register domain dns either during installation or in the nmtui interface
- Yum update
- Yum -y install wget (needed to download archives)
- Yum -y install krb5-workstation (allows CentOS to work with Kerberos)
- We work from under the root user, in the home directory.
Installing Java 20 and JDBC 12 on CentOSJava1
Installing Java 11 and JDBC 7 on CentOSJava2
Installing Java on Windows
To run uncompiled code files (.java) on Windows, you need to install the JDK package
https://docs.oracle.com/en/java/javase/20/install/installation-jdk-microsoft-windows-platforms.html
# Configuration snippets may be placed in this directory as well
includedir
/etc/krb5.conf.d/
[logging]
default = FILE:/var/log/krb5libs.log
kdc=FILE:/var/log/krb5kdc.log
admin_server = FILE:/var/log/kadmind.log
[libdefaults]
dns_lookup_realm = false
ticket_lifetime = 24h
renew_lifetime = 7d
forwardable=true
rdns=false
pkinit_anchors =FILE:/etc/pki/tls/certs/ca-bundle.crt
default_realm=
P4.LOCAL
default_ccache_name =
KEYRING:persistent:%{uid}
[realms]
P4.LOCAL = {
kdc=p4.local
admin_server =
p4.local
}
[domain_realm]
.p4.local = P4.LOCAL
p4.local = P4.LOCAL
[libdefaults]
dns_lookup_realm = false
ticket_lifetime = 24h
renew_lifetime = 7d
forwardable=true
rdns=false
default_realm=P4.LOCAL
default_ccache_name =
KEYRING:persistent:%{uid}
[realms]
P4.LOCAL = {
kdc=p4.local
admin_server =
p4.local
}
[domain_realm]
.p4.local = P4.LOCAL
p4.local = P4.LOCAL
The Java code itself which will be checking the connection type to MSSQL by executing the following query:
select @@servername as srv, system_user as usr, auth_scheme from sys.dm_exec_connections where session_id=@@spid
package com.example.kerbaut;
import java.sql.*;
public class KerbautApplication {
public static void main(String[] args) {
String connectionUrl = “jdbc:sqlserver://sqlcore1.p4.local:1433;integratedSecurity=true;authenticationScheme=JavaKerberos;domain=p4.local;userName=javawin;password=!Qaz123wsx;trustServerCertificate=true”;
try (Connection con = DriverManager.getConnection(connectionUrl); Statement stmt = con.createStatement();) {
String SQL = “select @@servername as srv, system_user as usr, auth_scheme from sys.dm_exec_connections where session_id=@@spid”;
ResultSet rs = stmt.executeQuery(SQL);
System.out.println(SQL);
while (rs.next()) {
System.out.println(rs.getString(1 )+ ” ” +rs.getString(2 ) + ” ” + rs.getString(3 ));
}
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
Running for java 20 and 12 JDBC on CentOSJava1
java -cp .:/root/sqljdbc_12.2/enu/mssql-jdbc-12.2.0.jre11.jar KerbautApplication.java
Running for java 11 and 7 JDBC CentOSJava2
java -cp .:/root/sqljdbc_7.4/enu/mssql-jdbc-7.4.1.jre11.jar KerbautApplication.java
Run from under windows
java.exe -cp “C:\java\sqljdbc_12.2.0.0_enu\sqljdbc_12.2\enu\mssql-jdbc-12.2.0.jre11.jar” KerbautApplication.java
I myself am neither a Java developer nor a linux administrator, I would appreciate comments and remarks on the topic.
I hope this article will be useful to the respected community.
