Building automated pipelines with your SQL database projects enables you to build a rich CI/CD ecosystem to ensure that your application is being deployed with good quality code and at high confidence of success. SQL Database Projects are compatible with just about every automation environment because fundamentally they're built on top of the .NET SDK, a free and cross-platform development platform. You can develop the Microsoft.Build.Sql projects in VS Code and SQL Server Management Studio (SSMS). In this post, we'll take a look at the things you need to know to get started with building and deploying SQL projects in Azure DevOps pipelines, a crucial part of integrating database development with the rest of your application development lifecycle. The concepts from this post will apply to any automation environment you choose.Prerequisites An Azure DevOps project with a repository containing your SQL project Permissions to create a service connection in Azure DevOps project settings The .NET SDK available in your pipeline environment (pre-installed on Microsoft-hosted agents) An Azure SQL Database on a logical server with: Microsoft Entra authentication enabled (Entra-only recommended) Public network access set to "Selected networks" An Entra admin configured on the server An Azure subscription where you can assign RBAC roles on the SQL Server resourceTo start our example, we setup a repository that contains our SQL project in the folder labeled AdventureWorks and an empty folder for our pipeline definitions. The AdventureWorks project is located inside the root of the repository, so our file tree looks similar to:.├── 📁 AdventureWorks/│ ├── AdventureWorks.sqlproj│ ├── 📁 dbo/│ │ ├── 📁 Functions/│ │ │ ├── ufnGetAllCategories.sql│ │ │ ├── ufnGetCustomerInformation.sql│ │ │ └── ufnGetSalesOrderStatusText.sql│ │ ├── 📁 StoredProcedures/│ │ │ ├── uspLogError.sql│ │ │ └── uspPrintError.sql│ │ ├── 📁 Tables/│ │ │ ├── BuildVersion.sql│ │ │ └── ErrorLog.sql│ │ └── 📁 UserDefinedTypes/│ │ ├── AccountNumber.sql│ │ ├── Flag.sql│ │ ├── Name.sql...│ └── 📁 Security/│ └── SalesLT.sql└── 📁 Pipelines/└── .gitignoreIf you're unsure of how to set up a SQL project from your current database, here's a tutorial article on getting started from an existing database: https://learn.microsoft.com/sql/tools/sql-database-projects/tutorials/start-from-existing-databaseBuild before deployWhen we work with a SQL project in an IDE like VS Code, Visual Studio, or SSMS, we often leverage the build and publish actions as the primary development checkpoints. SQL project build validates the syntax is correct and matches the target platform that we have selected. This is one way to ensure that the database code we're working with is compatible with the anticipated target (like Azure SQL Database), especially if we have an application that we've been developing while uncertain which platform it might be deployed to. While running build from a GUI interface involves using the menu option for build, in automation environments we need a command-line interaction to run project build.In this case, it's as straightforward as running "dotnet build" in an environment that has the .NET SDK installed. Microsoft-provided automation environments have a variety of software pre-installed, including the .NET SDK. If you leverage a self-hosted runner in the future, you would be responsible for installing the .NET SDK in that environment. Validating the project syntax represents the minimal continuous integration (CI) pipeline for our SQL project example.We will use the starter template for pipelines in Azure DevOps to establish the continuous integration (CI) pipeline that validates the project builds successfully. From that starter pipeline template, we: Modify the trigger to focus only on the main branch and only on changes within the Adventure Works folder. Remove the provided script steps and use the task panel to add the ".NET (Core)" task template. Set up the .NET task with the path to our SQL project file (AdventureWorks/AdventureWorks.sqlproj). Optionally, add the RunSqlCodeAnalysis property for additional insights on our database's code quality.When we save our pipeline definition, it looks similar to the below and can now be run ad-hoc as well as automatically when changes are made to our database project on the main branch.trigger: branches: include: - main paths: include: - AdventureWorkspool: vmImage: ubuntu-lateststeps:- task: DotNetCoreCLI@2 inputs: command: 'build' projects: 'AdventureWorks/AdventureWorks.sqlproj' arguments: '/p:RunSqlCodeAnalysis=true'Modifying the steps of an Azure DevOps pipeline can be easily done through the graphical task selection and settings pane. However, the YAML code itself is also directly editable. Continued customization, source control of the pipeline definition itself, and repeated use of specific components leverages the code-first nature of the automation definition. The comprehensive documentation for Azure DevOps Pipeline YAML schema is available at https://aka.ms/yaml.When the build pipeline runs, it prepares a summary of the errors and warnings on the pipeline run page. SQL project build produces a .dacpac build artifact, but if we do not explicitly preserve that file, it will not be captured by the build pipeline for future use. The .dacpac is temporarily available for use in the pipeline at its output location, which we can spot from the detailed build output. For the default configuration, the .dacpac is located in the bin/Debug folder of the SQL project.Now that we have our SQL project successfully building in an automated environment, we're ready to explore the steps needed to apply the deployment to different SQL environments. Deployment pipelines can be used for a variety of situations, including ephemeral (temporary) validation environments, shared staging instances, as well as production databases through gated pipelines.Publish the SQL projectTo build our understanding of a SQL project deployment, we're going to create a separate Azure DevOps pipeline and use it for deploying the SQL project to a development Azure SQL Database. Even though this is a development instance where we are free to apply database changes without impacting a production workload, we are not going to compromise on security standards that we would expect protecting our data. Create an Azure SQL Database on a server with only Microsoft Entra authentication enabled and public network access only enabled for selected networks. In Azure DevOps, we're able to adhere to these standards more easily through the use of service connections, which bind an Azure DevOps pipeline to an Azure Entra app registration. Setup of a service connection is done through the project settings in the Azure DevOps project.The name of the Azure DevOps service connection is an important value to know, as it will be used throughout the pipeline. The default value can be quite lengthy and difficult to distinguish, but can be modified from the Azure DevOps interface. In this example, our service connection name is ContosoAzure.We'll start a new pipeline definition from the Starter template and apply what we learned previously about building a SQL project to make that the first step in our new pipeline. Instead of this pipeline running automatically on code changes, we're going to remove all pipeline triggers such that it only runs ad-hoc.# AdHoc deployment pipelinetrigger:- nonepool: vmImage: ubuntu-lateststeps:- task: DotNetCoreCLI@2 inputs: command: 'build' projects: 'AdventureWorks/AdventureWorks.sqlproj' arguments: '--configuration Release' displayName: 'Build SQL Project to ./AdventureWorks/bin/Release/AdventureWorks.dacpac'The SqlPackage CLI provides a flexible way to extract and apply the SQL project database definition. The SqlPackage CLI is an automatable tool that fits well into CI/CD environments like Azure DevOps pipelines. While the SqlPackage CLI is not likely installed in our automation environments, it can be easily installed through a quick script command in the pipeline by adding a step to install it. This is the same command we would use to install SqlPackage across Windows, Linux, and macOS environments.- script: dotnet tool install -g microsoft.sqlpackage displayName: 'Install SqlPackage'Setup passwordless authentication in AzureThe service connection from Azure DevOps to Microsoft Azure is listed as an "Enterprise Application" (app registration) in Entra. By granting the proper permissions to this identity, we're able to interact with the database from the Azure DevOps pipeline without storing passwords or access tokens. Use the Entra blades in the Azure Portal to locate the app registration and note its display name, which may look something like "yourorg-yourproject-12345678-1234-5678-9012-123456789012". We need to provide the service connection with two layers of access: Database-level permissions as a contained user to deploy schema changes Azure RBAC permissions on the SQL Server resource to manage firewall rulesConnect to the database with an Entra admin user such that you can add the service connection user in the database and provide it with elevated permissions. We start with "db_ddladmin", "db_datareader", and "db_datawriter". These three roles together allow SqlPackage to create and modify schema, read existing data for comparison, and write static/reference data without granting full database ownership associated with "db_owner". However, it is possible that in the future your database project will include database settings and changes that require elevated permissions.CREATE USER [yourorg-yourproject-12345678-1234-5678-9012-123456789012] FROM EXTERNAL PROVIDER;ALTER ROLE db_ddladmin ADD MEMBER [yourorg-yourproject-12345678-1234-5678-9012-123456789012];ALTER ROLE db_datareader ADD MEMBER [yourorg-yourproject-12345678-1234-5678-9012-123456789012];ALTER ROLE db_datawriter ADD MEMBER [yourorg-yourproject-12345678-1234-5678-9012-123456789012];In addition to access to the SQL permissions, we also want to grant the service connection access to manage the firewall rules on the SQL Server. You may create a custom role with access to "Microsoft.Sql/servers/firewallRules/read", "Microsoft.Sql/servers/firewallRules/write", and "Microsoft.Sql/servers/firewallRules/delete" if your Entra license permits it. Alternatively, assigning the "SQL Server Contributor" role grants the identity access to manage the firewall and other aspects of the SQL Server in Azure.Learn more about the "SQL Server Contributor" role to see if it's right for your organization: https://learn.microsoft.com/azure/role-based-access-control/built-in-roles/databases#sql-server-contributorRun SqlPackage in the pipelineThe database change deployment (migration) is applied through the SqlPackage CLI, which dynamically calculates the difference between the SQL project build artifact (.dacpac) and the database we connect to. We provide a connection string to the database in a pipeline variable - "SQLDBCONNECTIONSTRING" - which we may also set as a secret. The connection string is retrieve for the specific database we're deploying to and we want the "Active Directory Default" format for ADO.NET:Server=tcp:yourserver.database.windows.net,1433;Initial Catalog=yourdatabase;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication="Active Directory Default";In addition to the connection string variable, we'll also need a variable for the server name (prefix to ".database.windows.net") and the resource group for a total of 3 preset pipeline variables: SqlDbConnectionString SqlServerName ResourceGroupThe "Azure PowerShell" task in Azure DevOps ensures that the script we provide is run with the pipeline authenticated to Azure with the specified service connection, simplifying the pipeline's definition a bit. We'll use this task several times to complete the pipeline. Adding the Azure PowerShell task and providing an inline script to execute sqlpackage publish enables the pipeline to run SqlPackage as the passwordless service connection to Azure.- task: AzurePowerShell@5 displayName: 'Run SqlPackage' inputs: azureSubscription: 'ContosoAzure' ScriptType: 'InlineScript' Inline: | sqlpackage /Action:Publish /SourceFile:"./AdventureWorks/bin/Release/AdventureWorks.dacpac" /TargetConnectionString:"${env:SQLDBCONNECTIONSTRING}" azurePowerShellVersion: 'LatestVersion' env: SQLDBCONNECTIONSTRING: $(SqlDbConnectionString)Navigate the Azure SQL Database firewallIf we were to run the pipeline now, it would fail to connect to the database and the error message would provide an IP Address that needs to be added to the server firewall. However, we want to ensure that the firewall is only open to the address that the pipeline environment has for the duration of the run and be able to automatically use the correct IP address for the pipeline (since it will change over time in a shared environment). To meet these requirements, we'll add 2 steps before SqlPackage runs and a final step at the end of the pipeline.Before the SqlPackage publish step, add a PowerShell step and an Azure PowerShell step. These 2 steps combine to: determine the IP address assigned to our pipeline environment add a firewall rule for the pipeline to access the server- task: PowerShell@2 displayName: 'Get Public IP and put in variable runnerIP' inputs: targetType: 'inline' script: | $runnerIP = (New-Object net.webclient).downloadstring("https://api.ipify.org") Write-Host "##vso[task.setvariable variable=runnerIP]$runnerIP"- task: AzurePowerShell@5 displayName: 'Create SQL Server Firewall Rule' inputs: azureSubscription: 'ContosoAzure' ScriptType: 'InlineScript' Inline: | New-AzSqlServerFirewallRule -ResourceGroupName "${env:RESOURCEGROUP}" -ServerName "${env:SQLSERVERNAME}" -FirewallRuleName ${env:FIREWALLRULENAME} -StartIpAddress ${env:RUNNERIP} -EndIpAddress ${env:RUNNERIP} azurePowerShellVersion: 'LatestVersion' env: SQLSERVERNAME: $(SqlServerName) RESOURCEGROUP: $(ResourceGroup) FIREWALLRULENAME: $(FirewallRuleName) RUNNERIP: $(runnerIP)After the SqlPackage step in the pipeline, add an Azure PowerShell step that will be used to remove the firewall rule. This step includes a "condition" parameter that ensures it will run even if a prior step fails, like the SqlPackage publish, such that a firewall rule doesn't remain in place.- task: AzurePowerShell@5 displayName: 'Remove SQL Server Firewall Rule' condition: always() inputs: azureSubscription: 'ContosoAzure' ScriptType: 'InlineScript' Inline: | Remove-AzSqlServerFirewallRule -ResourceGroupName "${env:RESOURCEGROUP}" -ServerName "${env:SQLSERVERNAME}" -FirewallRuleName ${env:FIREWALLRULENAME} azurePowerShellVersion: 'LatestVersion' env: SQLSERVERNAME: $(SqlServerName) RESOURCEGROUP: $(ResourceGroup) FIREWALLRULENAME: $(FirewallRuleName)The deployment pipeline we created is setup to only run on demand, although some development environments may be automatically updated from a shared branch in source control. When we invoke the pipeline from Azure DevOps, we can follow the steps in the logs, including the modification of the firewall rules and the steps taken by SqlPackage to update the database.The entire pipeline definition for ad-hoc deployments would be:# AdHoc deployment pipeline# Variables: SqlServerName, ResourceGroup, SqlDbConnectionStringtrigger:- nonepool: vmImage: ubuntu-lateststeps:- task: DotNetCoreCLI@2 inputs: command: 'build' projects: 'AdventureWorks/AdventureWorks.sqlproj' arguments: '--configuration Release' displayName: 'Build SQL Project to ./AdventureWorks/bin/Release/AdventureWorks.dacpac'- script: dotnet tool install -g microsoft.sqlpackage displayName: 'Install SqlPackage'- task: PowerShell@2 displayName: 'Generate Firewall Rule Name with BuildId suffix' inputs: targetType: 'inline' script: | $firewallRuleName = "FirewallRule-$(Build.BuildId)" Write-Host "Generated Firewall Rule Name: $firewallRuleName" Write-Host "##vso[task.setvariable variable=FirewallRuleName]$firewallRuleName"- task: PowerShell@2 displayName: 'Get Public IP and put in variable runnerIP' inputs: targetType: 'inline' script: | $runnerIP = (New-Object net.webclient).downloadstring("https://api.ipify.org") Write-Host "##vso[task.setvariable variable=runnerIP]$runnerIP"- task: AzurePowerShell@5 displayName: 'Create SQL Server Firewall Rule' inputs: azureSubscription: 'ContosoAzure' ScriptType: 'InlineScript' Inline: | New-AzSqlServerFirewallRule -ResourceGroupName "${env:RESOURCEGROUP}" -ServerName "${env:SQLSERVERNAME}" -FirewallRuleName ${env:FIREWALLRULENAME} -StartIpAddress ${env:RUNNERIP} -EndIpAddress ${env:RUNNERIP} azurePowerShellVersion: 'LatestVersion' env: SQLSERVERNAME: $(SqlServerName) RESOURCEGROUP: $(ResourceGroup) FIREWALLRULENAME: $(FirewallRuleName) RUNNERIP: $(runnerIP)- task: AzurePowerShell@5 displayName: 'Run SqlPackage' inputs: azureSubscription: 'ContosoAzure' ScriptType: 'InlineScript' Inline: | sqlpackage /Action:Publish /SourceFile:"./AdventureWorks/bin/Release/AdventureWorks.dacpac" /TargetConnectionString:"${env:SQLDBCONNECTIONSTRING}" azurePowerShellVersion: 'LatestVersion' env: SQLDBCONNECTIONSTRING: $(SqlDbConnectionString)- task: AzurePowerShell@5 displayName: 'Remove SQL Server Firewall Rule' condition: always() inputs: azureSubscription: 'ContosoAzure' ScriptType: 'InlineScript' Inline: | Remove-AzSqlServerFirewallRule -ResourceGroupName "${env:RESOURCEGROUP}" -ServerName "${env:SQLSERVERNAME}" -FirewallRuleName ${env:FIREWALLRULENAME} azurePowerShellVersion: 'LatestVersion' env: SQLSERVERNAME: $(SqlServerName) RESOURCEGROUP: $(ResourceGroup) FIREWALLRULENAME: $(FirewallRuleName)Wrap upIn this article we setup two Azure DevOps pipelines that enable minimal continuous integration (CI) and continuous delivery (CD) of our database, all based on a database source code format that applies to the entire Microsoft SQL family and is available in SSMS and VS Code. The CI pipeline we created focused on the SQL project build only and your future exploration could include setting it as a required pull request check for changes to the application code that impacts the database. Depending on the different testing and staging environments used by your database, you may establish multiple deployment pipelines or emit deployment scripts from SqlPackage for further review before updating an environment. Most importantly, you learned the basic components of an Azure DevOps pipeline required to keep your database secure and let the pipeline navigate both the network and authorization security steps through the Azure DevOps service connection.Links to learn more: https://aka.ms/sqlpackage-ref https://marketplace.visualstudio.com/items?itemName=ms-mssql.sql-database-projects-vscode https://learn.microsoft.com/azure/devops/pipelines/targets/azure-sqldb https://learn.microsoft.com/azure/devops/repos/git/branch-policies