MSSQL connection problem with instance

Roberto_Jr · September 29, 2022, 2:25pm

Hello

I’m having trouble configuring a connection using MSSQL, with instance, and port different from the default. Has anyone ever had this problem?

Example: local_ip\intancename

My n8n server in docker inside same network with sql database.

Share the output returned by the last node

Information on your n8n setup

n8n version: 0.195.5
Database you’re using (default: SQLite): Sqlite
Running n8n with the execution process [own(default), main]:
Running n8n via [Docker, npm, n8n.cloud, desktop app]: Docker

Jon · September 29, 2022, 2:39pm

Hey @Roberto_Jr,

Welcome to the community

That looks like the connection has timed out so the first thing I would do is check that the SQL Server is configured to accept connections from the host. It looks like you are using an instance name as well are you using full SQL or SQL Express? I know out of the box SQL Express doesn’t accept connections easily and you have to sometimes enable the TCP/IP setting in SQL Server Manager so that could be worth looking at as well.

I take it the local IP is not 127.0.0.1 as well?

Roberto_Jr · September 29, 2022, 2:45pm

Thanks

Using SQL not SQL Express.
I´m using the SQL server IP local same as using in other our systems.

Jon · September 29, 2022, 3:25pm

Hey @Roberto_Jr,

Is there anything network wise going on? It is timing out after 15 seconds so it could be that it can’t find its way to the host or it is waiting for a response back.

Roberto_Jr · September 29, 2022, 4:34pm

@Jon ,

Everything is right with the network. I believe it is some bug on the n8n.

Is there any log that I can see n8n executing when it fires to the sql server?

Ps. N8n it’s sensational, we’re trying hard to make it work, we really like the way you use the tool.

Jon · September 29, 2022, 4:49pm

Hey @Roberto_Jr,

There might be something in the console which you could see with the docker logs command, I do have a theory though if you are sure the network is all good the last 10% of cases I have seen over the years with that issue is down to the formatting for using an instance.

Just for fun can you try local_ip\\instanceName and also leaving the port empty and see if that works.

Roberto_Jr · October 3, 2022, 11:52am

Hi @Jon

Really, i believe is a bug. Everything is correct with the network.
I’m using docker, is there any help in this regard?

My compose file:

version: '3.3'
services:
    n8n:
        container_name: n8n
        restart: always
        ports:
            - '5678:5678'
        environment:
            - N8N_BASIC_AUTH_ACTIVE=true
            - N8N_BASIC_AUTH_USER
            - N8N_BASIC_AUTH_PASSWORD
            - N8N_HOST=${SUBDOMAIN}.${DOMAIN_NAME}
            - N8N_PORT=5678
            - N8N_PROTOCOL=https
            - WEBHOOK_URL=https://${SUBDOMAIN}.${DOMAIN_NAME}/
            - GENERIC_TIMEZONE=${GENERIC_TIMEZONE}
            - TZ=${GENERIC_TIMEZONE}
            #- WEBHOOK_TUNNEL_URL=${SUBDOMAIN}.${DOMAIN_NAME}
            - N8N_EMAIL_MODE=${N8N_EMAIL_MODE}
            - N8N_SMTP_HOST=${N8N_SMTP_HOST}
            - N8N_SMTP_PORT=${N8N_SMTP_PORT}
            - N8N_SMTP_SSL=${N8N_SMTP_SSL}
            - N8N_SMTP_USER=${N8N_SMTP_USER}
            - N8N_SMTP_PASS=${N8N_SMTP_PASS}
            - N8N_SMTP_SENDER=${N8N_SMTP_SENDER}
            - NODE_ENV=production
            - N8N_LOG_LEVEL=${N8N_LOG_LEVEL}
            - N8N_LOG_OUTPUT=${N8N_LOG_OUTPUT}
            - N8N_LOG_FILE_LOCATION=${N8N_LOG_FILE_LOCATION}
            - N8N_LOG_FILE_MAXSIZE=${N8N_LOG_FILE_MAXSIZE}
            - N8N_LOG_FILE_MAXCOUNT=${N8N_LOG_FILE_MAXCOUNT}
        volumes:
            - ${DATA_FOLDER}/n8n-local-files:/files
            - ${DATA_FOLDER}/n8n-local-files/database:/home/node/.n8n
        image: n8nio/n8n
        command: n8n start

Jon · October 3, 2022, 12:12pm

Hey @Roberto_Jr,

Everything looks fine in the compose file, What happened when you tried local_ip\\instanceName and an empty port?

I know you have said everything is correct and I suspect it probably is now, The connection is still timing out so it could still be that it is waiting for a response from the server or it can’t find the instance for some reason.

If you try with a hostname that is wrong or an incorrect username / password do you get a different error?

Roberto_Jr · October 3, 2022, 12:52pm

Hi @Jon

When local_ip\\instanceName and an empty port:

NO, if hostname wrong the message is SAME.

In the browser console:
Its normal put ‘\’ ‘\’ instead of ‘\’ ?

Jon · October 3, 2022, 2:23pm

Hey @Roberto_Jr,

That looks like the node might be automatically setting the \ then to escape the \ which is handy. What version of MSSQL is it?

That looks like you still have the port set, The package we use says in the documentation for it not to set a port.

Roberto_Jr · October 3, 2022, 2:53pm

Hi

In the previous attempt, I left without the door, as you asked. This second screen with the door is from the previous attempt. But the first screen is without port set.

Using 2012 version.

Jon · October 3, 2022, 4:40pm

Hi Roberto,

Last thing I can think of for now…

Empty Port and TDS to 7_4 instead of 7_3_B and see that does the job, I am working on setting up a SQL Server but the last one I tested on a few weeks back the node worked so I am fairly confident the node is fine.

What is really odd though is even with an invalid host you are getting the same error, I would have expected something like…

It is very strange.

Roberto_Jr · October 4, 2022, 7:35pm

Hi Again

The version of SQL is 2019 with problem.
We try another server with 2008 sql server version, and works.

Jon · October 5, 2022, 8:54am

Hey @Roberto_Jr,

So is that 2019 and 2012 that fail and only 2008 that works? Did you try changing the TDS setting? I believe that option was recently added so that could be causing the problem.

Roberto_Jr · October 5, 2022, 11:59am

Hi

We tried to install version 0.195.2 where it didn’t have TDS and it didn’t work either.

On this same server we have the BI metabase, and it connects to SQL normally, but we had to change settings in JAVA in TLS 1.1

We will install n8n on another server of ours with the 2019 version that we managed and installed from the beginning.

I’ll be back with the answer.

The current situation so far:
2019 doesn’t work
2008 works

Roberto_Jr · October 5, 2022, 12:32pm

Hi again

End of story, not work in another SQL server 2019.

We think the problem is about dynamic ports.

Thank @Jon for your support.

christiaanwesterbeek · October 11, 2022, 6:59am

Until yesterday we had no problems connecting from n8n to our SQL Server 2017. Then yesterday we upgraded n8n from 0.194 to 0.195.5. And our SQL Server connections are now not working anymore. After the timeout we get

ConnectionError: Failed to connect to 178.255.193.119:1433 in 15000ms
at /usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/node_modules/mssql/lib/tedious/connection-pool.js:78:17
at Connection.onConnect (/usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/node_modules/tedious/lib/connection.js:1051:9)
at Object.onceWrapper (node:events:628:26)
at Connection.emit (node:events:513:28)
at Connection.emit (node:domain:489:12)
at Connection.emit (/usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/node_modules/tedious/lib/connection.js:1079:18)
at Connection.connectTimeout (/usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/node_modules/tedious/lib/connection.js:1284:10)
at Timeout._onTimeout (/usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/node_modules/tedious/lib/connection.js:1229:12)
at listOnTimeout (node:internal/timers:559:17)
at processTimers (node:internal/timers:502:7)

We had and still have TLS disabled. We connect to a server IP and port.

I’m not sure if OP still has problem, but it seems that something after 0.194 has caused SQL Server connections in some situations to stop working

MutedJam · October 11, 2022, 7:09am

Hi @christiaanwesterbeek, I just tried connecting to SQL Server 2017 without issues on both [email protected] and the current version 0.197.1 (I tried both hostnames and IPs). Is your firewall perhaps dropping these connection attempts? Are you using n8n cloud and have whitelisted its IPs on your end by any chance? If so, your whitelist might need an update (or better, replace it with other auth methods, see our docs for details).

christiaanwesterbeek · October 11, 2022, 7:22am

Thank you. You maybe right since there was only 1 commit to the mssql node since 0.194 and that only changed the tdsVersion but the default isn’t any different than before.

It maybe so (I will ask) that our firewall only allows a few n8n IP addresses. That page you are referring to used to also present a range of IP addresses to whitelist, but that page is now updated and the range is gone. Google cache says:

Recommended practice is to whitelist 20.79.72.0/24 , but if more strict … 20.79.72.36 , and 20.79.227.226 must be whitelisted.

Short question, what range shall I ask the firewall guys to whitelist for n8n?

MutedJam · October 11, 2022, 7:23am

We just use the three IPs listed on the page, so need to specify a range. These IPs might change without notice though as n8n cloud scales up (and we can’t predict which IP a new NAT might get from Azure).