Connecting to an on-premise SQL Server via DataGrip on MacOS

While trying to connect to a database at work I encountered a few hurdles. There were a few moving parts that had to come together in order for me to finally run a single query on the database tables.

Those parts were:

  • The database was a SQL Server instance
  • The SQL Server was on-premise
  • Authentication was done via Active Directory using Domain Credentials
  • I’d be connecting to the SQL Server from my laptop running macOS

Once my Active Directory account was granted access to the database I encountered my first roadblock when trying to configure a connection to the database from within my favorite Database IDE, DataGrip.

I’ll start off by saying I had no idea how to approach connecting to the database using DataGrip. You can attribute some of these roadblocks to both how involved creating the connection was along with my limited understanding of how to connect to SQL Servers.

After entering the host, port, user, password, and database with “User & Password” selected under Authentication I was rewarded with this popup.

I thought the issue was that in my haste I selected “User & Password” under the authentication option instead of “Azure Active Directory password.” After switching the authentication option DataGrip prompted me to download a few additional libraries and I hit the “Test Connection” button again.

A new error!

I didn’t understand what the exception message was trying to convey so I clicked DataGrip’s “Search Error” button to take this problem to Google.

Here I found StackOverflow discussions about the DES_EDE_CBC algorithm, sqljdbc jar compatibility matrices, issues with the MS SQL JDBC driver, and one highly voted and accepted StackOverflow answer that mentioned setting a JVM parameter for the TLS Client Protocol. Needless to say, I was lost.

Looking for the simplest and quickest solution to the problem I came across this answer and updated my connection string to include ;encrypt=true;trustServerCertificate=true;.

I clicked “Test Connection” and hoped that this was an easy fix. DataGrip decided to switch things up and a similar popup flashed across my screen from before but this time the reported authentication selection was obviously “Azure Active Directory password.”

In over my head I reached out to the person that worked on my access ticket request and shared my progress so far. While they were happy to help we weren’t able to work out how to set up a connection. A few things we explored were:

  • DataGrip’s LDAP authentication options (I wasn’t sure where to start with exploring this option)
  • Downloading SQL Server Management Studio (not available on the Mac)
  • Remoting into a Windows server with SQL Server running on it (Didn’t have a spare Windows server around to remote into)

I didn’t consider pursuing options 1 or 3 and decided to dig more into option 2. SQL Server Management Studio (SSMS) is not available on the Mac, but Microsoft has another data management tool called Azure Data Studio that is cross-platform. I had nothing to lose and placed my hopes on Azure Data Studio.

I went to work on configuring the connection to the database using Azure Data Studio. The tool authenticated my Azure Active Directory account against my company’s tenant and automagically populated my account information. After I entered the SQL Server name and the database I wanted to connect to I held my breath and clicked the Connect button.

The odds weren’t in my favor and Azure Data Studio reported a Connection Error. At this point, I felt a bit defeated and reached out to a co-worker who had successfully connected to the database.

She was using SSMS and connecting via Windows Authentication on her Windows laptop.

The only option I could think of was to go back to Google and see if there were any other avenues that I could explore.

On the interwebs, I found a helpful article by the DataGrip folks on Connecting to MS SQL server using macOS and Linux. If I had read this first I would’ve saved myself a good amount of time. There were a few things they called out that were helpful under the Connect by using the Windows domain authentication option.

  • Connect using the Microsoft SQL Server (jTds) driver and not the Microsoft SQL Server driver.
  • Use Domain credentials to authenticate
  • Split out the Active Directory username (i.e., DOMAIN\John.Smith) with the domain name going in the Domain field and everything else going under the User field.

I went about following the directions and used the Microsoft SQL Server (jTds) driver. I found that it was out of date so used DataGrip to update it to a stable version.

After everything was set up I clicked on the “Test Connection” button for the third (okay maybe eighth) time and received another error.

I googled the error and landed on this StackOverflow question. What worked for me was an answer by a kind soul who mentioned setting a particular option on the jTds driver.

I entered a new option for USEENTLMV2 and set the value to true and hoped this journey was reaching its conclusion.

I clicked “Test Connection” one final time and saw a green success checkmark signaling that a successful connection had been made.

Lessons Learned

  • RTFM
  • Folks that answer StackOverflow questions are angels

Software Developer and Kim's Convenience Store enthusiast