Powershell Azure SQL DB (Yaml Pipeline)

Question

I am trying to use azure cli step in yaml pipeline to create azure ad group containing managed identity (system assigned) and then login to azure sql db and execute command to create roles.

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

Now I need to connect to azure sql db and run the below

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

I am trying to find a way to connect to azure sql db and execute the above permission assignment.

Answer 1

I tried the below Azure Devops CLI and SQL task to first create an Azure Ad group, Then take the Azure web app identity and add that Identity as a member in the Azure AD group created above.

My YAML Script:-

# 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'

Output:-

Azure AD group created with Web app identity assigned like below:-

In order to add Azure Managed identity as a user in Azure SQL you need to enable Azure AD authentication for your azure SQL Server like below:-

Added Azure AD after the pipeline

Select * FROM sysusers