topic Re: connect to azure sql database from databricks using service principal in Data Engineering

connect to azure sql database from databricks using service principal

DJey — Thu, 29 Jun 2023 14:28:13 GMT

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!

Re: connect to azure sql database from databricks using service principal

Alexkuva — Thu, 29 Jun 2023 15:04:46 GMT

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

Check attached.

Re: connect to azure sql database from databricks using service principal

DJey — Fri, 30 Jun 2023 04:41:59 GMT

@Alexkuva Thanks for your reply.

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

Re: connect to azure sql database from databricks using service principal

berserkersap — Fri, 30 Jun 2023 05:01:27 GMT

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

Re: connect to azure sql database from databricks using service principal

Anonymous — Fri, 30 Jun 2023 05:22:41 GMT

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.

Re: connect to azure sql database from databricks using service principal

Alexkuva — Tue, 11 Jul 2023 15:37:40 GMT

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

Re: connect to azure sql database from databricks using service principal

berserkersap — Sat, 16 Sep 2023 15:42:16 GMT

@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

Re: connect to azure sql database from databricks using service principal

Joe_Suarez — Tue, 19 Sep 2023 11:37:09 GMT

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.