azure - Powershell Azure SQL DB(Yaml 管道)

Question

我尝试在 yaml 管道中使用 azure cli 步骤创建包含托管身份(系统分配)的 azure 广告组，然后登录到 azure sql db 并执行命令来创建角色。

groupid=$(az ad group create --display-name myAzureSQLDBAccessGroup --mail-nickname myAzureSQLDBAccessGroup --query objectId --output tsv)
msiobjectid=$(az webapp identity show --resource-group myResourceGroup --name <app-name> --query principalId --output tsv)
az ad group member add --group $groupid --member-id $msiobjectid

现在我需要连接到 azure sql db 并运行以下命令

CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<identity-name>];

我正在尝试找到一种方法来连接到azure sql db并执行上述权限分配。

Answer 1

我尝试了以下 Azure Devops CLI 和 SQL 任务来首先创建一个 Azure Ad 组，然后获取 Azure Web 应用身份并将该身份添加为上面创建的 Azure AD 组中的成员。

我的 YAML 脚本:-

# Starter pipeline

# Start with a minimal pipeline that you can customize to build and deploy your code.

# Add steps that build, run tests, deploy, and more:

# https://aka.ms/yaml

  

trigger:

- main

  

pool:

vmImage: windows-latest

  

steps:

- script: echo Hello, world!

displayName: 'Run a one-line script'

  

- script: |

echo Add other tasks to build, test, and deploy your project.

echo See https://aka.ms/yaml

displayName: 'Run a multi-line script'

  

- task: AzureCLI@2

inputs:

azureSubscription: 'subscription(<sub-id>)'

scriptType: 'bash'

scriptLocation: 'inlineScript'

inlineScript: 'az ad group create --display-name avengersgrp987 --mail-nickname avengerss43 --query objectId --output tsv'

- task: AzureCLI@2

inputs:

azureSubscription: 'subscription(<sub-id>)'

scriptType: 'bash'

scriptLocation: 'inlineScript'

inlineScript: |

msiobjectid=$(az webapp identity show --resource-group siliconrg --name pratikwebapp --query principalId --output tsv)

az ad group member add --group avengersgrp987 --member-id $msiobjectid

az ad group member list --group avengersgrp987 --query [].objectId

  

- task: SqlAzureDacpacDeployment@1

inputs:

azureSubscription: 'subscription(<sub-id>)'

AuthenticationType: 'aadAuthenticationPassword'

ServerName: '<sql-servername>.database.windows.net'

DatabaseName: '<database-name>'

aadSqlUsername: 'AAD ADMIN IN SQL USERNAME'

aadSqlPassword: 'AADUSER PASSWORD'

deployType: 'InlineSqlTask'

SqlInline: |

CREATE USER pratikwebapp FROM EXTERNAL PROVIDER;

ALTER ROLE db_datareader ADD MEMBER pratikwebapp;

IpDetectionMethod: 'IPAddressRange'

StartIpAddress: '0.0.0.0'

EndIpAddress: '255.255.255.255'

输出:-

使用分配的 Web 应用身份创建的 Azure AD 组如下所示:-

为了添加 Azure 托管身份作为 Azure SQL 中的用户，您需要为 Azure SQL Server 启用 Azure AD 身份验证 如下所示:-

在管道后添加了 Azure AD

Select * FROM sysusers