I’ve been reading through connect to ms sql server by windows authentication and need some consolidated advice.
We are using KNIME server for a POC and I want to use SQL AD authentication. However, we are using server-side configuration for our KNIME end-users. Would the guide above (the very last post) when applied to the server work to make AD authentication work?
do you plan that all users use the same login when workflows are executed on the KNIME Server? The reason why I’m asking is that if you use SQL AD authentication the connection to the MS SQL Server is created for the Windows user that runs the KNIME process. On the client this is perfectly fine since every user is using her own machine with her account in which the KNIME Analytics Platform runs. However on the KNIME Server all workflows are executed with the Windows user the KNIME Server process runs in. So the connection to the SQL Server database would always be created for the KNIME Server windows user and not the different KNIME users that execute the workflow. If this is not intended you can not use windows authentication but either user name password based authentication or Kerberos based authentication with constraint delegation on the KNIME Server .
We need both - ideally anything running on the server would use sql auth service account, but in some cases we will probably use a temporary windows account while a service account is being created. Similar to Tableau publishing, we need both.
if the jobs on the KNIME server should all use the KNIME Server user windows account to authenticate against MS SQL Server this should be possible using the described settings in the KNIME executor.
However I do not understand what you mean with the temporary windows account and I’m not familiar with Tableau publishing. Could you please elaborate a bit on this.
We’ve been trying to get Windows A/D auth working using the “Customization profiles” feature, but we are having some trouble.
Do you know how to use server customization feature to push the sqljdbc_auth.dll file to the three file paths required under org.knime.binary.jre.win32.x86_64_18.104.22.168-b08?
unfortunately it is not possible to setup the native authentication via “Customization profiles” since it requires changes in the knime.ini file which is only loaded during startup.
What I would suggest is to setup one KNIME Analytics Platform/Executor to support native authentication which you can distribute by simply copy and paste the sqljdbc_auth.dll into the following folder:
The Microsoft SQL Connector node will then be able to load the dll-file automatically.
As an alternative you can also add the path to the dll-file in the knime.ini file of each installation by adding the following line to the end:
-Djava.library.path=<path to dll >
Is there a way to specify domain in the new SQL Connection node? The need is to be able to use an active directory user ID and password.
you can specify additional connection parameter via the JDBC Parameter tab of the corresponding DB Connector node such as the following: