cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

connect to azure sql database from databricks using service principal

DJey
New Contributor III

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. 

DJey_0-1688048752356.png

DJey_1-1688048784120.png

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

Please help. 
Thanks!

 

 

 

7 REPLIES 7

Alexkuva
New Contributor II

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

Check attached.

DJey
New Contributor III

@Alexkuva Thanks for your reply. 

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

DJey_0-1688100084736.png

 

 

Alexkuva
New Contributor II

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

berserkersap
Contributor

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

Anonymous
Not applicable

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
Contributor

Joe_Suarez
New Contributor III

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.

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group