GitHub Action for deploying updates to Azure SQL database

With the Azure SQL Action for GitHub, you can automate your workflow to deploy updates to Azure SQL database.

Get started today with a free Azure account!

This repository contains GitHub Action for Azure SQL to deploy.

The action uses a Connection string for authentication and DACPAC/SQL scripts to deploy to your SQL database.

If you are looking for more GitHub Actions to deploy code or a customized image into an Azure Webapp or a Kubernetes service, consider using Azure Actions.

The definition of this GitHub Action is in action.yml.

If you can use the option Allow Azure Services and resources to access this server, you are all set and you don't need to to anything else to allow GitHub Action to connect to your Azure SQL database.

If you cannot use the aforementioned option, additional steps are needed.

• Authenticate using Azure Login

In fact, for the action to run, the IP Address of the GitHub Action runner (automation agent) must be added to the 'Allowed IP Addresses' by setting SQL server firewall rules in Azure. Without the firewall rules, the runner cannot communicate with Azure SQL Database.

By default, the action would auto-detect the IP Address of the runner to automatically add firewall exception rule. These firewall rules will be deleted after the action executes.

However, this auto-provisioning of firewall rules needs a pre-req that the workflow includes an azure/login@v1 action before the azure/sql-action@v1 Action. Also, the service principal used in the Azure login action needs to have elevated permissions, i.e. membership in SQL Security Manager RBAC role, or a similarly high permission in the database to create the firewall rule.

If the login action is not included, then the sql action would fail with a firewall exception and appropriate messaging.

Alternatively, if enough permissions are not granted on the service principal or login action is not included, then the firewall rules have to be explicitly managed by user using CLI/PS scripts.

1. Follow the tutorial Azure SQL Quickstart
2. Copy the SQL-on-Azure.yml template and paste the contents in .github/workflows/ in your project repository as workflow.yml.
3. Update the connection string with your values. Connection string format is: Server=<server.database.windows.net>;User ID=<user>;Password=<password>;Initial Catalog=<database>
4. Commit and push your project to GitHub repository, you should see a new GitHub Action initiated in Actions tab.

For using any sensitive data/secrets like Azure Service Principal or SQL Connection strings within an Action, add them as secrets in the GitHub repository and then use them in the workflow.

Follow the steps to configure the secret:

• Define a new secret under your repository Settings > Secrets > Add a new secret menu
• Paste the contents of the Secret (Example: Connection String) as Value

If you need to configure Azure Credentials to automatically manage firewall rules, you need to create a Service Principal, and store the related credentials into a GitHub Secret so that it can be used by the Azure Login actions to authenticate and authorize any subsequent request.

Paste the output of the below az cli command as the value of secret variable, for example AZURE_CREDENTIALS.

az ad sp create-for-rbac --name "mySQLServer" --role contributor \
                         --scopes /subscriptions/{subscription-id}/resourceGroups/{resource-group} \
                         --sdk-auth
                            
# Replace {subscription-id}, {resource-group} with the subscription, resource group and name of the Azure SQL server
  
# The command should output a JSON object similar to this:

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

# .github/workflows/sql-deploy.yml
on: [push]

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: REPLACE_THIS_WITH_YOUR_SQL_SERVER_NAME
        connection-string: ${{ secrets.AZURE_SQL_CONNECTION_STRING }}
        dacpac-package: './Database.dacpac'

Note:

The above means you have to create secrets in GitHub which can be found within your repository within Settings and then Secrets and also be careful to check the connection string which you copy from Azure SQL as the connection string has this Password={your_password} and you will need to supply the correct password for your connection string.

The server-name is optional and is there only to provide backward compatibility. It is strongly recommended to put the server name in the connection string. The connection string uses this template: Server=<servername>; User ID=<user_id>; Password=<password>; Initial Catalog=<database>. In case the server name is put both in the server-name and in the connection-string, the server name used will be the one specified in the server-name YAML key.

For the above action to work, you will need to create a file called Database.dacpac and place it into the root of your GitHub repository. The following link will show you how to go about creating a dacpac file but make sure the file is called Database.dacpac.

Export a Data-tier application

Azure SQL Action for GitHub is supported for the Azure public cloud as well as Azure government clouds ('AzureUSGovernment' or 'AzureChinaCloud'). Before running this action, login to the respective Azure Cloud using Azure Login by setting appropriate value for the environment parameter.

This project welcomes contributions and suggestions. Most contributions require you to agree to a Contributor License Agreement (CLA) declaring that you have the right to, and actually do, grant us the rights to use your contribution. For details, visit https://cla.opensource.microsoft.com.

When you submit a pull request, a CLA bot will automatically determine whether you need to provide a CLA and decorate the PR appropriately (e.g. status check, comment). Simply follow the instructions provided by the bot. You will only need to do this once across all repos using our CLA.

This project has adopted the Microsoft Open Source Code of Conduct. For more information see the Code of Conduct FAQ or contact opencode@microsoft.com with any additional questions or comments.