0

Before posting this question I have gone through tons of posts however it did not helped me to fix my issue that is why I am asking this question and looking out for some help.

OS & Tools: Windows 10 having SQL Server, Docker and Python installed.

I am trying to create a docker image that would run the python script. The script has API endpoint (created using FastAPI) and SQL Server connection strings using pyodbc.

My ultimate goal is to run the docker image which would enable api that can access SQL Server and its tables.

Dockerfile is as follows:


WORKDIR /RSApp

COPY . /RSApp

COPY ./requirements.txt /requirements.txt

RUN apt-get update \
 && apt-get install unixodbc -y \
 && apt-get install unixodbc-dev -y \
 && apt-get install freetds-dev -y \
 && apt-get install freetds-bin -y \
 && apt-get install tdsodbc -y \
 && apt-get install --reinstall build-essential -y

# populate "ocbcinst.ini"
RUN echo "[FreeTDS]\n\
            Description = FreeTDS unixODBC Driver\n\
            Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so\n\
            Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so \n\
            UsageCount=1" >> /etc/odbcinst.ini

RUN echo "[DB1] \n\
            Description = My DSN \n\
            Driver = FreeTDS \n\
            Database = DB1 \n\
            Servername = mssql \n\
            TDS_Version = 8.0" >> /etc/odbc.ini

RUN echo "[mssql] \n\
            host = <hostname> \n\
            instance = SQLEXPRESS \n\
            port = 1433" >> /etc/freetds/freetds.conf

RUN pip install -r requirements.txt

EXPOSE 8000

CMD [ "uvicorn", "main:app", "--host", "0.0.0.0"]

PYODBC Connection is as follows:

connection = pyodbc.connect("DRIVER={FreeTDS}; \
                                    SERVER='<servername>'; \
                                    PORT=1433; \
                                    DATABASE={DB1}; \
                                    TDS_Version = '8.0'")

When I run the docker image and try to access the api I get the following error:

pyodbc.OperationalError: ('08001', '[08001] [FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnect)')

Any help is much appreciated, thanks!

Manish Shegokar
  • 101
  • 1
  • 10
  • Services running inside docker doesn't automagically have access to services running on the host; depending on how you're running docker and other services. See https://stackoverflow.com/questions/31324981/how-to-access-host-port-from-docker-container – MatsLindh May 12 '22 at 08:57
  • Aside... choose an Instance Name, or choose a Port, don't try to use both. Instance Name resolution uses the SQL Server Resolution Protocol and requires network access to udp/1434 on the target server so that the SQL Browser service can resolve the instance name to a TCP port number, which the client then uses to connect to the SQL Server instance and execute queries. If you already know the Port number that the instance listens on then avoid using the Instance Name. Especially in Docker environments where UDP networking is extremely... flakey. – AlwaysLearning May 12 '22 at 09:47
  • Aside... if you're using Docker containers without compose then, by default, they exist inside the default `bridge` network that lacks (normal) DNS resolution. To connect to TCP-based services on the host computer you probably want to use `host.docker.internal` instead of the host computer's actual name or IP address. – AlwaysLearning May 12 '22 at 09:52
  • I tried only port option @AlwaysLearning, it is not working! – Manish Shegokar May 12 '22 at 10:37
  • I am using `host.docker.internal` but still facing the issue – Manish Shegokar May 12 '22 at 10:37
  • how do I check if port 1433 is correct? I meant in SQL server properties when I checked for TCP/IP it was empty, and I added 1433 port. That is how i am using it. Is the correct way? – Manish Shegokar May 12 '22 at 10:39
  • Does it have to do with Inbound rules? – Manish Shegokar May 12 '22 at 10:43
  • You can check the instance's configuration via the SQL Server (version) Configuration Manager tool on the host. Look in the Server Protocols for SQLEXPRESS section to a) ensure that the TCP/IP Protocol is enabled, and b) inside the properties of the TCP/IP protocol check which addresses/ports it's enabled to listen on. If you make any changes in here be sure to stop and start the SQLEXPRESS service so that it uses the new configuration. – AlwaysLearning May 12 '22 at 10:51

0 Answers0