Connection to Azure SQL with MFA Authentication

[andrew.dun]

Hi, I’m looking to connect to an Azure SQL database, with MFA authentication, using the Microsoft SQL Server Connector node and Driver Name: AzureSQL. I note the response to

SQL Database Connection when Authentication is set to MFA

Hi, So far I was connection via KNIME to a database in MS SQL Management Studio by using the “Database Connector” and configuring the node as follows: Database driver: com.microsoft.sqlsever.jdbc.SQLServerDriver Database URL: jdbc:sqlserver://xxxxxx.database.windows.net:1433;database=aaaaa;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;authentication=ActiveDirectoryPassword user name & password: myname@mycompany.com and my windows passw…

I’ve tried creating an Auth token using the Microsoft Authentication node, and then using this with the JDBC ‘accessToken’ parameter, but without any success. I’m thinking perhaps I would need to use some kind of external code to request a token, but that seems like a fairly complicated approach.

Note that I can connect to an on-prem SQL server instance without any problem, using Native Authentication. Also, I’m able to connect to the Azure SQL database without problems via Microsoft SQL Server Management Studio, using ‘Authentication: Azure Active Directory - Universal with MFA’.

I’m wondering if any new approaches may have become available since the previous discussion?

[tobias.koetter]

Hello Andrew,
you can use a new function in the JDBC driver to get the token and do the authentication. To do so you need to register the latest driver and some additional jars which is all explained in this workflow:

Connect to Microsoft SQL Server with Azure Active Directory Authentication –...

Once the driver is registered you can use the component from the workflow to establish the connection.

To make this easier we are planning to add a dynamic input port to the Microsoft SQL Server Connector node to pass in the token from the the Microsoft Authentication node.
Bye
Tobias

[andrew.dun]

Thanks Tobias, this is great. I’ve followed the instructions and that’s all working now

For the benefit of anyone reading this later, a few configuration errors that I made initially:

• When registering the driver, I needed to select db type = mssqlserver (rather than default)
• Specifically download mssql-jdbc-9.2.1, rather than a newer version such as mssql-jdbc-9.4.x
• After configuration, select the ‘sqlserver_9_2’ driver in the ‘Microsoft SQL Server Connector’ node in the KNIME Hub workflow (this will have changed to another value if ‘sqlserver_9_2’ did not exist when the workflow was first loaded
• After selecting the ‘sqlserver_9_2’ driver, re-enter the 5 required values in the JDBC Parameters settings