Azure SQL DB with AD authentification (ActiveDirectoryPassword)

Support
#1

Hi,

we need to use AD authentication when connecting to Azure SQL DB.
Usually the JDBC URL would look like:
jdbc:sqlserver://servername.database.windows.net:1433;database=dbname-sql-db-dev;encrypt=true;trustServerCertificate=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;authentication=ActiveDirectoryPassword;user=user@domain.com;password=password

How to do that with JMIX for all DBs, including the main DB.

Kind Regards
Roland

#2

made some progress, but now get this error: Failed to load ADAL4J Java library for performing ActiveDirectoryPassword authentication.

#3

I added this:
implementation group: ‘com.microsoft.azure’, name: ‘com.microsoft.aad.msal4j’, version: ‘1.13.8’
to build.gradle, but does not work

#4

I also implemented all dependencies in build.gradle.
Using the same in i.e. SQL Workbench it works fine.
But not when using JMIX.
Only see
image

implementations:
implementation ‘com.microsoft.sqlserver:mssql-jdbc:11.2.3.jre11’
implementation ‘com.microsoft.azure:msal4j:1.13.8’
implementation ‘ch.qos.logback:logback-classic:1.2.3’
implementation ‘com.azure:azure-security-keyvault-secrets:4.3.5’
implementation ‘com.fasterxml.jackson.core:jackson-databind:2.13.4.2’
implementation ‘com.google.guava:guava:31.1-jre’
implementation ‘com.nimbusds:oauth2-oidc-sdk:9.35’
implementation ‘commons-io:commons-io:2.7’
implementation ‘net.minidev:json-smart:2.4.8’
implementation ‘org.apache.commons:commons-text:1.10.0’
implementation ‘org.apache.httpcomponents:httpclient:4.5.13’
implementation ‘org.easymock:easymock:4.0.2’
implementation ‘org.powermock:powermock-api-easymock:2.0.0’
implementation ‘org.powermock:powermock-module-testng:2.0.0’
implementation ‘org.projectlombok:lombok:1.18.6’
implementation ‘org.seleniumhq.selenium:selenium-java:3.14.0’
implementation ‘org.skyscreamer:jsonassert:1.5.0’
implementation ‘org.slf4j:slf4j-api:1.7.36’
implementation ‘org.testng:testng:7.1.0’

application.properties:
azuresql.datasource.url=jdbc:sqlserver://servername.database.windows.net;databaseName=databasename;authentication=ActiveDirectoryPassword
azuresql.datasource.username=user@domain.com
azuresql.datasource.password=password

#5

and one more test I did:
using the same Java version, IDEA, implementations, gradle… works fine.
Only in JMIX it does not work.
Here is the sample code und below the build.gradle

package org.example;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import com.microsoft.sqlserver.jdbc.SQLServerDataSource;
public class Main {
public static void main(String[] args) {
SQLServerDataSource ds = new SQLServerDataSource();
ds.setServerName(“servername.database.windows.net”); // Replace with your server name
ds.setDatabaseName(“dbname-db-dev”); // Replace with your database
ds.setUser(“user@domain.com”); // Replace with your user name
ds.setPassword(“password”); // Replace with your password
ds.setAuthentication(“ActiveDirectoryPassword”);

    try {
        Connection connection = ds.getConnection();
        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT SUSER_SNAME()");
        if (rs.next()) {
            System.out.println("You have successfully logged on as: " + rs.getString(1));
        }
    }
    catch(Exception e) {
        System.out.println(e.getMessage());
    }
}

}

build.gradle:

plugins {
id ‘java’
}

group ‘org.example’
version ‘1.0-SNAPSHOT’

repositories {
mavenCentral()
}

dependencies {
testImplementation ‘org.junit.jupiter:junit-jupiter-api:5.8.1’
testRuntimeOnly ‘org.junit.jupiter:junit-jupiter-engine:5.8.1’
implementation ‘com.microsoft.sqlserver:mssql-jdbc:11.2.3.jre11’
implementation ‘com.microsoft.azure:msal4j:1.13.8’
implementation ‘ch.qos.logback:logback-classic:1.2.3’
implementation ‘com.azure:azure-security-keyvault-secrets:4.3.5’
implementation ‘com.fasterxml.jackson.core:jackson-databind:2.13.4.2’
implementation ‘com.google.guava:guava:31.1-jre’
implementation ‘com.nimbusds:oauth2-oidc-sdk:9.35’
implementation ‘commons-io:commons-io:2.7’
implementation ‘net.minidev:json-smart:2.4.8’
implementation ‘org.apache.commons:commons-text:1.10.0’
implementation ‘org.apache.httpcomponents:httpclient:4.5.13’
implementation ‘org.easymock:easymock:4.0.2’
implementation ‘org.powermock:powermock-api-easymock:2.0.0’
implementation ‘org.powermock:powermock-module-testng:2.0.0’
implementation ‘org.projectlombok:lombok:1.18.6’
implementation ‘org.seleniumhq.selenium:selenium-java:3.14.0’
implementation ‘org.skyscreamer:jsonassert:1.5.0’
implementation ‘org.slf4j:slf4j-api:1.7.36’
implementation ‘org.testng:testng:7.1.0’
}

test {
useJUnitPlatform()
}

#6

one more interesting information:
I added an entity for the additional datastore which needs the ActiveDirectoryPassword Authentification manually and also create a simple entity browser screen.
When I run the application, it works fine.

@krivopustov
Which means,
there is an issue or bug in the JMIX plugin for testing the additional datastore
and also for generating the model.

#8

Hi

I assume you need to add libraries required for database connection in Jmix Studio. Go to Preferences > Languages & Frameworks > Jmix > Database Drivers settings page, select Microsoft SQL Server, add paths to the required jars to the Additional DB Drivers list.

1 Like
#9

Hi Alexander,

I also tried that and added all libraries which I listed in the build.gradle…
but that did not help…
The studio plugin cannot authenticate using ActiveDirectoryPassword,
because ‘failed to load msal4j library…’

Like I wrote, a additional datastore can connect on runtime,
but in the studio it does not work.
Which means, I can also not create or update the main DB.

Regards
Roland

#10

Hi Alexander,

got one step further:
I added all the libs, but when opening the settings again,
no setting was kept.
So I run the IDEA with Adminstrator rights… now the added libraries got used.
But, I still get an error message when trying to test the connection:
Unable to connect to database. Cause:
java.util.concurrent.ExecutionException: java.lang.RuntimeException: com/nimbusds/jose/util/Base64URL. Check Data Store connection parameters.

Here my connection params:

main.datasource.url = jdbc:sqlserver://servername.database.windows.net;databaseName=dbname-sql-db-dev;authentication=ActiveDirectoryPassword
main.datasource.username = user@domain.com
main.datasource.password =pwd$09

can that be caused by special chars in the password or username???

#11

got it:
I changed to older library versions…
here is a list of those which work for me together with OpenJDK11
image

1 Like
#12

Hi @gaslov, do you know if there is a similar setting in Cuba plugin?
We are facing the same issue there.
Roland

#13

got it…
just copied the .jar files into
C:\Users\username\AppData\Roaming\JetBrains\IdeaIC2022.3\plugins\cuba-studio\lib
but except the slf4j-api-1.7.28.jar because that one is collidating with another version already in use in IDEA.

#14

one more information if someone is having similar issues:
After getting JMIX and Cuba app running fine with Azure SQL activedirectorypassword authentification,
I got the next issue:
We also need to authenticate the user against the Active Directory Ldap and for that I need to add
the AD Ldap server certificate to the truststore we are using.
But, if you go that way and then point to the truststore in the setenv.bat/sh (catalina_opts=…) you might
face the problem, that you can then authenticate against the AD Ldap, but the connection to the
Azure SQL does not work any more.
I tried hundreds of settings in the JDBC URL and also different ways to load the truststore…
nothing worked… I could only authenticate or I could use the Azure SQL, but not both in one app.

In the end I found a solution, which is pretty simple when you know it :slight_smile:
I added all needed certifications into the cacerts file of the Java JDK/VM itself and took out the
settings from sentenv.bat.
Then it directly worked fine, login using AD security and using Azure SQL with ‘activedirectorypassword authentication’ in one app…

cheers