K8s集群通过pyodbc连接AWS SQL Server超时问题求助

从办公网络的Kubernetes集群连接SQL Server时，内部数据库连接正常，但连接AWS上的数据库抛出超时错误：

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

• 使用<ip.port>格式的IP地址连接
• 同网络下JS编写的API网关无此问题，排除网络层面故障，锁定pyodbc相关问题
• 已尝试切换ODBC驱动版本（18→17）、更换驱动路径，均无效

数据库连接字符串代码 #

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;"
    )

Docker镜像配置代码 #

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"]

结合社区讨论经验，针对Linux环境下pyodbc连接AWS SQL Server超时问题，可尝试以下步骤：

1. 调整连接字符串格式与参数

   • 将Server参数改为tcp:<ip>,<port>格式，明确指定TCP协议，避免驱动对<ip.port>格式解析异常
   • 添加Connection Timeout=30;（可根据情况调整时长）延长超时时间
   • 修改后的连接字符串生成函数示例：

     def create_db_string(*, server, database, username, password, driver=DEFAULT_DRIVER):
         # 转换<ip.port>格式为tcp:ip,port
         if "." in server and ":" not in server:
             ip_part, port_part = server.split(".", 1)
             server = f"tcp:{ip_part},{port_part}"
         return (
             f"Driver={driver};"
             f"Server={server};"
             f"Database={database};"
             f"uid={username};"
             f"pwd={password};"
             "Trusted_Connection=no;"
             "integratedSecurity=false;"
             "TrustServerCertificate=yes;"
             "Connection Timeout=30;"
         )
     

2. 修复unixODBC与驱动兼容性

   • 当前Docker镜像中手动编译了unixODBC 2.3.12，可能与msodbcsql18存在适配问题，改用系统默认安装的unixODBC版本：

     # 注释手动编译unixODBC的代码块
     # 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
     # 安装系统包版本
     RUN apt-get install -y unixodbc unixodbc-dev
     

3. 统一驱动路径配置

   • 代码中DEFAULT_DRIVER_UNIX指定的路径与odbcinst.ini中配置的驱动路径不一致，直接使用ini中的路径避免软链接潜在问题：

     DEFAULT_DRIVER_UNIX = "/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.4.so.1.1"
     

4. 临时禁用连接池排查

   • 在连接字符串中添加Pooling=no;，排查是否因连接池机制导致超时，若问题解决再调整池参数而非完全禁用

内容的提问来源于stack exchange，提问作者ajay_edupuganti