Configure passwordless Azure SQL in Wildfly

MikhailChibel
September 28, 2023
197 views
1 vote
2 Answers

I’m trying to configure an application deployed to Wildfly 23 to connect to Azure SQL using managed identity and struggle to visualise how it is supposed to work in principle.
There are tons of articles and how-to in the internet on how to use passwordless connection to connect to Azure hosted databases. Most of them requires to include azure-identity.jar to the class path and use authentication=ActiveDirectoryMSI option in jdbc connection string.
However, my application uses connection pool configured in wildly so adding azure-identity.jar to my application will not help. From my understanding I need to make azure-identity.jar available to wildfly, and I’m not sure how to do that. One idea is to register it as a module, I haven’t tried that yet as I need to manage somehow dependencies of the jar.

So far, I changed my standalone file to include a datasource

<datasource jndi-name="java:/jdbc/passwordless" pool-name="pwls-ds" enabled="true">
                    <connection-url>jdbc:sqlserver://xxxxx.database.windows.net:1433;database=aaa;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;authentication=ActiveDirectoryMSI</connection-url>
                    <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
                    <driver>mssql-jdbc-12.4.1.jre11.jar</driver>
                    <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
                    <pool>
                        <min-pool-size>2</min-pool-size>
                        <initial-pool-size>5</initial-pool-size>
                        <max-pool-size>20</max-pool-size>
                    </pool>
                </datasource>

When trying to test the connection I’m getting the following exception

com.microsoft.sqlserver.jdbc.SQLServerException: Failed to load MSAL4J Java library for performing ActiveDirectoryManagedIdentity authentication

Any hint/help is appreciated.

Answers

Chosen as BEST ANSWER

To make it work I created a custom fat jar that combines both azure-identity and mssql-jdbc driver.Then I deploy it in the same way as a normal driver. For references the pom file to achieve that:

<?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>com.XXX.driver.sql</groupId>
<artifactId>XXX-sql-driver</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
    <maven.compiler.source>11</maven.compiler.source>
    <maven.compiler.target>11</maven.compiler.target>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
    <dependency>
        <groupId>com.azure</groupId>
        <artifactId>azure-identity</artifactId>
        <version>1.8.1</version>
    </dependency>
    <dependency>
        <groupId>com.microsoft.sqlserver</groupId>
        <artifactId>mssql-jdbc</artifactId>
        <version>12.2.0.jre11</version>
    </dependency>
</dependencies>
<build>
    <plugins>
        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-compiler-plugin</artifactId>
            <version>3.11.0</version>
            <configuration>
                <source>11</source>
                <target>11</target>
            </configuration>
        </plugin>

        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-assembly-plugin</artifactId>
            <version>3.6.0</version>

            <configuration>
                <descriptorRefs>
                    <descriptorRef>jar-with-dependencies</descriptorRef>
                </descriptorRefs>
            </configuration>
            <executions>
                <execution>
                    <id>make-assembly</id>
                    <phase>package</phase>
                    <goals>
                        <goal>single</goal>
                    </goals>
                </execution>
            </executions>
        </plugin>
    </plugins>
</build>

I doubt it is the best solution it looks more like a workaround, so still on the hunt.