How do I connect to Synapse SQL serverless using Python?

Laura Martin 0 Reputation points
2024-04-18T15:06:25.2033333+00:00

Hi all!

A while ago I found this thread which contained the following code to connect to Synapse SQL serverless via REST API.

import requests

import json

# Azure app registration details

tenant_id = "<Your Tenant ID>"

client_id = "<Your Client ID>"

client_secret = "<Your Client Secret>"

# Get the Azure token

token_url = f"https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token"

token_data = {

'grant_type': 'client_credentials',

'client_id': client_id,

'client_secret': client_secret,

'scope': 'https://graph.microsoft.com/.default'

}

token_r = requests.post(token_url, data=token_data)

token = token_r.json().get("access_token")

# Synapse workspace details

workspace_name = "<Your Workspace Name>"

sql_endpoint = f"https://{workspace_name}.dev.azuresynapse.net"

# SQL Query

sql_query = "SELECT * FROM <Your Table>"

# REST API request to query data

headers = {

'Authorization': f'Bearer {token}',

'Content-Type': 'application/json',

'Accept': 'application/json'

}

response = requests.post(f"{sql_endpoint}/query", headers=headers, json={"sql": sql_query})

# Check response

if response.status_code == 200:

print(response.json())

else:

print(f"Error: Unable to fetch data. Status code: {response.status_code}")

print(response.text)

I need a bit more information on the Service Principal, its permissions, and how to find the relevant connection details like the client ID and client secret. Can anyone shower me with wisdom on this?

Thanks!

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,383 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. BhargavaGunnam-MSFT 26,221 Reputation points Microsoft Employee
    2024-04-19T23:25:51.3266667+00:00

    Hello Laura Martin

    Please follow the below documents that explains how to use service principal to secure connections

    https://www.mssqltips.com/sqlservertip/7914/secure-connections-to-azure-sql-using-service-principal-authentication/

    Mircrosoft doc: https://learn.microsoft.com/en-us/entra/identity-platform/app-objects-and-service-principals?tabs=browser

    I hope this helps. Please let me know if you have any further questions.

    0 comments
  2. Dillon Silzer 54,636 Reputation points
    2024-04-20T05:59:05.57+00:00

    Hi Laura,

    This information is right in the App Registration when you are setting one up:

    Go into Entra ID > App Registrations > Create a new application

    User's image

    To generate a secret go to Certificates & secrets > + New client secret

    User's image

    Once you have generated a new client secret you will want to take the value of the secret.

    If you have set up the API permissions correctly and assigned the Service Principal to your Azure service, then you should have all the information you need.

    If this is helpful please accept answer.

    0 comments