Java Spring Boot REST API连接本地SQL Server实例失败排查

错误信息 #

com.microsoft.sqlserver.jdbc.SQLServerException: 此驱动未配置集成身份验证。ClientConnectionId:a20200ea-d191-4404-8445-05ea0a3c2af8
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:3806) ~[mssql-jdbc-11.2.3.jre17.jar:na]
        at com.microsoft.sqlserver.jdbc.AuthenticationJNI.<init>(AuthenticationJNI.java:74) ~[mssql-jdbc-11.2.3.jre17.jar:na]
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:4860) ~[mssql-jdbc-11.2.3.jre17.jar:na]
        at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:4849) ~[mssql-jdbc-11.2.3.jre17.jar:na]
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7620) ~[mssql-jdbc-11.2.3.jre17.jar:na]
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3916) ~[mssql-jdbc-11.2.3.jre17.jar:na]
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:3358) ~[mssql-jdbc-11.2.3.jre17.jar:na]
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2950) ~[mssql-jdbc-11.2.3.jre17.jar:na]
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2790) ~[mssql-jdbc-11.2.3.jre17.jar:na]
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1663) ~[mssql-jdbc-11.2.3.jre17.jar:na]
        at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1064) ~[mssql-jdbc-11.2.3.jre17.jar:na]
        at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:121) ~[HikariCP-3.4.5.jar:na]
        at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:358) ~[HikariCP-3.4.5.jar:na]
        at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206) ~[HikariCP-3.4.5.jar:na]
        at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:477) ~[HikariCP-3.4.5.jar:na]
        at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:560) ~[HikariCP-3.4.5.jar:na]
        at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115) ~[HikariCP-3.4.5.jar:na]
        at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112) ~[HikariCP-3.4.5.jar:na]
        at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:158) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
        at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:116) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
        at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:79) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:324) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
        at org.springframework.boot.jdbc.EmbeddedDatabaseConnection.isEmbedded(EmbeddedDatabaseConnection.java:120) ~[spring-boot-2.3.3.RELEASE.jar:2.3.3.RELEASE]
        at org.springframework.boot.autoconfigure.orm.jpa.HibernateDefaultDdlAutoProvider.getDefaultDdlAuto(HibernateDefaultDdlAutoProvider.java:42) ~[spring-boot-autoconfigure-2.3.3.RELEASE.jar:2.3.3.RELEASE]
        at org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaConfiguration.lambda$getVendorProperties$1(HibernateJpaConfiguration.java:130) ~[spring-boot-autoconfigure-2.3.3.RELEASE.jar:2.3.3.RELEASE]
        at org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings.getDdlAuto(HibernateSettings.java:41) ~[spring-boot-autoconfigure-2.3.3.RELEASE.jar:2.3.3.RELEASE]
        at org.springframework.boot.autoconfigure.orm.jpa.HibernateProperties.determineDdlAuto(HibernateProperties.java:136) ~[spring-boot-autoconfigure-2.3.3.RELEASE.jar:2.3.3.RELEASE]    
        at org.springframework.boot.autoconfigure.orm.jpa.HibernateProperties.getAdditionalProperties(HibernateProperties.java:102) ~[spring-boot-autoconfigure-2.3.3.RELEASE.jar:2.3.3.RELEASE]
        at org.springframework.boot.autoconfigure.orm.jpa.HibernateProperties.determineHibernateProperties(HibernateProperties.java:94) ~[spring-boot-autoconfigure-2.3.3.RELEASE.jar:2.3.3.RELEASE]
        at org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaConfiguration.getVendorProperties(HibernateJpaConfiguration.java:132) ~[spring-boot-autoconfigure-2.3.3.RELEASE.jar:2.3.3.RELEASE]
        at org.springframework.boot.autoconfigure.orm.jpa.JpaBaseConfiguration.entityManagerFactory(JpaBaseConfiguration.java:133) ~[spring-boot-autoconfigure-2.3.3.RELEASE.jar:2.3.3.RELEASE]
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
        at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
        at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:154) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
        at org.springframework.beans.factory.support.ConstructorResolver.instantiate(ConstructorResolver.java:650) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
        at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:635) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1336) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1176) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:556) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:516) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
        at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:324) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:226) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]        
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:322) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
        at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1109) ~[spring-context-5.2.8.RELEASE.jar:5.2.8.RELEASE]
        at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:869) ~[spring-context-5.2.8.RELEASE.jar:5.2.8.RELEASE]
        at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:551) ~[spring-context-5.2.8.RELEASE.jar:5.2.8.RELEASE]
        at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:143) ~[spring-boot-2.3.3.RELEASE.jar:2.3.3.RELEASE]
        at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:758) ~[spring-boot-2.3.3.RELEASE.jar:2.3.3.RELEASE]
        at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:750) ~[spring-boot-2.3.3.RELEASE.jar:2.3.3.RELEASE]
        at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:397) ~[spring-boot-2.3.3.RELEASE.jar:2.3.3.RELEASE]
        at org.springframework.boot.SpringApplication.run(SpringApplication.java:315) ~[spring-boot-2.3.3.RELEASE.jar:2.3.3.RELEASE]
        at org.springframework.boot.SpringApplication.run(SpringApplication.java:1237) ~[spring-boot-2.3.3.RELEASE.jar:2.3.3.RELEASE]
        at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226) ~[spring-boot-2.3.3.RELEASE.jar:2.3.3.RELEASE]
        at com.example.myproject.Main.main(Main.java:11) ~[classes/:na]
Caused by: java.lang.UnsatisfiedLinkError: 无法加载身份验证DLL mssql-jdbc_auth-11.2.3.x64
        at com.microsoft.sqlserver.jdbc.AuthenticationJNI.<clinit>(AuthenticationJNI.java:63) ~[mssql-jdbc-11.2.3.jre17.jar:na]
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:4859) ~[mssql-jdbc-11.2.3.jre17.jar:na]
        ... 54 common frames omitted

配置文件 src/main/resources/application.properties #

spring.datasource.url=jdbc:sqlserver://localhost:1433;databaseName=TestDB;integratedSecurity=true;trustServerCertificate=true;
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver

pom.xml 内容 #

<project>
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.example.myproject</groupId>
  <artifactId>Java_API</artifactId>
  <version>0.0.1-SNAPSHOT</version>

  <dependencies>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
      <version>2.3.3.RELEASE</version>
    </dependency> 


    <dependency>
      <groupId>com.microsoft.sqlserver</groupId>
      <artifactId>mssql-jdbc</artifactId>
      <version>11.2.3.jre17</version>
    </dependency>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jpa</artifactId>
      <version>2.3.3.RELEASE</version>
    </dependency>

  
  </dependencies>

  <properties>
     <maven.compiler.source>1.8</maven.compiler.source>
     <maven.compiler.target>1.8</maven.compiler.target>
  </properties>

  <build>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
         <version>3.0.2</version>
      </plugin>
    </plugins>
  </build>
</project>

已尝试的排查步骤 #

• 检查连接属性是否正确，使用localhost作为实例名，怀疑可能是此问题；
• 确认SQL Server实例正在运行并接受指定端口的TCP/IP连接；
• 检查防火墙未阻止该端口的TCP连接；
• 确认SQL Server实例正常运行并接受连接；
• 验证Java版本为17，jdbc版本为11.2.3.jre17；
• 尝试在数据源URL中使用user:<server Name>且不设置密码（因SQL Server为Windows认证）；
• 尝试使用安装时配置的user:root和password:password作为数据源凭证。

解决方案建议 #

核心问题定位 #

错误堆栈明确显示无法加载身份验证DLL mssql-jdbc_auth-11.2.3.x64，这是使用Windows集成身份验证（integratedSecurity=true）时的典型问题——JDBC驱动需要依赖对应的本地DLL文件来完成Windows身份验证流程。

解决步骤 #

1. 下载对应版本的mssql-jdbc_auth DLL
   需下载与你的JDBC版本（11.2.3）、系统架构（x64）匹配的mssql-jdbc_auth-11.2.3.x64.dll文件。
2. 放置DLL到正确路径
   将DLL文件放到以下任一位置：
   • JRE的bin目录；
   • 项目运行时的工作目录；
   • Windows系统的System32目录；
   • 通过java.library.path指定的目录。
3. 版本兼容检查
   你的Spring Boot版本是2.3.3.RELEASE（对应Spring 5.2.8），但maven编译版本设置为1.8，却使用了JRE17版本的JDBC驱动，存在版本不匹配风险。建议：
   • 要么将JDBC版本换成适配Java 8的（比如9.4.1.jre8）；
   • 要么将maven编译版本改为17，同时升级Spring Boot到2.6.x及以上版本（2.3.x不支持Java 17）。
4. 简化连接配置（可选）
   如果不需要Windows集成身份验证，可改为SQL Server账号密码认证：
   • 修改URL去掉integratedSecurity=true；
   • 在application.properties中添加：

     spring.datasource.username=你的SQL账号
     spring.datasource.password=你的SQL密码
     

内容的提问来源于stack exchange，提问作者user21086690