无法验证服务器名称“iapps-in-dev-sql-server.privatelink.database.windows.net” 检索数据库元数据时出错;-解网

问：

升级后出现以下错误。它在 jdk8 和 spring boot 版本 1.5.3-release 和 MS Sql 驱动程序 mssql-jdbc-6.1.0.jre7.jar 中工作正常

现在我将 jdk 升级到 21，将 spring-boot 升级到 2.7.16，并使用下面的 jdbc 驱动程序

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>12.4.2.jre11</version>
</dependency>

我在连接到数据库时收到一个奇怪的异常。请在下面找到例外情况。请让我知道如何解决此问题

org.springframework.dao.DataAccessResourceFailureException: Error retrieving database meta-data; nested exception is org.springframework.jdbc.support.MetaDataAccessException: Could not get Connection for extracting meta-data; nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: Cannot create PoolableConnectionFactory ("encrypt" property is set to "true" and "trustServerCertificate" property is set to "false" but the driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption: Error: Failed to validate the server name "iapps-in-dev-sql-server.privatelink.database.windows.net"in a certificate during Secure Sockets Layer (SSL) initialization. Name in certificate "cr11.indiacentral1-a.control.database.windows.net". ClientConnectionId:225fcdb4-f187-4250-9dcc-07def6542715)
    at org.springframework.jdbc.core.metadata.CallMetaDataProviderFactory.createMetaDataProvider(CallMetaDataProviderFactory.java:144)
    at org.springframework.jdbc.core.metadata.CallMetaDataContext.initializeMetaData(CallMetaDataContext.java:254)
    at org.springframework.jdbc.core.simple.AbstractJdbcCall.compileInternal(AbstractJdbcCall.java:315)
    at org.springframework.jdbc.core.simple.AbstractJdbcCall.compile(AbstractJdbcCall.java:298)
    at com.gtl.industryapp.commonservice.middleware.service.db.helper.CommonServiceDatabaseCommunicator.setDefaultParameter(CommonServiceDatabaseCommunicator.java:178)
    at com.gtl.industryapp.commonservice.middleware.service.db.helper.CommonServiceDatabaseCommunicator.getResult(CommonServiceDatabaseCommunicator.java:157)
    at com.gtl.industryapp.commonservice.middleware.service.db.helper.CommonServiceDatabaseCommunicator.getSPResult(CommonServiceDatabaseCommunicator.java:70)
    at com.gtl.industryapp.commonservice.middleware.service.db.helper.CommonServiceDatabaseCommunicator$$FastClassBySpringCGLIB$$c9e927a0.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    at org.springframework.aop.framework.CglibAopProxy.invokeMethod(CglibAopProxy.java:386)
    at org.springframework.aop.framework.CglibAopProxy.access$000(CglibAopProxy.java:85)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:703)
    at com.gtl.industryapp.commonservice.middleware.service.db.helper.CommonServiceDatabaseCommunicator$$EnhancerBySpringCGLIB$$37381077.getSPResult(<generated>)
    at com.gtl.industryapp.commonservice.middleware.service.impl.GeneralDataServiceImpl.getData(GeneralDataServiceImpl.java:44)
    at com.gtl.industryapp.commonservice.middleware.service.impl.helper.AppStoreServiceHelper.executeForApi(AppStoreServiceHelper.java:56)
    at com.gtl.industryapp.commonservice.middleware.service.impl.AppStoreServiceImpl.getAppDataForApi(AppStoreServiceImpl.java:44)
    at com.gtl.industryapp.commonservice.middleware.controller.AppStoreApiServiceController.getAPPData(AppStoreApiServiceController.java:39)
    at com.gtl.industryapp.commonservice.middleware.controller.AppStoreApiServiceController$$FastClassBySpringCGLIB$$701775b8.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:792)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:762)
    at org.springframework.aop.aspectj.AspectJAfterAdvice.invoke(AspectJAfterAdvice.java:49)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:762)
    at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:58)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:762)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:762)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:707)
    at com.gtl.industryapp.commonservice.middleware.controller.AppStoreApiServiceController$$EnhancerBySpringCGLIB$$38c57cc5.getAPPData(<generated>)
    at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
    at java.base/java.lang.reflect.Method.invoke(Method.java:580)
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1072)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:965)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:529)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:623)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:209)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153)
    at com.gtl.iapps.db.multitenancy.routing.intercepter.SecurityContextFilter.doFilterInternal(SecurityContextFilter.java:69)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153)
    at org.springframework.web.filter.CorsFilter.doFilterInternal(CorsFilter.java:91)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:337)
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:115)
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:81)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346)
    at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:122)
    at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:116)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346)
    at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:126)
    at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:81)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346)
    at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:109)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346)
    at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:149)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346)
    at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346)
    at org.springframework.security.oauth2.provider.authentication.OAuth2AuthenticationProcessingFilter.doFilter(OAuth2AuthenticationProcessingFilter.java:182)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346)
    at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:103)
    at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:89)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346)
    at org.springframework.security.web.header.HeaderWriterFilter.doHeadersAfter(HeaderWriterFilter.java:90)
    at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:75)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346)
    at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:112)
    at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:82)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346)
    at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:55)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346)
    at org.springframework.security.web.session.DisableEncodeUrlFilter.doFilterInternal(DisableEncodeUrlFilter.java:42)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346)
    at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:221)
    at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:186)
    at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:354)
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:267)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153)
    at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:96)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153)
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:168)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:481)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:130)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:390)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:926)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1790)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
    at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
    at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.base/java.lang.Thread.run(Thread.java:1583)

原因：org.springframework.jdbc.support.MetaDataAccessException：无法获取用于提取元数据的连接;嵌套异常为 org.springframework.jdbc.CannotGetJdbcConnectionException： Failed to obtain JDBC Connection;嵌套异常是 java.sql.SQLException：无法创建 PoolableConnectionFactory（“encrypt”属性设置为“true”，“trustServerCertificate”属性设置为“false”，但驱动程序无法使用安全套接字层（SSL）加密与 SQL Server 建立安全连接：错误：在安全套接字层（SSL）初始化期间，无法验证证书中的服务器名称“iapps-in-dev-sql-server.privatelink.database.windows.net”。证书“cr11.indiacentral1-a.control.database.windows.net”中的名称。ClientConnectionId：225fcdb4-f187-4250-9dcc-07def6542715）

有时它以原因：org.springframework.jdbc.support.MetaDataAccessException：无法获取用于提取元数据的连接;嵌套异常为 org.springframework.jdbc.CannotGetJdbcConnectionException： Failed to obtain JDBC Connection;嵌套异常是 java.sql.SQLException：无法创建 PoolableConnectionFactory（“encrypt”属性设置为“true”，“trustServerCertificate”属性设置为“false”，但驱动程序无法使用安全套接字层（SSL）加密与 SQL Server 建立安全连接：错误：在安全套接字层（SSL）初始化期间，无法验证证书中的服务器名称“iapps-in-dev-sql-server.privatelink.database.windows.net”。证书“cr11.indiacentral1-a.control.database.windows.net”中的名称。ClientConnectionId：225fcdb4-f187-4250-9dcc-07def6542715）

其他一些时候作为原因：org.springframework.jdbc.support.MetaDataAccessException：无法获取用于提取元数据的连接;嵌套异常为 org.springframework.jdbc.CannotGetJdbcConnectionException： Failed to obtain JDBC Connection;嵌套异常是 java.sql.SQLException：无法创建 PoolableConnectionFactory（“encrypt”属性设置为“true”，“trustServerCertificate”属性设置为“false”，但驱动程序无法使用安全套接字层（SSL）加密与 SQL Server 建立安全连接：错误：在安全套接字层（SSL）初始化期间，无法验证证书中的服务器名称“iapps-in-dev-sql-server.privatelink.database.windows.net”。证书“cr13.indiacentral1-a.control.database.windows.net”中的名称。ClientConnectionId：225fcdb4-f187-4250-9dcc-07def6542715）

数据库位于 azure 中。我正在寻找这个问题的解决方案。

spring-boot jdbctemplates 的mssql-jdbc

0赞 JCompetence 11/1/2023

好吧，它与较旧的 JDK 一起使用感觉很奇怪？该错误告诉您，您正在尝试使用服务器名称为“cr11.indiacentral1-a.control.database.windows.net”的证书与“iapps-in-dev-sql-server.privatelink.database.windows.net”建立SSL连接，该证书与“”不匹配，或者您的证书不再有效。请参阅 techcommunity.microsoft.com/t5/azure-database-support-blog/...

答：

0赞 JCompetence 11/1/2023 #1

错误实际上是在告诉你出了什么问题。

"encrypt" property is set to "true" and "trustServerCertificate" property is set to "false"

文档声明如下：

encrypt = true
trustServerCertificate = false or blank
hostNameInCertificate = value
trustStore = blank
trustStorePassword = blank
The driver requests to use TLS encryption with the server.

If the server requires the client to support TLS encryption or if the server supports encryption, the driver will initiate the TLS certificate exchange.

The driver will validate the TLS certificate's subject value by using the value specified for the hostNameInCertificate property.

If the server isn't configured to support encryption, the driver will raise an error and terminate the connection.

https://learn.microsoft.com/en-us/sql/connect/jdbc/understanding-ssl-support?view=sql-server-ver16

这意味着，因为和，那么它将强制验证证书 TLS 链，您可能没有或拥有该链，但该链无效。（例如，TLS Subject 值与服务器的 hostName 不匹配encrypt=truetrustServerCertificate=false

现在，如果您查看 mssql-jdbc 的 github 代码

您将在版本 6.1.0 中看到，这意味着encrypt=falsetrustServerCertificate=false

encrypt = false or blank
trustServerCertificate = any
hostNameInCertificate = any
trustStore = any
trustStorePassword = any
The driver won't force the server to support TLS encryption. If the server has a self-signed certificate, the driver initiates the TLS certificate exchange. The TLS certificate won't be validated and only the credentials (in the login packet) are encrypted.

If the server requires the client to support TLS encryption, the driver will initiate the TLS certificate exchange. The TLS certificate won't be validated, but the entire communication will be encrypted.

https://github.com/microsoft/mssql-jdbc/blob/cd2f4c87403aa6a41cbe8b3dda3106392dc6ee0b/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerDriver.java#L917

如果您检查版本 12.4.2，它现在通过同时拥有和来强制执行 TLS 验证。encrypt=truetrustServerCertificate=false

https://github.com/microsoft/mssql-jdbc/blob/v12.4.2/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerDriver.java#L610

因此，您需要在 Java 信任库上拥有数据库服务器证书才能进行连接。

或设置，这是不安全的，因为它本质上意味着不验证 TLS 证书。encrypt=truetrustServerCertificate=true

encrypt = true
trustServerCertificate = true
hostNameInCertificate = any
trustStore = any
trustStorePassword = any
The driver requests to use TLS encryption with the server.

If the server requires the client to support TLS encryption or if the server supports encryption, the driver will initiate the TLS certificate exchange. If the trustServerCertificate property is set to "true", the driver won't validate the TLS certificate.

If the server isn't configured to support encryption, the driver will raise an error and terminate the connection.

0赞 Sreejesh 11/1/2023

没错，trustServerCertificate=true 解决了问题

上一个：Spring Boot - 使用 Spring Data JPA 和 JdbcTemplate

下一个：从结果中排除 NULL 值

无法验证服务器名称“iapps-in-dev-sql-server.privatelink.database.windows.net” 检索数据库元数据时出错;

Failed to validate the server name "iapps-in-dev-sql-server.privatelink.database.windows.net" Error retrieving database meta-data;

评论

评论