无法通过mssql-django连接现有MS SQL Server的问题求助

问题背景 #

执行manage.py runserver时无法连接MS SQL Server，已安装mssql-django和ODBC Driver 17 for SQL Server，尝试替换默认SQLite数据库，当前settings.py中的数据库配置如下：

DATABASES = {
    "default": {
        "ENGINE": "mssql",
        "NAME": "eHospital_DKAG_NSTL",
        "USER": "sa",
        "PASSWORD": "passwod",
        "HOST": "172.16.2.20\\sqlservertest",
        "PORT": "1433",
        "OPTIONS": {
            "driver": "ODBC Driver 17 for SQL Server", 
        },
    },
}

报错信息 #

Exception in thread django-main-thread:
Traceback (most recent call last):
  File "F:\Python\Django\fptdb\lib\site-packages\django\db\backends\base\base.py", line 244, in ensure_connection
    self.connect()
  File "F:\Python\Django\fptdb\lib\site-packages\django\utils\asyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "F:\Python\Django\fptdb\lib\site-packages\django\db\backends\base\base.py", line 225, in connect
    self.connection = self.get_new_connection(conn_params)
  File "F:\Python\Django\fptdb\lib\site-packages\mssql\base.py", line 353, in get_new_connection
    conn = Database.connect(connstr, **args)
pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: The wait operation timed out.\r\n (258) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (258)')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\Admin\AppData\Local\Programs\Python\Python310\lib\threading.py", line 1009, in _bootstrap_inner
    self.run()
  File "C:\Users\Admin\AppData\Local\Programs\Python\Python310\lib\threading.py", line 946, in run
    self._target(*self._args, **self._kwargs)
  File "F:\Python\Django\fptdb\lib\site-packages\django\utils\autoreload.py", line 64, in wrapper
    fn(*args, **kwargs)
  File "F:\Python\Django\fptdb\lib\site-packages\django\core\management\commands\runserver.py", line 137, in inner_run
    self.check_migrations()
  File "F:\Python\Django\fptdb\lib\site-packages\django\core\management\base.py", line 576, in check_migrations
    executor = MigrationExecutor(connections[DEFAULT_DB_ALIAS])
  File "F:\Python\Django\fptdb\lib\site-packages\django\db\migrations\executor.py", line 18, in __init__
    self.loader = MigrationLoader(self.connection)
  File "F:\Python\Django\fptdb\lib\site-packages\django\db\migrations\loader.py", line 58, in __init__
    self.build_graph()
  File "F:\Python\Django\fptdb\lib\site-packages\django\db\migrations\loader.py", line 235, in build_graph
    self.applied_migrations = recorder.applied_migrations()
  File "F:\Python\Django\fptdb\lib\site-packages\django\db\migrations\recorder.py", line 81, in applied_migrations
    if self.has_table():
  File "F:\Python\Django\fptdb\lib\site-packages\django\db\migrations\recorder.py", line 57, in has_table
    with self.connection.cursor() as cursor:
  File "F:\Python\Django\fptdb\lib\site-packages\django\utils\asyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "F:\Python\Django\fptdb\lib\site-packages\django\db\backends\base\base.py", line 284, in cursor
    return self._cursor()
  File "F:\Python\Django\fptdb\lib\site-packages\mssql\base.py", line 246, in _cursor
    conn = super()._cursor()
  File "F:\Python\Django\fptdb\lib\site-packages\django\db\backends\base\base.py", line 260, in _cursor
    self.ensure_connection()
  File "F:\Python\Django\fptdb\lib\site-packages\django\utils\asyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "F:\Python\Django\fptdb\lib\site-packages\django\db\backends\base\base.py", line 243, in ensure_connection
    with self.wrap_database_errors:
  File "F:\Python\Django\fptdb\lib\site-packages\django\db\utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "F:\Python\Django\fptdb\lib\site-packages\django\db\backends\base\base.py", line 244, in ensure_connection
    self.connect()
  File "F:\Python\Django\fptdb\lib\site-packages\django\utils\asyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "F:\Python\Django\fptdb\lib\site-packages\django\db\backends\base\base.py", line 225, in connect
    self.connection = self.get_new_connection(conn_params)
  File "F:\Python\Django\fptdb\lib\site-packages\mssql\base.py", line 353, in get_new_connection
    conn = Database.connect(connstr, **args)
django.db.utils.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: The wait operation timed out.\r\n (258) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (258)')

排查步骤 #

• 验证实例与端口匹配：命名实例默认使用动态端口，而非固定1433。打开SQL Server配置管理器，展开「SQL Server网络配置」→「实例名的协议」，启用TCP/IP，双击TCP/IP查看「IP地址」标签下的「TCP端口」，替换配置中的PORT值；或者将HOST改为172.16.2.20,实际端口（用逗号分隔IP和端口，无需加实例名）。
• 测试网络连通性：
  • 执行ping 172.16.2.20确认服务器可达
  • 执行telnet 172.16.2.20 1433（或对应端口）确认端口开放，Windows需先启用telnet客户端
• 检查SQL Server远程连接设置：
  • 在SQL Server Management Studio中，右键服务器→「属性」→「连接」，勾选「允许远程连接到此服务器」
  • 确保SQL Server服务已启动，且TCP/IP协议在配置管理器中处于启用状态
• 确认身份验证模式与账号权限：
  • 右键服务器→「属性」→「安全性」，确认服务器身份验证为「SQL Server和Windows身份验证模式」
  • 检查sa账号是否启用，密码是否正确，且拥有eHospital_DKAG_NSTL数据库的访问权限
• 简化连接测试：编写独立pyodbc脚本排除Django影响：

import pyodbc
try:
    conn = pyodbc.connect(
        'DRIVER={ODBC Driver 17 for SQL Server};'
        'SERVER=172.16.2.20\\sqlservertest;'
        'DATABASE=eHospital_DKAG_NSTL;'
        'UID=sa;'
        'PWD=passwod'
    )
    cursor = conn.cursor()
    cursor.execute("SELECT @@VERSION")
    print("连接成功:", cursor.fetchone())
except Exception as e:
    print("连接失败:", e)

若脚本报错，问题出在数据库连接本身，而非Django配置。

• 修正HOST字符串格式：Python字符串中反斜杠需转义，当前\\写法正确，也可尝试使用原始字符串HOST = r"172.16.2.20\sqlservertest"避免转义问题。

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