Creating Azure Function and SQL Database with Azure DevOps Pipeline

I have started a small project with an Azure Function that collects data from an external API and pushes the information into an Azure SQL Database. I will not go into detail what the solution does but wanted to share the Azure DevOps Pipeline that I am using.

Hope this can be useful for anyone in the world 😊

Some background

The Azure Function is written in .Net Core C# and connects to a database with an application setting. All Azure services are created with Azure json templates and the database table is created with a SQL script. To be able to deploy the SQL script two PowerShell scripts are used to open and close the Database Server Firewall ports for Azure DevOps.

The image, “windows-latest”, is used because of problems with the “vs2017-win2016” image when using Sqlcmd. When using the “vs2017-win2016” image Sqlcmd command could not be found. I didn’t troubleshoot this, instead I just changed to “windows-latest”.

Azure Repos have been used for the code, scripts and templates. The templates and SQL scripts are placed in different folders.

All variables that are used in the pipeline are defined in the yaml expect for the password to the database. That variable is defined in the Azure Pipeline variables as a secure string.

# Build and Deploy pipeline that creates an Azure .NET Core Function on Windows and Azure SQL Database

trigger:
- master

variables:
  azureSubscription: 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'
  functionAppName: 'MyApp'
  vmImageName: 'windows-latest'
  workingDirectory: '$(System.DefaultWorkingDirectory)/StocksApplication'
  solution: '**/*.sln'
  buildPlatform: 'Any CPU'
  buildConfiguration: 'Release' 
  resourcegroup: 'myRG'
  connectedServiceName: 'myconnectedServiceName'
  sqlServerName: 'mysqlServerName'
  sqlUserName: 'mysqlUserName'
  sqlDatabaseName: 'myDatabaseName'
  location: 'West Europe'

stages:
- stage: Build
  displayName: Build stage

  jobs:
  - job: Build
    displayName: Build
    pool:
      vmImage: $(vmImageName)

    steps:
    - task: DotNetCoreCLI@2
      displayName: Build
      inputs:
        command: 'build'
        projects: |
          $(workingDirectory)/*.csproj
        arguments: --output $(System.DefaultWorkingDirectory)/publish_output --configuration Release

    - task: ArchiveFiles@2
      displayName: 'Archive files'
      inputs:
        rootFolderOrFile: '$(System.DefaultWorkingDirectory)/publish_output'
        includeRootFolder: false
        archiveType: zip
        archiveFile: $(Build.ArtifactStagingDirectory)/$(Build.BuildId).zip
        replaceExistingArchive: true

    - publish: $(Build.ArtifactStagingDirectory)/$(Build.BuildId).zip
      artifact: drop
    
    - task: PublishPipelineArtifact@0
      inputs:
        targetPath: 'Templates'
        artifactName: 'templates'
        publishLocation: 'Container'
      displayName: 'Publish Template folder to drop'
      
    - task: PublishPipelineArtifact@0
      inputs:
        targetPath: 'SQL'
        artifactName: 'sql'
        publishLocation: 'Container'
      displayName: 'Publish SQL folder to drop'

- stage: Deploy
  displayName: Deploy stage
  dependsOn: Build
  condition: succeeded()

  jobs:
  - deployment: Deploy
    displayName: Deploy
    environment: 'development'
    pool:
      vmImage: $(vmImageName)

    strategy:
      runOnce:
        deploy:

          steps:
          # https://docs.microsoft.com/en-us/azure/azure-resource-manager/resource-manager-tutorial-use-azure-pipelines#create-a-pipeline
          # https://github.com/microsoft/azure-pipelines-yaml/issues/363
          # Deploy Function by template
          - task: AzureResourceManagerTemplateDeployment@3
            displayName: 'Deploy Function template'
            inputs:
              deploymentScope: 'Resource Group'
              connectedServiceName: ${{variables.connectedServiceName}}
              azureSubscription: '$(azureSubscription)'
              action: 'Create Or Update Resource Group'
              resourceGroupName: '$(resourcegroup)'
              location: '$(location)'
              templateLocation: 'Linked artifact'
              csmFile: '$(Pipeline.Workspace)\Templates\functionApp.json'
              csmParametersFile: '$(Pipeline.Workspace)\Templates\functionAppParamenter.json'
              deploymentMode: 'Incremental'

          # Deploy SQL Database server by template
          - task: AzureResourceManagerTemplateDeployment@3
            displayName: 'SQL Server - Azure Deployment: Create Or Update Resource Group'
            inputs:
              deploymentScope: 'Resource Group'
              connectedServiceName: ${{variables.connectedServiceName}}
              azureSubscription: '$(azureSubscription)'
              action: 'Create Or Update Resource Group'
              resourceGroupName: '$(resourcegroup)'
              location: '$(location)'
              templateLocation: 'Linked artifact'
              csmFile: '$(Pipeline.Workspace)\Templates\sqlServer.json'
              csmParametersFile: '$(Pipeline.Workspace)\Templates\sqlServerParameters.json'
              overrideParameters: -administratorLoginPassword $(sqlAdministratorLoginPassword)
              deploymentMode: 'Incremental'

          # Deploy SQL Database by template
          - task: AzureResourceManagerTemplateDeployment@3
            displayName: 'SQL Database - Azure Deployment: Create Or Update Resource Group'
            inputs:
              deploymentScope: 'Resource Group'
              connectedServiceName: ${{variables.connectedServiceName}}
              azureSubscription: '$(azureSubscription)'
              action: 'Create Or Update Resource Group'
              resourceGroupName: '$(resourcegroup)'
              location: '$(location)'
              templateLocation: 'Linked artifact'
              csmFile: '$(Pipeline.Workspace)\Templates\sqlDatabase.json'
              csmParametersFile: '$(Pipeline.Workspace)\Templates\sqlDatabaseparameters.json'
              deploymentMode: 'Incremental'

          # PowerShell
          # https://docs.microsoft.com/en-us/azure/devops/pipelines/targets/azure-sqldb?view=azure-devops&tabs=yaml#deploying-conditionally
          # https://docs.microsoft.com/en-us/azure/devops/pipelines/tasks/utility/powershell?view=azure-devops
          - task: AzurePowerShell@2
            displayName: 'Azure PowerShell: Deploy FW opening'
            inputs:
              azureSubscription: '$(azureSubscription)'
              scriptPath: '$(Pipeline.Workspace)\SQL\SetAzureFirewallRule.ps1'
              scriptArguments: -ResourceGroup '$(resourcegroup)' -ServerName '$(sqlServerName)' -AzureFirewallName 'AzureWebAppFirewall-$(Build.BuildId)'
              azurePowerShellVersion: LatestVersion

          # Create tables and stored procedure
          - task: CmdLine@1
            displayName: 'Run Sqlcmd to create table and stored procedure'
            inputs:
              filename: Sqlcmd
              arguments: '-S $(sqlServerName).database.windows.net -U $(sqlUserName) -P $(sqlAdministratorLoginPassword) -d $(sqlDatabaseName) -i $(Pipeline.Workspace)\SQL\StocksSQLCreate.sql'
         
          - task: AzurePowerShell@2
            displayName: 'Azure PowerShell: Delete FW opening'
            inputs:
              azureSubscription: '$(azureSubscription)'
              scriptPath: '$(Pipeline.Workspace)\SQL\RemoveAzureFirewall.ps1'
              scriptArguments: -ResourceGroup '$(resourcegroup)' -ServerName '$(sqlServerName)' -AzureFirewallName 'AzureWebAppFirewall-$(Build.BuildId)'
              azurePowerShellVersion: LatestVersion

          - task: AzureFunctionApp@1
            displayName: 'Azure functions app deploy'
            inputs:
              azureSubscription: '$(azureSubscription)'
              appType: functionApp
              appName: $(functionAppName)
              package: '$(Pipeline.Workspace)/drop/$(Build.BuildId).zip'

          # https://docs.microsoft.com/en-us/azure/devops/pipelines/tasks/deploy/azure-app-service-settings?view=azure-devops
          - task: AzureAppServiceSettings@1
            inputs:
              azureSubscription: '$(azureSubscription)'
              appName: $(functionAppName)
              resourceGroupName: '$(resourcegroup)'
              appSettings: |
                [
                   {
                    "name": "sqldb_connection",
                    "value": "Server=tcp:$(sqlServerName).database.windows.net,1433;Initial Catalog=$(sqlDatabaseName);Persist Security Info=False;User ID=$(sqlUserName);Password=$(sqlAdministratorLoginPassword);MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;",
                    "slotSetting": false
                   }
                ]

Disable access for everyone to the Azure Red Hat OpenShift portal

When creating an Azure Red Hat OpenShift (ARO) cluster you will notice that everyone with an AAD account will have access to the portal and to create a project. If you would like to disable this behavior and only let certain users to access ARO you can do this by edit the ARO Enterprise Application in AAD.

Under properties change “User assignment required” to “Yes”. Then populate the “Users and groups” on the Enterprise Application with the users that should have access to the ARO portal.

/usr/bin/python: No module named azure

If you get the message “/usr/bin/python: No module named azure” when running the az command in a Linux VM where you have installed azure-cli. Your problem could be that you are using an old version of Python.

Check what Python version is used with the command: python -V

If using an old Python version, install a newer. Check documentation how to update the Python version at your system. At the time of writing I am using 3.2.8.
After the installation check the bin directory for Python using the command:
ls -l /usr/bin/python*

To change for a new Python version, you need to remove and create a new link. Use the following command:
sudo rm /usr/bin/python
sudo ln -s /usr/bin/python3 /usr/bin/python

Check what version is used after creating a new link. Command:
python -V