1

I am trying to connect to a Docker container running SQL Server using pymssql and I am getting the following error,

Can't connect to db: (20009, b'DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist (localhost)
Net-Lib error during Connection refused (111)
DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist (localhost)
Net-Lib error during Connection refused (111)\n')

Now, I know similar questions have been asked before, such as the one given below,
Database connection failed for local MSSQL server with pymssql

However, most of the solutions given revolve around opening up the Sql Server Configuration Manager and making changes through the UI or allowing network traffic to pass through. However, I am not entirely certain how to do this with a Docker container.

How can I resolve this? Is there a different SQL Server I should run? Or a different Python package I should use to establish the connection?

I have spin up my container based on the instructions given here,
https://learn.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-ver16&pivots=cs1-bash

Given below are the parameters I am using to establish the connection,

{
    "host": "localhost",
    "port": "1433",
    "user": "SA",
    "password": "<my-password>",
    "database": "TestDB"
}

Update: I am actually trying to connect to the SQL Server Docker instance from within another container. It is the MindsDB container. This MindsDB container is, however, using pymysql to establish the connection.

Update: Given below are the two commands I am using to run my containers.
SQL Server:

sudo docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<my-password>" \
   -p 1433:1433 --name sql1 --hostname sql1 \
   -d \
   mcr.microsoft.com/mssql/server:2022-latest

MindsDB:

docker run -p 47334:47334 -p 47335:47335 mindsdb/mindsdb
Minura Punchihewa
  • 1,498
  • 1
  • 12
  • 35
  • are you trying to connect from inside the docker container, or from outside? SQL Server and localhost don't always play well together - you can try changing the hostname to (local) if you're connecting from inside the docker container - if you're connecting from outside the container, you'll need to use the ip or hostname assigned to the container - this might help -> https://stackoverflow.com/questions/50166869/connect-to-sql-server-in-local-machine-host-from-docker-using-host-docker-inte – Allan Elder Feb 03 '23 at 20:16
  • Two potential issues here: 1) Where is the Python script executing? Docker containers are essentially their own little Linux VMs so from a networking perspective `localhost` or `127.0.0.1` will only work if the script running inside the SQL Server container itself, or if it's on the Docker host itself. If it's running inside another Docker container you might be able to use `host.docker.internal` but, really, the containers should be running inside a Docker Composition together and using DNS/host names. 2) Have you created the `TestDB` database yet? You can only login to existing databases. – AlwaysLearning Feb 04 '23 at 06:24
  • @AllanElder I added an update to the question. – Minura Punchihewa Feb 04 '23 at 12:47
  • @AlwaysLearning I've made an update to the question. The Python script is executing within another container (MindsDB container) and it is using `pymysql` to establish the connection. Yes, the database is created. I logged into the container and tested it. – Minura Punchihewa Feb 04 '23 at 12:48
  • `localhost` in the MindsDB container resolves to itself, not the Docker host nor any other Docker container. Unless there's an SQL Server instance running inside the MindsDB container then it's not going to be able to connect to anything on `localhost,1433`. You might be able to connect on `host.docker.internal,1433` however. – AlwaysLearning Feb 04 '23 at 14:30
  • How did you run the SQL Server container? Did you include `-p` or `--publish` parameters to make the container's port 1433 available on the host? [Edit](https://stackoverflow.com/posts/75340307/edit) your question to include its `docker run` command, if you can. Connecting between standalone containers is always more difficult because the default `bridge` network doesn't have any DNS service running in it. Having DNS available to resolve container names is one of the benefits of using Docker Compositions to logically group services and containers together. – AlwaysLearning Feb 04 '23 at 14:33
  • @AlwaysLearning I have updated the question with my Docker commands. – Minura Punchihewa Feb 06 '23 at 18:41
  • @AllanElder `host.docker.internal` did not work either. – Minura Punchihewa Feb 06 '23 at 18:43
  • Are you sure that the SQL Server process is running in its container? You said you'd created a new `TestDB` database in it, but if the `MSSQL_SA_PASSWORD` doesn't meet [password complexity requirements](https://learn.microsoft.com/en-us/sql/relational-databases/security/password-policy) the service will not start successfully so clients will be unable to connect to it. Does the SQL container display any log output while you're trying to connect to it from the mindsdb container? – AlwaysLearning Feb 06 '23 at 21:41
  • @AlwaysLearning The password I have indicated here is just a placeholder, my actual password meets the password complexity requirements. I am sure it is running. I logged into the container and executed some queries on it. – Minura Punchihewa Feb 07 '23 at 06:03

1 Answers1

1

The only way I was able to solve this is by using the host 172.17.0.1. Nothing else worked: localhost, host.docker.internal etc.

This is how I ran my containers,

SQL Server:

sudo docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=SQLServer@123" \
   -p 1433:1433 --name sql1 --hostname sql1 \
   -d \
   mcr.microsoft.com/mssql/server:2022-latest

MindsDB:

docker run -p 47334:47334 -p 47335:47335 mindsdb/mindsdb

To establish a connection to SQL Server using MindsDB,

CREATE DATABASE mssql_datasource
WITH
engine='mssql',
PARAMETERS={
    "host": "172.17.0.1",
    "port": "1433",
    "user": "SA",
    "password": "SQLServer@123",
    "database": "TestDB"
};

Note: I am running Ubuntu. I have a feeling that this might be different for Windows or MacOS.

Minura Punchihewa
  • 1,498
  • 1
  • 12
  • 35
  • A better solution is to not use `pymssql`. All the addresses work just fine with `pyodbc`. Microsoft [supports `pyodbc`](https://learn.microsoft.com/en-us/sql/connect/python/python-driver-for-sql-server?view=sql-server-ver16), not `pymssql`. From the linked docs: *Microsoft places its testing efforts and its confidence in pyodbc driver* – Panagiotis Kanavos May 25 '23 at 14:11
  • Thanks for the insight @PanagiotisKanavos. As of now though, the MindsDB integration for SQL Server is implemented using `pymssl`. – Minura Punchihewa May 25 '23 at 14:14
  • Have you tried using the proof-of-concept samples in the documentation? Have you tried connecting from the *host* to the database inside Docker? You can't just connect from one container to another without setting up a network. – Panagiotis Kanavos May 25 '23 at 14:14
  • If MindsDB runs in another container, it's actually trying to connect to a completely different IP. `localhost` and `127.0.0.1` refer to the container itself, not the host and definitely not any other container. `host.docker.internal` is the *host*, not one of the other containers – Panagiotis Kanavos May 25 '23 at 14:16
  • If you want to start multiple containers at the same time you can use `docker-compose`, specify *both* containers and put them in the same "network". This will allow you to specify the containers by name. – Panagiotis Kanavos May 25 '23 at 14:18