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
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?
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:
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.
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