Azure SQL Database Contained user creation Issue
I am working on a solution to integrate the Azure FunctionApp with Azure SQL Database using Managed Identity. Resources are provisioned through terraform and the function app is now having a user assigned managed Identity. However the final step to add the managed identity as a contanied user inside the Azure SQL Database is failing/user is not created inside the Azure SQL database. Here are the steps I have tried.
Approach 1.
Got the below error
I am working on a solution to integrate the Azure FunctionApp with Azure SQL Database using Managed Identity. Resources are provisioned through terraform and the function app is now having a user assigned managed Identity. However the final step to add the managed identity as a contanied user inside the Azure SQL Database is failing/user is not created inside the Azure SQL database. Here are the steps I have tried. Approach 1. – task: AzureCLI@2 displayName: Determine SQL Server SID for managed identity created for Az Func inputs: azureSubscription: ${{variables.devServiceConnection}} scriptType: pscore scriptLocation: inlineScript failOnStandardError: false inlineScript: | # We need to have the ApplicationId of the App Registration that represents the # WebApp. The SID is calculated based on the ApplicationId $apiSp = az ad sp list –display-name mi-afsintegration-dev | ConvertFrom-Json $appId = $apiSp.appId [guid]$guid = [System.Guid]::Parse($appId) foreach ($byte in $guid.ToByteArray()) { $byteGuid += [System.String]::Format(“{0:X2}”, $byte) } $sid = “0x” + $byteGuid Write-Host “##vso[task.setvariable variable=mi-afsintegration-dev.Sid]$sid” – task: SqlAzureDacpacDeployment@1 displayName: ‘Create login for managed identity’ inputs: azureSubscription: ${{variables.devServiceConnection}} ServerName: ‘$(SQL_ServerName)’ DatabaseName: ‘$(DB_Name)’ SqlUsername: ‘$(SQL_Username)’ SqlPassword: ‘$(SQL_Password)’ deployType: ‘inlineSqlTask’ sqlInline: | IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = ‘mi-afsintegration-dev’) BEGIN CREATE USER [mi-afsintegration-dev] WITH sid = $(sid), TYPE = E ALTER ROLE db_datareader ADD MEMBER [mi-afsintegration-dev]; ALTER ROLE db_datawriter ADD MEMBER [mi-afsintegration-dev]; END IpDetectionMethod: ‘AutoDetect’ Got the below error Exception calling “Parse” with “1” argument(s): “Unrecognized Guid format.” At D:a_tempazureclitaskscript1717787636114_inlinescript.ps1:7 char:1 + [guid]$guid = [System.Guid]::Parse($appId) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], ParentContainsErrorRecordException + FullyQualifiedErrorId : FormatException ##[error]Script failed with exit code: 1 Have no idea, why this error is coming up. Approach 2. Removing the Azure CLI task and trying to create the SQL User with the managed Identity directly. However the user is not still created in the DB, but there are no errors this time. – task: SqlAzureDacpacDeployment@1 displayName: ‘Create login for managed identity’ inputs: azureSubscription: ${{variables.devServiceConnection}} ServerName: ‘$(SQL_ServerName)’ DatabaseName: ‘$(DB_Name)’ SqlUsername: ‘$(SQL_Username)’ SqlPassword: ‘$(SQL_Password)’ deployType: ‘inlineSqlTask’ sqlInline: | IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = ‘mi-afsintegration-dev’) BEGIN CREATE USER [mi-afsintegration-dev] FROM EXTERNAL PROVIDER ALTER ROLE db_datareader ADD MEMBER [mi-afsintegration-dev]; ALTER ROLE db_datawriter ADD MEMBER [mi-afsintegration-dev]; END IpDetectionMethod: ‘AutoDetect’ Can someone please provide any guidance on how to get this done? ThanksDD Read More