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/

2023-04-17更新：目录和包名被微软改了，https://packages.microsoft.com/rhel/7/prod/Packages/m/

msodbcsql开头的包

另外，可查看这里的微软官方文档

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

/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