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

Question

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

Ajay Verma 21

---------------------------------------------------------------------------
    Py4JJavaError                             Traceback (most recent call last)
    <command-3031308706598466> in <module>
          6   .option("tempDir", "wasbs://******@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

PRADEEPCHEEKATLA 91,946 Reputation points

2020-10-07T10:16:58.15+00:00

Hello @AjayVerma-764,

Could you please share the code snippet, which you are trying to connect Azure Synapse Analytics from Azure Databricks?

We recommend that you use the connection string provided by Azure portal, which enables Secure Sockets Layer (SSL) encryption for all data sent between the Spark driver and the Azure Synapse instance through the JDBC connection. To verify that the SSL encryption is enabled, you can search for encrypt=true in the connection string. To allow the Spark driver to reach Azure Synapse, we recommend that you set Allow access to Azure services to ON on the firewall pane of the Azure Synapse server through Azure portal. This setting allows communications from all Azure IP addresses and all Azure subnets, which allows Spark drivers to reach the Azure Synapse instance.

Meanwhile, I would request you to go through the Azure Synapse Analytics.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Answer accepted by question author

PRADEEPCHEEKATLA 91,946

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.

PRADEEPCHEEKATLA 91,946 Reputation points

2020-10-13T11:44:39.137+00:00

Hello @AjayVerma-764,

Just checking in to see if the above answer helped. If this answers your query, do click “Accept Answer” and Up-Vote for the same. And, if you have any further query do let us know.
PRADEEPCHEEKATLA 91,946 Reputation points

2020-10-14T10:17:02.573+00:00
Hello @AjayVerma-764,

Following up to see if the above suggestion was helpful. And, if you have any further query do let us know.
Take care & Stay safe!

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

Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.

Want a reminder to come back and check responses? Here is how to subscribe to a notification.
Ajay Verma 21 Reputation points

2020-10-15T03:26:09.307+00:00

Thanks @PRADEEPCHEEKATLA ! Appreciate the help, it worked
PRADEEPCHEEKATLA 91,946 Reputation points

2020-10-15T05:11:29.787+00:00

Hello @AjayVerma-764,

Glad to know that it helped.

1 additional answer

Your answer

PRADEEPCHEEKATLA 91,946 Reputation points

2020-10-07T10:16:58.15+00:00

Hello @AjayVerma-764,

Could you please share the code snippet, which you are trying to connect Azure Synapse Analytics from Azure Databricks?

We recommend that you use the connection string provided by Azure portal, which enables Secure Sockets Layer (SSL) encryption for all data sent between the Spark driver and the Azure Synapse instance through the JDBC connection. To verify that the SSL encryption is enabled, you can search for encrypt=true in the connection string. To allow the Spark driver to reach Azure Synapse, we recommend that you set Allow access to Azure services to ON on the firewall pane of the Azure Synapse server through Azure portal. This setting allows communications from all Azure IP addresses and all Azure subnets, which allows Spark drivers to reach the Azure Synapse instance.

Meanwhile, I would request you to go through the Azure Synapse Analytics.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
PRADEEPCHEEKATLA 91,946 Reputation points

2020-10-13T11:44:39.137+00:00

Hello @AjayVerma-764,

Just checking in to see if the above answer helped. If this answers your query, do click “Accept Answer” and Up-Vote for the same. And, if you have any further query do let us know.
PRADEEPCHEEKATLA 91,946 Reputation points

2020-10-14T10:17:02.573+00:00

Hello @AjayVerma-764,

Following up to see if the above suggestion was helpful. And, if you have any further query do let us know.
Take care & Stay safe!

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

Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.

Want a reminder to come back and check responses? Here is how to subscribe to a notification.
Ajay Verma 21 Reputation points

2020-10-15T03:26:09.307+00:00

Thanks @PRADEEPCHEEKATLA ! Appreciate the help, it worked
PRADEEPCHEEKATLA 91,946 Reputation points

2020-10-15T05:11:29.787+00:00

Hello @AjayVerma-764,

Glad to know that it helped.

Answer 1

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

1 deleted comment

Comments have been turned off. Learn more