SQL Database Connection when Authentication is set to MFA

KNIME Analytics Platform
1

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 password

and I was able to access the database and the tables.
Now, our corporate IT department changed the authentication in the SQL database to be:
Active Directory - Universal with MFA support

Of course the KNIME “Databas e connector” in KNIME does not load and give an error
could not create connection to database ID:3242 the security token could not be authenticated or authorized

How do I need to amend my KNIME connection to account for the change?

2

HI there,

sry for a delay on this one. Have you managed to solve it in the meantime or you still need help?

Br,
Ivan

3

Hi Ivan,

Not really, we haven’t tried again, but we also tried to figure out via Microsoft and haven’t heard back.
Do you know more?

Thanks
Manu

4

Hello Manu,

unfortunately it seems that no documentation exists for using MFA with the Microsoft SQL Server JDBC driver. The only thing I could find so far is the following information in the JDBC driver feature request:

Technically, DBeaver can implement AD MFA on top of the JDBC driver using the AccessToken authentication method. DBeaver can obtain the token itself via Azure AD REST APIs and pass the token to the driver. I do realize this comes with the overhead of maintaining that code. But this is exactly how Azure Data Studio has done it themselves on top of System.Data.SqlClient in .NET Core, which also does not support AD MFA itself but does support AccessToken.

So it seems to be possible to connect to the db using an access token.

Getting the access token
I could find the following information on how to obtain an access token via the Azure REST API:

Using the access token in KNIME Analytics Platform
Once you have obtained the token you can pass it on to the driver via the accessToken JDBC parameter in the dialog of the Microsoft SQL Server Connector node.

Bye
Tobias

1 Like
5

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

6

Hello everyone,
good news with the KNIME Analytics Platform 4.5 release the Microsoft SQL Server Connector node has now a dynamic input port that accepts the output of the Microsoft Authentication node. SO you can now use the Microsoft Authentication node to get the access token which is then automatically used in the Microsoft SQL Server Connector node for authentication.

Bye
Tobias

4 Likes