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.
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.
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?
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:
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.
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.
I am also trying to connect to Azure SQL using Azure AD Service principal. I have installed all the extra dependency jars as mentioned above in my local ignition. However i am not sure how to config this in Ignition Gateway . I tried the “Extra Connection Properties” as databaseName=;authentication=ActiveDirectoryServicePrincipal;aadSecurePrincipalId=;aadSecurePrincipalSecret
However, the connection status is stuck at “Reconnecting”
Can you help on this ?
I didn't use the Azure AD Service principal method so I don't know what string should be entered in Extra Connection Properties. You will probably need to open a support ticket to see if Ignition provides a method of specifying the required parameters. You might be able to use Ignition's Username and Password fields to enter your PrincipalID and principalSecret values.
Trying to get a valid connection to OneLake T-SQL with Entra autentication.
Uploaded a series of libraries, but still getting errors.
Gemini suggestion: Focus on Classpath Configuration:
The ClassNotFoundException often indicates a classpath issue. Your pom.xml doesn't directly control the classpath configuration for your application server (Ignition Gateway in this case). You need to verify that the JAR files from your local Maven repository, including com.nimbusds:oauth2-oidc-sdk-11.10.1.jar , are included in the classpath when running your application. This might involve modifying configuration files or settings specific to Ignition Gateway.
Then I get this. Bit unsure about the extra connection properties and what is required...
Cannot create PoolableConnectionFactory (Failed to authenticate the user REMOVED in Active Directory (Authentication=ActiveDirectoryServicePrincipal). Could not initialize class com.microsoft.aad.msal4j.JsonHelper)
Added this to my pom.xml file, and ran "mvn clean install" before rebooting the server. Still the same error.
<dependency>
<groupId>com.microsoft.azure</groupId>
<artifactId>msal4j</artifactId>
<version>1.15.0</version> <!-- Replace with the latest version -->
</dependency>
All that does is download the JAR files into various folders in ~/.m2/repository. It doesn't do anything with Ignition. You have to figure out all the JAR files required and then find and copy them into lib/core/gateway.
I'm trying my best to follow the dependencies links for msal4j at maven.org, and uploading all the jars files. Rebooting and checking again. It is not showing any errors in the diagnostics logs anymore, but the database connection status returns this one:
Cannot create PoolableConnectionFactory (Failed to authenticate the user removed in Active Directory (Authentication=ActiveDirectoryServicePrincipal). Could not initialize class com.microsoft.aad.msal4j.JsonHelper)
Can this still be JAR related, or do I need look at the connection props?
