Unable to connect to Azure SQL database using DB connector.

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”

3 Likes

@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.

1 Like

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]

1 Like

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:

  1. Are you sure you have access to the database “AnalyticsPOC”? Tested in SSMS?
  2. Are your credentials saved in SSMS? If so, can you re-type the credentials in SSMS to make sure you are using the same credentials in Knime?
  3. Is this db user an LDAP user (such as using your Windows credentials that are linked to the db user)?

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)

@bruno29a

  1. I have access to AnalyticsPOC and am able to run queries on that DB in SSMS.
  2. No, the credentials aren’t saved. I retried entering the credentials multiple times but it always returns the same error.
  3. No, this is not a LDAP user and I am using SQL server authentication to login through SSMS.

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.

1 Like

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! :wink:

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:
image

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.

5 Likes

Nice @takbb , I think the use of instance should solve the issue.

1 Like

@takbb This is amazing! I was able to connect using the steps listed by you. Thank you so much for your help!

2 Likes

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! :wink:

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.

1 Like

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.

2 Likes

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

1 Like

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.

2 Likes

Apologies for my late response @takbb
Yes python works for me.
Thanks a lot for your detail description and work - highly appreciated. I am currently short on time but hopefully have the time during the weekend to have a closer look at all your valuable insights. Thanks again!
(That’s why I love this community so much)

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

Hello takbb,
thanks a lot for the comprehensive blog post as usual. Due to license restrictions we weren’t able to ship the official Microsoft JDBC driver with the standard KNIME installation. However we offer it as an extension that can easily installed. This extension comes with all the required files to also support native authentication. So once you have installed the extension native authentication works out of the box. The bad news is that the official driver that we ship with the extension is version 7.2. Which is fairly old by now. The problem was that starting with version 7.4 it wasn’t allowed to distributed the the DLL necessary for native authentication. The good news is that with the recently released version 9.4 it is now allowed to do so. So we are planning to update the build in driver to the latest version with the winter release.

The problem with changing the settings is a Java limitation that each DLL can only be loaded once and every time you “change” something in the driver configs we create new Classloader for each driver. We might add better checks if something has changed at all before reloading the drivers in the future.
Bye
Tobias

3 Likes