Microsoft Authentication for MSSQL

[ipazin]

Hello there,

did anyone managed to connect to on premise MSSQL using Microsoft Authentication node? Should it be possible?

SQL Database Connection when Authentication is set to MFA

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

Br,
Ivan

[AlexanderFillbrunn]

Hi Ivan,
The MS Authentication node uses OAuth 2.0 for authentication and acquires a token it then passes on to the database. On-prem you probably do not have the right authentication endpoint for the token and so my guess is that it will not work. If an OAtuh 2.0 endpoint is available, it might be worth a shot configuring it under the “Advanced” tab of the Microsoft Authentication node.

Kind regards,
Alexander

[ipazin]

Hi Alex,

tnx. I thought so. Anyways will give it a shot. But do I need to request specific access or Azure SQL Database should do it?

Additionally do you know is there a way to use native authentication with different windows user from the one I’m logged in? Username/password expects local DB user…

Br,
Ivan

[AlexanderFillbrunn]

Hi,
At least for MS SQL on Azure you need to request the correct scope to be able to use the token for the DB. I am not sure how that works with an on-prem SQL Server. Native authentication only works with the logged in user, as far as I know.
Kind regards,
Alexander