CentOS连接Sql Server 驱动安装 unixODBC + freetds

一、使用微软的 Microsoft ODBC Driver for SQL Server

sudo su
#Download appropriate package for the OS version
#Choose only ONE of the following, corresponding to your OS version
#RedHat Enterprise Server 6
curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo
#RedHat Enterprise Server 7
curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo
exit
sudo yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflicts
sudo ACCEPT_EULA=Y yum install msodbcsql17
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y yum install mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

安装包地址（Centos7）：https://packages.microsoft.com/rhel/7/prod/

使用时指定数据库驱动目录：

'/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.4.so.2.1'

注意官方驱动不支持旧版本的SqlServer，比如SqlServer 2000；

二、使用开源的 unixODBC + freetds

1. 下载安装unixODBC，http://www.unixodbc.org/unixODBC-2.3.7.tar.gz

tar xzvf unixODBC-2.3.7.tar.gz
cd unixODBC-2.3.7
./configure --prefix=/usr/local/unixODBC
make && make install

2.下载安装最新freetds wget ftp://ftp.freetds.org/pub/freetds/stable/freetds-1.3.4.tar.gz

cd /

wget ftp://ftp.freetds.org/pub/freetds/stable/freetds-1.3.4.tar.gz

tar xzvf freetds-1.3.4.tar.gz

cd freetds-1.3.4

./configure --prefix=/usr/local/freetds --with-tdsver=7.4 --enable-msdblib --with-unixodbc=/usr/local/unixODBC

make && make install

3.添加PATH路径

echo 'export PATH="$PATH:/usr/local/unixODBC/bin:/usr/local/freetds/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/usr/local/unixODBC/bin:/usr/local/freetds/bin"' >> ~/.bashrc
source ~/.bashrc

4. 修改unixODBC配置 ,PS:我选择的是7.1 因为我链接的是mssql //参考http://www.freetds.org/userguide/ChoosingTdsProtocol.html

# cat /usr/local/unixODBC/etc/odbcinst.ini
[FreeTDS]
Description=FreeTDS driver
Driver=/usr/local/freetds/lib/libtdsodbc.so.0

# cat ~/.odbc.ini 另一个文件配置下面的
[toppe]
Description = sqlserver toppe 
Driver = FreeTDS 
TDS_Version = 7.1 
Trace = On 
TraceFile = stderr 
Server = xx.xx.xx.xx\MSSQLSERVER 
Database = TOPPE_XXX 

#这个配置成功
[toppe]
Description = sqlserver toppe 
Driver = FreeTDS 
TDS_Version = 7.1 
Trace = On 
TraceFile = stderr
Server = xx.xxxxx.xx
Port=1433

可通过使用命令 odbcinst -j来找到文件正确位置

5. 使用isql测试连接

# isql toppe user passwd
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select 1
+------------+
|            |
+------------+
| 1          |
+------------+
SQLRowCount returns 1
1 rows fetched
SQL>

6. 修改freetds配置，将如下配置追加到/usr/local/freetds/etc/freetds.conf中

[toppe]
        host = xx.xx.xx.xx
        instance = MSSQLSERVER
        tds version = 7.4
        client charset = UTF-8
        Port = 1433

7. 使用tsql测试连接

# tsql -S toppe -U user -P passwd
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> select 1
2> go
1
(1 row affected)
1>

使用 tsql -C 查看配置

连接也可使用：tsql -H x.x.x.x -p 1433 -U user -P passwd

可能用到数据库驱动：'/usr/local/freetds/lib/libtdsodbc.so.0'