Login failed for user ''.

[andi_braun96]

Hello all,

I have an issue with connecting to a MS SQL 2016 Server with the Microsoft SQL Connecter Node.
I’m using native authentication to connect to the server.
I checked the server name, port and the DB name and they are correct.

After executing the node I get this error:

The error log from Knime:
eclipse.buildId=unknown
java.version=11.0.10
java.vendor=AdoptOpenJDK
BootLoader constants: OS=win32, ARCH=x86_64, WS=win32, NL=en_US
Command-line arguments: -os win32 -ws win32 -arch x86_64

org.eclipse.core.net
Error
Wed Oct 26 13:55:28 CEST 2022
WinHttp.GetProxyForUrl for pac failed with error 'The proxy auto-configuration script could not be downloaded
’ #12167.

Error Log from the SQL Server:
Login failed for user ‘’. Reason: an attempt to login using SQL authentication failed. Server is configurated for Windows authentication only.
Error: 18456, Severity: 14, State: 58

What I’m not understanding is that the all the settings in the MS SQL Connector node are set for Windows Authentication but still get an error that a SQL authentication is expected.

Can anyone help with this one?

Best regards
@andi_braun96

[takbb]

Hi @andi_braun96

Connecting to SQL Server using windows authentication is something that periodically crops up here on the forum!

If you haven’t found them already, may I suggest starting with the following threads and if you still haven’t managed to resolve it, come back and let us know, and we can try to work through the steps required.

No connect to ms sql server by windows authentication

Hi @m_amann and welcome to the Knime Community. Can you show us how you are using the Connector? Can you show us the settings you are using? For using your windows credentials to connect to MSSQL, you should use the None/native authentication option for Authentication: [image] For integratedSecurity, if it’s needed, you can add it as a JDBC parameter like this: [image] Click on the Add button, and it will allow you to add an entry. Just add integratedSecurity as Name and true as Value

SQL Server Connector not working with Official driver and Native Authentication

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. [1] [2] [3] Thank you! Sergio Samudio V.

Unable to connect to Azure SQL database using DB connector.

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

Of course if you have already found those, then my apologies. Please post some screenshots of the config of your MS SQL Connector, including the jdbc parameters, and describe the steps you have taken so far as often it is something trivial that is missing or very slightly incorrect.

And lastly… the thing I should always ask… are you able to connect to SQL Server using WIndows Authentication outside of KNIME?

[andi_braun96]

Hi @takbb,

thank you for your reply.

@takbb

are you able to connect to SQL Server using WIndows Authentication outside of KNIME?

Yes, I am. I have access to the server over SSMS with Windows Authentication. It also works over Power BI Desktop and Database Connector of Excel, Access etc.

@takbb

Of course if you have already found those, then my apologies. Please post some screenshots of the config of your MS SQL Connector, including the jdbc parameters, and describe the steps you have taken so far as often it is something trivial that is missing or very slightly incorrect.

Also thank you for sharing the links. I also tried it for a longer time to make it work with all the settings proposed in those links but it still doesn’t work.

Also I realized that there were some changes on the SQL server. Now Force Encryption = true and the Extended Protection = required are set.

Before this changes everything worked fine with the jTDS driver.

I tried it with the jTDS driver with those parameters:

With and without integratedSecurity = true I get this error in Knime:

The SQL server error is:

And

SSPI handshake failed with error code 0x80090346, state 46 while establishing a connection with integrated security; the connection has been closed. Reason: The Channel Bindings from this client are missing or do notmatch the established Transport Layer Security (TLS) Channel. The service might be under attack, or the data provider or client operating system might need to be updated to support Extended Protection. Closing the connection. Client’s supplied SSPI channel bindings were incorrect.
Error: 17806, Severity: 20, State 46.

I’m connected with the company via VPN, Knime and my operating system is up to date.

With the newer JDBC driver v9.4 installed over the KNIME Extensions the settings look like (without adding some JDBC parameters):

I get the error in Knime:

The SQL server error is:

Sooooo, my thoughts are here now:
Since the SQL server was updated on Force Encryption = true and Extended Protection = required I have to set certain parameters to be able to establish a connection, this is clear so far.
But which one? Do I have to add an application name on the SQL server and provide the application name also with the JDBC parameters?
Am I wrong?

Thanks in advance for helping me!

Best regrads,
@andi_braun96

[takbb]

Hi @andi_braun96 , do you have any other jdbc client application you could try from the same machine that you are running KNIME from, to see if that gets a connection or results in the same message. (e.g. the opensource Squirrel SQL client (www.squirrelsql.org) or some other jdbc test tool)?

That might help rule out (or rule in!) any KNIME-specific config issues.

Incidentally, I just found this on a web search

Connection when both "Force Enryption" and "Extended Protection" are active

It still seemed to be an issue back in September and from my quick read, it looks like it isn’t resolved and is possibly down to the jdbc driver not supporting the sql server protection options. I don’t know though if this is the same jdbc driver you are using as I’m not that close to sql server jdbc issues generally.

[andi_braun96]

Hi @takbb, that’s it.

neither the JDBC drivers nor the jTDS driver are able to establish a connection to the MS SQL Server with the settings ‘Force Encryption = true’ and ‘Extended Protection = required’.

Also, there was a trial to implement the Channel Binding to the JDBC drivers but the JDK does not provide a way to get the clientVerifyData byte array in the TLS finished message (this byte array is used to calculate the channel binding information which is passed to the server in authentication) and it is also not planned to add this to the JDK in the nearest future…

Thanks for your help!

All the best,
@andi_braun96

[takbb]

Hi @andi_braun96 , glad to help, and sorry that on this occasion the “solution” wasn’t a real solution, but at least we now know the reason!