CODEWITHSUNDEEP
active-directoryconnectionazure-sql-databaseazure-databricks
Data_Insight
Data_Insight

Reputation: 585

How can we connect Azure SQL database using Active Directory authentication in Azure databricks

How to put AAD session value instead on password and user name in the code shown here:

    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()

Upvotes: 2

Views: 3078

Answers (3)

Mikel
Mikel

Reputation: 234

As the links provided by Leon Yue seem to be down this is the way to do it in Python that worked for me:

remote_table = (spark.read
  .format("sqlserver")
  .option("host", "<YOUR-HOST-HERE>")
  .option("user", "<YOUR-USERNAME-HERE>")
  .option("password", "<YOUR-PASSWORD-HERE>")
  .option("database", "<YOUR-DATABASE-HERE>")
  .option("dbtable", "<YOUR-SCHEMA-HERE>.<YOUR-TABLE-HERE>") # (if schema is not provided, it is defaulted to "dbo")
  .option("authentication", "ActiveDirectoryPassword")
  .load()
)

Upvotes: 0

Leon Yue
Leon Yue

Reputation: 16411

You can use the Spark connector for SQL Server and Azure SQL Database in Azure Databricks.

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. It provides interfaces that are similar to the built-in JDBC connector.

Here's the example:

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

val config = Config(Map(
  "url"            -> "mysqlserver.database.windows.net",
  "databaseName"   -> "MyDatabase",
  "user"           -> "username ",
  "password"       -> "*********",
  "authentication" -> "ActiveDirectoryPassword",
  "encrypt"        -> "true"
))

val collection = sqlContext.read.SqlDB(config)
collection.show()

For more details, please see: Connect Spark to Azure SQL Database using AAD authentication

Hope this helps.

Upvotes: 2

Alberto Morillo
Alberto Morillo

Reputation: 15668

Using Azure Active Directory (AAD) authentication to connect to Azure SQL Database on Azure Databricks is not possible at this time. You should use SQL authentication.

If you try AAD authentication on Azure Databricks you may get error "This driver is not configured for integrated authentication" or similar error message.

Although it is possible to use AAD in Azure Data Lake.

Upvotes: 0

Related Questions