Help with service principal authentication Azure SQL

Hi! Im struggling with a service principal connection to Azure SQL database. I have the following JDBC URL: jdbc:sqlserver://xxxx.datawarehouse.pbidedicated.windows.net:1433;database=Postgres_Mirror;user=xxx;password=xxx;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.datawarehouse.pbidedicated.windows.net;loginTimeout=30;authentication=ActiveDirectoryPassword

I have used the DB connector with the following configuration:

But I keep getting the following error: Execute failed: Failed to load MSAL4J Java library for performing ActiveDirectoryPassword authentication.

I looked trough the forums, and found that I need to install the MSAL4J prequisites, but I cannot find from anywhere how. Any help with this is much appriciated :slight_smile:

Hi,
Have you considered using the more specific Microsoft SQL Server Connector node for this? It has an optional input for a Microsoft Authentication node that provides different login options.
Kind regards,
Alexander

hi @AlexanderFillbrunn

I tried it also, with the following configuration (JDBC parameters empty):

URL=jdbc:sqlserver://xxx.datawarehouse.pbidedicated.windows.net:1433;database=Postgres_Mirror;user=xxx;password=xxx;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.datawarehouse.pbidedicated.windows.net;loginTimeout=30;authentication=ActiveDirectoryPassword

But I get the following error message: Execute failed: The authentication value “ActiveDirectoryPassword:1433” is not valid.

Hi,
I think you’ll need to choose Username/Password authentication in the Microsoft Authentication node (or maybe even just use interactive login). Then you can skip the username and password configuration in the Microsoft SQL Server Connector entirely.
In addition to that, the node works a little bit different than the DB Connector node, in that you cannot provide a JDBC URL directly. Instead, where you have the JDBC URL now, you should only provide the hostname (xxx.datawarehouse.pbidedicated.windows.net). The Database name config should be “Postgres_Mirror” as it is in your JDBC URL. All other parameters (encrypt=true, trustServerCertificate=false, etc) must go into the JDBC parameters tab.
Kind regards,
Alexander

hi @AlexanderFillbrunn
Thanks! I have used this authentication method, ad it should be for the service principal?
image

Tried it and I get the following error:


Configuration:

parameters:

@AlexanderFillbrunn I tested the connection with SSMS, and it works correctly, so it must be something in the configurations im using :confused:

@AlexanderFillbrunn The database is located under a fabric onelake, but should function as a standard azure SQL. No special configuration was needed when connecting with SSMS, just address and service principal as authentication:

Hi @AlexanderFillbrunn

Just as an additional piece of info, the workflow works, If I authenticate with my credentials instead of the service principal:

image

The only problem is that we have MFA, and I need these to work with SP for automation purposes…

Hi,
It seems like the Microsoft Authenticator is not the right node to use after all, you can use the Microsoft SQL Server Connector without it. In this forum thread a user made it work after following instructions by my colleague @tobias.koetter. Can you try this?
Kind regards,
Alexander

@AlexanderFillbrunn Thanks! Have done this, and it brings me back to my original question :slight_smile:

Execute failed: Failed to load MSAL4J Java library for performing ActiveDirectoryPassword authentication.

I looked through the forums, and found that I need to install the MSAL4J prequisites, but I cannot find from anywhere how. I read that thread also, but was not able to figure it out from there :confused:

@AlexanderFillbrunn this is what I currently have registered for the JDBC driver Im using:

Hi,
Thanks for the screenshot. I think the msal4j library also needs to be part of this classpath. However, the library also has dependencies to other libraries, which might have dependencies again. So I am trying to find out if there is any way to download all of them in one go. I’ll need to talk to our developers for that. I will keep you posted!
Kind regards,
Alexander

Thanks @AlexanderFillbrunn! Much appriciated! :pray:

Just double checking: When I install these libraries, and move the models to run them on teams hub (starting evaluation next week), will the used libraries move together with the Knime models?

Hi,
It’s good that you mention that! I do not think this will work in the first iteration of Teams on Community Hub. There, each executor has the same environment with standard settings. Your custom setup here is one in the environment and not in the workflow. It is a good point, though. I will discuss this with product management and see if we can find a solution. In the beginning, we might be flexible with what we offer to the early adopters.
Kind regards,
Alexander

1 Like

Hi,
On Community Hub, we can figure something out for you. Let’s see what our developers say about the libraries.
Kind regards,
Alexander

1 Like

Hi,
Can you try with the driver provided by this extension: KNIME Microsoft JDBC Driver For SQL Server – KNIME Community Hub? It should have the MSAL4j already included and should also be available on Community Hub. If that works, it’d be the most elegant solution.
Kind regards,
Alexander

Thanks @AlexanderFillbrunn I will try this out! :slight_smile:

Hi @AlexanderFillbrunn
We decided to change the service principal authentication to an Entra ID that is not a part of our conditional access policies, which enabled us to use it on scheduled tasks. I will give this SP authentication still a go once I have time, and let you know how it went :slight_smile:

1 Like

Hi,
Great, thank you for following up and letting me know. Good luck :crossed_fingers:
Alexander

1 Like

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.