DACPACs – Deploy Securely with Azure Pipelines

For several years, Microsoft has provided a project type in Visual Studio (previously aliased as BIDS) called SQL Server Database. This project allows developers to manage database schema in a project. When the project is built, a DACPAC is generated, which is used by sqlpackage.exe to deploy (technically publish) changes to a server. This is available for SQL Server, as well as Azure SQL Database/Azure SQL Managed Instance.

In Azure DevOps, a built-in task, Azure SQL Database deployment, is available to deploy DACPACs for Azure SQL DB/Azure SQL Managed Instance. (Going forward, I will shorten these two services to simply Azure SQL) The Classic Editor for Azure Pipelines provides a nice user interface to configure the task. It is important to remember when the pipeline executes, a YAML file will be executed. In YAML, everything is stored as a string. Within the properties, there are multiple authentication types, as shown in the screenshot below. For the 4 of the top 5 (exclude Active Directory – Integrated), passwords will be stored in the configuration…in plain text.

To provide a more secure deployment environment, these options are not optimal. However, the Service Principal is optimal, as it is an Azure Active Directory object, which can be referenced by GUIDs and a secret.

Let’s get started…

Create an App Registration

Creating an App Registration isn’t difficult, but it does require elevated permissions in Azure Active Directory. Unless you are an administrator, your infrastructure engineer or Azure administrator will need to perform the set up.

  • In the Azure Portal, search for your organization’s Azure Active Directory. Within the blade, locate App registrations from the left navigation bar, under Manage.
  • The organization’s existing enterprise applications will be listed. Click New registration from the top of the blade.
  • Define the Name of the App registration to get started. Under Supported account types, the most likely option is the first, Accounts in this organizational directory only. Click Register to create the registration.
  • Upon completion, the portal will take you to the application, which has information like the following screenshot.
  • Keep track of the Application (client) ID and Directory (tenant ID) from the Essentials section of the Overview.
  • Next, a secret needs to be created in the App registration. Under Manage on the navigation bar, choose Certificates & secrets.
  • In the middle of the screen, choose New client secret. Enter a description and the duration the secret should be valid. Click Add to create the secret.
  • The new secret will appear in the list. In the Value column, copy the secret to the clipboard and temporarily save it to a text editor. (Note: this needs to be done before navigating away from the secrets blade. Otherwise, the secret will not be available to copy and a new secret will need to be created.)

Create the Azure DevOps Service Connection

With the App registration created, the Azure DevOps service connection can be created. In PowerShell session, use the following script. From the steps above, <Secret>, <ApplicationID> and <DirectoryID> are known. The SubscriptionID can be found in the Azure Portal, searching for the company’s subscription that has the SQL instance. <DisplayName> is what the name of the Service Connection will be within Azure DevOps. The –organization argument needs to be filled out with your organization name in Azure DevOps. Your default URL to Azure DevOps is what you want here. Finally, <Project> is the Azure DevOps project in which you want to create the Service Connection.

$env:AZURE_DEVOPS_EXT_AZURE_RM_SERVICE_PRINCIPAL_KEY="<Secret>"
az devops service-endpoint azurerm create `
          --azure-rm-service-principal-id <ApplicationID>  `
          --azure-rm-subscription-id  <SubscriptionID>`
          --azure-rm-tenant-id <DirectoryID> `
          --name <DisplayName> `
          --organization "https://dev.azure.com/<Organization>" `
          --project <Project>

Assign SQL Permissions

The last step is to assign the App registration to the Azure SQL database. Within SQL Server Management Studio, or your preferred T-SQL editor connected to the database, run the following T-SQL statements, logged in as database administrator. Two assumptions are being made. First, the service connection name is WorkshopServiceConnection, and second, it should be added to the db_owner role. Please adjust these appropriately for your environment.

create user [WorkshopServiceConnection] from external provider
exec sp_addrolemember @rolename = 'db_owner', @memberName = 'WorkshopServiceConnection'

Wrap-up

With these items configured, the Azure Pipeline can now deploy DACPACs to an Azure SQL database in a much more secure manner. Passwords are not being stored in the clear. If someone gets a hold of your Azure DevOps project, they might see the application ID in Azure Active Directory, but the secret won’t be visible. The authentication to Azure SQL is managed through the App registration, which needs the secret, along with the ApplicationID.