Use Python to query an Azure SQL database with Azure AD

Question

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
)

Leon Yue · Accepted Answer · 2020-03-09 01:47:56Z

1

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

Get the AD Active Directory password authentication on Portal:

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.

answered Mar 9, 2020 at 1:47

Leon Yue

16.5k1 gold badge17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Panda Over a year ago

I don't want to save plain text password inside the source code. Could I use any other approach?

Leon Yue Over a year ago

@Jonas Yes, you can follow the this document to create the Always Encrypted SQL connection string, then using the encrypted connection string to connect to the Azure SQL database. This will help you protect the password.

Leon Yue Over a year ago

@Jonas You Here's the another tutorial Encrypting passwords for use with Python and SQL Server.

Irina Over a year ago

This is very helpful, but I am getting an error saying I must use multi-factor authentication to connect to the database. Is it still possible to connect?

Leon Yue Over a year ago

@Irina No, some thing changed now. Azure now suggest us use AD MFA authentication to connect SQL database. For some region, AD password is not work now.

Collectives™ on Stack Overflow

Use Python to query an Azure SQL database with Azure AD

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related