Windows, Linux Ubuntu Java ODBC连接SqlServer,Linux FreeTDS/jtds 连接SqlServer

绕过JDBC连接时发生以下报错的解决方式。

The server selected protocol version TLS10 is not accepted by client preferences [TLS12]


前言

经过试验:无论是Windows还是linux下,freetds连接比较好入手,流程较简单,成功率高,推荐先选择freetds连接,即java使用jtds

环境:

sqlserver Driver : 
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <version>9.4.1.jre8</version>
        </dependency>
        
java version "1.8.0_321"

sqlserver version: 
				SQL Server 2008 R2 (RTM)
				SQL Server 2019

项目需求,两个Sqlserver数据库,一个版本为 :SQL Server 2008 R2 (RTM) 另一个为:SQL Server 2019,两库之间需要同步数据,不想写两个项目,遂使用dynamic-datasource 管理加载数据源,后文也给出了单数据源配置。

但是发现连接SQL Server 2019正常,连接SQL Server 2008 R2 (RTM)会抛出异常:

com.microsoft.sqlserver.jdbc.SQLServerException: 
The driver could not establish a secure connection to sql server by using Secure Sockets Layer (SSL). 
Error:“The server selected protocol version TLS10 is not accepted by client preferences [TLS12]”。
 ClientConnectionId:7488157a-7d71-4424-b37e-3061e31729d9
at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:3422)
at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1916)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2970)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2628)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2471)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1470)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:915)

网上也有一些解决办法例如: "找到 JAVA_HOME\jre\lib\security\java.security" 的jdk.tls.disabledAlgorithms 去掉以下三个配置 "TLSv1, TLSv1.1, 3DES_EDE_CBC; 如下

#jdk.tls.disabledAlgorithms=SSLv3, TLSv1, TLSv1.1, RC4, DES, MD5withRSA, \
#    DH keySize < 1024, EC keySize < 224, 3DES_EDE_CBC, anon, NULL
jdk.tls.disabledAlgorithms=SSLv3, RC4, DES, MD5withRSA, \
    DH keySize < 1024, EC keySize < 224, 3DES_EDE_CBC, anon, NULL

但是我是反复测试了不管用,SQL Server 2008 R2 (RTM) 无论如何都是连接不上。
找了sqlserver drive 的官方开发人员提问,本人与其掰头地址:https://github.com/microsoft/mssql-jdbc/issues/1803
按照官方人员说的,给Sqlserver 的Driver 换成了 7.4之前的版本,依然报错。
例如:

<!-- https://mvnrepository.com/artifact/com.microsoft.sqlserver/mssql-jdbc -->
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>7.0.0.jre8</version>
</dependency>

(感觉他像是在建议我升级Sqlserver2008,虽然我也想但是不可能…)
最终采用ODBC,和jtds 连接


提示:以下正文是针对sqlserver2008 的连接,sqlserver2019可以正常使用jdbc连接,案例可供参考

一、FreeTDS 连接SqlServer,java jtds连接

1. Windows 安装freetds

ODBC在测试时候代码中只查询了一下数量成功了就没管其他,后续在查询数据的时候,发现疑似存在中文乱码 问题,但是没有找到解决办法。在sql执行查询时,序列化为Java Bean的时候,会莫名奇妙报出 Error attempting to get column '字段名' from result set 异常,有人说是set方法的问题,有人说是字段类型没有对应上,但是都仔细检查过了,并且用本地建表jdbc 方式和jtds 方式查询都没有问题,遂将本地Windows系统也安装freetds以连接。
freetds for Windows 下载地址:
https://sourceforge.net/projects/freetdswindows/
下载后是一个zip包,解压即可,然后找到freetds.conf文件,
例如:D:\freetdsWindows\FreeTDS-1.00-x86\freetds-1.00\etc\freetds.conf
加入配置保存:

[MSS]
host = 112.23.34.65
port = 1433
tds version = 7.3
client charset = UTF-8

2. Linux 安装freetds

线上服务器系统 ubuntu20.04
sudo apt-get install unixodbc unixodbc-dev freetds-dev tdsodbc

安装之后 /etc/freetds/ 下有个 freetds.conf 配置文件,直接加入实例配置:
vi freetds.conf

[MSS]
host = 112.23.34.65
port = 1433
tds version = 7.3
client charset = UTF-8

:wq 保存
测试连接:
sa和pwd123为数据用户名密码
MSSfreetds.conf 文件中配置的中括号内的数据源名
tsql -S MSS -U sa -P pwd123
连接上如图:
在这里插入图片描述
然后可以测试一条sql语句,两条命令
select count(*) from [db].[dbo].[table1];
go
我这里是成功查到了数据,接下来就可以在代码中实现了。

3. 代码实现连接和配置

i Java连接

引入包

<dependency>
		<groupId>net.sourceforge.jtds</groupId>
		<artifactId>jtds</artifactId>
		<version>1.3.1</version>
</dependency>

测试代码:
注意url的写法 dbname是要连接的数据库名,ip端口号冒号隔开 jdbc:jtds:sqlserver://112.23.34.65:1433/dbname

    public static void main(String[] args) throws Exception {
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://112.23.34.65:1433/dbname", "sa", "pwd123");
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery("SELECT count(*) FROM table1");
        while (rs.next()) {
            String a = rs.getString(0);
            System.out.println(a);
        }
        con.close();
    }

ii 配置文件

dynamic-datasource:

spring.datasource.dynamic.datasource.dsName.driverClassName=net.sourceforge.jtds.jdbc.Driver
spring.datasource.dynamic.datasource.dsName.url=jdbc:jtds:sqlserver://118.127.416.79:11011/dbName
spring.datasource.dynamic.datasource.dsName.username=sa
spring.datasource.dynamic.datasource.dsName.password=pwd123
spring.datasource.dynamic.datasource.dsName.druid.initialSize=5
spring.datasource.dynamic.datasource.dsName.druid.minIdle=5
spring.datasource.dynamic.datasource.dsName.druid.maxActive=20
spring.datasource.dynamic.datasource.dsName.druid.maxWait=60000
spring.datasource.dynamic.datasource.dsName.druid.timeBetweenEvictionRunsMillis=60000
spring.datasource.dynamic.datasource.dsName.druid.minEvictableIdleTimeMillis=300000
spring.datasource.dynamic.datasource.dsName.druid.validationQuery=SELECT 1
spring.datasource.dynamic.datasource.dsName.druid.testWhileIdle=true
spring.datasource.dynamic.datasource.dsName.druid.testOnBorrow=false
spring.datasource.dynamic.datasource.dsName.druid.testOnReturn=false
spring.datasource.dynamic.datasource.dsName.druid.poolPreparedStatements=true
spring.datasource.dynamic.datasource.dsName.druid.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.dynamic.datasource.dsName.druid.connection-properties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
spring.datasource.dynamic.datasource.dsName.druid.useGlobalDataSourceStat=true

普通单数据源配置

spring.datasourcedriverClassName=net.sourceforge.jtds.jdbc.Driver
spring.datasourceurl=jdbc:jtds:sqlserver://118.127.416.79:11011/dbName
spring.datasourceusername=sa
spring.datasourcepassword=pwd123
spring.datasourcedruid.initialSize=5
spring.datasourcedruid.minIdle=5
spring.datasourcedruid.maxActive=20
spring.datasourcedruid.maxWait=60000
spring.datasourcedruid.timeBetweenEvictionRunsMillis=60000
spring.datasourcedruid.minEvictableIdleTimeMillis=300000
spring.datasourcedruid.validationQuery=SELECT 1
spring.datasourcedruid.testWhileIdle=true
spring.datasourcedruid.testOnBorrow=false
spring.datasourcedruid.testOnReturn=false
spring.datasourcedruid.poolPreparedStatements=true
spring.datasourcedruid.maxPoolPreparedStatementPerConnectionSize=20
spring.datasourcedruid.connection-properties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
spring.datasourcedruid.useGlobalDataSourceStat=true

二、ODBC连接 Sqlserver

经过试验:无论是Windows还是linux下,freetds连接比较好入手,流程较简单,成功率高,推荐优先选择freetds连接,即java使用jtds
ODBC总是在意想不到的出问题…

本机的开发环境是Windows11系统,由于都是微软系的,过程比较顺利

1. Windows 下创建ODBC系统DSN

i. 配置ODBC数据源

在电脑内搜索ODBC
打开后选择 系统DSN,点 添加,选择SQL Server,点完成
名称 是你所创建的数据源名称,等会连接时候要用到,例如 sqlserver2008
描述 随便描述,
服务器 sqlserver地址和端口号,用逗号隔开 例如118.127.416.79,11011
下一页
选择 使用用户输入登录ID和密码 SQLserver 验证
勾选 连接sqlserver 以获得其他配置选项的默认配置
然后输入登录ID:账号,密码:密码
下一页
选择默认连接的数据库
然后下一页 完成,测试一下连接就可以了。
创建完如图:
在这里插入图片描述

ii. java实现以及配置

后续具体的jar配置和代码实现参考
https://blog.csdn.net/vdora/article/details/119870738?spm=1001.2014.3001.5506
中的二、在Java8中使用JDBC-ODBC Bridge
这里也简单说一下,主要操作如下

需要的文件可以从以下链接中获取:
链接:https://pan.baidu.com/s/1LywdcvzXypazELd0zkwBLg
提取码:r1s2
拿到两个文件 jdbc_64.jar 和 jdbcodbc.dll
将jdbc_64.jar放到jdk的jre的lib目录下。
原文:
PS: 如果不成功,请尝试放在jdk的jre的lib的ext目录下!目前我是windows环境的jdk放在前者可以,linux放在后者可以。
但是我这里Windows,直接放到了ext下面是好使的
例如:D:\java8\jdk\jre\lib\ext\jdbc_64.jar
复制jdbcodbc.dll 到jdk的jre\bin目录下。
注意:以上使用的jre都是指jdk中的jre目录

连接测试java代码

public static void main(String[] args) throws Exception {
        // 其中sqlserver2008为刚才创建的数据源名 
        String url = "jdbc:odbc:sqlserver2008";
        // 其中sa为用户名,pwd123为密码
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		Connection con = DriverManager.getConnection(url,"sa","pwd123");
		String sql = "SELECT count(*) FROM table1";
		//准备执行语句
		PreparedStatement pst = con.prepareStatement(sql);
		//执行语句,得到结果集
		ResultSet ret = pst.executeQuery();
		String s = null;
		while (ret.next()) {
			s = ret.getString(1);
			System.out.println(s);
		}
		con.close();
    } 

以上测试成功的话,配置就可以集成到项目里面了
dynamic-DataSource配置文件,sqlserver2008既刚才创建的数据源名:
注意url的写法 :jdbc:odbc:sqlserver2008

spring.datasource.dynamic.datasource.dsName.driverClassName=sun.jdbc.odbc.JdbcOdbcDriver
spring.datasource.dynamic.datasource.dsName.url=jdbc:odbc:sqlserver2008
spring.datasource.dynamic.datasource.dsName.username=sa
spring.datasource.dynamic.datasource.dsName.password=pwd123
spring.datasource.dynamic.datasource.dsName.druid.initialSize=5
spring.datasource.dynamic.datasource.dsName.druid.minIdle=5
spring.datasource.dynamic.datasource.dsName.druid.maxActive=20
spring.datasource.dynamic.datasource.dsName.druid.maxWait=60000
spring.datasource.dynamic.datasource.dsName.druid.timeBetweenEvictionRunsMillis=60000
spring.datasource.dynamic.datasource.dsName.druid.minEvictableIdleTimeMillis=300000
spring.datasource.dynamic.datasource.dsName.druid.validationQuery=SELECT 1
spring.datasource.dynamic.datasource.dsName.druid.testWhileIdle=true
spring.datasource.dynamic.datasource.dsName.druid.testOnBorrow=false
spring.datasource.dynamic.datasource.dsName.druid.testOnReturn=false
spring.datasource.dynamic.datasource.dsName.druid.poolPreparedStatements=true
spring.datasource.dynamic.datasource.dsName.druid.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.dynamic.datasource.dsName.druid.connection-properties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
spring.datasource.dynamic.datasource.dsName.druid.useGlobalDataSourceStat=true

普通单数据源配置:

spring.datasource.driverClassName=sun.jdbc.odbc.JdbcOdbcDriver
spring.datasource.url=jdbc:odbc:sqlserver2008
spring.datasource.username=sa
spring.datasource.password=pwd123
spring.datasource.druid.initialSize=5
spring.datasource.druid.minIdle=5
spring.datasource.druid.maxActive=20
spring.datasource.druid.maxWait=60000
spring.datasource.druid.timeBetweenEvictionRunsMillis=60000
spring.datasource.druid.minEvictableIdleTimeMillis=300000
spring.datasource.druid.validationQuery=SELECT 1
spring.datasource.druid.testWhileIdle=true
spring.datasource.druid.testOnBorrow=false
spring.datasource.druid.testOnReturn=false
spring.datasource.druid.poolPreparedStatements=true
spring.datasource.druid.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.druid.connection-properties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
spring.datasource.druid.useGlobalDataSourceStat=true

2. linux 下创建ODBC DSN

试了很多次并未成功,整体的安装步骤也都是根据官方提供的来的,但是不知道为什么一直报 [unixODBC][Driver Manager]Data source name not found and no default driver specified,仔细检查也没有发现配置有啥问题,现把安装配置步骤贴上,希望有大佬能指出哪里出了问题
线上服务器使用的是 ubuntu 20.04
参考链接:https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15#ubuntu18

i 安装ODBC和sqlserver驱动

sudo su
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

#Download appropriate package for the OS version
#Choose only ONE of the following, corresponding to your OS version

#Ubuntu 16.04
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

#Ubuntu 18.04
curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

#Ubuntu 20.04
curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

#Ubuntu 20.10
curl https://packages.microsoft.com/config/ubuntu/20.10/prod.list > /etc/apt/sources.list.d/mssql-release.list

exit
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y apt-get install -y mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
# optional: for unixODBC development headers
sudo apt-get install -y unixodbc-dev

ii 配置

安装完后在 /etc/ 下会有 odbc.iniodbcinst.ini两个配置文件,我的配置如下:

odbc.ini

[MSS]
ServerName=MSS
Driver=ODBCD
Description=sqlserver2008
Server=112.23.34.65
Port=1433
User=sa
Password=pwd@123
Database=dbname

odbcinst.ini

[ODBCD]
Description=MsDriver18
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.0.so.1.1
Setup=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.0.so.1.1
FileUsage=1
CPTimeout=5
CPReuse=5

有文章说如果有 @ 符号需要加转义符,但是我试了不好使

通过OTL连接数据库在使用连接字符串时需要注意,连接字符串中如果存在“@”符号,
OTL会认为是“oracle_format”,
后续解析连接字符串时将无法解析出正确的用户名密码等信息。
如果确实存在“@”符号,
例如“DSN=kb;UID=sa;PWD=scyz@123”
需要在“@”符号前添加转义字符“\”,
正确的连接字符串为“DSN=kb;UID=sa;PWD=scyz\@123”。

MSSodbc.ini 文件中配置的中括号内的数据源名
使用 isql -v MSS 命令运行,但是失败
isql -v 查看错误日志

由于ubuntu上面ODBC配置一直失败,后续在ubuntu上测试用 freetds 可以连接上,所以线上服务器采用jtds


总结

如果不是这次sqlserver这个大坑,之前还真没了解到odbcjtds这两种连接数据库的方式,以后可以进行灵活运用了。各路大神还有什么连接数据库的好方法,请留言补充吧~