Connect Azure SQL Server using Service Principal

It took me some time to figure this out, so I'll leave some code samples here in case it helps someone.

In my case, I had to connect to Synapse SQL Serverless from Databricks. Previously, I installed the driver "msodbcsql17" with this script:

%sh
#!/bin/bash
apt install unixodbc-dev
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
apt-get update
ACCEPT_EULA=Y apt-get install -y msodbcsql17

Then:

1. Get token:

from msal import ConfidentialClientApplication

creds = ConfidentialClientApplication(
    client_id='<your_client_id>', 
    authority='https://login.microsoftonline.com/<your_tenant_id>',
    client_credential= 'your_secret')

token = creds.acquire_token_for_client(scopes='https://database.windows.net//.default')

2. Encode token (more info here: https://www.linkedin.com/pulse/using-azure-ad-service-principals-connect-sql-from-python-andrade/):

import struct

SQL_COPT_SS_ACCESS_TOKEN = 1256 
tokenb = bytes(token["access_token"], "UTF-8")
exptoken = b'';
for i in tokenb:
    exptoken += bytes({i});
    exptoken += bytes(1);
tokenstruct = struct.pack("=i", len(exptoken)) + exptoken;

3. With pyodbc, open a connection to the database using the token and execute a SQL statement:

import pyodbc

connString = 'DRIVER={ODBC Driver 17 for SQL Server};' \
             + 'SERVER=<your_server>;' \
              + 'DATABASE=<your_database>;'
conn = pyodbc.connect(connString, attrs_before = { SQL_COPT_SS_ACCESS_TOKEN:tokenstruct});

cursor = conn.cursor()
query="select name from sys.databases"
cursor.execute(query) 
row = cursor.fetchall()

Look at this tutorial:Lesson Learned #49: Does Azure SQL Database support Azure Active Directory connections using Service Principals?

This tutorial teaches us connect the Azure SQL Database through AAD using Azure service principle, and it provides example code in Powershell and C#.

I didn't find the example code in Python. I think this tutorial may be helpful for you, so I want to share with you.

Hope this helps.