Azure SQL Server with Azure Active Directory Authentication

[Kevin.Herron]

@SBL_MC

Can this still be JAR related, or do I need look at the connection props?

It's probably still JAR related. Full stack trace to go along with the error would be useful.

[SBL_MC]

Wrapper.log and Diagnostic logs seem to not show any relevant.

The two artifacts you listed in your pom.xml suggestion I can find in /home/admintest/.m2/repository/com/microsoft/azure, these JAR files has been moved to the ignition folde.r

[AndrewWilliams]

I was recently trying to connect to an Azure SQL Server using the ActiveDirectoryServicePrincipal method, and this thread helped point me in the right direction, but for completeness I thought I would explain how I got it to work.

1. Get msal4j and all dependencies with Maven

• I used WSL for all of this which seemed easier than trying to do it on Windows directly
• Install jdk, I'm using openjdk 11
• Download and unpack Maven, add to path

wget https://dlcdn.apache.org/maven/maven-3/3.9.8/binaries/apache-maven-3.9.8-bin.tar.gz
tar xzvf apache-maven-3.9.8-bin.tar.gz
mv apache-maven-3.9.8 /opt/
# Add mvn to path, I did this in my .zshrc
export PATH=$PATH:/opt/apache-maven-3.9.8/bin

• See Maven – Installing Apache Maven for latest version/instructions
• As @Kevin.Herron mentioned, create a temp folder with a pom.xml file based on his post - I changed the version to the latest 1.16.2
• Run mvn install dependency:copy-dependencies in the temp folder with the pom.xml file
• This will create a new folder target, all of the required .jar files will be in target > dependency

2. Copy all of the dependencies to {IGNITION_PATH}/lib/core/gateway
3. You also need to put the mssql-jdbc-auth-xxx.dll in the {IGNITION_PATH}/lib folder, you can get that directly from MSFT: Release notes - JDBC Driver for SQL Server | Microsoft Learn
   • Note I'm using the stock driver, 9.4.0
4. Restart your gateway
5. If you are using the stock 9.4.0 driver, you will need to add some properties to the Extra Connection Properties section, here's how mine looks

databaseName={db_name};authentication=ActiveDirectoryServicePrincipal;AADSecurePrincipalId={clientId};AADSecurePrincipalSecret={clientSecret};

• You can leave the username and password blank
• If you are using a newer jdbc driver, you will need to put the clientId in the Username field, and the clientSecret in the Password field, and DO NOT add the AADSecurePrincipalId or AADSecurePrincipalSecret properties, otherwise it won't connect.
• MSFT docs: Connect using Microsoft Entra authentication - JDBC Driver for SQL Server | Microsoft Learn.

Hope this helps someone out!