MSSQL: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

[AJAYKE]

Hi am tyring to connect with MSSQL server from my kubernetes cluster. Cluster is hosted on office network.

Scenario 1:
Connecting with the database server hosted in the office itself, working fine.

Scenario 2:
Connecting with the db server hosted on AWS, Throwing above error.

How am I connecting?
Using IP address of the db server.
I tried using <ip.port> as well same error.

Am not getting this error from my api gateway which is in js. So it has to be pyodbc issue.

This isthe db string

DEFAULT_DRIVER = "SQL Server"
DEFAULT_DRIVER_ODBC = "ODBC Driver 18 for SQL Server"
DEFAULT_DIALECT = "mssql+pyodbc"


# allows Solenoid to work on macs/unix systems
DEFAULT_DRIVER_UNIX = "/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.4.so.1.0"
DEFAULT_DRIVER_ODBC_UNIX = "/usr/local/lib/libmsodbcsql.17.dylib"

if platform.system() == "Linux":
    DEFAULT_DRIVER = DEFAULT_DRIVER_UNIX
    DEFAULT_DRIVER_ODBC = DEFAULT_DRIVER_UNIX


def create_db_string(*, server, database, username, password, driver=DEFAULT_DRIVER):
    return (
        f"Driver={driver};"
        f"Server={server};"
        f"Database={database};"
        f"uid={username};"
        f"pwd={password};"
        "Trusted_Connection=no;"
        "integratedSecurity=false;"
        "TrustServerCertificate=yes;"
    )

I tried using DEFAULT_DRIVER_ODBC as well for the driver, did not work.

This is my docker image

FROM python:3.9

WORKDIR /app

ENV PYTHONPATH "${PYTHONPATH}:/app"

ENV ACCEPT_EULA=Y
ENV DEBIAN_FRONTEND=noninteractive

RUN apt update && apt upgrade -y

# Install required packages
RUN apt install -y \
    python3 python3-pip \
    unixodbc-dev \
    libssl-dev \
    wget \
    curl \
    gnupg2 \
    dpkg-dev \
    gcc \
    gnupg \
    libbluetooth-dev \
    libbz2-dev \
    libc6-dev \
    libdb-dev \
    libexpat1-dev \
    libffi-dev \
    libgdbm-dev \
    liblzma-dev \
    libncursesw5-dev \
    libreadline-dev \
    libsqlite3-dev \
    libssl-dev \
    make \
    tk-dev \
    uuid-dev \
    wget \
    xz-utils \
    zlib1g-dev \
    apt-transport-https \
    ca-certificates \
    build-essential \
    gcc \
    g++ 

# Install unixODBC 2.3.12
RUN apt-get update && apt-get install -y ca-certificates curl
RUN curl -L --insecure https://www.unixodbc.org/unixODBC-2.3.12.tar.gz -o unixODBC-2.3.12.tar.gz
RUN tar zxvf unixODBC-2.3.12.tar.gz
RUN cd unixODBC-2.3.12 && ./configure && make && make install
RUN rm -rf unixODBC-2.3.12 unixODBC-2.3.12.tar.gz

# Add the Microsoft repository and install the ODBC driver 18 for SQL Server
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - && \
    curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list > /etc/apt/sources.list.d/mssql-release.list && \
    apt-get update && \
    ACCEPT_EULA=Y apt-get install -y msodbcsql18 && \
    ACCEPT_EULA=Y apt-get install -y mssql-tools18 && \
    echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> /root/.bashrc && \
    . /root/.bashrc

# # Create a symbolic link for the Microsoft SQL Server client library
RUN ln -s /opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.4.so.1.1 /opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.4.so.1.0

# Modify the existing odbcinst.ini file
RUN echo '[ODBC]\n\
    Trace = No\n\
    Trace File = /tmp/sql.log\n\
    Pooling = Yes\n\
    \n\
    [ODBC Driver 18 for SQL Server]\n\
    Description=Microsoft ODBC Driver 18 for SQL Server\n\
    Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.4.so.1.1\n\
    UsageCount=1\n\
    CPTimeout=600' >> /etc/odbcinst.ini

# Copy only the requirements file first to leverage Docker cache
COPY app/prod_requirements.txt app/prod_requirements.txt

RUN pip install --upgrade --force-reinstall -r app/prod_requirements.txt
RUN pip install pandas numpy scipy gunicorn gevent SQLAlchemy==2.0.32

# Copy the application code
COPY app app

# Expose port 4000
EXPOSE 4000

# Run the application with gunicorn
CMD ["gunicorn", "-w", "2", "-k", "gevent", "-b", "0.0.0.0:4000", "app.app:app"]

[AJAYKE]

Reduced The version to 17, still the issue persists

Working for internal network db server connection and not able to connect to db server on aws

OperationalError('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')",

# Use the official Python image from the Docker Hub
FROM python:3.9

# Set the working directory in the container
WORKDIR /app

# Set Environment variable
ENV PYTHONPATH "${PYTHONPATH}:/app"

# Set environment variables
ENV ACCEPT_EULA=Y
ENV DEBIAN_FRONTEND=noninteractive

RUN apt update && apt upgrade -y

# Install required packages
RUN apt install -y \
    python3 python3-pip \
    unixodbc-dev \
    libssl-dev \
    wget \
    curl \
    gnupg2 \
    dpkg-dev \
    gcc \
    gnupg \
    libbluetooth-dev \
    libbz2-dev \
    libc6-dev \
    libdb-dev \
    libexpat1-dev \
    libffi-dev \
    libgdbm-dev \
    liblzma-dev \
    libncursesw5-dev \
    libreadline-dev \
    libsqlite3-dev \
    libssl-dev \
    make \
    tk-dev \
    uuid-dev \
    wget \
    xz-utils \
    zlib1g-dev \
    apt-transport-https \
    ca-certificates \
    build-essential \
    gcc \
    g++ 

# Install unixODBC 2.3.12
RUN apt-get update && apt-get install -y ca-certificates curl
RUN curl -L --insecure https://www.unixodbc.org/unixODBC-2.3.12.tar.gz -o unixODBC-2.3.12.tar.gz
RUN tar zxvf unixODBC-2.3.12.tar.gz
RUN cd unixODBC-2.3.12 && ./configure && make && make install
RUN rm -rf unixODBC-2.3.12 unixODBC-2.3.12.tar.gz

# Add the Microsoft repository and install the ODBC driver 17 for SQL Server
RUN curl https://packages.microsoft.com/keys/microsoft.asc | tee /etc/apt/trusted.gpg.d/microsoft.asc
RUN curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
RUN apt update && apt upgrade -y
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 /bin/bash -c "source ~/.bashrc"

# Copy only the requirements file first to leverage Docker cache
COPY app/prod_requirements.txt app/prod_requirements.txt

# Install Python dependencies
RUN pip install -r app/prod_requirements.txt
RUN pip install pandas numpy scipy gunicorn gevent SQLAlchemy==2.0.32

# Copy the application code
COPY app app

# Expose port 4000
EXPOSE 4000

# Run the application with gunicorn
CMD ["gunicorn", "-w", "2", "-k", "gevent", "-b", "0.0.0.0:4000", "app.app:app"]

db_string

DEFAULT_DRIVER = "SQL Server"
DEFAULT_DRIVER_ODBC = "ODBC Driver 17 for SQL Server"
DEFAULT_DIALECT = "mssql+pyodbc"


# allows Solenoid to work on macs/unix systems
DEFAULT_DRIVER_UNIX = "/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.6.1"
DEFAULT_DRIVER_ODBC_UNIX = "/usr/local/lib/libmsodbcsql.17.dylib"

if platform.system() == "Linux":
    DEFAULT_DRIVER = DEFAULT_DRIVER_UNIX
    DEFAULT_DRIVER_ODBC = DEFAULT_DRIVER_UNIX


def create_db_string(*, server, database, username, password, driver=DEFAULT_DRIVER):
    return (
        f"Driver={driver};"
        f"Server={server};"
        f"Database={database};"
        f"uid={username};"
        f"pwd={password};"
        "Trusted_Connection=no;"
        "integratedSecurity=false;"
        "TrustServerCertificate=yes;"
    )

[ArthurSteijn]

I had the same error on a different setup. I was able to resolve this by updating the Server param to include the port number:

Server=tcp:<servername>.database.windows.net,1433

[Shulamith]

I had the same issue, was able to resolve this by adding a driver connection timeout variable like so pyodbc.connect(conn_string,timeout=300). Try setting timeout variable to large number at first to see if this resolves it.

[AjayEdupuganti]

@mkleehammer can you help me with this?

[AjayEdupuganti]

@gordthompson can you help me with this?