How connect to azure sql database with jdbc and python in a databricks notebook?

Question

In an Azure Databricks notebook, I would like with python to connect to azure sql database with JDBC (Active Directory password authentication).

I tried this:

jdbcHostname = "....database.windows.net"
jdbcDatabase = "..."
jdbcPort = ...

jdbcUrl = "jdbc:sqlserver://{0}:{1};database={2}".format(jdbcHostname, jdbcPort, jdbcDatabase)
connectionProperties = {
  "user" : "...",
  "password" : "...",
  "driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver"
}

pushdown_query = "(select * FROM [db].[...])"
df = spark.read.jdbc(url=jdbcUrl, table=pushdown_query, properties=connectionProperties)
display(df)

But I have the error message:

com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open server "....com" requested by the login.  The login failed. ClientConnectionId:...

Where am I wrong ? I tried several username, always same error

Even when I create a user with SSMS I have the same error

Answer 1

You can try with another library: com.microsoft.azure:spark-mssql-connector_2.12:1.2.0

https://learn.microsoft.com/en-us/sql/connect/spark/connector?view=sql-server-ver15

Answer 2

jdbcurl should look like this I think

jdbcUrl=f"jdbc:sqlserver://{jdbcHostname}:{jdbcPort};databaseName={jdbcDatabase};user={jdbcUsername};password={jdbcPassword}"