Connect to Azure SQL Database from DataBricks using Service Principal

Question

I have a requirement to connect to Azure SQL Database from Azure Databricks via Service Principal. Tried searching forums but unable to find the right approach. Any help is greatly appreciated.

Tried a similar approach with SQL User ID and Password with JDBC Connection and it worked successfully. Now looking into Service Principal approach.

P.S: The SP ID and Key should be placed in the Azure Key Vault and needs to be accessed here on Databricks.

Answer 1

You can use Apache Spark Connector for SQL Server and Azure SQL and an example of what you have to do in Databricks can be found in following Python file

As you can see, we are not directly connecting with the Service Principal, instead, we are using the Service Principal to generate an access token that is going to be used later when specifying the connection parameters:

          jdbc_df = spark.read.format("com.microsoft.sqlserver.jdbc.spark") \
          .option("url", url) \
          .option("dbtable", db_table) \
          .option("accessToken", access_token) \
          .option("encrypt", "true") \
          .option("databaseName", database_name) \
          .option("hostNameInCertificate", "*.database.windows.net") \
          .load() 

But if you can't or don't want to use previous library, you can also do the same with the native Azure-SQL JDBC connector of Spark:

          jdbc_df = spark.read.format("com.microsoft.sqlserver.jdbc.SQLServerDriver")\
          .option("url", url) \
          .option("dbtable", db_table) \
          .option("accessToken", access_token) \
          .option("encrypt", "true") \
          .option("databaseName", database_name) \
          .option("hostNameInCertificate", "*.database.windows.net") \
          .load() 

Answer 2

Maybe you can reference this tutorial: Configuring AAD Authentication to Azure SQL Databases.

Summary:

Azure SQL is a great service - you get your databases into the cloud without having to manage all that nasty server stuff. However, one of the problems with Azure SQL is that you have to authenticate using SQL authentication - a username and password. However, you can also authenticate via Azure Active Directory (AAD) tokens. This is analogous to integrated login using Windows Authentication - but instead of Active Directory, you're using AAD.

There are a number of advantages to AAD Authentication:

1. You no longer have to share logins since users log in with their AAD credentials, so auditing is better
2. You can manage access to databases using AAD groups
3. You can enable "app" logins via Service Principals

In order to get this working, you need:

1. To enable AAD authentication on the Azure SQL Server
2. A Service Principal
3. Add logins to the database granting whatever rights required to the service principal
4. Add code to get an auth token for accessing the database

But in this post, author will walk through creating a service principal, configuring the database for AAD auth, creating code for retrieving a token and configuring an EF DbContext for AAD auth.

Still hope this tutorial can helps.

Answer 3

Azure Key Vault support with Azure Databricks

https://docs.azuredatabricks.net/user-guide/secrets/secret-scopes.html#akv-ss

Answer 4

**Here's the working Solution**


sql_url=sqlserver://#SERVER_NAME#.database.windows.net:1433;database=#DATABASE_NAME#

properties = {"user":"#APP_NAME#","password":dbutils.secrets.get(scope = 
"#SCOPE_NAME#", key = 
"#KEYVAULT_SECRET_NAME#"),"driver":"com.microsoft.sqlserver.jdbc.SQLServerDriver"}


**APP_NAME**==>which is created under app registration in Azure active directory.

**SCOPE_NAME**==>Which you have create mentioned on docs Follow the 
URL(https://docs.azuredatabricks.net/user-guide/secrets/secret-scopes.html)

**KEYVAULT_SECRET_NAME**==>Secret Key name which is put into AKV.



**NOTE PROVIDE ACCESS TO YOUR APP_ID ON DATABASE STEPS MENTIONED BELOW**

CREATE USER #APP_NAME# FROM EXTERNAL PROVIDER

EXEC sp_addrolemember 'db_owner', '#APP_NAME#';