时间戳

首页 > 解决方案 > 无法使用 FreeTDS 从 Linux VM 连接到 MSSQL Server

sql-server - 无法使用 FreeTDS 从 Linux VM 连接到 MSSQL Server

问题描述

我已经在我的 RHEL VM 上设置了 FreeTDS + UnixODBC，并且默认使用了使用 Windows Auth 的 MS SQL Server。

我正在尝试使用 Windows 身份验证连接到此 MS SQL 服务器。

以下是我的 freeTDS 和 ODBC 配置文件

freetds.conf

[global]
        tds version = 7.2
        dump file = /tmp/freetds.log
        debug flags = 0xffff
        timeout = 10
        connect timeout = 10
        text size = 64512

[mssql_db1_freetds]
    host = {ip}
    port = 1433
    timeout = 600
    connect timeout = 600
    tds version = 7.2
    text size = 20971520

odbc.ini

[mssql_db1_freetds]
Description             = MSSQL Server
Driver                  = freetds
Database                = {DB_Name}
ServerName              = mssql_db1_freetds
TDS_Version             = 7.2
Trace = Yes
TraceFile       = /tmp/mstest.log

odbcinst.ini

[FreeTDS]
Description=v0.91 with protocol v7.2
Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
UsageCount=1
FileUsage=1

我正在使用它通过 tsql 传递我的 Windows 身份验证：

/usr/bin/tsql -S {ip} -U "{domain}\{username}" -I /etc/freetds/freetds.conf

我收到以下错误：

locale is "C"
locale charset is "ANSI_X3.4-1968"
using default charset "ISO-8859-1"
Error 100 (severity 11):
        unrecognized msgno
Msg 18452 (severity 14, state 1) from {Server_Name} Line 1:
        "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication."
Error 20002 (severity 9):
        Adaptive Server connection failed
There was a problem connecting to the server

这是我的 freetds 日志：

09:03:25.326729 25546 (log.c:196):Starting log file for FreeTDS 0.91
        on 2019-08-26 09:03:25 with debug flags 0xffff.
09:03:25.326925 25546 (util.c:331):tdserror(0x239dbb0, 0x239dcd0, 100, 0)
09:03:25.326961 25546 (util.c:361):tdserror: client library returned TDS_INT_CANCEL(2)
09:03:25.326966 25546 (util.c:384):tdserror: returning TDS_INT_CANCEL(2)
09:03:25.326972 25546 (iconv.c:330):tds_iconv_open(0x239dcd0, ISO-8859-1)
09:03:25.327102 25546 (iconv.c:187):local name for ISO-8859-1 is ISO-8859-1
09:03:25.327110 25546 (iconv.c:187):local name for UTF-8 is UTF-8
09:03:25.327113 25546 (iconv.c:187):local name for UCS-2LE is UCS-2LE
09:03:25.327115 25546 (iconv.c:187):local name for UCS-2BE is UCS-2BE
09:03:25.327117 25546 (iconv.c:349):setting up conversions for client charset "ISO-8859-1"
09:03:25.327120 25546 (iconv.c:351):preparing iconv for "ISO-8859-1" <-> "UCS-2LE" conversion
09:03:25.327128 25546 (iconv.c:391):preparing iconv for "ISO-8859-1" <-> "UCS-2LE" conversion
09:03:25.327136 25546 (iconv.c:394):tds_iconv_open: done
09:03:25.327146 25546 (net.c:207):Connecting to {ip} port 1433 (TDS version 7.1)
09:03:25.327344 25546 (net.c:272):tds_open_socket: connect(2) returned "Operation now in progress"
09:03:25.329401 25546 (net.c:312):tds_open_socket() succeeded
09:03:25.329418 25546 (util.c:156):Changed query state from DEAD to IDLE
09:03:25.329427 25546 (net.c:743):Sending packet
.
.
.
.
09:03:25.345470 25546 (token.c:337):looking for login token, got  aa(ERROR)
09:03:25.345473 25546 (token.c:122):tds_process_default_tokens() marker is aa(ERROR)
09:03:25.345478 25546 (token.c:2588):tds_process_msg() reading message 18452 from server
09:03:25.345487 25546 (token.c:2661):tds_process_msg() calling client msg handler
09:03:25.345501 25546 (token.c:2674):tds_process_msg() returning TDS_SUCCEED
09:03:25.345504 25546 (token.c:337):looking for login token, got  fd(DONE)
09:03:25.345506 25546 (token.c:122):tds_process_default_tokens() marker is fd(DONE)
09:03:25.345509 25546 (token.c:2339):tds_process_end: more_results = 0
                was_cancelled = 0
                error = 1
                done_count_valid = 0
09:03:25.345513 25546 (token.c:2355):tds_process_end() state set to TDS_IDLE
09:03:25.345515 25546 (token.c:2370):                rows_affected = 0
09:03:25.345518 25546 (token.c:438):tds_process_login_tokens() returning TDS_FAIL
09:03:25.345520 25546 (login.c:466):login packet accepted

任何人都可以指导我在配置或其他方面做错了什么。FreeTds 在运行“-C”命令时返回版本为 4.2。不确定这是否是问题，因为我正在为配置文件传递“-I”参数

标签： sql-servertsqlwindows-authenticationfreetdsunixodbc

解决方案

/usr/bin/tsql -S {ip} -U "{domain}\{username}" -I /etc/freetds/freetds.conf

我相信，当您使用它时，它会识别为 SQL 身份验证。通常，Windows Authentication识别为当前登录的用户，这要求服务器加入域控制器管理kerberos 身份验证的域。否则，它需要来自 unix/linux 的全套配置kerberos 身份验证。

如果您没有任何安全问题，我建议SQL Authentication您通过SSMS -> 服务器属性 -> 安全性（选项卡）-> SQL Server 和 Windows 身份验证-（需要重新启动服务）来更改它。

推荐阅读