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

view raw azure-powershell-task-get-sql-access-token.yml hosted with ❤ by GitHub

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 UID, PWD, 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

view raw azure-powershell-task-get-sql-access-token-and-execute-python.yml hosted with ❤ by GitHub

	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

view raw python-script-with-pyodbc.py hosted with ❤ by GitHub

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