I try the following to accomplish: Connect to a loacl sql server on a windows machine using a docker image. The problem I am facing is that there is no successful connection, the main error I get with different configurations is:
sqlalchemy.exc.OperationalError: (pyodbc.OperationalError) ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')
I tried:
- different drivers
- checked tcp protocol enabled on port 1433 (sql server configuration manager) + allow remote connections
- different docker images
- different connection strings
Setup:
- pyodbc + sqlalchemy with engine notation
- fastAPI backend with uvicorn
SQLALCHEMY_DATABASE_URL = (
"mssql+pyodbc://user_name:[email protected]:1433/db_name"
"?driver=ODBC+Driver+17+for+SQL+Server"
)
engine = create_engine(
SQLALCHEMY_DATABASE_URL,
echo=True,
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Dockerfile: Odbc Installation driver from: Microsoft Driver Installation
#
FROM python:3.11.1
#
WORKDIR /code
#
COPY ./requirements.txt /code/requirements.txt
RUN curl https://packages.microsoft.com/keys/microsoft.asc > /etc/apt/trusted.gpg.d/microsoft.asc
#Download appropriate package for the OS version
#Choose only ONE of the following, corresponding to your OS version
#Debian 11
RUN curl https://packages.microsoft.com/config/debian/11/prod.list > /etc/apt/sources.list.d/mssql-release.list
RUN apt-get update
RUN ACCEPT_EULA=Y apt-get install -y msodbcsql17
# optional: for bcp and sqlcmd
#RUN ACCEPT_EULA=Y apt-get install -y mssql-tools
#RUN echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
#RUN source ~/.bashrc
# optional: for unixODBC development headers
RUN apt-get install -y unixodbc-dev
# optional: kerberos library for debian-slim distributions
RUN apt-get install -y libgssapi-krb5-2
#
RUN pip install --no-cache-dir --upgrade -r /code/requirements.txt
#
COPY ./app /code/app
#
CMD ["uvicorn", "app.main:app", "--host", "0.0.0.0", "--port", "5001"]
The docker build is successful and I run it wirh:
docker run -d --name container_name -p 1433:1433 dockerhub_name/image_name:latest
The container runs fro a few seconds and then says:
sqlalchemy.exc.OperationalError: (pyodbc.OperationalError) ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')
I think it is a network issue, but cant get my head arround :S The main hint i tried to follow also was: question from July
With the answer from @J.C. Gras
