From MacOS to MS SQL Server with Windows Authentication

In a recent scenario, I found myself assisting a friend who had encountered a connectivity challenge with an old version of MS SQL database. The unique aspect of this situation was the authentication method employed by the database, which relied on Windows Authentication as the default protocol. This became a particularly intricate issue for my friend, who, as a new employee, found himself amidst a predominantly Windows-oriented environment within the company. Complicating matters further, my friend, a dedicated Mac user, faced uncertainty in identifying the appropriate person within the organization to seek assistance from. In light of these circumstances, I stepped in to provide guidance and support, ensuring a successful connection to the MS SQL database on his Mac system.

Situation

The company my friend works for has its own VPN, which he had already installed on his Mac. The company policy doesn't allow employees to connect to the database by using a username and password, but only by using Windows Authentication.

He had also installed the Azure Data Studio, because there is no SQL Server Management Studio (SSMS) for Mac. However, the Azure Data Studio, with Windows Authentication, keeps asking for kinit (Kerberos) credentials, which he doesn't have. And yes, running kinit doesn't help because it is not yet right configured on his Mac.

Trying with DBeaver, it also doesn't work. While other Windows users can use the mssql-jdbc_auth library to connect to the database, the DLLs are not for MacOS.

Solution

Actually, the solution is quite simple, but it took me a while to figure it out. The right authentication method is to use the Integrated Windows Auth (NTLM).

I couldn't find out how to use the Integrated Windows Auth (NTLM) with Azure Data Studio, but I found out how to use it with DBeaver and IDE like PyCharm.

DBeaver

Using the NTLM and the JDBC driver from Microsoft, we can connect to the database with our domain username and password.

PyCharm

To setup the IntelliJ IDE like PyCharm to connect to the database, we need to do the following:

1. Using jtds Driver that does support Windows Authentication
2. Using Domain Authentication authentication method
3. Visiting the Advance tab and enable the USENTLMV2 attribute.

Ref. https://www.jetbrains.com/help/pycharm/db-tutorial-connecting-to-ms-sql-server.html#connect-by-using-windows-domain-authentication-macos-and-linux

Kerberos and NTLM

Firstly, here is a good article about Kerberos and NTLM:

Both Kerberos and NTLM are important authentication protocols used in Windows environments. However, Kerberos is more secure, scalable, and compatible with modern systems, while NTLM is more straightforward to configure and manage and works well with older systems. https://www.geeksforgeeks.org/difference-between-kerberos-and-ntlm/

It means that in another scenario, we may encounter the opposite situation, where the database supports Kerberos but not NTLM. In that case, we need to use Kerberos to connect to the database.

Kerberos

There is a quite good article about using Kerberos on MacOS to connect to MS SQL Server with Windows Authentication in Azure Data Studio below.

• https://medium.com/@chriskuchar/connecting-to-ms-sql-server-from-a-mac-with-windows-authentication-44375ec1953e

The only thing I want to add is where we can find the domain name and KDC server to fill in the krb5.conf file.

# File /etc/krb5.conf 

[libdefaults]
default_realm = DOMAIN_IN_CAPPITAL_LETTERS
 
[realms]
DOMAIN_IN_CAPPITAL_LETTERS = {
kdc = kdc.address.in.domain
}

First, the simplest way is to use a Windows machine, that already connected to VPNs if your organization has one, to run the cmd command below to get the domain name and KDC server.

# I'm not sure if `dig` can list all the domain names and KDC servers, but `nslookup` can.
# If you happen to know how to do it with `dig`, please let me know.
# At least the command `scutil --dns` in Mac will return the Domain Name Servers (DNS) of the VPN.
nslookup -type=any _kerberos._tcp 

A list of domain names and KDC servers will be returned, that we can use any of them to fill in the krb5.conf file.

# File /etc/krb5.conf 

[libdefaults]
default_realm = DOMAIN.COM
 
[realms]
DOMAIN.COM = {
kdc = kdc-1.domain.com
}

Now, after the krb5.conf file is configured, we can get a valid Kerberos ticket by running the command below.

kinit USERNAME@DEFAULT_REALM
# and enter your password when prompted

After it is done without any error, we can see our ticket by running the command below.

klist

With a valid Kerberos ticket, we can connect to the database that supports Kerberos with Azure Data Studio or any client.

References

• https://stackoverflow.com/questions/8616818/integrated-windows-auth-ntlm-on-a-mac-using-google-chrome-or-safari
• https://learn.rdr-it.io/Le%C3%A7on/authentification-ntlm-et-kerberos/