CODEWITHSUNDEEP
azureazure-active-directoryazure-sql-database
Panda
Panda

Reputation: 103

Use Python to query an Azure SQL database with Azure AD

import pyodbc
server = '<server>.database.windows.net'
database = '<database>'
username = '<username>'
password = '<password>'
driver= '{ODBC Driver 17 for SQL Server}'
cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
cursor.execute("SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName FROM [SalesLT].[ProductCategory] pc JOIN [SalesLT].[Product] p ON pc.productcategoryid = p.productcategoryid")
row = cursor.fetchone()
while row:
    print (str(row[0]) + " " + str(row[1]))
    row = cursor.fetchone()

I only see there is a username&&password credential to allow me to connect to Azure SQL database right now.

Is a way to let me connect to SQL database with Azure AD credential? e.g I want to use device_code_credentials

device_code_credentials = DeviceCodeCredential(client_id, tenant_id=tenant_id, authority=authority_host_uri)

I can call blob service client with this credential and wondering is there a simple way in Python SDK to connect to Azure SQL database.

blob_service_client = BlobServiceClient(
   account_url=self.url,
   credential=self.credential
)

Upvotes: 0

Views: 1206

Answers (1)

Leon Yue
Leon Yue

Reputation: 16411

We can using Azure AD authentication to connect to Azure SQL database.

Get the AD Active Directory password authentication on Portal: enter image description here

Python code example:

import pyodbc

server = 'your_server.database.windows.net'
database = 'your_database'
username = 'your_username'
password = 'your_password'
driver= '{ODBC Driver 17 for SQL Server}'
Authentication='ActiveDirectoryPassword'

cnxn = pyodbc.connect(
    'DRIVER='+driver+
    ';PORT=1433;SERVER='+server+
    ';PORT=1443;DATABASE='+database+
    ';UID='+username+
    ';PWD='+ password+
    ';Authentication='+Authentication)

cursor = cnxn.cursor()

cursor.execute("SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName FROM [SalesLT].[ProductCategory] pc JOIN [SalesLT].[Product] p ON pc.productcategoryid = p.productcategoryid")
row = cursor.fetchone()
while row:
    print (str(row[0]) + " " + str(row[1]))
    row = cursor.fetchone()

Hope this helps.

Upvotes: 1

Related Questions