Azure SQL Access Token Authentication with Python and Azure PowerShell from Azure DevOps Pipelines

In case you need to access an Azure SQL Database from your DevOps deployment pipeline to execute some custom script on a database. If you need to use something other than a username and password authentication and want to leverage Azure Active Directory, using an Access Token might be your solution.

In this post, I will provide an example of how to authenticate to Azure SQL with an Azure Active Directory Access Token. The Azure DevOps Service Connection is used to get the Access Token. A prerequisite for this to work is having a Service Connection that is added to the database as a user. The recommended way to set up a Service Connection is with an Azure Active Directory Service Principal also known as an Application Registration.


!! From a security perspective, it is not recommended to use SQL Authentication with a username and password. In the current version of Azure SQL it is even possible to completely turn off SQL Authentication and only use Active Directory authentication.


Prerequisites

  1. An Azure DevOps Project with Service Connection
  2. A SQL Database with a database user representing the Service Connection

Obtaining an Access Token with Azure PowerShell

In the example below we use the Azure PowerShell task for Azure Pipelines to leverage the Service Connection credentials to get an access token. With the SQLServer PowerShell module, we can use ‘Invoke-Sqlcmd’ to execute a query.

# Azure PowerShell
# https://docs.microsoft.com/en-us/azure/devops/pipelines/tasks/deploy/azure-powershell?view=azure-devops
# Run a PowerShell script within an Azure environment
task: AzurePowerShell@5
inputs:
azureSubscription: <YourServiceConnection> # Required. Name of Azure Resource Manager service connection
scriptType: 'inlineScript' # Optional. Options: filePath, inlineScript
#scriptPath: # Optional
inline: | # '# You can write your Azure PowerShell scripts inline here. # You can also pass predefined and custom variables to this script using arguments' # Optional
# Import needed modules
Import-Module SQLServer
Import-Module Az.Accounts -MinimumVersion 2.2.0
# Get an access token with the Service Pricipal used in the Azure DevOps Pipeline
$access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
$ServerInstance = <myserver.database.windows.net>
$database_name = <mydatabase>
$query = <SELECT * FROM dbo.Table>
# Execute the query on the SQL Serverless instance
Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $currentcon -AccessToken $access_token -query $query
#scriptArguments: # Optional
#errorActionPreference: 'stop' # Optional. Options: stop, continue, silentlyContinue
#failOnStandardError: false # Optional
azurePowerShellVersion: 'latestVersion' # Required. Options: latestVersion, otherVersion
#preferredAzurePowerShellVersion: # Required when azurePowerShellVersion == OtherVersion
#pwsh: true # Optional. If true, then will use PowerShell Core pwsh.exe

Using the Access Token with Python

In the next example, we install the pyodbc module and execute a custom python script against our database. Be sure to code some logic to pass and catch the parameters into the python script. I added an example python function with setting up the connecting string. Getting this to work took me quite some time. The catch is in the connection string, it is mentioned in the documentation, but off-course I missed this sentence: “The connection string must not contain UIDPWD, Authentication, or Trusted_Connection keywords.”

# Azure PowerShell
# https://docs.microsoft.com/en-us/azure/devops/pipelines/tasks/deploy/azure-powershell?view=azure-devops
# Run a PowerShell script within an Azure environment
task: AzurePowerShell@5
inputs:
azureSubscription: <YourServiceConnection> # Required. Name of Azure Resource Manager service connection
scriptType: 'inlineScript' # Optional. Options: filePath, inlineScript
#scriptPath: # Optional
inline: | # '# You can write your Azure PowerShell scripts inline here. # You can also pass predefined and custom variables to this script using arguments' # Optional
# Import needed modules
Import-Module SQLServer
Import-Module Az.Accounts -MinimumVersion 2.2.0
# Install needed python modules
python -m pip install pyodbc
# Get an access token with the Service Pricipal used in the Azure DevOps Pipeline
$access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
# Set Variables as example
$server_name = <myserver.database.windows.net>
$database_name = <mydatabase>
$query = <SELECT * FROM dbo.Table>
python.exe 'python-script-with-pyodbc.py' -server_name $server_name -access_token $access_token
#scriptArguments: # Optional
#errorActionPreference: 'stop' # Optional. Options: stop, continue, silentlyContinue
#failOnStandardError: false # Optional
azurePowerShellVersion: 'latestVersion' # Required. Options: latestVersion, otherVersion
#preferredAzurePowerShellVersion: # Required when azurePowerShellVersion == OtherVersion
#pwsh: true # Optional. If true, then will use PowerShell Core pwsh.exe
import pyodbc
import struct
def use_token(server_name, access_token):
connection_string = 'Driver={ODBC Driver 17 for SQL Server};server='+server_name+';database=master;'
SQL_COPT_SS_ACCESS_TOKEN = 1256
exptoken = b''
for i in bytes(access_token, "UTF-8"):
exptoken += bytes({i})
exptoken += bytes(1)
tokenstruct = struct.pack("=i", len(exptoken)) + exptoken
cnxn = pyodbc.connect(connection_string, attrs_before = { SQL_COPT_SS_ACCESS_TOKEN:tokenstruct })
cursor = cnxn.cursor()
return cursor, cnxn

To be able to use the access token a function is needed to ‘expand’ the access token. See the documentation for more information.

Now you are able to do all kinds of actions on your database, depending on the database role the user has been added to! Always be careful with dropping and deleting tables 🙂

References:

Microsoft docs – Azure SQL Authenticating with an access token

Microsoft docs – Azure SQL Using Azure Active Directory

Microsoft docs – Configure and manage Azure AD authentication with Azure SQL

Microsoft docs – Authentication with Python

1 thought on “Azure SQL Access Token Authentication with Python and Azure PowerShell from Azure DevOps Pipelines

  1. Greetings! Very helpful advice within this article! It is the little changes which will make the greatest changes. Thanks for sharing!

    Liked by 1 person

Leave a Reply to Josue Ambriz Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this:
search previous next tag category expand menu location phone mail time cart zoom edit close