Azure SQL Server with Azure Active Directory Authentication

I’m trying to establish a database connection in Ignition to an Azure-hosted SQL Server instance using Azure Active Directory authentication. I’ve tried following the guidance in the Microsoft documentation but I keep running into obstacles. I’ve updated the Microsoft SQL Server JDBC driver to version 9.4 and I’ve downloaded and copied “mssql-jdbc_auth-9.4.0.x64.dll” to “C:\Program Files\Inductive Automation\Ignition\lib”. I’ve also downloaded the file “msal4j-1.11.0.jar” and copied it to “C:\Program Files\Inductive Automation\Ignition\lib\core\gateway”.

Continued error messages associated with the faulted database connection have led me to download and copy files “oauth2-oidc-sdk-9.12.jar” and “json-smart-2.4.7.jar” to “C:\Program Files\Inductive Automation\Ignition\lib\core\gateway” but I still have a faulted connection, this time with the error message “Could not initialize class com.microsoft.aad.msal4j.JsonHelper”. Does anyone know of a trusted website from which I can download all the files required to establish this type of connection with this particular authentication method? If I use SQL Server authentication, it works fine but that’s not what we want to do.

Can you post the full error message and stack trace from the logs?

I suspect you are also missing this dependency: https://search.maven.org/artifact/com.fasterxml.jackson.core/jackson-databind/2.12.1/bundle

What you are doing is difficult because it’s not meant to be done by an end user; this is all stuff the developers of an application that supports Azure SQL Server + Azure AD Auth would do, and would do it with their build tools, not by hand.

Looks like you might have more JAR wranglin’ ahead of you:

[INFO] --- maven-dependency-plugin:2.8:tree (default-cli) @ msal4j_tmp ---
[INFO] org.example:msal4j_tmp:jar:1.0-SNAPSHOT
[INFO] \- com.microsoft.azure:msal4j:jar:1.11.0:compile
[INFO]    +- com.nimbusds:oauth2-oidc-sdk:jar:9.7:compile
[INFO]    |  +- com.github.stephenc.jcip:jcip-annotations:jar:1.0-1:compile
[INFO]    |  +- com.nimbusds:content-type:jar:2.1:compile
[INFO]    |  +- net.minidev:json-smart:jar:2.4.7:compile (version selected from constraint [1.3.3,2.4.7])
[INFO]    |  |  \- net.minidev:accessors-smart:jar:2.4.7:compile
[INFO]    |  |     \- org.ow2.asm:asm:jar:9.1:compile
[INFO]    |  +- com.nimbusds:lang-tag:jar:1.5:compile
[INFO]    |  \- com.nimbusds:nimbus-jose-jwt:jar:9.9.3:compile
[INFO]    +- org.slf4j:slf4j-api:jar:1.7.28:compile
[INFO]    \- com.fasterxml.jackson.core:jackson-databind:jar:2.12.1:compile
[INFO]       +- com.fasterxml.jackson.core:jackson-annotations:jar:2.12.1:compile
[INFO]       \- com.fasterxml.jackson.core:jackson-core:jar:2.12.1:compile

Kevin,

No error message is being written to the gateway log. The status of the connection toggles between “Reconnecting” and “Cannot create PoolableConnectionFactory (Failed to authenticate the user appignition@natgyp.com in Active Directory (Authentication=ActiveDirectoryPassword). Could not initialize class com.microsoft.aad.msal4j.JsonHelper)”.

I added the dependency you referenced, but that did not change the results, so you’re probably correct that I need to track down some additional dependencies. I had already downloaded and installed a couple of those (oauth2-oidc-sdk-9.12.jar and json-smart-2.4.7.jar). For what it’s worth, I work for a systems integration firm. I was able to get this connection to work with SQL Server JDBC driver version 8.2 when I installed all the .jar files that I downloaded from website Download adal4j JAR file with all dependencies. I don’t really trust that site and I’m trying to find a more trustworthy method and would prefer to rely on files provided by or recommended by Inductive Automation.

Your last sentence is interesting. I’m trying to get this to work for a client where we already have Ignition making connections to local and remote “standard” SQL Server databases with integrated security where the Ignition service is running under a domain account. The client has since decided to migrate one of the databases to Azure SQL and I didn’t think the implementation would be beyond Ignition’s capabilities as long as we could get the community support we’ve come to expect. I really appreciate the help. Do you know if there is a way on Maven to download not just a single .jar, but all of its dependencies without searching for each one individually?

Thank you,

Larry

It’s more about whether the SQL Server JDBC driver supports this out of the box than Ignition itself. Now that I’ve seen how many additional dependencies are required I can maybe see why they aren’t packaging this as part of the JDBC driver. The same reasoning might apply if were to evaluate shipping this support built into Ignition - it’s a lot of overhead for some relatively obscure functionality, not to mention it also involves native libraries, which makes it platform-dependent.

Make yourself a temp directory, put this into a file called pom.xml:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>msal4j-deps</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>11</maven.compiler.source>
        <maven.compiler.target>11</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>com.microsoft.azure</groupId>
            <artifactId>msal4j</artifactId>
            <version>1.11.0</version>
        </dependency>
    </dependencies>

</project>

Change into that directory and run mvn dependency:resolve. You’ll need to have Maven installed and available on $PATH. It will download the dependency artifacts to folders in ~/.m2/repository corresponding with the package name.

Thank you, Kevin. After getting all the necessary jar files in place, the connection is good.

Glad it’s working. You’ll need to document everything you’ve done because none of these files are going to come along in your gateway backups. If you restore a backup into a new installation some day in the future you’ll need to put these files in place again.

1 Like