Use GitHub Actions to connect to Azure SQL Database

Get started with GitHub Actions by using a workflow to deploy database updates to Azure SQL Database.

Prerequisites

You will need:

Workflow file overview

A GitHub Actions workflow is defined by a YAML (.yml) file in the /.github/workflows/ path in your repository. This definition contains the various steps and parameters that make up the workflow.

The file has two sections:

Section Tasks
Authentication 1.1. Generate deployment credentials.
Deploy 1. Deploy the database.

Generate deployment credentials

You can create a service principal with the az ad sp create-for-rbac command in the Azure CLI. Run this command with Azure Cloud Shell in the Azure portal or by selecting the Try it button.

Replace the placeholders server-name with the name of your SQL server hosted on Azure. Replace the subscription-id and resource-group with the subscription ID and resource group connected to your SQL server.

   az ad sp create-for-rbac --name {server-name} --role contributor 
                            --scopes /subscriptions/{subscription-id}/resourceGroups/{resource-group} 
                            --sdk-auth

The output is a JSON object with the role assignment credentials that provide access to your database similar to this example. Copy your output JSON object for later.

  {
    "clientId": "<GUID>",
    "clientSecret": "<GUID>",
    "subscriptionId": "<GUID>",
    "tenantId": "<GUID>",
    (...)
  }

Important

It is always a good practice to grant minimum access. The scope in the previous example is limited to the specific server and not the entire resource group.

Copy the SQL connection string

In the Azure portal, go to your Azure SQL Database and open Settings > Connection strings. Copy the ADO.NET connection string. Replace the placeholder values for your_database and your_password. The connection string will look similar to this output.

    Server=tcp:my-sql-server.database.windows.net,1433;Initial Catalog={your-database};Persist Security Info=False;User ID={admin-name};Password={your-password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

You'll use the connection string as a GitHub secret.

Configure the GitHub secrets

  1. In GitHub, browse your repository.

  2. Select Settings > Secrets > New secret.

  3. Paste the entire JSON output from the Azure CLI command into the secret's value field. Give the secret the name AZURE_CREDENTIALS.

    When you configure the workflow file later, you use the secret for the input creds of the Azure Login action. For example:

    - uses: azure/login@v1
    with:
        creds: ${{ secrets.AZURE_CREDENTIALS }}
    
  4. Select New secret again.

  5. Paste the connection string value into the secret's value field. Give the secret the name AZURE_SQL_CONNECTION_STRING.

Add your workflow

  1. Go to Actions for your GitHub repository.

  2. Select Set up your workflow yourself.

  3. Delete everything after the on: section of your workflow file. For example, your remaining workflow may look like this.

    name: CI
    
    on:
    push:
        branches: [ main ]
    pull_request:
        branches: [ main ]
    
  4. Rename your workflow SQL for GitHub Actions and add the checkout and login actions. These actions will check out your site code and authenticate with Azure using the AZURE_CREDENTIALS GitHub secret you created earlier.

    name: SQL for GitHub Actions
    
    on:
    push:
        branches: [ main ]
    pull_request:
        branches: [ main ]
    
    jobs:
    build:
        runs-on: windows-latest
        steps:
        - uses: actions/checkout@v1
        - uses: azure/login@v1
        with:
            creds: ${{ secrets.AZURE_CREDENTIALS }}
    

  1. Use the Azure SQL Deploy action to connect to your SQL instance. You should have a dacpac package (Database.dacpac) at the root level of your repository.

    - uses: azure/sql-action@v2
      with:
        connection-string: ${{ secrets.AZURE_SQL_CONNECTION_STRING }}
        path: './Database.dacpac'
        action: 'Publish'
    
  2. Complete your workflow by adding an action to logout of Azure. Here's the completed workflow. The file will appear in the .github/workflows folder of your repository.

    name: SQL for GitHub Actions
    
    on:
    push:
        branches: [ main ]
    pull_request:
        branches: [ main ]
    
    
    jobs:
    build:
        runs-on: windows-latest
        steps:
        - uses: actions/checkout@v1
        - uses: azure/login@v1
        with:
            creds: ${{ secrets.AZURE_CREDENTIALS }}
    
    - uses: azure/sql-action@v2
      with:
        connection-string: ${{ secrets.AZURE_SQL_CONNECTION_STRING }}
        path: './Database.dacpac'
        action: 'Publish'
    
        # Azure logout 
    - name: logout
      run: |
         az logout
    

Review your deployment

  1. Go to Actions for your GitHub repository.

  2. Open the first result to see detailed logs of your workflow's run.

    Log of GitHub actions run

Clean up resources

When your Azure SQL database and repository are no longer needed, clean up the resources you deployed by deleting the resource group and your GitHub repository.

Next steps