How To Connect to SQL Server On-Premise/Virtual Machine from Linux using Kerberos Authentication

by | Mar 26, 2021 | Technology | 0 comments

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

 


Hi! 


It is known that SQL Server can be accessed with many different authentication methods. We often recommend the use of Integrated Security using Kerberos mainly because it allows delegated authentication, besides being an efficient method compared to others such as NTLM, for example.


 


On today’s post we will be following the steps to help you configuring Kerberos on Linux and test it by connecting to a SQL Server instance found On-Premise/Virtual Machine .


 


The steps will be covered in the following sections:


 



  1.  Joining the Linux Server to the Windows Domain

  2. Setting Kerberos on Linux

  3. Testing the connection

  4. Troubleshooting


 


 


1 – Joining the Server to the Windows Domain:


 


To join Linux server into a Windows Domain it is needed to change the network device to look for the right DNS entries.


This can be done by editing the files /etc/network/interfaces on Ubuntu versions < 18 or /etc/netplan/******.yaml on newer Ubuntu


 


Ubuntu 16: Join SQL Server on Linux to Active Directory – SQL Server | Microsoft Docs


Ubuntu 18: Join SQL Server on Linux to Active Directory – SQL Server | Microsoft Docs


Red Hat 7.x: Join SQL Server on Linux to Active Directory – SQL Server | Microsoft Docs


Suse 12: Join SQL Server on Linux to Active Directory – SQL Server | Microsoft Docs


 


The final files may vary depending on the distro. As an example, this is the outcome for Ubuntu 18 (available on Azure):


 


Test environment:


Domain Name: BORBA.LOCAL


Domain Controller (BORBADC.borba.local) IP: 10.0.1.4


 


File /etc/netplan/50-cloud-init.yaml :


# This file is generated from information provided by the datasource.  Changes
# to it will not persist across an instance reboot.  To disable cloud-init’s
# network configuration capabilities, write a file
# /etc/cloud/cloud.cfg.d/99-disable-network-config.cfg with the following:
# network: {config: disabled}
network:
    ethernets:
        eth0:
            dhcp4: true
            dhcp4-overrides:
                route-metric: 100
            dhcp6: false
            match:
                driver: hv_netvsc
                macaddress: 00:0d:3a:da:4d:53
            set-name: eth0
            nameservers:
                addresses: [10.0.1.4]
    version: 2

 


As soon as you change the yaml file under /etc/netplan you will need to apply the same by running the following command-line:


netplan apply

 


You will also need to add your DNS to /etc/resolv.conf:


File /etc/resolv.conf:


# This file is managed by man:systemd-resolved(8). Do not edit.
#
# This is a dynamic resolv.conf file for connecting local clients to the
# internal DNS stub resolver of systemd-resolved. This file lists all
# configured search domains.
#
# Run “systemd-resolve –status” to see details about the uplink DNS servers
# currently in use.
#
# Third party programs must not access this file directly, but only through the
# symlink at /etc/resolv.conf. To manage man:resolv.conf(5) in a different way,
# replace this symlink by a static file or a different symlink.
#
# See man:systemd-resolved.service(8) for details about the supported modes of
# operation for /etc/resolv.conf.
nameserver 10.0.1.4
options edns0
search borba.local

 


Now that your DNS settings are properly set, you can test it by running a nslookup with the domain controller as parameter. On my environment the domain controller is called “BORBADC”. Check the output from nslookup command below:


 


Testing name resolution with the command “nslookup borbadc.borba.local”:


dineu@LinuxDev:~$ nslookup borbadc
Server:         10.0.1.4
Address:        10.0.1.4#53

Name:   borbadc.borba.local
Address: 10.0.1.4

 


You’ve just finished the first part :grinning_face:


As we could see, nslookup is correctly resolving the names for the realm BORBA.LOCAL.


 


 


2- Setting Kerberos on Linux:


 


Now that your Linux server is joined to the Windows Active Directory, we will now continue configuring settings for Kerberos.


The process goes through two simple steps:


 



  • Installing the required libraries

  • Adapting Kerberos Configuration file.


 


2.1) Installing the required libraries:


As usual, the command lines may vary depending on what is you Linux distro. I will be sharing two solutions that can be used if your Linux is Ubuntu or Red Hat.


 


Ubuntu:


sudo apt-get install realmd krb5-user

RedHat:


sudo yum install realmd krb5-workstation

Once the libraries are installed, you can move on to the next step.


 


2.2) Adapting Kerberos Configuration File


The whole settings for Kerberos are defined in one single file called “krb5.conf”.


This is found under /etc/krb5.conf and you will need to adapt the following sections:



  • [libdefaults]

  • [realms]

  • [domain_realm]


As an example, please check the final version of my krb5.conf used to configure the settings in the same Test Environment:


[libdefaults]
        default_realm = BORBA.LOCAL
        dns_lookup_kdc = true
        dns_lookup_realm = true
# The following krb5.conf variables are only for MIT Kerberos.
        kdc_timesync = 1
        ccache_type = 4
        forwardable = true
        proxiable = true

[realms]
        BORBA.LOCAL = {
                kdc = BORBADC.BORBA.LOCAL
                admin_server = BORBADC.BORBA.LOCAL
        }

[domain_realm]
        .borba.local = BORBA.LOCAL

 


 


 


3- Testing the connection


 


Ok, so you have the server joined to the domain and also finished configuring the Krb5.conf. 


As you know, Kerberos relies on a ticket-granting service and you will need to have this first ticket (TGT) if you want to connect to any service using your credentials.


 


This process responsible for asking and receiving the TGT is called “kinit”.


Once you get the TGT, other processes (such as your application that connects to SQL Server) will be able to ask another ticket called Ticket-Granting-Service (TGS).


 


1.1) Get Ticket-Granting Ticket


 


To test the connection on Linux you will first need to get a TGT (Ticket-Granting-Ticket). This is done throgh the command kinit as follow:


 


kinit username@DOMAIN.COMPANY.COM

*** Please notice that realm is in capital letters


 


In our example, this was the output:


dineu@LinuxDev:~$ kinit dineu@BORBA.LOCAL
Password for dineu@BORBA.LOCAL:
dineu@LinuxDev:~$

You can see the ticket received by running the command “klist” (see the krbtgt in the output):


aa
dineu@LinuxDev:~/JavaTest$ klist
Ticket cache: FILE:/tmp/krb5cc_1000
Default principal: dineu@BORBA.LOCAL
Valid starting Expires Service principal
03/26/21 13:02:41 03/26/21 23:02:41 krbtgt/BORBA.LOCAL@BORBA.LOCAL
renew until 03/27/21 13:02:38

 


1.2) Connect to SQL Server


Brillant! 


I see you already have a TGT from the output of your klist. Now it is time to test our connection to SQL Server.


 


1.2.1) Testing the connection using sqlcmd:


To test the connection using SQLCMD you will first need to install it:



Once the tool is installed, you can can connect to one of your SQL Instances as below:


aa
dineu@LinuxDev:~$ sqlcmd -S MySQLInstance.borba.local -E
1> SELECT auth_scheme from sys.dm_exec_connections where session_id = @@SPID;
2> GO
auth_scheme
—————————————-
KERBEROS
(1 rows affected)

 


1.2.2) Testing the connection using Microsoft JDBC (MS-JDBC):


 


To test the connection using Microsoft JDBC please ensure you have the latest JRE and JDK properly installed on your server



  • Please check the steps 1.2 and 1.3 of the following article:


https://sqlchoice.azurewebsites.net/en-us/sql-server/developer-get-started/java/ubuntu/


 


1.2.2.1) Create / Compile you Java file to test the connection:


 


Connection String used as an example:


jdbc:sqlserver://MySQLInstance.borba.local:1433;databaseName=master;integratedSecurity=true;authenticationScheme=JavaKerberos

And this is our sample script (found as a sample code when you install MS-JDBC) adapted with the connection string that will ensure Kerberos is used:


 


File ConnectUrl.java:


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ConnectURL {
    public static void main(String[] args) {
        // Create a variable for the connection string.
        String connectionUrl = “jdbc:sqlserver://MySqlInstance.borba.local:1433;databaseName=master;integratedSecurity=true;authenticationScheme=JavaKerberos”;

        try (Connection con = DriverManager.getConnection(connectionUrl); Statement stmt = con.createStatement();) {
            String SQL = “SELECT auth_sceme from sys.dm_exec_connections where session_id = @@SPID”;
            ResultSet rs = stmt.executeQuery(SQL);

            // Iterate through the data in the result set and display it.
            while (rs.next()) {
                System.out.println(rs.getString(“auth_scheme”));
            }
        }
        // Handle any errors that may have occurred.
        catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

 


Compile the script by running the command below:


javac ConnectURL.java

 


Finally, you can now call java to execute the script (don’t forget to inform the path for the MSJDBC jar files):


java -cp .:/home/dineu/JavaTest/mssql-jdbc-8.4.0.jre8.jar ConnectURL


Output: If the settings were properly configured, you will find the following output:


KERBEROS

 


…and this is Done! If you found Kerberos as the output of the execution, you’ve finished to configure Kerberosn on your Linux Environment.


 


 


4 – Troubleshooting


Okay, we know sometimes things may behave in an unexpected way. I would like to share here some error messages that you may find and also some suggestions on how to collect more information if you get stuck anywhere while configuring all these settings.



 


4.1) Error Messages


Please find below some error messages that you may find along this journey:


 


Error #1) kinit: Cannot find KDC for realm “borba.local” while getting initial credentials


– Check if the name of your realm is being correctly resolved:


– You can find the IP Address of your DNS under /etc/resolv.conf


– You can also test it by running the nslookup: nslookup YourDomain or nslookup YourDomainController


 


Error #2) kinit: KDC reply did not match expectations while getting initial credentials


This message says the kinit failed to get the TGT. Possible reasons are:


– Realm was not in capital letters. Example: (wrong) kinit myuser@domain.com versus (right) kinit myuser@DOMAIN.COM.


– Krb5.conf was not properly configured (please review the sections [libdefaults], [realms] and [domain_realm] under /etc/krb5.conf


 


Error #3) Cannot generate SSPI context.


Usually the error message “Cannot Generate SSPI Context” comes with some other error messages, just like below:


 


Testing from SQLCMD:


dineu@LinuxDev:~/JavaTest$ sqlcmd -S sqlao1 -E
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : SSPI Provider: Server not found in Kerberos database.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Cannot generate SSPI context.

 


Testing from MS-JDBC:


aa
com.microsoft.sqlserver.jdbc.SQLServerException: Integrated authentication failed. ClientConnectionId:….
Caused by: GSSException: No valid credentials provided (Mechanism level: Server not found in Kerberos database (7)
Caused by: KrbException: Server not found in Kerberos database (7)
Caused by: KrbException: Identifier doesn’t match expected value (906)

In this case, it is clearly saying that the SPN was not found.


Therefore you can check if the SPNs for SQL Service are properly registered to the SQL Service Account.


More info: Register a Service Principal Name for Kerberos Connections 


 


4.2) Logs


If you find an unexpected error, the following logs can help to understand what could be causing the problem.


 


4.2.1) Provider Logs:


You can always collect provider logs from ODBC or MSJDBC.



 


4.2.2) Network Traces:


You may consider to use a network capture tool (such as tcpdump) on Linux and then filter by the Kerberos messages.


The following command line will start a circular capture in the server. It will create up to 10 files, each one containing the maximum size of 2Gb


sudo tcpdump -i any -w /var/tmp/trace -W 10 -C 2000 -K -n

Once you start capturing the traces, reproduce the issue and then press Ctrl+C to stop capturing them.


The files would be stored in the same folder used in the command line (in this case, /var/tmp).


 


 


…and that’s all folks! 


I would love to hear your feedback..!


Did you find the steps described in this article helpful?


What else would you like to have added to this post? 



We hope you have enjoyed reading about this content.


See you in the next post!

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

Submit a Comment