This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Secure, optimize, and deploy database solutions (35–40%)
   --> Implement CI/CD by using SQL Database Projects
      --> Design and implement controls for deployment pipelines, including branching policies, triggers in approvals, authentication tables, and code owners

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

In Part 1, you learned how branching strategies, pull requests, branch protection policies, and Code Owners help organizations maintain secure and reliable SQL Database Projects. In this section, we focus on how deployment pipelines automatically execute, how approvals and authentication secure deployments, and how organizations protect production environments.

Pipeline Triggers

A pipeline trigger determines when a build or deployment pipeline starts.

Rather than requiring developers to manually start every pipeline, modern CI/CD systems automatically execute pipelines based on predefined events.

Common trigger types include:

• Source code commits
• Pull requests
• Scheduled executions
• Manual execution
• Completion of another pipeline
• Tag creation
• Release approvals

Choosing the appropriate trigger helps balance automation with governance.

Continuous Integration Triggers

Continuous Integration (CI) pipelines usually start automatically after code changes.

Typical CI trigger:

Developer Commit
↓
Git Repository
↓
Automatic Build
↓
Compile SQL Database Project
↓
Run Validation
↓
Publish Build Artifact

Benefits include:

• Immediate feedback
• Early detection of errors
• Frequent validation
• Consistent builds
• Reduced integration problems

Common CI Trigger Events

Commit Trigger

The pipeline starts whenever a developer commits changes.

Example:

Commit to feature branch
↓
Build Pipeline Starts

Useful for:

• Early validation
• Fast feedback
• Developer productivity

Pull Request Trigger

Instead of triggering on every commit, organizations often build whenever a pull request is created or updated.

Example:

Feature Branch
↓
Create Pull Request
↓
Automatic Validation
↓
Review

Benefits:

• Ensures only validated code reaches protected branches
• Prevents broken code from being merged
• Supports branch protection policies

Scheduled Trigger

Some validation pipelines execute on a schedule.

Example:

Every Night
↓
Run Full Test Suite

Useful for:

• Long-running tests
• Security scanning
• Dependency validation
• Performance testing

Manual Trigger

Certain deployments should never execute automatically.

Example:

Release Manager
↓
Start Production Deployment

Manual triggers provide additional governance before production releases.

Continuous Delivery Triggers

Continuous Delivery (CD) pipelines move validated artifacts through multiple environments.

Example:

Build Artifact
↓
Development
↓
Testing
↓
Staging
↓
Production

Each stage may have different approval requirements.

Deployment Approvals

Approvals ensure that qualified personnel review changes before deployment.

Instead of automatically deploying to production, pipelines pause until an authorized user approves the release.

Example:

Deployment Ready
↓
Approval Required
↓
Manager Approves
↓
Deployment Continues

Types of Deployment Approvals

Manual Approval

A designated reviewer manually approves deployment.

Common reviewers include:

• Database Administrator
• Development Lead
• Security Team
• Operations Team
• Product Owner

Multi-Stage Approval

Different environments require different reviewers.

Example:

This layered approval process minimizes production risk.

Conditional Approval

Approval requirements may depend on:

• Database type
• Environment
• Change size
• Security classification
• Time of deployment

Example:

Production Deployment
↓
Contains Schema Changes?
↓
Yes
↓
Require DBA Approval

Environment Protection

Modern DevOps platforms allow organizations to protect deployment environments.

Environment protection can require:

• Manual approvals
• Deployment windows
• Authentication verification
• Security policies
• Health checks

Example:

Pipeline
↓
Staging
↓
Approval
↓
Production

Only authorized deployments can proceed.

Deployment Gates

Deployment gates evaluate conditions before allowing deployment.

Common gates include:

• Successful testing
• Security scan completion
• Vulnerability assessment
• Performance validation
• Business approval
• Service availability

Example:

Security Scan Passed?
↓
Yes
↓
Continue Deployment

If any gate fails, deployment stops automatically.

Authentication in Deployment Pipelines

Authentication verifies the identity of the pipeline when accessing resources.

The deployment pipeline may need to access:

• SQL Server
• Azure SQL Database
• Azure Key Vault
• Azure Storage
• Azure AI Services
• Microsoft Fabric
• Azure OpenAI
• Azure Resource Manager

Secure authentication is essential because deployment pipelines often operate without human intervention.

Authentication Methods

Common authentication methods include:

• Microsoft Entra ID (Azure AD)
• Managed Identity
• Service Principal
• OAuth tokens
• Personal Access Tokens (PATs)
• SQL Authentication (legacy scenarios)

Microsoft recommends avoiding passwords whenever possible.

Service Principals

A Service Principal represents an application identity in Microsoft Entra ID.

Instead of using a person’s account, the deployment pipeline authenticates using its own identity.

Example:

Pipeline
↓
Service Principal
↓
Microsoft Entra ID
↓
Azure SQL Database

Benefits:

• Non-interactive authentication
• Fine-grained permissions
• Centralized identity management
• Easy auditing
• Supports automation

Managed Identity

A Managed Identity is the preferred authentication method for Azure-hosted services.

Instead of storing credentials, Azure automatically manages authentication.

Example:

Azure DevOps Agent
↓
Managed Identity
↓
Microsoft Entra ID
↓
Azure SQL Database

Advantages include:

• No stored passwords
• Automatic credential rotation
• Improved security
• Simplified administration
• Reduced risk of credential leakage

Managed Identity is increasingly emphasized across Microsoft certifications, including DP-800.

System-Assigned vs User-Assigned Managed Identity

System-Assigned Managed Identity

Characteristics:

• Tied to one Azure resource
• Automatically created
• Automatically deleted with the resource
• Ideal for single-resource scenarios

Example:

App Service
↓
System Managed Identity
↓
Azure SQL

User-Assigned Managed Identity

Characteristics:

• Independent Azure resource
• Shared across multiple services
• Longer lifecycle
• Reusable

Example:

Managed Identity
↓
Web App
↓
Azure Function
↓
Azure SQL Database

Useful when multiple applications require the same identity.

Least Privilege Principle

Deployment identities should have only the permissions necessary to perform deployments.

Avoid granting:

• sysadmin
• db_owner (unless required)
• Subscription Owner
• Global Administrator

Instead, assign only the permissions needed.

Example:

Deployment pipeline requires:

• ALTER TABLE
• CREATE PROCEDURE
• CREATE VIEW

It does not require:

• DROP DATABASE
• Server Administration
• Security Administration

Following the principle of least privilege reduces the impact of compromised credentials.

Secrets Management

Pipelines often require sensitive information, such as:

• Connection strings
• API keys
• Certificates
• Tokens
• Database credentials

Hardcoding these values in source control is a major security risk.

Azure Key Vault

Azure Key Vault is the recommended solution for storing secrets.

Instead of embedding credentials:

Pipeline
↓
Azure Key Vault
↓
Retrieve Secret
↓
Deploy Database

Benefits include:

• Centralized secret storage
• Encryption at rest
• Access auditing
• Role-based access control
• Automatic secret rotation
• Integration with Azure DevOps and GitHub Actions

Secure Pipeline Variables

CI/CD platforms support secure variables that:

• Encrypt values
• Hide secrets in logs
• Restrict access
• Limit modification permissions

Examples include:

• SQL connection strings
• Azure subscription IDs
• API tokens
• Storage account keys

Sensitive values should never be committed to a Git repository.

Environment-Specific Configuration

Different deployment environments often require different configuration values.

Example:

Pipelines should dynamically retrieve the correct configuration for each environment rather than hardcoding values.

Deployment Strategies

Different deployment strategies reduce downtime and deployment risk.

Common strategies include:

Incremental Deployment

Deploy only changed objects.

Advantages:

• Faster deployments
• Lower risk
• Reduced downtime

Rolling Deployment

Deploy changes gradually across multiple instances.

Useful for:

• High availability
• Large distributed systems

Blue-Green Deployment

Maintain two production environments.

Blue Environment
(Current)
↓
Switch
↓
Green Environment
(New Version)

Advantages:

• Minimal downtime
• Fast rollback
• Lower deployment risk

Canary Deployment

Deploy to a small subset of users first.

If successful:

5%
↓
25%
↓
50%
↓
100%

This strategy helps identify issues before a full rollout.

Best Practices for Secure Deployment Pipelines

Microsoft recommends the following practices:

• Automate builds whenever possible.
• Require approvals for production deployments.
• Use Microsoft Entra ID authentication.
• Prefer Managed Identity over stored credentials.
• Store secrets in Azure Key Vault.
• Apply least privilege permissions.
• Protect production environments with approval gates.
• Separate development, test, staging, and production environments.
• Monitor deployment history and audit logs.
• Validate deployments before promotion to production.

DP-800 Exam Tips

For the exam, be prepared to identify when to use:

• Pull request triggers versus commit triggers.
• Manual approvals for production deployments.
• Managed Identity instead of passwords or embedded credentials.
• Service Principals for automated, non-interactive deployments.
• Azure Key Vault for secure secrets management.
• Environment protection rules to safeguard production resources.
• Least privilege permissions for deployment identities.
• Appropriate deployment strategies such as blue-green, rolling, or incremental deployments based on business requirements.

Part 2 Summary

In this section, you learned how organizations secure and automate SQL deployment pipelines through:

• Pipeline triggers and CI/CD automation
• Manual and conditional deployment approvals
• Environment protection and deployment gates
• Authentication using Microsoft Entra ID
• Service Principals and Managed Identity
• Least privilege access
• Secrets management with Azure Key Vault
• Secure pipeline variables
• Environment-specific configuration
• Common deployment strategies
• Microsoft-recommended security and governance practices

Go to the DP-800 Exam Prep Hub main page