CentOS下pymssql/pyodbc连接SQLServer报18452登录失败问题咨询

问题复现 #

在CentOS 7.6服务器上连接SQL Server数据库时，无论用pymssql还是pyodbc+FreeTDS驱动，都抛出如下错误：

登录失败。该登录名来自不受信任的域，不能与Windows身份验证一起使用。(错误码18452)

pymssql连接代码 #

try:
    self.__db_conn = pymssql.connect(self.__db_host, self.__db_user, self.__db_pass, self.__db_name)
    self.__db_cursor = self.__db_conn.cursor(as_dict=True)
except pymssql.DatabaseError as e:
    print("Error: Could not connect to ODSREAD Database : %S", e)

对应报错：

Error: Could not connect to ODSREAD Database : %S (18452, b'Login failed. 
The login is from an untrusted domain and cannot be used with Windows authentication.DB-Lib error message 20018,
severity 14:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20002,
severity 9:\nAdaptive Server connection failed (ods.domain.net:1433)\n')

pyodbc+FreeTDS连接代码 #

__db_driver = '{FreeTDS}'
__db_ip = ODS_IP
__db_port = ODS_PORT
__db_dbname = ODS_DBNAME
__db_username = ODS_USERNAME
__db_password = ODS_PASSWORD
__db_conn = None
__db_cursor = None
__connect_url = None

connectUrl = 'DRIVER={driver};SERVER={server};PORT={port};DATABASE={db_name};UID={username};PWD={password};TDS_VERSION=7.0'.format(
            driver=self.__db_driver,
            server=self.__db_ip,
            port=self.__db_port,
            db_name=self.__db_dbname,
            username=self.__db_username,
            password=self.__db_password
        )
self.__db_conn = pyodbc.connect(self.__connect_url)

对应报错：

pyodbc.ProgrammingError: ('42000', '[42000] [unixODBC][FreeTDS][SQL Server]Login failed.
 The login is from an untrusted domain and cannot be used with Windows authentication. (18452) (SQLDriverConnect)')

当前相关配置 #

freetds.conf配置：

[global]
        # TDS protocol version
;       tds version = 4.2

        # Whether to write a TDSDUMP file for diagnostic purposes
        # (setting this to /tmp is insecure on a multi-user system)
;       dump file = /tmp/freetds.log
;       debug flags = 0xffff

        # Command and connection timeouts
;       timeout = 10
;       connect timeout = 10

        # If you get out-of-memory errors, it may mean that your client
        # is trying to allocate a huge buffer for a TEXT field.
        # Try setting 'text size' to a more reasonable limit
        text size = 64512
        use ntlmv2 = yes
# A typical Sybase server
[egServer50]
        host = symachine.domain.com
        port = 5000
        tds version = 5.0

# A typical Microsoft server
[egServer70]
        host = ntmachine.domain.com
        port = 1433
        tds version = 7.0

odbcinst.ini配置：

[PostgreSQL]
Description=ODBC for PostgreSQL
Driver=/usr/lib/psqlodbcw.so
Setup=/usr/lib/libodbcpsqlS.so
Driver64=/usr/lib64/psqlodbcw.so
Setup64=/usr/lib64/libodbcpsqlS.so
FileUsage=1

[MySQL]
Description=ODBC for MySQL
Driver=/usr/lib/libmyodbc5.so
Setup=/usr/lib/libodbcmyS.so
Driver64=/usr/lib64/libmyodbc5.so
Setup64=/usr/lib64/libodbcmyS.so
FileUsage=1

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1
UsageCount=1

[FreeTDS]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/usr/lib64/libtdsodbc.so.0
UsageCount=1

问题原因 #

• 当前环境已经安装了微软官方的ODBC Driver 17 for SQL Server，兼容性远好于FreeTDS，没必要硬用FreeTDS
• FreeTDS配置里全局TDS协议版本是注释状态，连接串里写的TDS_VERSION=7.0版本过老，不支持新版SQL Server的SQL账号认证逻辑，默认回退到Windows集成认证，Linux环境不支持域集成认证就会报18452
• 连接串没有显式指定认证模式为SQL账号密码认证，FreeTDS会按默认逻辑优先尝试Windows认证。

修复方案 #

方案1：使用微软官方ODBC驱动（推荐，稳定性最好） #

直接替换pyodbc连接串，用已经安装好的ODBC Driver 17 for SQL Server驱动，显式关闭集成认证：

connectUrl = 'DRIVER={ODBC Driver 17 for SQL Server};SERVER={server},{port};DATABASE={db_name};UID={username};PWD={password};TrustServerCertificate=yes;Authentication=SqlPassword'.format(
            server=self.__db_ip,
            port=self.__db_port,
            db_name=self.__db_dbname,
            username=self.__db_username,
            password=self.__db_password
        )
self.__db_conn = pyodbc.connect(self.__connect_url)

如果要用pymssql，先升级pymssql到最新版，连接参数里加tds_version="7.4"即可：

self.__db_conn = pymssql.connect(
    server=self.__db_host, 
    user=self.__db_user, 
    password=self.__db_pass, 
    database=self.__db_name,
    tds_version="7.4"
)

方案2：修复FreeTDS配置（如果必须用FreeTDS） #

1. 编辑freetds.conf，在[global]段打开tds版本注释，改成7.4（对应SQL Server 2012及以上版本，2008及更早版本改成7.3），同时加上强制SQL认证的参数，再新增你实际要连接的数据库服务器配置段：

[global]
        tds version = 7.4
        text size = 64512
        use ntlmv2 = yes
        # 强制使用SQL账号认证，不尝试Windows集成认证
        security = secusql
[ods_server]
        host = ods.domain.net
        port = 1433
        tds version = 7.4

2. 修改pyodbc连接串，TDS_VERSION改成7.4，显式关闭集成安全认证：

connectUrl = 'DRIVER={FreeTDS};SERVER={server};PORT={port};DATABASE={db_name};UID={username};PWD={password};TDS_VERSION=7.4;IntegratedSecurity=False;TrustServerCertificate=yes'.format(
            server=self.__db_ip,
            port=self.__db_port,
            db_name=self.__db_dbname,
            username=self.__db_username,
            password=self.__db_password
        )

3. 改完后先用tsql -S ods_server -U 你的数据库账号 -P 你的密码命令测试FreeTDS连接是否正常，返回SQL命令行提示符说明连接正常，再跑业务代码即可。

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