This article is contributed. See the original author and article here.

How to configure windows authentication for Microsoft JDBC in Linux. (We use Squirrel to do this test)

 

  1. Install necessary packages

sudo yum install realmd krb5-workstation

sudo yum install sssd-tools

sudo yum install samba-common-tools

 

  1. Edit below network adapter file

sudo vi /etc/sysconfig/network-scripts/ifcfg-ens33

 

Add below information into this file

PEERDNS=no

DNS1=**<AD domain controller IP address>**

 

  1. Restart Network Service

sudo systemctl restart network

 

  1. Add below information into  /etc/resolv.conf  file

sudo vi /etc/resolv.conf

 

search MYTEST.COM

nameserver **<AD domain controller IP address>**

 

  1. Modify /etc/hosts  file, add domain controller records

sudo vi /etc/hosts

192.168.232.128  Win2012AD

 

  1. Specify hostname in case the server hasn’t

sudo vi /etc/hostname

 

  1. Join Linux server into windows domain. Please replace yellow highlighted domain name and domain account. This account should have permission to add computer into domain and also have permission to login SQL SERVER.

sudo realm join MYTEST.COM -U ‘bobcai@MYTEST.COM’ -v

 

  1. When you have successfully joined windows domain, please modify  /etc/krb5.conf  file

sudo vi /etc/krb5.conf

 

The following yellow highlight parts should be changed to your own domain name. Domain name should be “uppercase”

 

# 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]

default_tkt_enctypes = aes256-cts

default_tgs_enctypes = aes256-cts

permitted_enctypes = aes256-cts

dns_lookup_realm = false

dns_lookup_kdc = yes

ticket_lifetime = 24h

renew_lifetime = 7d

forwardable = true

rdns = false

pkinit_anchors = /etc/pki/tls/certs/ca-bundle.crt

default_realm = MYTEST.COM

default_ccache_name = KEYRING:persistent:%{uid}

 

default_realm = MYTEST.COM

[realms]

# EXAMPLE.COM = {

#  kdc = kerberos.example.com

#  admin_server = kerberos.example.com

# }

 

MYTEST.COM = {

   kdc = Win2012AD.MYTEST.COM:88

   admin_server = Win2012AD.MYTEST.COM:749

   default_domain = MYTEST.COM

}

 

[domain_realm]

# .example.com = EXAMPLE.COM

 

  1. Use  Kinit  to get Kerberos TGT.

I will use “bobcai” ticket to login SQL Server. So we need to run “kinit bobcai” to get bobcai’s TGT package.

 

[bobcai@centos7 Packages]$ kinit bobcai

Password for bobcai@MYTEST.COM:

 

  1. Use  Klist  to verify bobcai’s Kerberos ticket

 

[bobcai@centos7 Packages]$ klist

Ticket cache: KEYRING:persistent:1000:1000

Default principal: bobcai@MYTEST.COM

 

Valid starting       Expires              Service principal

03/03/2019 18:55:06  03/04/2019 04:55:06  krbtgt/MYTEST.COM@MYTEST.COM

      renew until 03/10/2019 18:55:01

[bobcai@centos7 Packages]$

 

 

  1. Install Java using below commands

 

[bobcai@JDBCTEST ~]$ sudo su

[sudo] password for bobcai:

[root@JDBCTEST bobcai]# yum install java

 

 

  1. Download Squirrel SQL client. We use this tool to verify JDBC connectivity/authentication issue.

 

http://www.squirrelsql.org/#installation

 

  1. Install Squirrel. Download squirrel-sql-3.9.1-standard.jar from above official web site. Install it using below command

 

[bobcai@JDBCTEST ~]$ java -jar squirrel-sql-3.9.1-standard.jar

Bob_Cai_0-1594427454358.jpeg

 

 

Bob_Cai_1-1594427454365.jpeg

 

 

  1. When Squirrel is installed, Please download Microsoft JDBC. I am using Microsoft JDBC 6.0 to do this test.

    https://www.microsoft.com/en-sg/download/details.aspx?id=11774

 

  1. Unzip  sqljdbc_6.0.8112.200_enu.tar.gz   which has been downloaded from above link. You will find ‘sqljdbc_6.0’ folder was created under the same path.

 

[bobcai@JDBCTEST ~]$ tar -zxf sqljdbc_6.0.8112.200_enu.tar.gz

 

  1. Double click Squirrel icon on Desktop

Bob_Cai_2-1594427454369.jpeg

 

 

  1. Add SQLJDBC driver into Squirrel

 

Bob_Cai_3-1594427454383.jpeg

 

 

Load sqljdbc42.jar in jre8 folder.

 

Bob_Cai_4-1594427454386.jpeg

 

 

Bob_Cai_5-1594427454395.jpeg

 

 

  1. Add environment variable. ‘krb5cc_bobcai’ is Kerberos ticket cache file.

 

[bobcai@JDBCTEST ~]$ sudo vi /etc/profile

export KRB5CCNAME=/home/bobcai/krb5cc_bobcai

export JAVA_HOME=/usr/lib/jvm/jre-1.8.0

19. Create SQLJDBCDriver.conf file inside squirrel-sql-3.9.1 folder. Put below information into SQLJDBCDriver.conf file

 

SQLJDBCDriver {

   com.sun.security.auth.module.Krb5LoginModule required useTicketCache=true;

};

 

 

  1. Go into squirrel-sql-3.9.1 folder, modify squirrel-sql.bat file.

[bobcai@JDBCTEST squirrel-sql-3.9.1]$ vi squirrel-sql.bat

Add below parameters

-Djava.security.auth.login.config=SQLJDBCDriver.conf -Djava.security.krb5.conf=/etc/krb5.conf

 

Bob_Cai_6-1594427454403.jpeg

 

 

  1. Then test windows authentication in Squirrel in Linux. Connection successful !

Bob_Cai_7-1594427454435.png

 

 

 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.