# Help with service principal authentication Azure SQL **URL:** \ **Category:** KNIME Analytics Platform\ **Tags:** azure\ **Created:** [January 19, 2024, 8:28am UTC](https://forum.knime.com/t/help-with-service-principal-authentication-azure-sql/76654 "2024-01-19T08:28:48Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Data\_consumer](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/d/58f4c7/32.png) [@Data\_consumer](https://forum.knime.com/u/Data_consumer)\ **Post date:** [January 19, 2024, 8:28am UTC](https://forum.knime.com/t/help-with-service-principal-authentication-azure-sql/76654/1 "2024-01-19T08:28:48Z")
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: ![image](https://forum-cdn.knime.com/uploads/default/original/3X/7/4/74950eced044f194dc32515e0a8bd4fa639dc14a.png) 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 🙂 ---
**Author:** ![AlexanderFillbrunn](https://forum-cdn.knime.com/user_avatar/forum.knime.com/alexanderfillbrunn/32/357_2.png) [@AlexanderFillbrunn](https://forum.knime.com/u/AlexanderFillbrunn)\ **Post date:** [January 19, 2024, 10:21am UTC](https://forum.knime.com/t/help-with-service-principal-authentication-azure-sql/76654/2 "2024-01-19T10:21:05Z")
Hi, Have you considered using the more specific [Microsoft SQL Server Connector](https://kni.me/n/lbG8X3On6ol70XyD) node for this? It has an optional input for a Microsoft Authentication node that provides different login options. Kind regards, Alexander ---
**Author:** ![Data\_consumer](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/d/58f4c7/32.png) [@Data\_consumer](https://forum.knime.com/u/Data_consumer)\ **Post date:** [January 19, 2024, 10:34am UTC](https://forum.knime.com/t/help-with-service-principal-authentication-azure-sql/76654/3 "2024-01-19T10:34:31Z")
> [@Data\_consumer](#): > > DBC 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 hi @AlexanderFillbrunn I tried it also, with the following configuration (JDBC parameters empty): ![image](https://forum-cdn.knime.com/uploads/default/original/3X/f/5/f5f55cd5c34e6994b6ae52e6f5577fe341e67c7c.png) 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. ---
**Author:** ![AlexanderFillbrunn](https://forum-cdn.knime.com/user_avatar/forum.knime.com/alexanderfillbrunn/32/357_2.png) [@AlexanderFillbrunn](https://forum.knime.com/u/AlexanderFillbrunn)\ **Post date:** [January 19, 2024, 10:41am UTC](https://forum.knime.com/t/help-with-service-principal-authentication-azure-sql/76654/4 "2024-01-19T10:41:58Z")
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](http://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 ---
**Author:** ![Data\_consumer](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/d/58f4c7/32.png) [@Data\_consumer](https://forum.knime.com/u/Data_consumer)\ **Post date:** [January 19, 2024, 10:50am UTC](https://forum.knime.com/t/help-with-service-principal-authentication-azure-sql/76654/5 "2024-01-19T10:50:54Z")
hi @AlexanderFillbrunn Thanks! I have used this authentication method, ad it should be for the service principal? ![image](https://forum-cdn.knime.com/uploads/default/original/3X/9/6/96ecd5a60889ee4b1d0715d712c8d2fa5d862dce.png) Tried it and I get the following error: ![image](https://forum-cdn.knime.com/uploads/default/original/3X/1/6/1618224d887cb7a251de7d02b64a4d4c7e867700.png) Configuration: ![image](https://forum-cdn.knime.com/uploads/default/original/3X/a/4/a4c368af8157be8acc18e4149cd11f9a9c9c97fa.png) parameters: ![image](https://forum-cdn.knime.com/uploads/default/original/3X/f/8/f8f2abbd91bf3bc20245c6dd60ec67d49f053599.png) ---
**Author:** ![Data\_consumer](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/d/58f4c7/32.png) [@Data\_consumer](https://forum.knime.com/u/Data_consumer)\ **Post date:** [January 19, 2024, 11:02am UTC](https://forum.knime.com/t/help-with-service-principal-authentication-azure-sql/76654/6 "2024-01-19T11:02:35Z")
> [@AlexanderFillbrunn](#): > > orks a little bit different than the DB Connector no @AlexanderFillbrunn I tested the connection with SSMS, and it works correctly, so it must be something in the configurations im using 😕 ---
**Author:** ![Data\_consumer](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/d/58f4c7/32.png) [@Data\_consumer](https://forum.knime.com/u/Data_consumer)\ **Post date:** [January 19, 2024, 11:10am UTC](https://forum.knime.com/t/help-with-service-principal-authentication-azure-sql/76654/7 "2024-01-19T11:10:57Z")
> [@Data\_consumer](#): > > e configurations im using @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: ![image](https://forum-cdn.knime.com/uploads/default/original/3X/9/0/90ad93a4553d43294b31d897ea9a22a4a8cc0825.png) ---
**Author:** ![Data\_consumer](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/d/58f4c7/32.png) [@Data\_consumer](https://forum.knime.com/u/Data_consumer)\ **Post date:** [January 19, 2024, 11:33am UTC](https://forum.knime.com/t/help-with-service-principal-authentication-azure-sql/76654/8 "2024-01-19T11:33:03Z")
Hi @AlexanderFillbrunn Just as an additional piece of info, the workflow works, If I authenticate with my credentials instead of the service principal: ![image](https://forum-cdn.knime.com/uploads/default/original/3X/a/8/a8311ff80f8bb1f18412f3972c2613ed7f149846.png) The only problem is that we have MFA, and I need these to work with SP for automation purposes… ---
**Author:** ![AlexanderFillbrunn](https://forum-cdn.knime.com/user_avatar/forum.knime.com/alexanderfillbrunn/32/357_2.png) [@AlexanderFillbrunn](https://forum.knime.com/u/AlexanderFillbrunn)\ **Post date:** [January 19, 2024, 12:09pm UTC](https://forum.knime.com/t/help-with-service-principal-authentication-azure-sql/76654/9 "2024-01-19T12:09:12Z")
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](https://forum.knime.com/t/connect-to-azure-database/20585) a user made it work after following instructions by my colleague @tobias.koetter. Can you try this? Kind regards, Alexander ---
**Author:** ![Data\_consumer](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/d/58f4c7/32.png) [@Data\_consumer](https://forum.knime.com/u/Data_consumer)\ **Post date:** [January 19, 2024, 1:00pm UTC](https://forum.knime.com/t/help-with-service-principal-authentication-azure-sql/76654/10 "2024-01-19T13:00:45Z")
> [@AlexanderFillbrunn](#): > > Microsoft SQL Server Connector without it @AlexanderFillbrunn Thanks! Have done this, and it brings me back to my original question 🙂 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 😕 ---
**Author:** ![Data\_consumer](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/d/58f4c7/32.png) [@Data\_consumer](https://forum.knime.com/u/Data_consumer)\ **Post date:** [January 19, 2024, 1:13pm UTC](https://forum.knime.com/t/help-with-service-principal-authentication-azure-sql/76654/11 "2024-01-19T13:13:04Z")
@AlexanderFillbrunn this is what I currently have registered for the JDBC driver Im using: ![image](https://forum-cdn.knime.com/uploads/default/original/3X/0/b/0bc78549fd7c83d6b2e687eb4ede75f3a2fa0a49.png) ---
**Author:** ![AlexanderFillbrunn](https://forum-cdn.knime.com/user_avatar/forum.knime.com/alexanderfillbrunn/32/357_2.png) [@AlexanderFillbrunn](https://forum.knime.com/u/AlexanderFillbrunn)\ **Post date:** [January 19, 2024, 1:15pm UTC](https://forum.knime.com/t/help-with-service-principal-authentication-azure-sql/76654/12 "2024-01-19T13:15:50Z")
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 ---
**Author:** ![Data\_consumer](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/d/58f4c7/32.png) [@Data\_consumer](https://forum.knime.com/u/Data_consumer)\ **Post date:** [January 19, 2024, 1:18pm UTC](https://forum.knime.com/t/help-with-service-principal-authentication-azure-sql/76654/13 "2024-01-19T13:18:28Z")
Thanks @AlexanderFillbrunn! Much appriciated! 🙏 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? ---
**Author:** ![AlexanderFillbrunn](https://forum-cdn.knime.com/user_avatar/forum.knime.com/alexanderfillbrunn/32/357_2.png) [@AlexanderFillbrunn](https://forum.knime.com/u/AlexanderFillbrunn)\ **Post date:** [January 19, 2024, 1:31pm UTC](https://forum.knime.com/t/help-with-service-principal-authentication-azure-sql/76654/14 "2024-01-19T13:31:55Z")
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 ---
**Author:** ![AlexanderFillbrunn](https://forum-cdn.knime.com/user_avatar/forum.knime.com/alexanderfillbrunn/32/357_2.png) [@AlexanderFillbrunn](https://forum.knime.com/u/AlexanderFillbrunn)\ **Post date:** [January 19, 2024, 1:50pm UTC](https://forum.knime.com/t/help-with-service-principal-authentication-azure-sql/76654/15 "2024-01-19T13:50:46Z")
Hi, On Community Hub, we can figure something out for you. Let’s see what our developers say about the libraries. Kind regards, Alexander ---
**Author:** ![AlexanderFillbrunn](https://forum-cdn.knime.com/user_avatar/forum.knime.com/alexanderfillbrunn/32/357_2.png) [@AlexanderFillbrunn](https://forum.knime.com/u/AlexanderFillbrunn)\ **Post date:** [January 19, 2024, 2:17pm UTC](https://forum.knime.com/t/help-with-service-principal-authentication-azure-sql/76654/16 "2024-01-19T14:17:40Z")
Hi, Can you try with the driver provided by this extension: [KNIME Microsoft JDBC Driver For SQL Server – KNIME Community Hub](https://hub.knime.com/knime/extensions/org.knime.features.database.extensions.sqlserver.driver/latest)? 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 ---
**Author:** ![Data\_consumer](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/d/58f4c7/32.png) [@Data\_consumer](https://forum.knime.com/u/Data_consumer)\ **Post date:** [January 19, 2024, 2:18pm UTC](https://forum.knime.com/t/help-with-service-principal-authentication-azure-sql/76654/17 "2024-01-19T14:18:35Z")
Thanks @AlexanderFillbrunn I will try this out! 🙂 ---
**Author:** ![Data\_consumer](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/d/58f4c7/32.png) [@Data\_consumer](https://forum.knime.com/u/Data_consumer)\ **Post date:** [January 26, 2024, 12:13pm UTC](https://forum.knime.com/t/help-with-service-principal-authentication-azure-sql/76654/18 "2024-01-26T12:13:04Z")
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 🙂 ---
**Author:** ![AlexanderFillbrunn](https://forum-cdn.knime.com/user_avatar/forum.knime.com/alexanderfillbrunn/32/357_2.png) [@AlexanderFillbrunn](https://forum.knime.com/u/AlexanderFillbrunn)\ **Post date:** [January 26, 2024, 12:15pm UTC](https://forum.knime.com/t/help-with-service-principal-authentication-azure-sql/76654/19 "2024-01-26T12:15:14Z")
Hi, Great, thank you for following up and letting me know. Good luck 🤞 Alexander ---
**Author:** ![system](https://forum-cdn.knime.com/uploads/default/original/1X/ab3ccf34482a0329361734a18199390177204f15.png) [@system](https://forum.knime.com/u/system)\ **Post date:** [April 25, 2024, 12:15pm UTC](https://forum.knime.com/t/help-with-service-principal-authentication-azure-sql/76654/20 "2024-04-25T12:15:46Z")
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.