Well I have good news and bad news. I’ve been playing with Knime AP 4.4 which I had to install on my DB server in order to try out Windows Authenticaton. As the only place I have access to a SQL Server installation is on my home network which doesn’t have any domain controller or AD, I cannot just try it out from a connected PC.
Working in Windows Authentication mode requires that I be on the server machine, or on a machine which is part of a controlled domain (and not just any old Windows PC that happens to be on my network) in order that I can connect to the database.
The good news is that I managed to get Windows Authentication to work both with the jTDS driver (which appears to be the only MS SQL driver shipped with KNIME AP4.4) and with a download MS SQL Server JDBC driver (v9.2.1).
The good news continues in that with the jTDS driver, Windows Authentication just seems to work without any issues. I simply selected “None/native authentication” and it worked first time and every time!
The not so good (aka bad) news is that the official MS SQL Server jdbc is a real Pain-in-the-Apex! Yes you do need to put a DLL on the classpath or in your system path to make Win Authentication work, but even then boy is it fragile!
If you configure it in Knime-preferences-database drivers, and run it, and it works… then all well and good… but if you change the details in preferences (or in fact even if you just open the driver config in preferences change nothing but simply say “Apply and Close” then from that point on it tells you that the driver “is not configured for Integrated Authentication” and steadfastly refuses to connect the the database. The only way I have found to reset this situation is to restart KNIME AP. I discovered the reason, by looking in the KNIME log: the Microsoft DLL gets loaded by a java classloader, and it appears that when you modify the underlying config for the driver, this DLL gets locked by the old classloader, and cannot be loaded by what I’m guessing is a new classloader for the (apparently) new configuration. I don’t know if that’s a bug in KNIME or in Java, but it’s certainly not a great feature!
When I get a chance, probably in the next few days, I’ll put together an article under “Knowledge Sharing” (rather than labouring this thread), of what I did to get authentication to work, and what issues I discovered. For anybody needing Windows Authentication in the meantime, I have to say that at the moment the jTDS driver appears to be the way to go.
I find it hard to recommend going jTDS, though, as it hasn’t been updated since 2013, meaning it officially supports only SQL Server up to 2012. That I guess may or may not be an issue and depends on what feature changes there have been with later versions of SQL Server (if any) that affect the driver, but I’m guessing the reason why it ships with KNIME AP 4.4 is something to do with its reliability and ease-of-use compared with the official driver from MS.