Getting SSL certificate error while connecting to Synapse SQL pool from Databricks in Azure using JDBC

Question

---------------------------------------------------------------------------
    Py4JJavaError                             Traceback (most recent call last)
    <command-3031308706598466> in <module>
          6   .option("tempDir", "wasbs://ajay-test@stgen2datalake01prod.blob.core.windows.net/tempDir") \
          7   .option("forwardSparkAzureStorageCredentials", "true") \
    ----> 8   .option("dbTable", "TEST_TABLE") \
          9   .load()

    /databricks/spark/python/pyspark/sql/readwriter.py in load(self, path, format, schema, **options)
        182             return self._df(self._jreader.load(self._spark._sc._jvm.PythonUtils.toSeq(path)))
        183         else:
    --> 184             return self._df(self._jreader.load())
        185 
        186     @since(1.4)

    /databricks/spark/python/lib/py4j-0.10.9-src.zip/py4j/java_gateway.py in __call__(self, *args)
       1303         answer = self.gateway_client.send_command(command)
       1304         return_value = get_return_value(
    -> 1305             answer, self.gateway_client, self.target_id, self.name)
       1306 
       1307         for temp_arg in temp_args:

    /databricks/spark/python/pyspark/sql/utils.py in deco(*a, **kw)
        125     def deco(*a, **kw):
        126         try:
    --> 127             return f(*a, **kw)
        128         except py4j.protocol.Py4JJavaError as e:
        129             converted = convert_exception(e.java_exception)

    /databricks/spark/python/lib/py4j-0.10.9-src.zip/py4j/protocol.py in get_return_value(answer, gateway_client, target_id, name)
        326                 raise Py4JJavaError(
        327                     "An error occurred while calling {0}{1}{2}.\n".
    --> 328                     format(target_id, ".", name), value)
        329             else:
        330                 raise Py4JError(

    Py4JJavaError: An error occurred while calling o284.load.
    : com.databricks.spark.sqldw.SqlDWSideException: Azure Synapse Analytics failed to execute the JDBC query produced by the connector.
    Underlying SQLException(s):
      - com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "java.security.cert.CertificateException: Failed to validate the server name in a certificate during Secure Sockets Layer (SSL) initialization.". ClientConnectionId:8aa2c9a6-331b-4685-821c-5c9dd17bbf07 [ErrorCode = 0] [SQLState = 08S01]

    at com.databricks.spark.sqldw.Utils$.wrapExceptions(Utils.scala:441)
    at com.databricks.spark.sqldw.DefaultSource.createRelation(DefaultSource.scala:61)
    at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:365)
    at org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:366)
    at org.apache.spark.sql.DataFrameReader.$anonfun$load$2(DataFrameReader.scala:355)
    at scala.Option.getOrElse(Option.scala:189)
    at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:355)
    at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:240)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)
    at py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:380)
    at py4j.Gateway.invoke(Gateway.java:295)
    at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)
    at py4j.commands.CallCommand.execute(CallCommand.java:79)
    at py4j.GatewayConnection.run(GatewayConnection.java:251)
    at java.lang.Thread.run(Thread.java:748)
    Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "java.security.cert.CertificateException: Failed to validate the server name in a certificate during Secure Sockets Layer (SSL) initialization.". ClientConnectionId:8aa2c9a6-331b-4685-821c-5c9dd17bbf07
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2998)
    at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1884)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2558)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2216)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2067)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1204)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:825)
    at com.databricks.spark.sqldw.JDBCWrapper.getConnector(SqlDWJDBCWrapper.scala:256)
    at com.databricks.spark.sqldw.JDBCWrapper.withConnection(SqlDWJDBCWrapper.scala:282)
    at com.databricks.spark.sqldw.DefaultSource.validateJdbcConnection(DefaultSource.scala:138)
    at com.databricks.spark.sqldw.DefaultSource.$anonfun$createRelation$1(DefaultSource.scala:63)
    at com.databricks.spark.sqldw.Utils$.wrapExceptions(Utils.scala:410)
    ... 18 more
    Caused by: javax.net.ssl.SSLHandshakeException: java.security.cert.CertificateException: Failed to validate the server name in a certificate during Secure Sockets Layer (SSL) initialization.
    at sun.security.ssl.Alerts.getSSLException(Alerts.java:198)
    at sun.security.ssl.SSLSocketImpl.fatal(SSLSocketImpl.java:1967)
    at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:331)
    at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:325)
    at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1688)
    at sun.security.ssl.ClientHandshaker.processMessage(ClientHandshaker.java:226)
    at sun.security.ssl.Handshaker.processLoop(Handshaker.java:1082)
    at sun.security.ssl.Handshaker.process_record(Handshaker.java:1010)
    at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:1079)
    at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1388)
    at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1416)
    at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1400)
    at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1802)
    ... 28 more
    Caused by: java.security.cert.CertificateException: Failed to validate the server name in a certificate during Secure Sockets Layer (SSL) initialization.
    at com.microsoft.sqlserver.jdbc.TDSChannel$HostNameOverrideX509TrustManager.validateServerNameInCertificate(IOBuffer.java:1578)
    at com.microsoft.sqlserver.jdbc.TDSChannel$HostNameOverrideX509TrustManager.checkServerTrusted(IOBuffer.java:1489)
    at sun.security.ssl.AbstractTrustManagerWrapper.checkServerTrusted(SSLContextImpl.java:1099)
    at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1670)
    ... 36 more

Answer 1

Hello @AjayVerma-764,

Apologize for the delay in response.

Above answer I have used: JDBC (SQL authentication)

jdbc:sqlserver://cheprasynapse.sql.azuresynapse.net:1433;database=chepra;user=chepra@cheprasynapse;password={your_password_here};encrypt=true;trustServerCertificate=true;hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=30;

For AAD auth to work correctly, some extra JARs are needed. In addition, since the Azure Databricks runtime already ships an older version the MSSQL JDBC driver, we need to replace that with a newer version first.

These are the below steps:

Use a Databricks level init script to delete the default mssql JDBC driver that ships with Databricks runtime

Use Maven to get a matching set of JARs for a given JDBC driver version. To do this, you downloaded the POM file for the 6.4 release of the driver (extract from the source here), install Apache Maven and OpenJDK if you don’t already have those and then execute mvn dependency:copy-dependencies to get the JARs under the target\dependency sub-folder.

Then use the appropriate minimum set of JARs required for the MSSQL JDBC 6.4.0 JAR within Databricks. To do this, there are 2 phases. You need the Databricks CLI to do this efficiently. We think the UI might work as well but we just prefer to use the CLI.

dbutils.fs.put("/databricks/init/scripts/sqlaadauth.sh",
"""#!/bin/bash
rm /databricks/jars/*mssql*
sleep 10s
wget https://repo1.maven.org/maven2/com/microsoft/azure/adal4j/1.6.4/adal4j-1.6.4.jar -O /databricks/jars/adal4j-1.6.4.jar
wget https://repo1.maven.org/maven2/com/nimbusds/oauth2-oidc-sdk/6.5/oauth2-oidc-sdk-6.5.jar -O /databricks/jars/oauth2-oidc-sdk-6.5.jar
wget https://repo1.maven.org/maven2/com/google/code/gson/gson/2.8.0/gson-2.8.0.jar -O /databricks/jars/gson-2.8.0.jar
wget https://repo1.maven.org/maven2/net/minidev/json-smart/1.3.1/json-smart-1.3.1.jar -O /databricks/jars/json-smart-1.3.1.jar
wget https://repo1.maven.org/maven2/com/nimbusds/nimbus-jose-jwt/8.2.1/nimbus-jose-jwt-8.2.1.jar -O /databricks/jars/nimbus-jose-jwt-8.2.1.jar
wget https://repo1.maven.org/maven2/org/slf4j/slf4j-api/1.7.21/slf4j-api-1.7.21.jar -O /databricks/jars/slf4j-api-1.7.21.jar
wget https://repo1.maven.org/maven2/com/microsoft/sqlserver/mssql-jdbc/6.4.0.jre8/mssql-jdbc-6.4.0.jre8.jar -O /databricks/jars/mssql-jdbc-6.4.0.jre8.jar""")

For more details, please do check this GitHub for your reference: https://github.com/Azure/azure-sqldb-spark/issues/28

Hope this helps. Do let us know if you any further queries.

---

Do click on "Accept Answer" and Upvote on the post that helps you, this can be beneficial to other community members.

Answer 2

Hello @AjayVerma-764,

If the encrypt property is set to true and the trustServerCertificate property is set to false and if the server name in the connection string doesn't match the server name in the TLS certificate, the following error will be issued: The driver couldn't establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "java.security.cert.CertificateException: Failed to validate the server name in a certificate during Secure Sockets Layer (SSL) initialization.". As of version 7.2, the driver supports wildcard pattern matching in the left-most label of the server name in the TLS certificate.

("url", "jdbc:sqlserver://cheprasynapse.sql.azuresynapse.net:1433;database=chepra;user={Your_username};password={Your_Password};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=30;")  

When the encrypt property is set to true and the trustServerCertificate property is set to true, the Microsoft JDBC Driver for SQL Server won't validate the SQL Server TLS certificate. This is usually required for allowing connections in test environments, such as where the SQL Server instance has only a self signed certificate.

("url", "jdbc:sqlserver://cheprasynapse.sql.azuresynapse.net:1433;database=chepra;user={Your_username};password={Your_Password};encrypt=true;trustServerCertificate=true;hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=30;")  

For more details, refer Connecting with encryption

Hope this helps. Do let us know if you any further queries.

----------------------------------------------------------------------------------------

Do click on "Accept Answer" and Upvote on the post that helps you, this can be beneficial to other community members.