AWS Databricks Pyspark - Unable to connect to Azure MySQL - Shows "SSL Connection is required"

[Rexton]

Even after specifying SSL options, unable to connect to MySQL. What could have gone wrong? Could anyone experience similar issues?

df_target_master = spark.read.format("jdbc")\

.option("driver", "com.mysql.jdbc.Driver")\

.option("url", host_url)\

.option("dbtable", supply_master)\

.option("user", user_id)\

.option("password", pwd)\

.option("ssl", True) \

.option("sslmode", "verify-ca" ) \

.option("sslrootcert", "<s3 bucket location") \

.load()

[Kaniz]

Hi @Rexton Das​,

By default, Azure Database for MySQL enforces SSL connections between your server and your client applications to protect against MITM (man in the middle) attacks. This is done to make the connection to your server as secure as possible.

Although not recommended, you can disable requiring SSL for connecting to your server if your client application does not support SSL connectivity. Please check How to Configure SSL Connectivity for your MySQL server in Azure for more details. You can disable requiring SSL connections from either the portal or using CLI. Note that Azure does not recommend disabling requiring SSL connections when connecting to your server.

[User16764241763]

@Rexton Das​ 

Can you try disabling below SSL setting on MySQL Azure Database -> Connection Security and see if it connects?

[User16764241763]

Or you can try enabling SSL in the URL using &useSSL=true&requireSSL=true

[Kaniz]

Hi @Rexton Das​ , We haven’t heard from you on the last response from me and @Arvind Ravish​ , and I was checking back to see if our suggestions helped you. Or else, If you have any solution, please do share that with the community as it can be helpful to others.

[a2barbosa]

Hey,

Here the solution: The correct option for ssl is "useSSL" and not just "ssl".

This code below could works:

df_target_master = spark.read.format("jdbc")\

.option("driver", "com.mysql.jdbc.Driver")\

.option("url", host_url)\

.option("dbtable", supply_master)\

.option("user", user_id)\

.option("password", pwd)\

.option("useSSL", True) \

.option("sslmode", "verify-ca" ) \

.option("sslrootcert", "<s3 bucket location") \

.load()

I'm able to access a mySQL with "Enforce SSL Connection" Enabled in Azure with only this change.

In Databricks Runtime 11.2 and above you can use the MySQL connector in Databricks Runtime. The code could be like this:

remote_table = (spark.read

.format("mysql")

.option("dbtable", table)

.option("host", database_host_url)

.option("port", 3306)

.option("database", database_name)

.option("user", uid)

.option("password", pwd)

.option("useSSL", True)

.option("sslmode", "verify-ca" )

.load()

)