In order to connect to Synapse SQL Pool using a JDBC driver there are some additional aspects to consider (https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server?view=azure-sq... )
Your newly created Java application might not be able to successfully connect from your SSL enabled Java server.
Depending on your configuration you might encounter an error like the following:
[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
The error means the certificate path could not be built for the secured connection to succeed.
The typical solution to this error is to download the certificate from the server you are connecting to and storing it in the local trust store.
Applying this approach to an Azure Synapse SQL Pool is not ideal, as the user has no control over certificate management.. Certificates update or roll over would cause the application to fail connection. In that case the new certificate must be downloaded and included in the application local store to re-establish connectivity.
Keeping the above in mind, the approach will work for Azure Synapse SQL Pools.
For the purpose of this article we will be connecting to a SQL Pool instance named mysqlpool, from a custom Java application we named myApp.
We won’t be covering the usage details of the Java tools, but you can refer to official online Java documentation for more information.
Now you can go ahead and download the server certificate for the instance mysqlpool.
You can use OpenSSL (https://www.openssl.org/ ) or other tool that would allow you to download the server certificate, and issue a command similar to:
openssl s_client -showcerts -connect mysqlpool.database.windows.net:1443 < /dev/null | openssl x509 -outform DER > mysqlpoolcert.der
Once you have your certificate you can import it in your local trusts tore using the keytool command that is included with the Java SDK.
You can choose to apply the policy that best suits your application. In our case we have created a specific keyStore for our application to use, and have imported mysqlpoolcert.der using the following command:
keytool -import -alias 1433 -file mysqlpool.der -keystore myAppKS.jks
If the keystore doesn’t exist, you will be prompted with a set of information to set it up.
Ok now that you have the server certificate you might want to start being productive with your application.
You’ll have to launch the application using -D option to set the trustStore property:
-Djavax.net.ssl.trustStore= myAppKS.jks
If executing from the command line something like:
java -Djavax.net.ssl.trustStore=myAppKS.jks myApp
But to your surprise you still cannot connect, apparently receiving the same error:
[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: f4e109ba-a86e-47de-8703-7eee03c762dd
The error still references a path build exception, but you have the certificate loaded locally, so what is exactly happening?
While the application could load the server certificate, it could not build a trust chain with the required Certification Authorities to establish a secure connection.
The solution is to add the intermediate certificates needed to the keyStore, so to have the trust chain completely available to your application.
Microsoft’s PKI repository is public and can be found at:
https://www.microsoft.com/pki/mscorp/cps/default.htm
Your step to success is now to download and import the CAs certificates listed on the public page. Simply click on the link for the CA Certificate for all the listed CAs (at the time of this writing we have CA1, CA2, CA4 and CA5), and import them in the application keyStore using a syntax similar to:
keytool -import -alias myAppCA1 -file "Microsoft IT TLS CA 1.crt" -keystore myAppKS.jks
Repeat the command (change the value for the -alias parameter) for all the certificates you have downloaded, then you can enjoy your working, secure connection to Synapse SQL Pool!
