The documentation for the MS connector says there is an option called “Client/Application Secret Authentication” but it doesn’t appear in the options menu. Any ideas? I’m using Knime 4.6.1 and just reinstalled the package containing this node.
Hi @afaik
thank you for your question!
Could you please check if the requirements to use this mode are fulfilled? You can find them in the Node details → “Options” tab → “Client/ Application secret authentication”:
Before using this mode, (1) an Azure AD application has to be registered, (2) an application secret has to be created, and (3) an admin has to grant application permissions (not delegated) to the application service principal in the Azure AD tenant to access. In this mode, Azure/Office 365 cloud services are accessed with the application identity and not on behalf of a user. See here for more information.
Please, let me know if requirements are fulfilled and the option is still unavailable.
Best,
Daria
1 Like
Hi @darspir
All of these requirements are azure setup requirements so the ‘authentication mode’ should show “Client/ Application secret authentication” regardless of these requirements. However, I have already setup everything as required and have tested the login credentials successfully. Any other ideas I can try?
Hi @afaik ,
thanks for writing us back.
I have asked internally and found an answer: you do not see the option since it was added only with 4.7 release. If you look in the Description tab in your KNIME Analytics Platform 4.6.1, you will see that there are only 4 options avaliable:
In order to use the Client/Application Secret Authentication mode, you need to update KNIME Analytics Platform to 4.7.
Best,
Daria
2 Likes
Hi,
I’m also trying to make this work in 4.7. One question I got from my Azure admins was ‘which application permissions exactly are needed’? The Microsoft documentation on this topic shows this:
source - Set up Azure Active Directory authentication for SQL Server - SQL Server | Microsoft Learn
However, the KNIME documentation instructs to use only application permissions and not delegated ones. Is Directory.Read.All sufficient, or do you need other/additional ones to replace the delegated permissions shown in the image?
3 Likes
Hi @alinkdaniel ,
thank you for your question.
You can see more about the permissions required and when/why they are required in the Node description:
Node details > Options > Interactive authentication.
Let me know if questions remain.
Best,
Daria
1 Like
I’m sorry, I didn’t specifiy it was for KNIME Server. Starting with Excecutor version 4.7, Microsoft Authentication has the option of using a Client/Application secret instead of user credentials.The description says:
Before using this mode, (1) an Azure AD application has to be registered, (2) an application secret has to be created, and (3)an admin has to grant applicationpermissions (not delegated) to the application service principal in the Azure AD tenant to access
When I showed this to my Azure admins, they told me it is not specific enough. We would really appreciate some more direction on how to use this new mode.
1 Like
Hi @alinkdaniel ,
I wanted to let you know that I am working on the solution for you. I or one of my colleagues will contact you once we have something.
Best,
Daria
2 Likes
Hi @alinkdaniel
apologies for the late reply.
DISCLAIMER: I have not actually tested the steps below with an on-premise SQL Server. According to Microsoft’s own documentation however it should work that way. If it does not work you should ask Microsoft support on how to give an Azure AD service principal access to an Azure-enabled SQL Server.
To my understanding, the screenshot you showed explains part of the server-side SQL Server configuration to enable Azure AD authentication – which is a prerequisite, but nothing that KNIME can help you with (-> questions should go to Microsoft support).
Instead, you need to know how to create a service principal to use for authentication in KNIME. For this, the service principal needs application permissions (which are a different kind than delegated). The Microsoft Authentication node description provides the answer:
However this application permission is well hidden in the Azure Web UI:
Pressing “Load more” several times will make “Azure SQL Database” show up in the list. (Let’s for a minute ignore the fact that you probably have an on-premise SQL Server, and not an Azure SQL database). Click on it, which gives you the following screen:
After having pressed “Add permissions” there is one more click on “Grant admin consent for…” necessary:
This allows the Microsoft Athentication node to get an access token for the service principal:
To allow the service principal to actually do anything in the database, it needs to have a DB user, which needs to be created with a something like this (by a DB admin):
CREATE USER [service-principal] FROM EXTERNAL PROVIDER
Hope this helps,
Björn
4 Likes
Hi Björn,
This looks like what I was looking for, thank you so much! It does indeed concern an Azure SQL database, so hopefully these steps will be enough. I will be testing this next week and will post an update on my findings.
Daniel
2 Likes
Good news, it worked! Thanks again for the elaborate explanation. It would be helpful if some of this information could make it into the node description, I think.
1 Like