Connecting JDBC to MS Azure SQL through Microsoft Entra and Active Directory Password Authentication

Not sure there are many that connect to MS SQL to execute queries, stored procedures, etc, but our Healthsystem has many different MS SQL based databases we use within the Interoperability environment for various reasons.

With the push to moving from on-prem to the Cloud we ran into some difficulties with our SQL Gateway connections and knowing how to config them to use Microsoft Entra for Active Directory Authentication.

There are many articles out on the web, but there was not one that could give us the full answer to what we needed to do, and Microsoft wasn’t much help.
I figured I would write this out, so if there are others it might help.

Background
We are currently running RedHat 8.10 and openjdk version "1.8.0_432". Using the SQL Gateway we connect to MS SQL using the mssql-jdbc-12.2.0.jre8.jar driver with an Active Directory Service Account user.
jdbc:sqlserver://<server>:<port>;database=<database>;trustServerCertificate=true;integratedSecurity=true;authenticationScheme=NTLM;domain=<domain>;authentication=NotSpecified

When the database was moved to Azure SQL, I was given a new URL that included 
jdbc:sqlserver://<server>:<port>;databaseName=<database>;domain=<domain>;encrypt=true;trustServerCertificate=true;hostNameInCertificate=<server name>;Authentication=ActiveDirectoryPassword

When I clicked on Test Connection, I got 
Remote JDBC error: com.microsoft.sqlserver.jdbc.SQLServerException: Failed to load MSAL4J Java library for performing ActiveDirectoryPassword authentication..

I tried updating the MS SQL JDBC driver to mssql-jdbc-12.8.1.jre8.jar but got the same error so something was still missing.

I then tried to connect using DBeaver, and eventually I was successful after some tweaks of the account. I then downloaded what DBeaver was using as the class file and uploaded the jar files onto my server. 

Working with WRC, I updated my %JDBC Server to use the path to the additional jar files and left my driver as the Class Path in my SQL Gateway Connection string.

However, Test Connection, would still throw an error.

Installing Maven
Microsoft had referred me to update Maven, but I had no clue what it was or how to use it. I tried manually downloading msal4j.jar but kept hitting error after error. The Maven Repository listed dependencies, so manually I tried downloading all the Dependency jar files but still could not get it to work.
There are many different ways to install Maven but I used… How to Install Maven on Windows, Linux, and Mac | Baeldung to figure out how to download it for my server, since “yum install maven” could not find the repository to download.

Once downloaded and installed, I had to add $JAVA_HOME, $M2_HOME, and $MAVEN_HOME to my .profile.

Downloading and installing necessary jar file and dependencies
In my troubleshooting of trying to connect to Azure SQL, I kept on getting an error message about msal4j.jar.
Using Maven from the command line… I executed
:>mvn dependency:get -Dartifact=com.microsoft.azure:msal4j:1.18.0

Which it downloaded msal4j.jar and all its dependencies to the .m2 repository on the local server.
From there I wanted to grab the jar files and copy them to the directory structure I had setup for IRIS. So I executed
:>mvn dependency:copy-dependencies -f /archive/.m2/repository/com/microsoft/azure/msal4j/ -DoutputDirectory=/nfs/data/drivers/java/

But using Maven you still have to copy the msal4j.jar, so I manually copied that jar file from the .m2 repository structure to /nfs/data/drivers/java/

Updating %JDBC Server and SQL Gateway Connection
To successfully connect, I had to make sure that all proper jar files were defined within the Class Paths.

So, on the %JDBC Server, I set the Class Path to /nfs/data/drivers/java/* which contained the msal4j.jar and all its dependencies.
Within the SQL Gateway Connection, I had to verify that the User had the appropriate domain defined, I reentered the password, clicked on Test Connection, and was able to get a Connection Successful.

In the end the hardest part was knowing how to download msal4j.jar and its dependencies to ensure that the proper authentication through Microsoft Entra would happen. 

So hopefully this will help someone down the road when trying to connect to Azure SQL Server using Active Directory Password Authentication through Microsoft Entra.