connect to azure sql database from databricks using service principal

[DJey]

Hi All, 

Can someone please help me with the Python code to connect Azure SQL Database to Databricks using Service Principle instead of directly passing username and password. 

I'm using above code but getting above error. Refer Screenshot 2.

Please help. 
Thanks!

[Alexkuva]

You can use, as an alternative, azure ad token in order to work (still with service principal).

Check attached.

[DJey]

@Alexkuva Thanks for your reply. 

But as I can see in Py Documentation, "adal" library will no longer receive any future improvements. 

[Alexkuva]

You can use azure.identity instead and get the needed token

[berserkersap]

You can get the secrets using "dbutils.secrets.get" if you have azure key vault ready.

[Anonymous]

Hi @DJey 

We haven't heard from you since the last response from @Alexkuva, and I was checking back to see if her suggestions helped you.

Or else, If you have any solution, please share it with the community, as it can be helpful to others. 

Also, Please don't forget to click on the "Select As Best" button whenever the information provided helps resolve your question.

[berserkersap]

@DJey  I think you can use this https://learn.microsoft.com/en-us/azure/active-directory/develop/msal-migration

https://learn.microsoft.com/en-us/azure/active-directory/develop/msal-acquire-cache-tokens

It seems now we need to use MSAL

[Joe_Suarez]

First, you need to create a service principal in Azure and grant it the necessary permissions to access your Azure SQL Database to do crm data enrichment. You can do this using the Azure CLI or the Azure Portal. Ensure that your Databricks cluster has the necessary libraries installed. You will need to use the pyodbc library to connect to Azure SQL Database. You can install it using the following command in a Databricks notebook cell:

%pip install pyodbc

Python Code:

Here's a Python code snippet to connect to Azure SQL Database using a Service Principal:

import pyodbc

# Define the connection string
server = "your_server_name.database.windows.net"
database = "your_database_name"
clientId = "your_service_principal_client_id"
clientSecret = "your_service_principal_client_secret"
authority = "https://login.microsoftonline.com/your_tenant_id"

connection_string = (
f"Driver={{ODBC Driver 17 for SQL Server}};"
f"Server={server};"
f"Database={database};"
f"Authentication=ActiveDirectoryServicePrincipal;"
f"UID={clientId};"
f"PWD={clientSecret};"
f"Encrypt=yes;"
f"TrustServerCertificate=no;"
)

try:
# Establish the database connection
conn = pyodbc.connect(connection_string)
cursor = conn.cursor()

# Execute SQL queries here
cursor.execute("SELECT * FROM your_table_name")
rows = cursor.fetchall()

for row in rows:
print(row)

except Exception as e:
print(f"Error: {str(e)}")
finally:
# Close the connection
conn.close()

Replace your_server_name, your_database_name, your_service_principal_client_id, your_service_principal_client_secret, your_tenant_id, and your_table_name with your actual Azure SQL Database and Service Principal information.