Блог

Я могу создать базу данных SQL Server с помощью действий GitHub, используя «azure / arm-deploy @ v1», https://github.com/HansKindberg-Lab/IdentityServer-Demo-Deploy-NEW/blob/master/.github/workflows/Azure-Deploy.yml#L74

Как мне, используя действия GitHub, добавить владельца базы данных в базу данных SQL Server в Azure?

Я решил это с помощью Invoke-Sqlcmd, https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps .

Рабочий процесс:

 name: "Azure-Deploy"

on:
  #push:
  #  branches: [ master ]
  workflow_dispatch:

env:
  SQLSERVER_INSTANCE: "${{ secrets.SQLSERVER_NAME }}.database.windows.net"

jobs:
  Azure-Deploy:
    name: "Azure-Deploy"
    runs-on: "ubuntu-latest"
    steps:
      - name: "Checkout code"
        uses: actions/checkout@v2
      - name: "Set up dotnet"
        uses: actions/setup-dotnet@v1
        with:
          dotnet-version: "5.0"
      - name: "Build"
        run: dotnet build --configuration Release
        working-directory: "Source"
      - name: "Get AppSettings.json path"
        id: appSettingsJsonPath
        run: |
          $appSettingsJsonPath = (Get-ChildItem -Filter "AppSettings.json").FullName;
          echo "::set-output name=value::$($appSettingsJsonPath)";
        shell: pwsh
        working-directory: "Source"
      - name: "Convert AppSettings.json to Azure App Service settings"
        id: azureAppServiceSettings
        run: |
          Add-Type -AssemblyName "Project.dll";
          $appServiceName = "${{ secrets.AZURE_APPSERVICE }}";
          $appSettingsJsonPath = "${{ steps.appSettingsJsonPath.outputs.value }}";
          $signingCertificateThumbprint = "${{ secrets.SIGNING_CERTIFICATE_THUMBPRINT }}";
          $validationCertificateThumbprints = "${{ secrets.VALIDATION_CERTIFICATE_THUMBPRINTS }}";
          $azureAppServiceSettings = [Project.ConfigurationHelper]::ConvertToAzureAppServiceSettings($appServiceName, $appSettingsJsonPath, $false, $signingCertificateThumbprint, $validationCertificateThumbprints);
          echo "::set-output name=value::$($azureAppServiceSettings)"
        shell: pwsh
        working-directory: "Source/Project/bin/Release/net5.0"
      - name: "Azure sign-in"
        uses: azure/login@v1
        with:
          creds: ${{ secrets.AZURE_CREDENTIALS }}
      - name: "Ensure resource-group"
        uses: azure/cli@v1
        with:
          inlineScript: |
            az group create --location ${{ secrets.AZURE_LOCATION }} --name ${{ secrets.AZURE_RESOURCEGROUP }}
      - name: "ARM deploy"
        uses: azure/arm-deploy@v1
        with:
          parameters: appServiceName=${{ secrets.AZURE_APPSERVICE }} databaseName=${{ secrets.DATABASE_NAME }} signingCertificate=${{ secrets.SIGNING_CERTIFICATE }} signingCertificateName=${{ secrets.SIGNING_CERTIFICATE_NAME }} signingCertificatePassword=${{ secrets.SIGNING_CERTIFICATE_PASSWORD }} sithsRootCertificate=${{ secrets.SITHS_ROOTCERTIFICATE }} sqlServerAccessClientIp=${{ secrets.SQLSERVER_ACCESS_CLIENT_IP }} sqlServerAdministrator=${{ secrets.SQLSERVER_ADMINISTRATOR }} sqlServerAdministratorPassword=${{ secrets.SQLSERVER_ADMINISTRATOR_PASSWORD }} sqlServerName=${{ secrets.SQLSERVER_NAME }}
          resourceGroupName: ${{ secrets.AZURE_RESOURCEGROUP }}
          subscriptionId: ${{ secrets.AZURE_SUBSCRIPTION }}
          template: "Source/Template.json"
      - name: "Ensure user amp; owner for database"
        env:
          ENSURE_USER_SQL: |
            IF NOT EXISTS(SELECT [name] FROM [sys].[sql_logins] WHERE [name] = '${{ secrets.DATABASE_USER }}')
            BEGIN
              SELECT 'Creating login ''${{ secrets.DATABASE_USER }}''...' AS 'Output';
              EXECUTE ('CREATE LOGIN [${{ secrets.DATABASE_USER }}] WITH PASSWORD = ''${{ secrets.DATABASE_USER_PASSWORD }}'';');
            END
            ELSE
            BEGIN
              SELECT 'Setting password for login ''${{ secrets.DATABASE_USER }}''...' AS 'Output';
              EXECUTE ('ALTER LOGIN [${{ secrets.DATABASE_USER }}] WITH PASSWORD = ''${{ secrets.DATABASE_USER_PASSWORD }}'';');
            END
          ENSURE_OWNER_SQL: |
            IF NOT EXISTS (SELECT [name] FROM [sys].[database_principals] WHERE [name] = '${{ secrets.DATABASE_USER }}' AND [type] = 'S')
            BEGIN
              SELECT 'Setting ''${{ secrets.DATABASE_USER }}'' as owner for database ''${{ secrets.DATABASE_NAME }}''...' AS 'Output';
              EXECUTE ('CREATE USER [${{ secrets.DATABASE_USER }}] FOR LOGIN [${{ secrets.DATABASE_USER }}];');
              EXECUTE ('ALTER ROLE [db_owner] ADD MEMBER [${{ secrets.DATABASE_USER }}];');
            END
            ELSE
            BEGIN
              SELECT '''${{ secrets.DATABASE_USER }}'' is already owner for database ''${{ secrets.DATABASE_NAME }}''.' AS 'Output';
            END
        run: |
          Install-Module -Force -Name SqlServer -Repository PSGallery;
          Import-Module SqlServer;
          $note = "Instead of using -Credential we could also use -Password amp; -Username, but if doing so and the login fails the password gets exposed in the console when the error is written."
          $securePassword = ConvertTo-SecureString '${{ secrets.SQLSERVER_ADMINISTRATOR_PASSWORD }}' -AsPlainText -Force;
          $credential = [PSCredential]::New("${{ secrets.SQLSERVER_ADMINISTRATOR }}", $securePassword);
          Invoke-Sqlcmd -Credential $credential -Database "master" -Query "${{ env.ENSURE_USER_SQL }}" -ServerInstance "${{ env.SQLSERVER_INSTANCE }}";
          Invoke-Sqlcmd -Credential $credential -Database "${{ secrets.DATABASE_NAME }}" -Query "${{ env.ENSURE_OWNER_SQL }}" -ServerInstance "${{ env.SQLSERVER_INSTANCE }}";
        shell: pwsh
      - name: "Set App Service settings"
        uses: azure/appservice-settings@v1
        with:
          app-name: "${{ secrets.AZURE_APPSERVICE }}"
          app-settings-json: "${{ steps.azureAppServiceSettings.outputs.value }}"
          connection-strings-json: "${{ secrets.CONNECTION_STRINGS }}"
          mask-inputs: true
      - name: "Set MTLS App Service settings"
        uses: azure/appservice-settings@v1
        with:
          app-name: "${{ secrets.AZURE_APPSERVICE }}-mtls"
          app-settings-json: "${{ steps.azureAppServiceSettings.outputs.value }}"
          connection-strings-json: "${{ secrets.CONNECTION_STRINGS }}"
          mask-inputs: true