问题描述
我正在尝试使用freetds和unixodbc连接到MS SQL数据库。我已经阅读了各种指南如何做到这一点，但没有人适合我。当我尝试使用isql工具连接到数据库时，我收到以下错误：
$ isql -v TS username password
[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
[ISQL]ERROR: Could not SQLConnect
有人在Ubuntu 12.04上使用freetds和unixodbc成功建立了与MS SQL数据库的连接吗？我真的很感激一些帮助。
下面是我用来配置freetds和unixodbc的过程。感谢您的帮助！
Procedure
首先，我安装了以下软件包：
sudo apt-get install unixodbc unixodbc-dev freetds-dev tdsodbc
并配置freetds如下：
--- /etc/freetds/freetds.conf ---
[TS]
host = SERVER
port = 1433
tds version = 7.0
client charset = UTF-8
使用tsql工具我可以通过执行成功连接到数据库
tsql -S TS -U username -P password
由于我需要一个odbc连接，我按如下方式配置了odbcinst.ini：
--- /etc/odbcinst.ini ---
[FreeTDS]
Description = FreeTDS
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
FileUsage = 1
CPTimeout =
CPResuse =
client charset = utf-8
和odbc.ini如下：
--- /etc/odbc.ini ---
[TS]
Description = "test"
Driver = FreeTDS
Servername = SERVER
Server = SERVER
Port = 1433
Database = DBNAME
Trace = No
尝试使用具有此类配置的isql工具连接到数据库会导致以下错误：
$ isql -v TS username password
[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
[ISQL]ERROR: Could not SQLConnect
最佳解决思路
谢谢，你的帖子对我非常有用。通过从odbcinst.ini文件中删除以下行，我能够使它工作
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
FileUsage = 1
CPTimeout =
CPResuse =
client charset = utf-8
所以现在我的odbcinst.ini文件看起来像这样：
--- /etc/odbcinst.ini ---
[FreeTDS]
Description = FreeTDS
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
我的odbc.ini文件现在看起来像这样：
--- /etc/odbc.ini ---
[TS]
Description = "test"
Driver = FreeTDS
Server = SERVER
Port = 1433
Database = DBNAME
一旦我简化了一切，它就很有效。我仍然无法让它与RODBC一起使用，但它适用于isql。
我不知道这会有所帮助，但你的帖子对我有帮助。谢谢。
次佳解决思路
这是一个最小但完整的示例，如何使用Ubuntu 14.04.1 LTS中的isql连接到Azure SQL Database。该示例摘自How To Connect Azure SQL Database From Ubuntu(免责声明：这是我的个人维基)。
安装必要的包
$ sudo apt-get -y install freetds-bin tdsodbc unixodbc
配置FreeTDS
文件/etc/freetds/freetds.conf
[global]
tds version = 7.1
[<SERVERNAME>]
host = <HOST>.database.windows.net
port = 1433
测试连接
此时连接tsql应该可以工作：
$ tsql -S <SERVERNAME> -U <USERNAME>@<HOST> -P <PASSWORD>
请注意，@<HOST>是必需的。否则连接以错误结束：
Msg 40531 (severity 11, state 1) from [<SERVERNAME>] Line 1:
"Server name cannot be determined. It must appear as the first segment of the server's dns name (servername.database.windows.net). Some libraries do not send the server name, in which case the server name must be included as part of the user name (username@servername). In addition, if both formats are used, the server names must match."
Error 20002 (severity 9):
Adaptive Server connection failed
There was a problem connecting to the server
配置ODBC驱动程序
文件/etc/odbcinst.ini
[FreeTDS]
Description = FreeTDS Driver
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
配置ODBC数据源
文件/etc/odbc.ini
[<DATA_SOURCE_NAME>]
Driver = FreeTDS
Servername = <SERVERNAME>
Port = 1433
Database = <DBNAME>
<SERVERNAME>与freetds.conf相同。
与isql连接
$ isql -v <DATA_SOURCE_NAME> <USER>@<HOST> <PASSWORD>
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select current_timestamp
+------------------------+
| |
+------------------------+
| 2015-01-02 09:05:55.593|
+------------------------+
SQLRowCount returns 1
1 rows fetched
SQL>
请注意，@<HOST>是必需的。否则连接以错误结束：
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[37000][unixODBC][FreeTDS][SQL Server]Server name cannot be determined. It must appear as the first segment of the server's dns name (servername.database.windows.net). Some libraries do not send the server name, in which case the server name must be included as part of the user name (username@servername). In addition, if both formats are used, the server names must match.
[ISQL]ERROR: Could not SQLConnect
第三种解决思路
在我的情况下，问题出现是因为我的配置文件中有简单的缩进。所以在/etc/odbc.ini中，我删除了所有的缩进和瞧！
(odbcinst.ini表现得像一个普通的孩子，似乎没有任何发脾气。)
第四种思路
12.04之前的Ubuntu在/etc/odbcinst.ini文件中有一个不同的odbc路径。
旧的驱动程序路径是：
Driver = /usr/lib/odbc/libtdsodbc.so
我改成了：
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
这是完整的配置：
--- /etc/odbcinst.ini ---
[FreeTDS]
Description = tdsodbc
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
FileUsage = 1
CPTimeout = 5
CPReuse = 5
现在就像魅力一样！谢谢！