Github Actions中pyodbc连接SQL Server登录超时错误求助

问题描述 #

我使用Windows11系统，基于Django和venv搭建了一个部署时序模型的API，数据库采用外部Microsoft SQL Server，通过pyodbc进行连接。在Github Actions中执行CI/CD时，构建环节正常，但测试阶段运行单元测试时出现错误：

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

相关代码文件 #

数据库连接代码 #

import pyodbc
import os
from dotenv import load_dotenv
load_dotenv()

def connect_to_database():
    db_server = os.environ.get('DB_SERVER')
    db_name = os.environ.get('DB_NAME')
    db_user = os.environ.get('DB_USER')
    db_password = os.environ.get('DB_PASSWORD')
    db_encrypt = os.environ.get('DB_ENCRYPT')
    db_trusted_connection = os.environ.get('DB_TRUSTED_CONNECTION')
    db_driver = 'ODBC Driver 17 for SQL Server'

    conn = pyodbc.connect(
        DRIVER=db_driver,
        SERVER = db_server,
        DATABASE = db_name,
        ENCRYPT = db_encrypt,
        UID = db_user,
        PWD = db_password,
        Trusted_Connection = db_trusted_connection)

    return conn

Dockerfile #

# Use an official Python runtime as a parent image
FROM python:3.9-slim-buster

# Set the working directory to /app
WORKDIR /app

# Copy the current directory contents into the container at /app
COPY . /app

# Set environment variables
ENV SECRET_KEY=$SECRET_KEY \
    DEBUG=$DEBUG \
    ALLOWED_HOSTS_1=$ALLOWED_HOSTS_1 \
    ALLOWED_HOSTS_2=$ALLOWED_HOSTS_2 \
    AGENCY_1=$AGENCY_1\
    DB_SERVER=$DB_SERVER \
    DB_NAME=$DB_NAME \
    DB_USER=$DB_USER \
    DB_PASSWORD=$DB_PASSWORD \
    DB_ENCRYPT=$DB_ENCRYPT \
    DB_TRUSTED_CONNECTION=$DB_TRUSTED_CONNECTION

# Install any needed packages specified in requirements.txt
# installation de unixodbc-dev
# Install SQL Server ODBC driver
RUN apt-get update && apt-get install -y \
    unixodbc-dev \
    libpq-dev \
    gcc \
    gnupg2 \
    curl && \
    curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - && \
    curl https://packages.microsoft.com/config/debian/10/prod.list > /etc/apt/sources.list.d/mssql-release.list && \
    apt-get update \
    && ACCEPT_EULA=Y apt-get install -y msodbcsql17=17.7.2.1-1 \
    && ACCEPT_EULA=Y apt-get -y install mssql-tools \
    && echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc

# Install Python dependencies
RUN pip install -r requirements.txt

# Install pyodbc
RUN pip install pyodbc

# Expose the port for the application
EXPOSE 8000

# Run the command to start the API
CMD ["python", "manage.py", "runserver", "0.0.0.0:8000"]

CI/CD YAML配置 #

name: CI/CD

on: push

jobs:
  build:
    runs-on: ubuntu-latest

    steps:
      - name: Checkout repository
        uses: actions/checkout@v3

      - name: Set up Docker Compose
        run: |
          sudo rm /usr/local/bin/docker-compose
          curl -L https://github.com/docker/compose/releases/latest/download/docker-compose-$(uname -s)-$(uname -m) -o /usr/local/bin/docker-compose
          sudo chmod +x /usr/local/bin/docker-compose

      - name: Build and push Docker image
        env:
          DOCKER_USERNAME: ${{ secrets.DOCKER_USERNAME }}
          DOCKER_PASSWORD: ${{ secrets.DOCKER_PASSWORD }}
          SECRET_KEY: ${{ secrets.SECRET_KEY }}
          DEBUG: ${{ secrets.DEBUG }}
          ALLOWED_HOSTS_1: ${{ secrets.ALLOWED_HOSTS_1 }}
          ALLOWED_HOSTS_2: ${{ secrets.ALLOWED_HOSTS_2 }}
          AGENCY_1: ${{ secrets.AGENCY_1 }}
          DB_SERVER: ${{ secrets.DB_SERVER }}
          DB_NAME: ${{ secrets.DB_NAME }}
          DB_USER: ${{ secrets.DB_USER }}
          DB_PASSWORD: ${{ secrets.DB_PASSWORD }}
          DB_ENCRYPT: ${{ secrets.DB_ENCRYPT }}
          DB_TRUSTED_CONNECTION: ${{ secrets.DB_TRUSTED_CONNECTION }}
        run: |
          docker build -t $DOCKER_USERNAME/my_image:latest .
          echo $DOCKER_PASSWORD | docker login -u $DOCKER_USERNAME --password-stdin
          docker push $DOCKER_USERNAME/my_image:latest

  test:
    needs: build
    runs-on: ubuntu-latest

    steps:
      - name: Checkout repository
        uses: actions/checkout@v3

      - name: Set up Python
        uses: actions/setup-python@v3
        with:
          python-version: 3.9

      - name: Install dependencies
        run: pip install -r requirements.txt

      - name: Run tests
        env:
            DOCKER_USERNAME: ${{ secrets.DOCKER_USERNAME }}
            DOCKER_PASSWORD: ${{ secrets.DOCKER_PASSWORD }}
            SECRET_KEY: ${{ secrets.SECRET_KEY }}
            DEBUG: ${{ secrets.DEBUG }}
            ALLOWED_HOSTS_1: ${{ secrets.ALLOWED_HOSTS_1 }}
            ALLOWED_HOSTS_2: ${{ secrets.ALLOWED_HOSTS_2 }}
            AGENCY_1: ${{ secrets.AGENCY_1 }}
            DB_SERVER: ${{ secrets.DB_SERVER }}
            DB_NAME: ${{ secrets.DB_NAME }}
            DB_USER: ${{ secrets.DB_USER }}
            DB_PASSWORD: ${{ secrets.DB_PASSWORD }}
            DB_ENCRYPT: ${{ secrets.DB_ENCRYPT }}
            DB_TRUSTED_CONNECTION: ${{ secrets.DB_TRUSTED_CONNECTION }}
        run: coverage run -m pytest  -v -s
  
      - name: Generate Coverage Report  
        run: coverage report -m 

问题排查与解决思路 #

1. 测试环境缺少SQL Server ODBC驱动 #

Github Actions的Ubuntu runner默认未安装ODBC Driver 17 for SQL Server，测试阶段仅安装了Python依赖，缺少系统级的ODBC驱动及相关依赖，导致pyodbc无法建立连接。

修复方法：在测试阶段添加安装ODBC驱动的步骤，参考Dockerfile中的安装逻辑，将以下步骤插入到Install dependencies步骤之后：

- name: Install SQL Server ODBC Driver
  run: |
    sudo apt-get update && sudo apt-get install -y \
      unixodbc-dev \
      gnupg2 \
      curl && \
      curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add - && \
      curl https://packages.microsoft.com/config/debian/10/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list && \
      sudo apt-get update && \
      ACCEPT_EULA=Y sudo apt-get install -y msodbcsql17

2. 外部SQL Server网络访问限制 #

Github Actions的runner运行在微软云虚拟机上，外部SQL Server可能未开放对应IP段的访问权限，导致连接超时。

修复方法：

• 若使用云平台托管的SQL Server（如Azure、AWS），登录对应平台配置防火墙规则，添加Github Actions runner的IP段；可临时设置允许所有IP（仅测试用，禁止生产环境使用）验证问题。
• 若使用本地SQL Server，需确保其可被公网访问，路由器已完成端口转发，且本地防火墙允许SQL Server默认端口（1433）的入站连接。

3. 数据库连接参数校验 #

检查环境变量中的参数格式：

• DB_SERVER：若使用非默认端口，格式应为服务器名称,端口号
• DB_ENCRYPT：需设置为yes或no的明确值
• Trusted_Connection：因使用账号密码登录，应设置为no（禁用Windows认证）

4. 复用容器环境运行测试 #

若上述方法无效，可直接复用构建好的Docker镜像运行测试，确保测试环境与构建环境一致：

- name: Run tests in Docker container
  run: |
    docker pull ${{ secrets.DOCKER_USERNAME }}/my_image:latest
    docker run -e SECRET_KEY=${{ secrets.SECRET_KEY }} \
      -e DB_SERVER=${{ secrets.DB_SERVER }} \
      -e DB_NAME=${{ secrets.DB_NAME }} \
      -e DB_USER=${{ secrets.DB_USER }} \
      -e DB_PASSWORD=${{ secrets.DB_PASSWORD }} \
      -e DB_ENCRYPT=${{ secrets.DB_ENCRYPT }} \
      -e DB_TRUSTED_CONNECTION=${{ secrets.DB_TRUSTED_CONNECTION }} \
      ${{ secrets.DOCKER_USERNAME }}/my_image:latest coverage run -m pytest -v -s

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