Hey! I just downloaded Knime and have a sample Azure SQL database that I want to connect to. I tried using “DB Connector” node and configured the connection setting but it returns an error saying “Execute failed: Cannot open server “defisolutions.com” requested by the login. The login failed.”
Can someone please help?
Hi @aarora and welcome to the Knime community.
Can you share the configurations that you used for the DB Connector?
Also, make sure you are using the proper db host and credentials. Are you sure that defisolutions.com is a db server?
Thanks for your response @bruno29a!! It looks like the multi factor authentication on that database was not going through. I thus created another database and used SQL server authentication this time with for an existing user. I am still getting an error that says: “Execute failed: Unknown server host name ‘10.56.50.4\POLYBASE’.”
However, I am able to directly log in to the server using the same credentials on SSMS.
Hi @aarora , as indicated by the message, the issue is with the hostname, not with the credentials, and you do appear to be using an invalid hostname.
Hostname can be IP Addresses, such as “10.56.50.4”, or your server name, for example “mssqlserver001”, or a hostname, for example “mssqlserver001.yourcompany.com”. In the background, the server name and the hostname will resolve to an IP Address. You can ping them for example to see what IP Address they resolve to.
“10.56.50.4\POLYBASE” is not a valid hostname. As a test, you can’t ping “10.56.50.4\POLYBASE”
@bruno29a I changed the host name to only use the IP address and now getting an error that says “Execute failed: Login failed for user ‘Knime’.” I can still log in to SSMS directly with same credentials.
hey @aarora
please if you can send the error log to provide more information would be nice.
Some tips:
try to make the connection using the other driver “Official Driver”.
If this option does not appear, and your KNIME version is 4.4, please use this link to install.
second option: Please try to make the same connection using node DB Connector.
and insert at the end of the connection string the option
integratedsecurity = true
at the end of your connection string that will be something:
jdbc:sqlserver://:;databaseName=[database]
Hi @aarora , when logging in via SSMS, I don’t think you connect to any database, but rather to the DB server. Once connected, then you use a database.
Some questions:
I couldn’t see the “Official Driver” option and when trying to drag & drop the latest version from the link above, it returns an error “Invalid URL”.
Also, using DB connector node didn’t resolve the issue. Please find the error log attached.Knime Error Log.txt (65.4 KB)
Hi @aarora , I am not sure why it’s not working then… if the same host, user and password are being used in SSMS and in Knime, and it’s working in SSMS, it should work in Knime - I am assuming that you are using the correct driver/connector, or it would have complained with a different message.
Hi @aarora,
I have a demo ms sql server database and I’ve been trying to make it “not work” in a similar way to yours. I think I have succeeded! 
I now have a database where from the same machine, I can login using SSMS but not with KNIME (or more specifically, any jdbc connection).
Going back to your original attempt to connect the host called “10.56.50.4\POLYBASE”, I was in agreement with @bruno29a that this was an invalid hostname.
What I now realise is that yes, your hostname is 10.56.50.4 but that POLYBASE is your “instance name”. So I needed to work out
(1) how to put the instance name into your jdbc connection string and
(2) how to achieve that using KNIME.
Through SSMS, my connection dialog looks like this:
where in my case:
host: takahenas
port:1433
instance:TAKDEV
The database that I connect to is called knimedemodb
As you use jTDS, this is what I am going to use.
What I found is that the jTDS connection string that worked for me was this, using my demo server so I don’t mind showing the full string here:
jdbc:jtds:sqlserver://takahenas:1433/knimedemodb;instance=TAKDEV
So using your credentials, I believe the equivalent connection string you are aiming for is:
jdbc:jtds:sqlserver://10.56.50.4:1433/AnalyticsPOC;instance=POLYBASE
To create that connection string, you will need to have your details on the Microsoft SQL Server Connector as follows:
but crucially, you need to tell it the name of the “instance” which is POLYBASE
Enter the details by selecting the JDBC parameters tab and entering the information there as follows:
Alternatively, you can use the DB Connector node instead, and enter the JDBC connection string in full there
I hope that helps resolve your connection problem.
Nice @takbb , I think the use of instance should solve the issue.
@takbb This is amazing! I was able to connect using the steps listed by you. Thank you so much for your help!
Great work @takbb
Have you tried the same way with native authentication? Does that work here as well?
br
Thanks @Daniel_Weikert, do you mean for example “Windows authentication”? No I haven’t tried that out. Maybe a challenge for next week!
I have tried Windows authentication in the past, unsuccessfully though, and I thought that it could not be done, hence why I was asking @aarora if the db user was an LDAP user as a reason for failed authentication.
I’d be glad to see if it’s possible.
I’m going to park a few links on the subject here so that I can find them next week (or some time in the future) , and also they may be of use to anybody else who wants to investigate.
I note mentions of additional DLLs being required.
Thanks a lot @takbb
On my local system I face issues for months connecting to sql server (local SSMS)
I have used python instead but it still drives me nuts
Hi @Daniel_Weikert, so you got python to work with Windows Authentiocation?
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.
