Keep your Azure Synapse secrets secret in GitHub

Reading Time: 5 minutes

In this post I want to cover how you can keep your Azure Synapse secrets secret in GitHub. Because you need to do this if you are working with sensitive information in GitHub.

For example, things like connection strings. I thought I better write this post for a couple of reasons.

First of all, I want to raise more awareness about it and make sure others avoid putting secrets directly in their workflows. Like in the example in the next section of this post.

Secondly, because I while ago I wrote a post about how to keep your Azure Synapse secrets secret in Azure DevOps. With this in mind, I thought I better do a GitHub version.

To clarify, when I refer to Azure Synapse in this post I mean Azure Synapse Analytics. In reality, you can apply the contents of this post to other services that you can deploy to using GitHub Actions. Such as Azure SQL Database and Azure Kubernetes Service.

Examples you see in this post are based on a new GitHub repository that is now public called GitHub-SynapseDedicatedSQLPool. Which contains a template you can use to perform CI/CD for Azure Synapse dedicated SQL Pools using GitHub Actions.

In fact, the sql-action GitHub Action I use in this example is the same one you can use to deploy state-based updates to Azure SQL Database.

By the end of this post, you will know various ways to keep your Azure Synapse secrets secret within Azure DevOps.

In reality, there are other ways to keep secrets in GitHub. However, I decided to select only a few to raise awareness. To summarize, this post covers the three methods below:

• Creating secrets for a single repository.
• Creating secrets for an organization.
• Getting secrets from Azure Key Vault.

Why keep secrets secret

You should keep your secrets secret to avoid exposing sensitive details about your Azure services. If you are fairly new to creating GitHub Actions workflows it can be easy to expose these details and I want to make sure you avoid that.

For example, say you were working with a new workflow in GitHub. Similar to the one that I covered in a previous post about how to create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using GitHub Actions.

From there you decided to add a new Azure SQL Deploy GitHub Action from the Marketplace as below.

You copy and paste the snippet for it into your workflow. You then populate it with information which can be sensitive. Like the login credentials in the connection string. As you can see in the below example.

  - name: Azure SQL Deploy
  uses: Azure/sql-action@v1.2
  with:
    # Name of the Azure SQL Server name, like Fabrikam.database.windows.net.
    server-name: 'productionworkspace.sql.azuresynapse.net'
    # The connection string, including authentication information, for the Azure SQL Server database.
    connection-string: 'Data Source=productionworkspace.sql.azuresynapse.net;Initial Catalog=ProductionDB;User ID=madeupsqlaccount;Password=D0N0t3nteraP@$$w0rdh3r3;Persist Security Info=True'
    # Path to DACPAC file to deploy
    dacpac-package: # optional
    # Path to SQL script file to deploy
    sql-file: 'mydacpac.dacpac'

From there, you commit the change in GitHub. Once it is committed those details are saved in GitHub.

If it is a repository is in a public GitHub repository those sensitive details are available for all to see.

Even if you change your workflow and commit the change those details are still in the history of your Git repository. Plus, the data can still be cached by GitHub.

To fully undo this can take some time. As you can see in the GitHub documentation about removing sensitive data from a repository.

How to keep Azure Synapse secrets secret

One common way to keep your Azure Synapse secrets secret in GitHub is to use encrypted secrets. Also known known in the GitHub community as just secrets.

Now, you can create secrets at various levels in GitHub. Including at the repository level or for an entire GitHub organization. One of the most well-known places to create them is in the GitHub repository. You do this in the settings section of your repository.

This section has recently gone through some changes and is now more organized. To add a secret to your repository you go to the ‘Security’ section and expand ‘Secrets’. From there, you select ‘Actions’ to add a secret to be used with GitHub Actions.

Afterwards, you can click on the ‘New Repository secret’ button to add a new secret. Once you have entered in your secrets you can reference them in your workflow. Just like in the below example.

      # Deploys SQLPool dacpac
      - name: Deploy SQLPool Dacpac
        uses: Azure/sql-action@v1.2
        with:
          # Name of the SQL Server name
          server-name: '${{ secrets.DEDICATED_SQL_ENDPOINT }}'
          # The connection string, including authentication information, for the SQL Server database.
          connection-string: '${{ secrets.DEDICATED_SQLPOOL_CONNECTION_STRING }}'
          # Path to DACPAC file. *.dacpac or a folder to deploy
          dacpac-package: 'SQLPool.dacpac'

Alternatively, you can add a secret to an organization level by going to the Settings section within your organization account. You can add a secret in the same menu location there.

This can be useful if you want a secret to be shared across multiple repositories within your organization. Such as Azure credentials. However, bear in mind that the GitHub plan you use for your organization can affect which repositories can use the secrets at an organization level.

Getting secrets from Azure Key Vault

Last method that I want to cover is getting secrets from Azure Key Vault. You can do this by using an action called get-keyvault-secrets.

At the end of last month, a deprecation notion was added to the readme file for this action. However, Microsoft still recommends this method to use Key Vault secrets in GitHub Actions workflows. Plus, it is easy to use as an introduction to the concept.

Basically, you add the action to your workflow to connect up to your Azure Key Vault. Adding the secrets that you want to get from there. Like in the below example taken from the GitHubActions-AzureSQLDatabase repository.

      - uses: Azure/get-keyvault-secrets@v1
        with: 
          keyvault: '${{ secrets.AZURE_KEYVAULT }}'
          secrets: 'AzureSQLServer, AzureSQLDB-Connection-String'
        id: myKeyVaultSecrets 

You can than reference the secrets in a similar way as you reference the secrets I mentioned earlier. Except that you put ‘steps.{id value you used in the action}.outputs.’ in front of the name of the Key Vault secrets. As you can see below.

      - name: Azure SQL Deploy
        uses: Azure/sql-action@v1.2
        with:
          # Enter the dedicated SQL endpoint as the Server name 
          # Using a secret from Key Vault
          server-name: '${{ steps.myKeyVaultSecrets.outputs.DedicatedEndPoint }}'
          # The connection string, including authentication information, for the dedicated SQL Pool.
          connection-string: '${{ steps.myKeyVaultSecrets.outputs.DedicatedSQLPool-Connection-String }}'
          # Name of the dacpac file in the artifact
          dacpac-package: 'SQLPool.dacpac'

Personally, I like this action. Shame it is being deprecated.

Data Saturday Stockholm

If you are at Data Saturday Stockholm on Saturday you can watch myself and Sander Stad present a session relating to using GitHub and Azure DevOps together.

Of course, there are other great sessions there as well. Including an Azure DevOps session by Erwin de Kreuk and a GitHub Actions session by Grant Fritchey.

Final words about keeping your Azure Synapse secrets secret

I really hope that some of you find this post about keeping your Azure Synapse secrets secret in GitHub useful. In reality, there are other methods this is just an introduction to some of the methods.

I really want to get the message across that you should not be storing sensitive information in your GitHub repositories. Especially public repositories. Once they are in the public domain it is hard to remove them.

Of course, if you have any comments or queries about this post feel free to reach out to me.