0

I need help to run a simple Python script in Docker to query a SQL Server database. The SQL Server instance is not Dockerised, it's just running on a VM. The Python script succeeds when run from my laptop, but not when run via Docker.

Based on chat with a colleague I suspect it is to do with my work's networking (ports, DirectAccess, ip addresses, DNS etc) but I don't have enough foundational knowledge to know how to test/fix this. I've spent hours, but not been able to get this working.

pyodbc kept giving me 'timeout' errors

pymssql gives me the following:

pymssql._mssql.MSSQLDatabaseException: (20009, b'DB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist ([email protected])\nNet-Lib error during Connection refused (111)\nDB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist ([email protected])\nNet-Lib error during Connection refused (111)\n')

Can someone help me fix the Docker - SQLServer connectivity issues?

I am in no way wedded to pymssql. At this stage, any solutions/suggestions are very welcome - happy to try anything!

My Python script (with credentials obfuscated):

*import pymssql


server = '[email protected]' 
database = 'MyDatabase'
username = 'my_username'
password = 'my_password'


conn = pymssql.connect(server=server, user=username, password=password, database=database)
cursor = conn.cursor()
cursor.execute('SELECT Source FROM config.Columns ')
row = cursor.fetchone()
print(row)*

My Dockerfile:

    # syntax=docker/dockerfile:1

FROM python:3.8-buster


WORKDIR /app

RUN apt-get update -y && \
    apt-get install -y \
    libpq-dev \
    gcc \
    python3-pip \
    unixodbc-dev

RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
RUN curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
RUN apt-get update
RUN ACCEPT_EULA=Y apt-get install -y --allow-unauthenticated msodbcsql17

RUN pip3 install pyodbc


COPY requirements.txt requirements.txt
RUN pip3 install -r requirements.txt

COPY . .

CMD [ "python", "-m" , "main", "run", "--host=0.0.0.0"]
Improve this question
4
  • 1
    That server name just has a bare host name. In the Docker environment, the Docker DNS might be trying to resolve that as a container name. Does it work to use a fully-qualified domain name like azu-inf-drds02.example.com? (Also see How to connect external MS SQL server database from container.) Commented Jun 9, 2022 at 16:03
  • Thanks for your help @DavidMaze. I updated my python connection string based on your feedback but still unable to connect with a slightly longer error message. I have edited the orginal question post following your helpful post, to make my question clearer for others. Commented Jun 10, 2022 at 8:19
  • Aside... you're trying to use Ubuntu 18.04 repos and packages with python:3.8-buster? When working with Docker images you should always try to figure out which specific distro and version they're using, e.g.: docker run -it --rm python:3.8-buster cat /etc/os-release tells you that it's Debian 10 (Buster), so you should be following the instructions for Install the Microsoft ODBC driver for SQL Server (Linux) # Debian to install msodbcsql17/8 for Debian 10. Commented Jun 10, 2022 at 8:24
  • Thank you @AlwaysLearning. Yes, my Dockerfile definitely become a bit muddled after so many edits(!) Appreciate it. Commented Jun 10, 2022 at 8:53

1 Answer 1

Reset to default
0

A similar issue bugged me for days and finally was able to solve it by following the steps below.

In my case the SQL server was hosted on Azure managed instance and exposed a private endpoint. The docker was able to resolve the DNS name of the Azure managed instance to a private IP address (as expected ). But this resolved IP was considered by the docker network/bridge as a locally running application because upon inspecting docker's bridge (docker network inspect bridge)The subnet of the bridge was found to be a range of IP addresses where the resolved IP of the SQL server also falls. Hence, the docker assumed the SQL server to be hosted in the same local network and not outside (here enterprise network). In my case the Windows VM I was working was already on the private network of the enterprise.

Solution:

In the docker desktop -> settings -> Docker Engine json file, add a new key value pair "bip": "<local IP address range which is outside of the resolved sql server IP>". Restart the docker desktop and the issue should be solved.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.