Introduction

This post is part of the SQL Database series that I am compiling. This specific topic assumes that you have already built a .dacpac file via Azure DevOps YAML Pipeline and are ready to now deploy your .dacpac to Azure. Congratulations!  If you’d like to follow along all source code is in my GitHub repository.

PreReqs

To be successful here we’d require some items to be setup:

An Azure SQL Server and Database already deployed in Azure
An Azure DevOps Service Connection that has access to deploy the database (for me I like to have the Service Connection be a member of the Entra SQL Admin group, more on that later)
A .dacpac built by ADO and ready to publish. I used the SDK style project to create mine, you could use other methods as long as you have the .dacpac file.
Network connectivity to the Database. For this specific example we will be using an Azure SQL instance and leverage MS Hosted Azure DevOps Agents. Variations of this process is possible leveraging either a Windows self hosted build agents or the newer Managed DevOps Pools. 

Deploy Steps

When writing one of these I have found it can be helpful to write out the individual steps required for our build. In our case it will consist of:

Download the pipeline artifact
Open up on the Azure SQL Server Firewall to the Agent
Deploy .dacpac
Delete the Azure SQL Server Firewall rule

The good news here is that first, the job will automatically download the pipeline artifact. To reiterate this will download the .dacpac which was built in the previous stage so that subsequent jobs can leverage it for deployments to one or more environments. The second piece of good news is the opening of the Azure SQL Server Firewall rules can be handled by SqlAzureDacpacDeployment@1 task. In addition, there is the option to delete the firewall rule after the task has been completed.

So, this means we effectively just need a single job in our deployment stage!

SqlAzureDacpacDeployment@1

Here is the YAML code for the job to handle the deployment:

jobs:
– deployment: sqlmoveemecicd_app_dev_eus
environment:
name: dev
dependsOn: []
strategy:
runOnce:
deploy:
steps:
– task: SqlAzureDacpacDeployment@1
displayName: Publish sqlmoveme on sql-moveme-dev2-eus.database.windows.net
inputs:
DeploymentAction: Publish
azureSubscription: [Insert Service Connection Name]
AuthenticationType: servicePrincipal
ServerName: [SQL Server Destination]
DatabaseName: [SQL Database Destination]
deployType: DacpacTask
DacpacFile: $(Agent.BuildDirectory)sqlmoveme***.dacpac
AdditionalArguments: ”
DeleteFirewallRule: True

So, an item here to discuss which is a bit of a pre-requisite when discussing Azure DevOps YAML Pipelines is the deployment job concept. I do cover this in a previous post in the YAML Pipeline series on Azure DevOps Pipelines: Tasks, Jobs, Stages. Suffice it to say deployment jobs are special types of jobs in Azure DevOps which are to be leveraged for the actual deployment of artifacts and one of the key capabilities of deployment jobs is the ability to tie them to an environment. Environments can have gates which is a set of criteria that can include manual or automatic checks prior to deployment.

Lets take a step back and talk a little more on some of the requirements. First, we need to establish authentication from Azure DevOps to our Azure SQL Server. The most secure way to do this would be through Entra Authentication. The credentials we will be using would be the service principal associated with an Azure DevOps Service connection. This connection will either have credentials stored as part of the App Registration or leveraged workload identity federation.

Personally, I would recommend using the workload identity federation process as this will eliminate the need for a secret. This Service Connection can be the same one used to deploy other resources in the environment, though I understand and respect the separation of data and management plane activities so a separate one specific to the database is acceptable. If you’d rather not use Entra Auth for authenticating to the database, you can alternatively pass credentials stored in a Variable Group, though usually it’s a good idea not to use passwords when possible.

So now that we know how and what we are going to authenticate with it’s time to go over how the access to the service account would be provisioned. When configuring an Azure SQL Server one can designate an Entra Security group as the Admin.

Below is a screenshot showing that the Microsoft Entra Admin ID has been granted to an Entra Security Group. If this is a new concept please follow up with Using automation to set up the Microsoft Entra admin for SQL Server – SQL Server | Microsoft Learn. Additionally, here is a great walkthrough put together by MVP Stephan van Rooij, Azure SQL and Entra ID authentication, tips from the field.  

The service principle being used for the deployment is in turn added to this group. Thus, our deployment will have full access to deploy the .dacpac to the Azure SQL Server. Additionally, I have Microsoft Entra Authentication only configured; this is considered a best practice as SQL user credentials expose a potential credential liability. If new to this concept feel free to read more on Microsoft Entra-only authentication – Azure SQL Database & Azure SQL Managed Instance & Azure Synapse Analytics | Microsoft Learn

Results

After we add this deployment stage to our previous build stage our results in ADO will look like:

A two stage pipeline where the first stage will generate an artifact of our .dacpac and the second stage which will take the .dacpac produced in the first stage and deploy it. A complete YAML definition of this pipeline can be found on my GitHub repository.

Next Steps

Now that we have covered how to effectively build .sqlproj into a .dacpac anddeploy said .dacpac to Azure our next step will be to deploy tto multiple environments via different configurations! Feel free to subscribe to this series on SQL Databases alternatively if you like my posts feel free to follow me.

​Microsoft Tech Community – Latest Blogs –Read More