by: KarolPosted on:

Deploy Azure App Service integrated with Azure SQL and APIM

In this article, I’ll demonstrate how to deploy the .NET 7.0 Rest API with Managed Identity and integrate it with Azure SQL. As a result, you can access Azure SQL without requiring a password in your application. Cool setup, in your perception?

This is the next episode of the Network Series. Just to recap, in the Network series, I demonstrate how to expose Rest APIs using Azure API Management. Private endpoints are used to connect all dependencies.

As is usual, all source code can be found at the beginning of the article, which can be found here. If you don’t know how to set up a pipeline, all the instructions are provided below!

You must first deploy Azure components from the Network Series scenario before you can use the pipeline described in this article. To do so, navigate to the Rest API with Private Endpoint, exposed to API Management by Terraform to download Terraform sources and ready-to-use pipelines.

The deployment pipeline consists of four stages:

So, first, you must configure variables in dotnet-sql-app-build-and-deploy.yml file. Set variables values where you find # sign. All names of the App Service and Azure SQL should be in the Azure portal after terraform script execution.

pool: Default

variables:
  buildConfiguration: 'Release'
  webAppName: '#WEB_APP_NAME#'
  azureSubscription: '#SERIVCE_CONNECTION_NAME#'
  apimResourceGroup: '#APIM_RG#'
  apimName: '#APIM_NAME#'
  apimServiceName: '#APIM_SERVICE_NAME#'
  apimPath: '#APIM_SERVICE_PATH'
  specificationUrl: 'https://#WEB_APP_NAME#.azurewebsites.net/swagger/v1/swagger.json'
  serviceUrl: 'https://#WEB_APP_NAME#.azurewebsites.net'
  specificationFormat: 'OpenApiJson'  
  productName: TodoProduct
  productNameId: TodoProductId
  projectPath: $(Build.SourcesDirectory)/TodoApi/TodoApi.csproj
  databaseContext: MyDatabaseContext
  connectionString: Server=tcp:#AZURE_SQL_SERVER_NAME#.database.windows.net;Authentication=Active Directory Default;Database=#AZURE_SQL_DB_NAME#;

Since every API Management variable in the Network Series is common, you can simply copy it from another pipeline. However, the route to OpenApiJson and the project name may vary, so be careful!

Take a look at the below parameter, you can find it on the top pipeline:

pool: Default

The Default pool in Azure DevOps is designed for self-hosted agents. This solution is isolated at the network level, so we require it. More information is available here if you click here: Rest API with Private Endpoint, exposed to API Management by Terraform.

Build .NET 7.0 app

So building .NET 7.0 app is a trivial thing, but in this case, are some tricky aspects:

  • Executing Entity Framework migration – running in the Linux self-hosted agent. I described in more detail here: Execute entity framework migration on a Linux
  • Authentication in the Azure SQL – authentication is made without a password, why? We will get there in a second.

I don’t want to repeat Entity Framework migration on Linux once, so I only mention that you need to use a bundle to make it execute on the Linux agent.

About the second part, of course, the answer is very simple, our App Service has Managed Identity turn on. So this identity is used to authenticate in the Azure SQL. Before you can use this feature, Managed Identity user must be added to the Azure SQL. You need to log in as administrator, and execute the below query:

CREATE USER [MI_NAME] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [MI_NAME];
ALTER ROLE db_datawriter ADD MEMBER [MI_NAME];
ALTER ROLE db_ddladmin ADD MEMBER [MI_NAME];

Replace MI_NAME with your application name, because the basic name of the Managed identity equals the service name which is used.

The connection string for Azure SQL DB looks like this:

Server=tcp:#SERVER_NAME#.database.windows.net;Authentication=Active Directory Default;Database=#DB_NAME#;

Please update #SERVER_NAME# and #DB_NAME# for proper values in the appsettings.json file. Take a look, at that on the authentication part, Active Directory Default is set. This indicates that we are using Managed Identity.

Run Entity Framework migration for Azure SQL DB on Linux self-hosted agent

As you probably know, executing the Entity Framework bundle, is very easy, look below:

- task: AzureCLI@2
  inputs:
    azureSubscription: '$(azureSubscription)'
    scriptType: bash
    scriptLocation: inlineScript
    inlineScript: |
      chmod +x $(Pipeline.Workspace)/SQLScripts/bundle
      $(Pipeline.Workspace)/SQLScripts/bundle --connection "$(connectionString)"

First, the artefact from the build stage is downloaded, just after our bundle is executed. Pay attention to chmod +x, it set that $(Pipeline.Workspace)/SQLScripts/bundle is executable.

The full pipeline you can get from here.

Deploy .NET 7.0 application into Azure App Service

Azure App Service deployment is the easiest part of the pipeline. We only must ensure that previous steps are successful, you can do it by adding:

- stage: Deploy
    displayName: 'Deploy web app'
    dependsOn: Update_db
    condition: succeeded()

When the above condition is satisfied, we can make a deployment:

strategy:
          runOnce:
            deploy:
              steps:
                - task: AzureWebApp@1
                  inputs:
                    azureSubscription: '$(azureSubscription)'
                    appType: 'webAppLinux'
                    appName: $(webAppName) 
                    package: '$(Pipeline.Workspace)/drop/TodoApi.zip'

We need only three parameters to make it work:

  • azureSubscription – name of our connection to the Azure Cloud
  • webAppName – name of the Azure App Service component, created during infrastructure deployment
  • package – path to an artefact with application build archive

API Management and Azure App Service integration

Before we update API Management, we have to wait for Azure App Service to update. I add 3 minutes of sleep before the APIM update will execute.

To deploy Azure App Service to the API Management we have to:

Get API revision

If we want to import new versions of the API, we need to set new revisions for each deployment.

currentApiRevision=$(az apim api revision list --resource-group $APIMRESOURCEGROUP --service-name $APIMNAME --api-id $APIMSERVICENAME | jq '.[0].apiRevision' ) 
parsedApiRevision=$(echo $currentApiRevision | tr -d '"')
nextApiRevision=$(($parsedApiRevision + 1))

Create/get product

Contains one or more APIs, defines a usage quota, and the terms of use.

az apim product create --resource-group $APIMRESOURCEGROUP  --service-name $APIMNAME --product-id $PRODUCTNAMEID --product-name $d --subscription-required true --approval-required false --state "published"

Add our API to the product

az apim product api add --resource-group $APIMRESOURCEGROUP --service-name $APIMNAME --product-id $PRODUCTNAMEID --api-id $APIMSERVICENAME

Import API from OpenApi definition, hosted by Azure Function

az apim api import -g $APIMRESOURCEGROUP --service-name $APIMNAME --path $APIMPATH --service-url $SERVICEURL --specification-url $SPECIFICATIONURL --specification-format $SPECIFICATIONFORMAT --api-type http --api-revision $nextApiRevision --api-id $APIMSERVICENAME --subscription-required false

Create a release of the revision, to publish the newest changes.

az apim api release create --resource-group $APIMRESOURCEGROUP --service-name $APIMNAME --api-id $APIMSERVICENAME --release-id MyRelease_$nextApiRevision --api-revision $nextApiRevision

The above API Management deployment is valid for all Rest APIs for which you can expose OpenAPI documents, for example, swagger. You need to remember to give some time between deployment and updating your API because you need a new version of the API to be available.

I hope this article was useful for you 🙂 Check out more articles from this series:

Leave a Reply

Your email address will not be published. Required fields are marked *