Cannot connect to SQL Server within Docker (but can with sqlcmd)

[isaackogan]

Expected behaviour:

Within docker, node-mssql should be able to connect to the SQL Server. The docker container is being run with the flag --network="host", so it should be the same network. The identical Node.JS script running out of docker is able to connect to the server.

Actual behaviour:

node-mssql hangs and times out. This screamed 'issue with docker config!' so I continued to investigate instead of making an issue, since I assume issues are on my end.

What I've done to test:

I downloaded some SQL tools (i.e. sqlcmd) and found that connecting to the server WAS possible with the following command, within the container:

sqlcmd -S 'server.host.name' -U 'user' -P 'pwd' -d 'Database'

This is when I decided to make an issue, because it seems that a connection is possible with sqltools (i.e. it's not the docker network settings causing a problem), but not with node-mssql

Here is my quite rough Dockerfile: https://pastebin.com/0qJ2Egvy

Configuration:

{
        user: 'user',
        password: 'pwd',
        server: 'server.host.name',
        trustedConnection: false,
        driver: 'msnodesqlv8',
        validateConnection: false,
        port: 1433,
        pool: {
            max: 50,
            min: 10,
            idleTimeoutMillis: 30000
        }
    }

Software versions

• NodeJS: Node 16
• node-mssql: latest
• SQL Server: 2017

Addendum

Please let me know any additional debug I should provide. Turning on debug mode for node-mssql's logger was unhelpful.

[dhensby]

So you can connect to the SQL Server running inside the docker container from the host machine using the sqlcmd util, but you can't connect from scripts inside the docker container? is that right?

If you "ssh" into the docker container (docker exec -it <name|id> bash) can you connect using sqlcmd tools there?

My first guess is that the server hostname is wrong and you need to be using server: 'localhost' or server: '127.0.0.1' in your config, because (unless the docker image knows its own hostname) it won't be resolving properly... but that wouldn't explain hanging/timeouts...

Does it work with the tedious driver?

[isaackogan]

Hi, not quite. My apologies for the confusion.

1. An SQL Server is running on a separate server on the same network.
2. Your Node-MSSQL connector was running within Docker and unable to connect.
3. Using a python library, also within Docker, I was able to connect.

What my initial comment is saying is that I attached to the terminal of the docker container where node-mssql was running, installed sqlcmd, and was able to connect to the server with the same config that fails in node-mssql

The docker container already has --network="host", which is necessary as the SQL Server is not public to the internet, only the network both this VPS and the SQL Server are running on.

Given that it works in Python, I could not see why it should not work in Node. My thinking was that it could perhaps be missing system drivers, but my Dockerfile matches the similar ones that I've seen that use your library.

Isaac

[dhensby]

Ok. Sorry if this is a bit tedious, but just so I'm clear on this...

You have 1 docker container running an sql server.

You have another docker container running your node app.

Your node app cannot connect to the sql server.

You can connect from the docker container running the node app using other tools (sqlcmd and python apps).

Are those statements correct?

---

Sorry, I've re-read and think I understand (it's late and a Friday, apologies)

You have an sql server running on the network.

You are running docker container on your machine which is hosting a node app.

Your node app cannot connect, but sqlcmd and python can (when run in the container).

So what is the exact error thrown by the mssql library?

Again, does it work with the tedious driver?

[isaackogan]

Hi, I was wondering how to go about verifying this; you are correct in your understanding of the goal.

Ok. Sorry if this is a bit tedious, but just so I'm clear on this...

Unintended but funny pun, by the way.

   sqlConn = await sql.connect({
            connectionString: get_dsn(config["testConfig"]), // Built from below JSON config
            port: 1433,
            pool: {
                max: 50,
                min: 10,
                idleTimeoutMillis: 30000
            }
        });

That's my config currently, and works outside of docker.

{
    "Driver": "ODBC Driver 17 for SQL Server",
    "Server": "localhost",
    "Database": "ResearchTest",
    "Trusted_Connection": "no",
    "uid": "AdminTest",
    "pwd": ""
  }

That is my DSN string. Changing the "Driver" field to "tedious" fails, after importing "mssql/tedious". I assume I am misunderstanding something?

It says `TypeError: The "config.server" property is required and must be of type string.`. Does tedious not support connection strings?

[isaackogan]

Successful with the following config for connection object-got rid of DSN, with tedious driver. Could not get to work with msnodesqlv8. Ditto!

{
            server: "host.docker.internal",
            database: "ResearchTest",
            user: "AdminTest",
            password: "",
            port: 1434, // non-default port on my machine for some reason lol
            trustServerCertificate: true
}