mbarber78/dotnetcore-sqldb-ghactions

GitHub Actions sample for .NET Core Web Application with Azure SQL Server database backend

page_type	description	products	languages
sample	Deploy dotnet application using GitHub Actions	GitHub Actions Azure Web Apps Azure SQL Database	dotnet

ASP.NET Core and Azure SQL db for GitHub Actions

This repo contains a sample ASP.NET Core web application which uses an Azure SQL database as a backend. The web app can be deployed to Azure Web Apps by using GitHub Actions. The workflow for this is already present in the .github folder.

For a sample on how to set up your first GitHub workflow from scratch, see Create your first workflow

The sample source code is based on the tutorial for building ASP.NET Core and Azure SQL Database app in App Services.

Setup an end-to-end CI/CD workflow

This repo contains two different GitHub workflows:

• infraworkflow.yml: To create the Azure Resources required for the sample by using an ARM template. This workflow will create the following resources:
  • Resource Group
  • App Service Plan
  • Web App (with one staging slot)
  • Azure SQL Server and the Azure SQL Database for the sample.
• workflow.yml: this workflow will build the sample app and publish it to a folder on the build agent and deploy this build code to the Web App staging slot. Next it will update the database and, finally, swap the slots.

To start, you can directly fork this repo and follow the instructions below to properly setup the workflows.

Create Azure Resources (Infraworkflow.yml) setup

1. Setup environment variables

The infraworkflow.yml file for creating your azure resources, holds a couple of default values for the name of the resource group, as well as the name for the web app and SQL database. If you want to use other naming, you can change these values on lines 10 to 14.

2. Setup your subscription ID

Additionaly this workflow uses a secret value for your Azure subscription ID. In your forked GitHub repository, go to Settings - Secrets and create a new secret called AZURE_SUBSCRIPTION_ID. Give it your subscription ID as a value.

To find your current subscription ID, in an Azure cloud shell execute the following statements:

az account list -o table

3. Create Azure credentials with federated security

You'll need an Active Directory application and service principal that can access resources. These instructions show how to create credentials with Azure CLI. You can also create credentials in the Azure Portal.

If you do not have an existing application, create the Active Directory application. This command will output JSON with an appId that is your client-id.

az ad app create --display-name myApp

Save the value to use as the AZURE_CLIENT_ID GitHub secret later. You'll use the objectId value when creating federated credentials with Graph API and reference it as the APPLICATION-OBJECT-ID.

Create a service principal. Replace the appID with the appId from your JSON output. This command generates JSON output with a different objectId and will be used in the next step. The new objectId is the assignee-object-id.

 az ad sp create --id <appId>

Create a new role assignment by subscription and object. By default, the role assignment will be tied to your default subscription. Replace with your subscription ID, with your resource group name, and with the generated assignee-object-id. Learn how to manage Azure subscriptions with the Azure CLI.

az role assignment create --role contributor --subscription --assignee-object-id --scopes /subscriptions/$subscriptionId/resourceGroups//providers/Microsoft.Web/sites/--assignee-principal-type ServicePrincipal

Run the following command to create a new federated identity credential for your active directory application.

az rest --method POST --uri 'https://graph.microsoft.com/beta/applications/<APPLICATION-OBJECT-ID>/federatedIdentityCredentials' --body '{"name":"<CREDENTIAL-NAME>","issuer":"https://token.actions.githubusercontent.com","subject":"repo:organization/repository:ref:refs/heads/main","description":"Testing","audiences":["api://AzureADTokenExchange"]}' 

• Replace APPLICATION-OBJECT-ID with the objectId (generated while creating app) for your Active Directory application.
• Set a value for CREDENTIAL-NAME to reference later.
• Set the subject. The value of this is defined by GitHub depending on your workflow:
  • For Jobs not tied to an environment, include the ref path for branch/tag based on the ref path used for triggering the workflow: repo:< Organization/Repository >:ref:< ref path>. For example, repo:n-username/ node_express:ref:refs/heads/my-branch or repo:n-username/ node_express:ref:refs/tags/my-tag.
  • For workflows triggered by a pull request event: repo:< Organization/Repository >:pull_request.

You'll need to create GitHub secrets for these values from your Azure Active Directory application: - AZURE_CLIENT_ID - AZURE_TENANT_ID

For more information, see Connect GitHub and Azure.

3. (Alternate approach) Create Azure credentials with a service principal

You will also need to create a service principal and paste its details in another secret called AZURE_CREDENTIALS. This is called a deployment credential. The process for doing this is described here.

Since the infraworkflow also deploys the resourcegroup, you will need a wider credential, which is allowed to perform action on the subscription level. For this you can create the service principal as such:

az ad sp create-for-rbac --name "spname" --sdk-auth --role contributor --scopes /subscriptions/<subscription-id>

In case you don't want the service principal to have such wide permissions, you can create a regular resource group service principal with contributor rights as explained here and create your resource group through the portal or through CLI statements. You will then also need to delete lines 39-48 in the infraworkflow.yml file.

The deployment credential is used on line 27 in the infraworkflow.yml file.

4. Create database username and password secrets

Last, you will need 2 additional secrets for your SQL database username and password. Create 2 additional secrets for this called SQLADMIN_LOGIN and SQLADMIN_PASS. Make sure you choose a complex password, otherwise the create step for the SQL database server will fail. If it does, changing the secret value to a more complex one and re-running the workflow should fix this issue.

For further details, check https://docs.github.com/en/free-pro-team@latest/actions/reference/encrypted-secrets#creating-encrypted-secrets-for-a-repository

Finally, review the workflow and ensure the defined variables have the correct values and matches the pre-requisites you have just setup.

5. Execute the Create Resources workflow

Go to your repo Actions tab, under All workflows you will see the Create Azure Resources workflow.

Launch the workflow by using the workflow_dispatch event trigger.

This will create all the required resources in the Azure Subscription and Resource Group you configured.

This workflow will also trigger in case you make any changes to the infraworkflow.yml file or to any of the templates in the templates folder/.

Build and deploy app (Workflow.yml) setup

Now that the Azure resources got created, you can deploy the application to it.

1. Setup environment variables

In case you changed any of the values of the environment variables in the infraworkflow.yml file, make sure you also change these values in the workflow.yml file to the same values.

2. Setup secrets

This workflow uses 1 additional secret as opposed to the infraworkflow.yml file and that is the web apps' publish profile. To get at this file, execute the following steps:

• In the Azure portal navigate to the web app that got created by executing the infraworkflow.yml file.
• On the overview page of the web app click on 'download publish profile'.
• Copy paste the entire content of this publish profile to a new secret in your GitHub repo called AZURE_WEBAPP_PUBLISH_PROFILE.

This secret is used in line 53 of the workflow.yml file.

3. Execute the 'Build and deploy app' workflow

Go to your repo Actions tab, under All workflows you will see the Build and deploy app workflow.

Launch the workflow by using the workflow_dispatch event trigger.

This will deploy all the code and database changes on the Azure resources.

This workflow will also trigger in case you make any changes to any of the files in this repository.

Workflows YAML explained

As mentioned before, there are two workflows defined in the repo Actions tab: the Create Azure Resources and the Build and deploy app.

Create Azure Resources

Use this workflow to initially setup the Azure Resources by executing the ARM template which contain the resource definitions. This workflow is defined in the infraworkflow.yaml file, and has the following steps:

• Lines 2-6: triggers for this workflow.
• Lines 8-16: definition of environment variables.
• Lines 24-27: Login to azure CLI to gather environment and azure resources information by using the Azure Login action.
• Lines 30-33: Check out the source code by using the Checkout action.
• Lines 36-44: Deploy the resources defined in the provided ARM template by using the ARM Deploy action.
• Lines 47-56: Deploy the resources defined in the provided ARM template by using the ARM Deploy action.

Build and deploy app

Use this workflow to deploy the code and database changes. This workflow is defined in the workflow.yaml file, and has the following steps:

• Lines 3-10: triggers for this workflow.
• Lines 18-26: definition of environment variables.
• Line 34: Check out the source code by using the Checkout action.
• Lines 37-40: Set the correct .Net version on the agent machine using the setup-dotnet action.
• Lines 43-46: Execute dotnet build and publish statements.
• Lines 49-55: Update the App Service Web Application deployment for the staging slot by using the Azure Web App Deploy action.
• Lines 57-60: Login to azure CLI to gather environment and azure resources information by using the Azure Login action.
• Lines 62-67: Create the database connectionstring
• Lines 69-74: Execute the database update with entity framework using the above created connection string.
• Lines 76-79: Swap the web apps staging and production slots.

Contributing

Refer to the Contributing page