Connect Azure Data Studio to SQL Server using Windows authentication - Kerberos

Connect Azure Data Studio to SQL Server using Windows authentication - Kerberos

2020, Aug 16    

If you want to connect to SQL Server from Mac OS or CentOS, you can either use SQLCMD, the command line tool or Azure Data Studio. The most popular tool, SQL Server Management Studio can only be run in Windows OS.

This article explains on how to connect to SQL Server using Windows authentication from CentOS via Azure Data Studio.

Below is the error message I got initially without setting up anything on my CentOS box. This was when trying to connect to SQL Server instance via Windows Auth.

connection error

Before proceeding further, let me state the environment that I tested this in.

  • OS: CentOS 8
  • DC: DBTORQUE.SU
  • SQL Server Instance: lab-mssql01 hosted in Windows 2019 Server

The CentOS machine is already connected to the domain controller, which is DBTORQUE.SU.

You can follow the steps below:

On SQL Server box

[1]. Find the Kerberos KDC (Key Distribution Center) configuration value. This is on the SQL Server box. (lab-mssql01.dbtorque.su is my SQL Server machine)

nltest /dsgetdc:dbtorque.su

output of nltest

[2]. Copy the DC name (pointed with the red arrow). It is the required KDC configuration value, in this case, ssdbalab-dc-01.dbtorque.su

On CentOS box

[3]. Install krb5-workstation. This was already installed on the CentOS box that I was testing. See below image. krb5-workstation

[4]. Edit /etc/sysconfig/network-scripts/ifcfg-ens192 (interface config. Ens192 is the name in my machine and in your case, it might be a different name). I added below two lines to the above file. DNS1 is the DNS IP address in my lab environment.

PEERDNS=no
DNS1=192.168.0.168

[5]. Restart the network interface. If this command does not work on Mac, there are other ways to do it. Even restarting the Mac will do the trick. Sledge hammer approach :)

sudo nmcli connection down ens192
sudo nmcli connection up ens192

[6]. Edit /etc/krb5.conf file as shown below:

sudo vi /etc/krb5.conf

[libdefaults]
  default_realm = DBTORQUE.SU
 
[realms]
dbtorque.su = {
   kdc = ssdbalab-dc-01.dbtorque.su
}

Note: Domain must be in ALL CAPS in the default_realm. I did not think this seriously initially but later found out, without ALL CAPS it is not working. Also remember the value we copied in step #1, KDC value. We need to enter that value in the same file. See the red arrows below:

krb5-conf

[7]. Get a Ticket Granting Ticket (TGT) from KDC Enter the below command. It should be your Windows domain account name.

sudo kinit susanthab@dbtorque.su

krb5-conf

If you see the above message as KDC reply did not match then we have some issues. Otherwise you should get the prompt like below, which means, it’s a success. One more step to go.

krb5-conf-2

[8]. View the available Ticket Issue the command below:

sudo klist

If the kinit was successful, you should see a ticket like below. klist

[9]. Connecting to SQL Server using Azure Data Studio

ads-conn

ads-conn-2

That is it. I believe these steps will help you to setup Windows Authentication for Azure Data Studio on Linux OS.

About the post header picture: It was taken while hiking Grays peak and Torreys peak mountains on July, 2020. Both peaks are 14ers and you can do both of them in a single day.

Twitter
# tools # linux