Azure: .SQL files deployment using Github Actions

Overview

In current fast paced cloud environment, automating SQL deployments is crucial for maintaining consistency, reducing human error, and ensuring rapid delivery of database changes. Here I have outlined GitHub Actions workflow that automates the execution of .sql scripts stored in a repository to run specific queries, update certain tables, or make incremental changes without deploying an entire database schema, dynamically allows the GitHub runner’s IP to access Azure SQL, and cleans up firewall rules after execution.

Problem Statement

One of the requirement was to have some incremental changes deployment rather than having full DB Deployment approach.

When working with Azure SQL in a CI/CD pipeline, executing database scripts presents several challenges:

Firewall Restrictions:

Azure SQL requires explicit firewall rules to allow connections. Since GitHub-hosted runners use dynamic IPs, managing access manually is inefficient. This is applicable only when you are using controlled private Vnet environment.

Automated SQL Execution:

Executing .sql files dynamically from a repository without manual intervention is necessary for database versioning.

Security & Authentication:

Securely authenticating with Azure SQL using GitHub Secrets and service principal authentication.

Efficient Cleanup:

After execution, removing temporary firewall rules is essential to maintain security.

This workflow addresses these challenges by leveraging GitHub Actions, Azure CLI, and SQLCMD.

Workflow Breakdown

1. Triggering the Workflow

Currently my workflow is triggered by:

Manual Dispatch (workflow_dispatch)

Another Workflow (workflow_call)

Code Push to main Branch (push), specifically if changes occur in specific folder path/ directory.

on:
 workflow_dispatch:
 workflow_call:
 push:
 branches:
 - main --> Change branch based on your requirement
 paths:
 - Path1/Path2/**
 - Path2/**

2. Setting Up the Environment

The job runs on ubuntu-latest and loads necessary environment variables, including Azure SQL credentials and Azure Service Principal credentials. You need to add everything in your Github secrets

jobs:
 build:
 runs-on: ubuntu-latest
 environment:
 name: TestEnvironment
 env:
 SQL_SERVER: ${{ secrets.AZURE_SQL_SERVER }}
 SQL_DATABASE: ${{ secrets.AZURE_SQL_DATABASE }}
 SQL_USERNAME: ${{ secrets.AZURE_SQL_USERNAME }}
 SQL_PASSWORD: ${{ secrets.AZURE_SQL_PASSWORD }}
 ARM_CLIENT_ID: ${{secrets.SPN_CLIENT_ID}}
 ARM_CLIENT_CERTIFICATE_PASSWORD: ${{ secrets.SPN_CERT_PASSWORD }}
 ARM_SUBSCRIPTION_ID: ${{ secrets.SPN_SUBSCRIPTION_ID }}
 ARM_TENANT_ID: ${{ secrets.SPN_TENANT_ID }}
 CERTIFICATE: ${{ secrets.SPN_CERT }}

3. Checkout Code Repository

The repository is cloned to the runner using:

- name: Checkout
 uses: actions/checkout@v4
 with:
 fetch-depth: 0

4. Authenticate with Azure

Service principal authentication is done using a certificate stored in GitHub Secrets.

- name: Download Secrets and Authenticate
  run: |
  echo "${{ secrets.SPN_CERT }}" | base64 - decode > ARM_CLIENT_CERTIFICATE_PEM.pem
  az login - service-principal -u ${{ env.ARM_CLIENT_ID }} - certificate ./ARM_CLIENT_CERTIFICATE_PEM.pem - tenant ${{ env.ARM_TENANT_ID }}

5. Allow GitHub Runner IP in Azure SQL Firewall

If you are using Private Vnet environment and if you dont have public access then you may need to tweak to retrieve Runner IP and adds a firewall rule to allow database access.

- name: Allow Runner IP on Azure SQL Server
 uses: azure/powershell@v1
 with:
 inlineScript: |
   $agentIPAddress = (Invoke-WebRequest -Uri "http://ipinfo.io/ip").Content.Trim()
   echo "::set-output name=AGENTIP::$agentIPAddress"
   Write-Host "Agent IP Address: $agentIPAddress"
   az sql server firewall-rule create  --resource-group "Resource-Group-Name" --server "Azure-SQL-Server-Name" --name "GitHubIP" --start-ip-address "$agentIPAddress" --end-ip-address "$agentIPAddress"
   Start-Sleep -Seconds 15
 azPSVersion: "latest"

6. Install SQLCMD on GitHub Runner

Since we are going to use SQLCMD, then you need to install them on the fly SQLCMD is required to execute .sql scripts, Currently we are using Ubuntu one’s. Here we are downloading the required packages & installing some dependencies and then adding GPG key. Later I am adding Microsoft package repository.

Finally I am adding it to local Path environment environment as a temporary so that it can be used in next stage.

- name: Install sqlcmd
 run: |
 sudo apt-get update
 sudo apt-get install -y curl apt-transport-https gnupg
 curl -fsSL https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc > /dev/null
 echo "deb [arch=amd64,arm64,armhf] https://packages.microsoft.com/ubuntu/22.04/prod jammy main" | sudo tee /etc/apt/sources.list.d/microsoft.list

 sudo apt-get install -y mssql-tools unixodbc-dev
 echo 'export PATH="$PATH:/opt/mssql-tools/bin"' | sudo tee -a /etc/profile
 echo 'export PATH="$PATH:/opt/mssql-tools/bin"' | sudo tee -a ~/.bashrc
 source /etc/profile
 source ~/.bashrc

7. Execute SQL Scripts Dynamically

This step finds all .sql files in the specific folder path which we have defined earlier and executes them against the Azure SQL database.

Again here I am executing all the sql scripts in my repo, however if you have any specific one’s to be executed then rather than using . (dot) you can use folderpath/subpath Under find.

- name: Execute SQL Scripts
 run: |
 find . -type f -name "*.sql" | while read file; do
 echo "Running $file"
 /opt/mssql-tools/bin/sqlcmd -S "$SQL_SERVER" -d "$SQL_DATABASE" -U "$SQL_USERNAME" -P "$SQL_PASSWORD" -i "$file"
 done

8. Remove Firewall Rule After Execution

After .sql files execution you want to delete earlier added IP address to maintain security, the temporary IP access rule is deleted once the workflow completes.

- name: Remove Runner IP from Azure SQL
  uses: azure/powershell@v1
  with:
  inlineScript: |
    $agentIPAddress = (Invoke-WebRequest -Uri "http://ipinfo.io/ip").Content.Trim()
    Write-Host "Removing IP Address: $agentIPAddress"
    az sql server firewall-rule delete --resource-group "Resource-Group-Name" --server "Azure-SQL-Server-Name" --name "GitHubIP"
   azPSVersion: "latest"

Here is the full Github workflow

name: .SQL files Deployment workflow
 
on:
  workflow_dispatch:
  workflow_call:
  push:
    branches:
      - main
    paths:
 
      - ccs/Tables/**
jobs:
  build:
    runs-on: ubuntu-latest
    environment:
      name: TestEnvironment
    env:
      SQL_SERVER: ${{ secrets.AZURE_SQL_SERVER }}
      SQL_DATABASE: ${{ secrets.AZURE_SQL_DATABASE }}
      SQL_USERNAME: ${{ secrets.AZURE_SQL_USERNAME }}
      SQL_PASSWORD: ${{ secrets.AZURE_SQL_PASSWORD }}
      ARM_CLIENT_ID: ${{secrets.SPN_CLIENT_ID}}
      ARM_CLIENT_CERTIFICATE_PASSWORD: ${{ secrets.SPN_CERT_PASSWORD }}
      ARM_SUBSCRIPTION_ID: ${{ secrets.SPN_SUBSCRIPTION_ID }}
      ARM_TENANT_ID: ${{ secrets.SPN_TENANT_ID }}
      CERTIFICATE: ${{ secrets.SPN_CERT }}
 
 
    steps:
      - name: Checkout
        uses: actions/checkout@v4
        with:
          fetch-depth: 0
 
     
 
      - name: download secrets
        run: |
          echo "${{ secrets.SPN_CERT }}" | base64 --decode > ARM_CLIENT_CERTIFICATE_PEM.pem
          az login --service-principal -u ${{ env.ARM_CLIENT_ID }} --certificate ./ARM_CLIENT_CERTIFICATE_PEM.pem --tenant ${{ env.ARM_TENANT_ID }}
       
      - name: Allow Runner IP on Storage Account
        uses: azure/powershell@v1
        with:
            inlineScript: |      
              $agentIPAddress = (Invoke-WebRequest -Uri "http://ipinfo.io/ip").Content.Trim()      
              echo "::set-output name=AGENTIP::$agentIPAddress"
              Write-Host "Agent IP Address: $agentIPAddress"
              az sql server firewall-rule create  --resource-group "Resource-Group-Name" --server "Azure-SQL-Server-Name" --name "GitHubIP" --start-ip-address "$agentIPAddress" --end-ip-address "$agentIPAddress"
              Start-Sleep -Seconds 15
            azPSVersion: "latest"
      - name: Install sqlcmd
 
 
        run: |
 
              sudo apt-get update
              sudo apt-get install -y curl apt-transport-https gnupg
              curl -fsSL https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc > /dev/null
              echo "deb [arch=amd64,arm64,armhf] https://packages.microsoft.com/ubuntu/22.04/prod jammy main" | sudo tee /etc/apt/sources.list.d/microsoft.list
              sudo apt-get update
              sudo apt-get install -y mssql-tools unixodbc-dev
              echo 'export PATH="$PATH:/opt/mssql-tools/bin"' | sudo tee -a /etc/profile
              echo 'export PATH="$PATH:/opt/mssql-tools/bin"' | sudo tee -a ~/.bashrc
              source /etc/profile
              source ~/.bashrc
       
 
      - name: Execute SQL Scripts
        run: |
            find . -type f -name "*.sql" | while read file; do #if you want to deploy all .sql files then use . or else define specific folder path
            # something like FolderPath1/Folderpath2/
            /opt/mssql-tools/bin/sqlcmd -S "$SQL_SERVER" -d "$SQL_DATABASE" -U "$SQL_USERNAME" -P "$SQL_PASSWORD" -i "$file"
            done
  
      - name: Remove Runner IP from Azure SQL
        uses: azure/powershell@v1
        with:
        inlineScript: |
          $agentIPAddress = (Invoke-WebRequest -Uri "http://ipinfo.io/ip").Content.Trim()
          az sql server firewall-rule delete --resource-group "Resource-Group-Name" --server "Azure-SQL-Server-Name" --name "GitHubIP"
         azPSVersion: "latest"

Conclusion

This GitHub Actions workflow provides an efficient, automated method for executing SQL scripts on an Azure SQL Server while dynamically handling firewall rules. By integrating Azure CLI, PowerShell, and SQLCMD, this workflow ensures a secure and repeatable database deployment process.

One of the other reason for selecting specific files is when you want to do some simple changes in Azure SQL Data. dacpac approach will also be useful however if you are making entire schema change then you can go with Dacpac.

and then DACPAC requires SQL Server Data Tools (SSDT) or sqlpackage for deployment. But for .sql scripts only need sqlcmd, which is lightweight and easy to integrate into CI/CD workflows.

Reference Links:

How to Install sqlcmd & bcp on Ubuntu

Ubuntu: Install SQL Server on Linux - SQL Server

Install SQL Server command-line tools on Linux - SQL Server