Spring Boot后端连接SQL Server数据库失败求助

环境与配置 #

• 使用SQL Server LocalDB实例 LocalDB/MSSQLLocalDB，数据库为vitrinedb，采用SQL Server认证
• 创建了拥有sysadmin服务器角色的登录名springboot，已映射到vitrinedb的用户springbootuser

application.yml配置 #

app:
  datasource:
    jdbc-url: jdbc:sqlserver://localhost;databaseName=vitrinedb;trustServerCertificate=true
    username: springboot
    password: *****
    pool-size: 30

报错信息 #

SQL State : S0001
Error Code : 18456
Message : 用户'springboot'登录失败。ClientConnectionId:8e1db9be-a1a5-4faa-9be2-b3b75795798f

堆栈跟踪 #

at org.flywaydb.core.internal.jdbc.JdbcUtils.openConnection(JdbcUtils.java:60) ~[flyway-core-9.16.3.jar:na]
at org.flywaydb.core.internal.jdbc.JdbcConnectionFactory.(JdbcConnectionFactory.java:74) ~[flyway-core-9.16.3.jar:na]
at org.flywaydb.core.FlywayExecutor.execute(FlywayExecutor.java:140) ~[flyway-core-9.16.3.jar:na]
at org.flywaydb.core.Flyway.migrate(Flyway.java:140) ~[flyway-core-9.16.3.jar:na]
at org.springframework.boot.autoconfigure.flyway.FlywayMigrationInitializer.afterPropertiesSet(FlywayMigrationInitializer.java:66) ~[spring-boot-autoconfigure-3.1.3.jar:3.1.3]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1817) ~[spring-beans-6.0.11.jar:6.0.11]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1766) ~[spring-beans-6.0.11.jar:6.0.11]
... 23 common frames omitted
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: 用户'springboot'登录失败。ClientConnectionId:8e1db9be-a1a5-4faa-9be2-b3b75795798f
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265) ~[mssql-jdbc-11.2.3.jre17.jar:na]
at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:300) ~[mssql-jdbc-11.2.3.jre17.jar:na]
at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:133) ~[mssql-jdbc-11.2.3.jre17.jar:na]
at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:37) ~[mssql-jdbc-11.2.3.jre17.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:6299) ~[mssql-jdbc-11.2.3.jre17.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:4911) ~[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:138) ~[HikariCP-5.0.1.jar:na]
at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:359) ~[HikariCP-5.0.1.jar:na]
at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:201) ~[HikariCP-5.0.1.jar:na]
at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:470) ~[HikariCP-5.0.1.jar:na]
at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:561) ~[HikariCP-5.0.1.jar:na]
at com.zaxxer.hikari.pool.HikariPool.(HikariPool.java:100) ~[HikariCP-5.0.1.jar:na]
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112) ~[HikariCP-5.0.1.jar:na]
at org.flywaydb.core.internal.jdbc.JdbcUtils.openConnection(JdbcUtils.java:48) ~[flyway-core-9.16.3.jar:na]
... 29 common frames omitted

解决建议 #

1. 修正JDBC连接URL #

LocalDB的连接格式和常规SQL Server实例不同，需使用专用格式：

jdbc-url: jdbc:sqlserver://(LocalDB)\\MSSQLLocalDB;databaseName=vitrinedb;trustServerCertificate=true

或命名管道格式：

jdbc-url: jdbc:sqlserver://localhost\\MSSQLLocalDB;databaseName=vitrinedb;trustServerCertificate=true

2. 启用SQL Server混合认证模式 #

• 打开SSMS连接到LocalDB实例，右键实例→属性→安全性
• 确认选中「SQL Server和Windows身份验证模式」
• 重启LocalDB：执行命令 sqllocaldb stop MSSQLLocalDB，再执行 sqllocaldb start MSSQLLocalDB

3. 验证登录名有效性 #

• 用SSMS直接以springboot账号登录LocalDB，确认密码正确
• 检查登录名「状态」选项卡，确保「登录」权限为「授予」且未被禁用

4. 修正Spring Boot配置前缀 #

若使用默认数据源自动配置，需改用spring.datasource前缀（而非app.datasource）：

spring:
  datasource:
    url: jdbc:sqlserver://(LocalDB)\\MSSQLLocalDB;databaseName=vitrinedb;trustServerCertificate=true
    username: springboot
    password: *****
    hikari:
      maximum-pool-size: 30

5. 确认驱动兼容性 #

当前mssql-jdbc-11.2.3.jre17.jar与Spring Boot 3.1.3（Java 17）兼容，可尝试升级驱动到最新稳定版排除版本问题。

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