Create User Managed Identity and add to SQL Sever

Question

I want to set up a CI/CD Create User Managed Identity and add it to SQL Sever But after finished step add UMI to Webapp and move to step SQL I had error "Invoke-Sqlcmd : Principal 'AAATEST' could not be created. Only connections established with Active Directory ", I read some documents because I have used a local account, not AAD. But I can't set it up like that because it breaches security. Can anybody give me an idea for finishing it?

Thanks for your helping!

$MyIdentity = 'AAATEST'

$Identity = "/subscriptions/$(SubsriptionID)/resourceGroups/$(ResourceGroup)/providers/Microsoft.ManagedIdentity/userAssignedIdentities/$MyIdentity"

Create User Managed Identity

az identity create --resource-group $(ResourceGroup) --name $MyIdentity

Add User Managed Identity to List Web App+Function App

$webapps= @( $(Webapplist) )

Foreach ($name in $webapps) {az webapp identity assign --resource-group $(ResourceGroup) --name $name --identities $Identity}

Add+Grant permission User Managed Identity to SQL

$query = "

CREATE USER $MyIdentity FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER $MyIdentity;
ALTER ROLE db_datawriter ADD MEMBER $MyIdentity;
ALTER ROLE db_ddladmin ADD MEMBER $MyIdentity;

GRANT EXECUTE TO $MyIdentity

GO
"
write-output "Create DB Account named $MyIdentity"
Invoke-Sqlcmd -ServerInstance tcp:$(SQLServer) -Username $(SQLServerAdmin) -Password $(SQLServerPass) -Database $(Database ) -Query $query

Error

2022-11-22T11:07:53.1709134Z Invoke-Sqlcmd : Principal 'AAATEST' could not be created. Only connections established with Active Directory
2022-11-22T11:07:53.1710479Z accounts can create other Active Directory users.
2022-11-22T11:07:53.1711090Z Cannot add the principal 'AAATEST', because it does not exist or you do not have permission.
2022-11-22T11:07:53.1713046Z Cannot find the user 'AAATEST', because it does not exist or you do not have permission.
2022-11-22T11:07:53.1713594Z Msg 33159, Level 16, State 1, Procedure , Line 3.
2022-11-22T11:07:53.1714076Z At C:\AzSource\vm05_temp\azureclitaskscript1669115260062_inlinescript.ps1:25 char:1
2022-11-22T11:07:53.1714746Z + Invoke-Sqlcmd -ServerInstance tcp:azcacaufd1devsql01.database.windows ...
2022-11-22T11:07:53.1715180Z + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2022-11-22T11:07:53.1718280Z + CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
2022-11-22T11:07:53.1718841Z + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Document guild connect like this:
$connectString = "Server=tcp:$sqlServerName.database.windows.net,1433;Initial Catalog=$sqlDBName;Authentication=Active Directory Password;user=$AADAdminUser;pwd=$AADAdminPw"
$query ;"

Answer 1

Hey,
Based on the error message it can be either due to below :

1. have you logged in to the database wherein you are trying to grant access via an AD Account?
   Because you can grant an ad app/ group/ account by logging in via an Ad account only
2. what is the access of the ad account via which in case if you have logged inkn the database?
   3)is there any object with name in aaatest in your ad?