Not able to connect to Azure database for MySQL server from databricks cluster using jdbc and spark connector

Question

I am trying to connect to Azure database for MySQL server using databricks clusters. I tried using 2 ways described below-

1. using jdbc

   val jdbcHostname = "<serverName>.mysql.database.azure.com"
   val jdbcPort = 3306
   val jdbcDatabase = "<db>"
   val jdbcUrl = s"jdbc:mysql://${jdbcHostname}:${jdbcPort}/${jdbcDatabase}?useSSL=true&requireSSL=false"
   
   import java.sql.DriverManager
   val connection = DriverManager.getConnection(jdbcUrl, "<user>@<serverName>", "<password>")
   

but I got the error saying "Client with IP address 'SOME_IP_ADDRESS' is not allowed to connect to this MySQL server"

I added this ip to firewall rules of Azure database for MySQL server and was able to access then. But everytime cluster restarts, ip address changes and it throws error. I don't want to "Allow access to Azure services" in mySQL server as it will allow users from another subscription as well.

2. using Spark connector- downlaoded "com.microsoft.azure:azure-sqldb-spark:1.0.2" jar

   val config = Config(Map(
   "driver"         -> "org.mariadb.jdbc.Driver",
   "url"            -> "<serverName>.mysql.database.azure.com:3306",
   "databaseName"   -> "<db>",
   "dbTable"        -> "<dbtable>",
   "user"           -> "<user>@<serverName>",
   "password"       -> "<password>"
   ))
   val data = spark.read.sqlDB(config)
   

But it throws error saying "java.lang.IllegalArgumentException: requirement failed: The driver could not open a JDBC connection. Check the URL: jdbc:sqlserver://<serverName>.mysql.database.azure.com:3306"

I tried in this way also-

    val df = spark.read.format("jdbc").option("driver", "org.mariadb.jdbc.Driver")
   .option("url", "jdbc:mysql://<serverName>.mysql.database.azure.com:3306/<db>?useSSL=true&requireSSL=false")
   .option("databaseName", "<db>")
   .option("dbTable", "<dbtable>")
   .option("user", "<user>@<serverName>")
   .option("password", "<password>")
   .load()

but it also throws error "Client with IP address 'SOME_IP_ADDRESS' is not allowed to connect to this MySQL server"

Answer 1

Azure SQL database for MySQL is not supported as data source in Azure Databricks. It's not on the list.

Please reference: Data sources for Azure Databricks and the document @Jim Xu provided for you.

Hope this helps.