How to connect to Azure SQL Server in databricks using active directory authentication

1
votes

In the below code I am using SQL Server user id and password to connect to my db.

But I get an error while connecting with active directory.

Please Suggest some solution to connect Azure SQL Server DB in Azure Databricks using Active Directory authentication.

As of now, I am able to connect with a JDBC connection:

sql_url="jdbc:sqlserver://kkk-server.database.windows.net:1433;database=database;user=UserName;password=Password"
1
azure-active-directoryazure-sql-databaseazure-databricks

1 Answers

1
votes

According the Azure databricks document Connecting to Microsoft SQL Server and Azure SQL Database with the Spark Connector:

The Spark connector for SQL Server and Azure SQL Database also supports Azure Active Directory (AAD) authentication. It allows you to securely connect to your Azure SQL databases from Azure Databricks using your AAD account.

Example: Read from Azure SQL Database or SQL Server:

import com.microsoft.azure.sqldb.spark.config.Config
import com.microsoft.azure.sqldb.spark.connect._

val config = Config(Map(
  "url"            -> "kkk-server.database.windows.net:1433",
  "databaseName"   -> "MyDatabase",
  "dbTable"        -> "dbo.Clients",
  "user"           -> "AD-account",
  "password"       -> "xxxxxxxx",
  "connectTimeout" -> "5", //seconds
  "queryTimeout"   -> "5"  //seconds
))

val collection = spark.read.sqlDB(config)
collection.show()

Replace the user with your AD account name.

Hope this helps.