pyodbc will support connecting to an Azure SQL DB using the AD access token instead of user/password?

user_6183212 0
Viewed 3479

Currently, I use device code credential to get the access to Azure AD.

device_code_credential = DeviceCodeCredential(
        azure_client_id,
        tenant_id=azure_tenant_id,
        authority=azure_authority_uri)

But I still need to use Azure account username/password to connect to Azure SQL server

driver = 'ODBC Driver 17 for SQL Server'
db_connection_string = f'DRIVER={driver};SERVER={server};' \
    f'DATABASE={database};UID={user_name};PWD={password};'\
    f'Authentication=ActiveDirectoryPassword;'\
    'Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;'
connector = pyodbc.connect(db_connection_string)

Is any way in python under linux/MacOS can allow me to use device_code_credential and access_token to connect to Azure SQL server?

https://github.com/mkleehammer/pyodbc/issues/228

I only got this link and it doesn't seem to work.

Anyone has a fully working sample?

azure-active-directory azure-sql-database azure
2 Answers

You could reference this tutorial: AzureAD/azure-activedirectory-library-for-python: Connect to Azure SQL Database.

It is doable to connect to Azure SQL Database by obtaining a token from Azure Active Directory (AAD), via ADAL Python. We do not currently maintain a full sample for it, but this essay outlines some key ingredients.

  1. You follow the instruction of Connecting using Access Token to provision your application. There is another similar blog post here.
  2. Your SQL admin need to add permissions for the app-registration to the specific database that you are trying to access. See details in this blog post Token-based authentication support for Azure SQL DB using Azure AD auth by Mirek H Sztajno.
  3. It was not particularly highlighted in either of the documents above, but you need to use https://database.windows.net/ as the resource string. Note that you need to keep the trailing slash, otherwise the token issued would not work.
  4. Feed the configuration above into ADAL Python's Client Credentials sample.
  5. Once you get the access token, use it in this way in pyodbc to connect to SQL Database.

This works with AAD access tokens. Example code to expand the token and prepend the length as described on the page linked above, in Python 2.x:

token = "eyJ0eXAiOi...";
exptoken = "";
for i in token:
    exptoken += i;
    exptoken += chr(0);
tokenstruct = struct.pack("=i", len(exptoken)) + exptoken;
conn = pyodbc.connect(connstr, attrs_before = { 1256:bytearray(tokenstruct) });

3.x is only slightly more involved due to annoying char/bytes split:

token = b"eyJ0eXAiOi...";
exptoken = b"";
for i in token:
    exptoken += bytes({i});
    exptoken += bytes(1);
tokenstruct = struct.pack("=i", len(exptoken)) + exptoken;
conn = pyodbc.connect(connstr, attrs_before = { 1256:tokenstruct });

(SQL_COPT_SS_ACCESS_TOKEN is 1256; it's specific to msodbcsql driver so pyodbc does not have it defined, and likely will not.)

Hope this helps.

user_105492810

You can get a token via

from azure.identity import DeviceCodeCredential

# Recommended to allocate a new ClientID in your tenant.
AZURE_CLI_CLIENT_ID = "04b07795-8ddb-461a-bbee-02f9e1bf7b46"
credential = DeviceCodeCredential(client_id=AZURE_CLI_CLIENT_ID)
databaseToken = credential.get_token('https://database.windows.net/.default')

Then use databaseToken.token as an AAD Access Token as described in Leon Yue's answer.

user_140202540
Related