Question:

How to connect JDBC Consumer Stage to MS SQL Server via AAD or Active Directory?

Answer:

Our product teams are currently evaluating to see this could be added natively. Also, our Product Teams has this on the future Product Road Map for native support.

For now you will get the following error message if you try to use “authentication=ActiveDirectoryPassword” in your JDBC URL.

JDBC_06 - Failed to initialize connection pool: com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: Failed to load MSAL4J Java library for performing ActiveDirectoryPassword authentication.
: hikariConfigBean.connectionString

This is because the out of box Microsoft SQL Server Drivers comes directly from Microsoft. Microsoft does not package support directly for the authentication method ActiveDirectoryPassword.  For this to work you must acquire MSAL4J and it’s dependencies and add them to SDC’s java classpath for the given JDBC Stage. You can add the jars via the pipeline gui in the ‘external libraries’ tab.

This is currently classified under custom implementation and is currently not officially supported. For best efforts support, you’ll need to reach out to Microsoft, if you have difficulties acquiring dependencies. You’ll need to acquire and or compile dependency libraries to make it run.

Please see Microsoft Site for references:

Azure Active Directory authentication
https://docs.microsoft.com/en-us/sql/connect/jdbc/connecting-using-azure-active-directory-authentication?view=sql-server-ver15

• For other authentication modes, the below components must be installed on the client machine:

• If you're using the ActiveDirectoryPassword authentication mode, you need either Microsoft Authentication Library (MSAL) for Java and its dependencies for JDBC Driver 9.1 and above, or Microsoft Azure Active Directory Authentication Library (ADAL) for Java and its dependencies for driver versions before JDBC Driver 9.1. For more information, see the Connect using ActiveDirectoryPassword authentication mode section.

References:

https://mvnrepository.com/artifact/com.microsoft.azure/msal4j/1.11.3

https://jarcasting.com/artifacts/com.microsoft.azure/msal4j/1.11.3/

https://docs.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url?redirectedfrom=MSDN&view=azuresqldb-mi-current

https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?tabs=azure-powershell