Skip to content

connect-github-actions-sql-db.md

Latest commit

 

History

History
194 lines (139 loc) · 6.9 KB

connect-github-actions-sql-db.md

File metadata and controls

194 lines (139 loc) · 6.9 KB
title Quickstart: Connect to Azure SQL Database with GitHub Actions
description Use Azure SQL from a GitHub Actions workflow
author juliakm
services sql-database
ms.service sql-database
ms.subservice connect
ms.topic quickstart
ms.author jukullam
ms.date 05/05/2021
ms.custom github-actions-azure, mode-other
ms.reviewer kendralittle, mathoma

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. Define a service principal.
2. Create a GitHub secret.
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: [ master ]
pull_request:
    branches: [ master ]

  4. Rename your workflow SQL for GitHub Actions and add the checkout and login actions. These actions will checkout your site code and authenticate with Azure using the AZURE_CREDENTIALS GitHub secret you created earlier.

    name: SQL for GitHub Actions

on:
push:
    branches: [ master ]
pull_request:
    branches: [ master ]

jobs:
build:
    runs-on: windows-latest
    steps:
    - uses: actions/checkout@v1
    - uses: azure/login@v1
    with:
        creds: ${{ secrets.AZURE_CREDENTIALS }}

  5. Use the Azure SQL Deploy action to connect to your SQL instance. Replace SQL_SERVER_NAME with the name of your server. You should have a dacpac package (Database.dacpac) at the root level of your repository.

    - uses: azure/sql-action@v1
  with:
    server-name: SQL_SERVER_NAME
    connection-string: ${{ secrets.AZURE_SQL_CONNECTION_STRING }}
    dacpac-package: './Database.dacpac'

  6. Complete your workflow by adding an action to logout of Azure. Here is the completed workflow. The file will appear in the .github/workflows folder of your repository.

    name: SQL for GitHub Actions

on:
push:
    branches: [ master ]
pull_request:
    branches: [ master ]


jobs:
build:
    runs-on: windows-latest
    steps:
    - uses: actions/checkout@v1
    - uses: azure/login@v1
    with:
        creds: ${{ secrets.AZURE_CREDENTIALS }}

- uses: azure/sql-action@v1
  with:
    server-name: SQL_SERVER_NAME
    connection-string: ${{ secrets.AZURE_SQL_CONNECTION_STRING }}
    dacpac-package: './Database.dacpac'

    # 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.

    :::image type="content" source="media/quickstart-sql-github-actions/github-actions-run-sql.png" alt-text="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

[!div class="nextstepaction"] Learn about Azure and GitHub integration