CentOS连接Sql Server 驱动安装

一、使用微软的 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，ftp://ftp.freetds.org/pub/freetds/stable/freetds-1.1.1.tar.gz  新版：https://www.freetds.org/files/stable/freetds-1.1.24.tar.gz

# tar xzvf freetds-1.1.1.tar.gz
# cd freetds-1.1.1
# ./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配置

# 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.4 
Trace = On 
TraceFile = stderr 
Server = xx.xx.xx.xx\MSSQLSERVER 
Database = TOPPE_XXX 

可能需要使用命令 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

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'

关于 tds version 的说明：https://www.freetds.org/userguide/choosingtdsprotocol.htm