Environment:
- Streamsets Data Collector 5.1.
- Producer or consumer Library for Microsoft SQL Server.
Issue:
The pipeline starts failing with following messages when try to fetch or produce the data to Microsoft SQL Server.
This happens on data collector is 5.1 but the same pipeline is working fine on 4.x versions.
com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target". ClientConnectionId:af15a6cf-f28d-4e21-adb3-141826becad0
at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:3680) ~[mssql-jdbc-10.2.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:2113) ~[mssql-jdbc-10.2.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:3204) ~[mssql-jdbc-10.2.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2833) ~[mssql-jdbc-10.2.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2671) ~[mssql-jdbc-10.2.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1640) ~[mssql-jdbc-10.2.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:936) ~[mssql-jdbc-10.2.1.jre8.jar:?]
at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138) ~[HikariCP-4.0.3.jar:?]
at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:364) ~[HikariCP-4.0.3.jar:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_341]
at java.lang.Thread.run(Thread.java:750) ~[?:1.8.0_341]
Caused by: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
at sun.security.ssl.Alert.createSSLException(Alert.java:131) ~[?:1.8.0_341]
at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:2021) ~[mssql-jdbc-10.2.1.jre8.jar:?]
... 45 more
Caused by: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
at sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:439) ~[?:1.8.0_341]
at sun.security.validator.PKIXValidator.engineValidate(PKIXValidator.java:306) ~[?:1.8.0_341]
at sun.security.validator.Validator.validate(Validator.java:271) ~[?:1.8.0_341]
at sun.security.ssl.X509TrustManagerImpl.validate(X509TrustManagerImpl.java:312) ~[?:1.8.0_341]
at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:2021) ~[mssql-jdbc-10.2.1.jre8.jar:?]
... 45 more
Caused by: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
at sun.security.provider.certpath.SunCertPathBuilder.build(SunCertPathBuilder.java:141) ~[?:1.8.0_341]
at sun.security.provider.certpath.SunCertPathBuilder.engineBuild(SunCertPathBuilder.java:126) ~[?:1.8.0_341]
at java.security.cert.CertPathBuilder.build(CertPathBuilder.java:280) ~[?:1.8.0_341]
Resolution:
With 5.1.0, Data Collector uses Microsoft JDBC Driver for SQL Server version 10.2.1 to connect to Microsoft SQL Server. According to Microsoft, this version has introduced a breaking backward-incompatible change.
As a result, after you upgrade to Data Collector 5.1.0, upgraded pipelines that connect to Microsoft SQL Server without SSL/TLS encryption will likely fail with a message.
This issue can be resolved by configuring SSL/TLS encryption between Microsoft SQL Server and Data Collector. For details on configuring clients for SSL/TLS encryption, see the Microsoft SQL Server documentation.
Otherwise, you can address this issue at a pipeline level by adding encrypt=false to the connection string, or by adding encrypt as an additional JDBC property and setting it to false.
For more details, please review release notes of v5.1: