Saturday, June 22, 2024
HomeMicrosoft ExchangeAzure SQL Database Contained person creation Concern

Azure SQL Database Contained person creation Concern


I’m engaged on an answer to combine the Azure FunctionApp with Azure SQL Database utilizing Managed Id. Sources are provisioned by means of terraform and the operate app is now having a person assigned managed Id. Nevertheless the ultimate step so as to add the managed identification as a contanied person contained in the Azure SQL Database is failing/person is just not created contained in the Azure SQL database. Listed here are the steps I’ve tried.

 

Strategy 1. 

 

– processAzureCLI@2

            displayNameDecide SQL Server SID for managed identification created for Az Func

            inputs:

              azureSubscription${{variables.devServiceConnection}}

              scriptTypepscore

              scriptLocationinlineScript

              failOnStandardErrorfalse

              inlineScript|      

                # We want to have the ApplicationId of the App Registration that represents the

                # WebApp.   The SID is calculated based mostly on the ApplicationId

                      

                $apiSp = az advert sp checklist –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”

          – processSqlAzureDacpacDeployment@1

            displayName‘Create login for managed identification’

            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 identify = ‘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’

 

Received the under error

 

Exception calling “Parse” with “1” argument(s): “Unrecognized Guid format.”

At D:a_tempazureclitaskscript1717787636114_inlinescript.ps1:7 char:1

+ https://techcommunity.microsoft.com/t5/azure/azure-sql-database-contained-user-creation-issue/m-p/4163674#M16784$guid = [System.Guid]::Parse($appId)

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

+ CategoryInfo : NotSpecified: (:) [], ParentContainsErrorRecordException

+ FullyQualifiedErrorId : FormatException

##[error]Script failed with exit code: 1

 

Don’t know, why this error is developing.

 

Strategy 2.

 

Eradicating the Azure CLI process and making an attempt to create the SQL Person with the managed Id straight. Nevertheless the person is just not nonetheless created within the DB, however there are not any errors this time.

 

– processSqlAzureDacpacDeployment@1

            displayName‘Create login for managed identification’

            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 identify = ‘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 somebody please present any steering on how one can get this accomplished?

 

Thanks

DD



RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments