如何在GitHub操作中将数据库所有者添加到Azure SQL Server数据库

Question

我可以通过使用"azure/arm-deploy@v1&"；，https://github.com/HansKindberg-Lab/IdentityServer-Demo-Deploy-NEW/blob/master/.github/workflows/Azure-Deploy.yml#L74

如何使用GitHub操作将数据库所有者添加到Azure中的SQL Server数据库？

Answer 1

我使用Invoke-Sqlcmd解决了它，https://learn.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 & 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 & -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