使用 FreeTDS 从 Ubuntu 访问 RODBC MS SQL

我正在尝试从 Ubuntu 机器访问托管在我的 Windows 机器上的 MS SQL 服务器.我有一个闪亮的应用程序，可以访问在 Windows 上运行良好的 MSSQL.我想在 Ubuntu 上使用闪亮的服务器托管它，以便其他人可以访问网页并将他们的 SQL 服务器作为应用程序的输入提供.

所有这些都可以在 Windows 上正常运行.现在我无法让 unixODBC 工作.我想我的 odbc/freeTDS 安装和配置是正确的.我可以使用 tsql -S 从 Ubuntu 终端连接和查询 SQL 数据库实例.用了一些谷歌搜索，但最终它奏效了.

现在，当我尝试从 R 连接时，出现此错误.

sql <- odbcConnect("abc.xyz.com", "uname", "passwd")

<块引用>

在odbcDriverConnect("DSN=abc.xyz.com;UID=uname;PWD=passwd"):[RODBC] 错误:状态 08001，代码 0，消息[unixODBC][FreeTDS][SQL Server]无法连接到数据源 2:在odbcDriverConnect("DSN=abc.xyz.com;UID=uname;PWD=passwd"):[RODBC] 错误:状态 01000，代码 20002，消息[unixODBC][FreeTDS][SQL Server]Adaptive Server 连接失败 3:在odbcDriverConnect("DSN=abc.xyz.com;UID=uname;PWD=passwd"):[RODBC] 错误:状态 01000，代码 20017，消息[unixODBC][FreeTDS][SQL Server]来自服务器的意外 EOF 4:在odbcDriverConnect("DSN=abc.xyz.com;UID=uname;PWD=passwd"): ODBC 连接失败

freeTDS.conf[abc.xyz.com]主机 = abc.xyz.com端口 = 49475tds 版本 = 8.0odbcinst.ini[免费TDS]说明 = FreeTDS unixODBC 驱动程序驱动程序 =/usr/local/lib/libtdsodbc.so设置 =/usr/local/lib/libtdsodbc.so数据库配置文件[abc.xyz.com]描述 = 闪亮测试驱动程序 = FreeTDS跟踪 = 否服务器 = abc.xyz.com\instance_name数据库 = dbanme端口 = 49475

这个错误来自服务器的意外 EOF"对我来说并不陌生.当我尝试使用 tsql 进行连接时，我遇到了同样的错误.我能够通过向 freeTDS.conf 添加tds version = 8.0"行来克服这个问题.不确定如何让 RODBC 使用此配置.我读过其他用户可以使用 freeTDS 进行 sql-server 的帖子.不确定这里缺少什么.我也尝试过重新安装 RODBC.

解决方案

我解决了这个问题.odbc.ini 文件中需要 TDS 版本.

TDS_Version = 8.0

tsql 从 freeTDS.conf 读取版本信息并工作.isql 因同样的错误而失败，它也在 odbc.ini 中寻找此配置.因此，如果您正在为 R/Python 进行配置，请使用 isql 进行测试.

I'm trying to access MS SQL server hosted on my Windows box from an Ubuntu machine. I have a shiny app that access MSSQL that works fine on Windows. I want to host this with shiny server on Ubuntu so that others can access the webpage and provide their SQL server as input to the app.

All of this works fine on Windows. Now I'm not able to get unixODBC working. I think I've got the odbc/freeTDS installation and configuration correct. I'm able to connect and query SQL DB instance from Ubuntu terminal using tsql -S . It took some googling but eventually it worked.

Now, when I try to connect from R I get this error.

sql <- odbcConnect("abc.xyz.com", "uname", "passwd")

In odbcDriverConnect("DSN=abc.xyz.com;UID=uname;PWD=passwd") : [RODBC] ERROR: state 08001, code 0, message [unixODBC][FreeTDS][SQL Server]Unable to connect to data source 2: In odbcDriverConnect("DSN=abc.xyz.com;UID=uname;PWD=passwd") : [RODBC] ERROR: state 01000, code 20002, message [unixODBC][FreeTDS][SQL Server]Adaptive Server connection failed 3: In odbcDriverConnect("DSN=abc.xyz.com;UID=uname;PWD=passwd") : [RODBC] ERROR: state 01000, code 20017, message [unixODBC][FreeTDS][SQL Server]Unexpected EOF from the server 4: In odbcDriverConnect("DSN=abc.xyz.com;UID=uname;PWD=passwd") : ODBC connection failed

freeTDS.conf
[abc.xyz.com]
        host = abc.xyz.com
        port = 49475
        tds version = 8.0

odbcinst.ini
[FreeTDS]
Description   = FreeTDS unixODBC Driver
Driver        = /usr/local/lib/libtdsodbc.so
Setup         = /usr/local/lib/libtdsodbc.so

odbc.ini
[abc.xyz.com]
Description = Shiny testing
Driver = FreeTDS
Trace = No
Server = abc.xyz.com\instance_name
Database = dbanme
port = 49475

This error "Unexpected EOF from the server" is not new to me. I got the same error when I was trying to connect using tsql. I was able to overcome this by adding "tds version = 8.0" line to freeTDS.conf. Not sure how I can get RODBC to use this config. I've read posts were other users were able to sql-server using freeTDS. Not sure what is missing here. I also tried re-installing RODBC.

解决方案

I fixed this. TDS version was needed in odbc.ini file.

TDS_Version = 8.0

tsql reads version info from freeTDS.conf and worked. isql was failing with same error and it was also looking for this config in odbc.ini. So test with isql if you are configuring for R/Python.