SQL Server Connector not working with Official driver and Native Authentication

Hi everybody!

I can see some peripherally related posts on this issue but none that replicate my exact issue. I am using KNIME 4.5.2.

Following the commens on the topic SQL Server Connector not working with Official driver and Native Authentication , and writing the parameter that solved the issue ;integratedSecurity=true, I have the following error message: ERROR DB Connector 0:1964 Execute failed: Cannot open database “[xyz]” requested by the login. The login failed. ClientConnectionId:50490a94-73ec-40dc-8fa8-6c7cfb4be273

The DB Connector node tries to do connection for 10 seconds (ave) but then the error message appears.

What I should to do?

Thank you all a lot and greetings from Paraguay!

Hi @sisvok , and welcome to the KNIME community.

Could you post screenshots of your sql server connector node parameters - the main connection settings tab and also the jdbc and advanced setting tabs. You may wish to obscure any sensitive connection details.

Oh and just to double check, can you confirm you are able to connect using Windows Authentication from other software, eg SSMS

2 Likes

Hi @takbb and thanks a lot!

Well, I’m using the DB Connector node, not the Microsoft SQL Server Connector. But I’ll post parameters from SQL Server Connector.

And how can I know that I’m able to use Win Authentication from third party apps? I’m not from IT.

Thank you!

Sergio Samudio V.

Adding information, I can connect using user and password, and also using Win Authentication with jTDS driver, but not using the official one.

Hi @sisvok, sorry I was assuming you were using the SQL Server connector node because of the link you posted, but I see now that you said you are using the DB Connector node.

The jdbc parameter integratedSecurity=true is the parameter that tells the jdbc driver to use Windows Authentication so that it connects using your network/domain username rather than a dedicated sqlserver username/password, so if you have included that parameter then it will be trying to use windows authentication which is why I was trying to establish that this is what you were expecting, and that it maybe worked with another app, but if you don’t have other apps to try it with, then that’s fine but it’s just a common trouble-shooting first step.

You say that you connect with a username and password (Sql Server Authentication) and that you can connect with Win Authentication using the jTDS driver but not the official (Microsoft) Driver. This sounds like an issue from a while back in that to make Windows Authentication work with the microsoft driver it was necessary to also install (outside of KNIME) the sqljdbc_auth.dll file and other things.

According to the Database Extension Guide for KNIME 4.5, the necessary files for using WIndows Authentication (sqljdbc_auth.dll or ntlmauth.dll ) are bundled with KNIME with the jTDS driver, and also with the official Microsoft Driver, but in the case of the Microsoft driver only if it is installed via the plugin . I believe there are licensing issues with the Microsoft driver which mean it cannot be simply installed as standard, so you need to install it and acknowledge a specific licence agreement, which is why there is this additional step.

I’m guessing that you are currently using the DB Connector node because you have tried to load the Microsoft driver separately. If that is the case, I’d suggest taking a look at how to install it in the above link, which is essentially telling you to use “File - Install KNIME extensions” and then select the driver as follows (screenshot from KNIME 4.6):

(and by the way, I didn’t know about this installer for the drivers until about 30 minutes ago! :slight_smile: )

In KNIME 4.6, for example, as standard the following jTDS driver is available on the Microsoft SQL Server Connector node:

But after following steps to install the Microsoft driver via the Software installer, it shows the following drivers:

I think this is similar in KNIME 4.5

Unfortunately I have no SQL server database at the moment to try this out with, but please let us know if this helps.

2 Likes