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
- An Azure Devops Project with Service Connection
- 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.
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_."
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
