Can't Authenticate to Azure SQL in Python code from DevOps pipeline using workload identity federation service connection

Question

I was having a hard time finding guidance on how to do this so I apologize if I missed a step/config. Trying to query an Azure SQL database in an python application running in DevOps Pipeline

My current pipeline error is this:

Starting: Unit Test
==============================================================================
Task         : Command line
Description  : Run a command line script using Bash on Linux and macOS and cmd.exe on Windows
Version      : 2.231.0
Author       : Microsoft Corporation
Help         : https://docs.microsoft.com/azure/devops/pipelines/tasks/utility/command-line
==============================================================================
Generating script.
Script contents:
python3 app.py
========================== Starting Command Output ===========================
/usr/bin/bash --noprofile --norc /azp/_work/_temp/2b90c137-aea9-43d5-8709-8aef64dbbbcc.sh
InteractiveBrowserCredential.get_token failed: Failed to open a browser
DefaultAzureCredential failed to retrieve a token from the included credentials.
Attempted credentials:
    EnvironmentCredential: EnvironmentCredential authentication unavailable. Environment variables are not fully configured.
Visit https://aka.ms/azsdk/python/identity/environmentcredential/troubleshoot to troubleshoot this issue.
    ManagedIdentityCredential: ManagedIdentityCredential authentication unavailable, no response from the IMDS endpoint.
    SharedTokenCacheCredential: SharedTokenCacheCredential authentication unavailable. No accounts were found in the cache.
    AzureCliCredential: Azure CLI not found on path
    AzurePowerShellCredential: PowerShell is not installed
    AzureDeveloperCliCredential: Azure Developer CLI could not be found. Please visit https://aka.ms/azure-dev for installation instructions and then,once installed, authenticate to your Azure account using 'azd auth login'.
    InteractiveBrowserCredential: Failed to open a browser
To mitigate this issue, please refer to the troubleshooting guidelines here at https://aka.ms/azsdk/python/identity/defaultazurecredential/troubleshoot.
Traceback (most recent call last):

1. created 'workload identity federation' service connection for Azure ARM in Azure DevOps project

2. pipeline permissions for service connection has no restrictions

3. granted the created Enterprise Application 'Azure SQL Database' permission.

4. ran SQL queries from SSMS

CREATE LOGIN [app_name] FROM EXTERNAL PROVIDER;

USE [Profile]
GO;

CREATE USER [app_name] FROM LOGIN [app_name];

ALTER ROLE db_datareader ADD MEMBER [app_name];
ALTER ROLE db_datawriter ADD MEMBER [app_name];

5. python code

import pyodbc, struct
from azure import identity

connection_string = 'Driver={ODBC Driver 18 for SQL Server};Server=tcp:dylanbartley.database.windows.net,1433;Database=Profile;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30'

def get_conn():
  credential = identity.DefaultAzureCredential(exclude_interactive_browser_credential=False)
  token = credential.get_token("https://database.windows.net/.default").token
  token_bytes = token.encode("UTF-16-LE")
  token_struct = struct.pack(f'<I{len(token_bytes)}s', len(token_bytes), token_bytes)
  SQL_COPT_SS_ACCESS_TOKEN = 1256  # This connection option is defined by microsoft in msodbcsql.h
  conn = pyodbc.connect(connection_string, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: token_struct})
  return conn

def get_contacts():
  records = []
  with get_conn() as conn:
    cursor = conn.cursor()
    cursor.execute("select * from Contacts")

    for row in cursor.fetchall():
      records.append(row.Id)
  return records

rows = get_contacts()
print(len(rows))

6. current pipeline yaml

trigger:
- main

pool: Default
strategy:
  matrix:
    Python311:
      python.version: '3.11'

steps:
- script: |
    python3 -m pip install --upgrade pip
    pip3 install pyodbc azure-identity
  displayName: 'Install dependencies'
    
- script: |
    python3 app.py
  displayName: 'Run Conn'

Answer 1

I found the last piece of the puzzle I was missing. Needed to use a task that supports azureSubscription parameter, which is where the service connection name goes. for my case using Azure CLI task. updated pipeline looks like this:

trigger:
- main

pool: Default
strategy:
  matrix:
    Python311:
      python.version: '3.11'

steps:
- script: |
    python3 -m pip install --upgrade pip
    pip3 install pyodbc azure-identity
  displayName: 'Install dependencies'
    
- task: AzureCLI@2
  inputs:
    azureSubscription: 'ProfileSite'
    scriptType: 'bash'
    scriptLocation: 'inlineScript'
    inlineScript: |
      python3 app.py
  displayName: 'Run Conn'

there were a few other things I needed to get it working e.g. correct ODBC drivers, az cli on the host, etc.