Login failed for user '<token-identified principal>' while authorizing to SQL Server via Service Principal from AAD group assigned

I visited and tried several examples, but error still the same. I have a AAD group named Azure_SqlServer_Admins assigned to SQL Server in Entra section as Microsoft Entra admin. I have a service principal name DevEnvSP bound to this group(inside). DevEnvSP is used in my Azure DevOps pipelines as well for deploying sql server and building etc. This Service Principal has Contributor role, can read users (Directory Read). I did as per guide

The goal: from pipeline as service principal is to execute SQL script for database. I tried several different codes for example:

- task: SqlAzureDacpacDeployment@1
  displayName: 'name'
  inputs:
    azureSubscription: ${{ variables.subscriptionName }}
    AuthenticationType: 'servicePrincipal'
    ServerName: ${{ variables.sqlServerName }}.database.windows.net
    DatabaseName: ${{ parameters.sqlDbName }}
    IpDetectionMethod: 'AutoDetect'
    deployType: 'InlineSqlTask'
    SqlInline: my sql code

I receive the error Login failed for user '' from my pipeline step. Ok , I also tried different case from Microsoft Docs, I got secret and got client id and went through accessToken:

- task: AzurePowerShell@5
  displayName: Creating user for ${{ parameters.sqlDbName }}
  inputs:
    azureSubscription: ${{ variables.subscriptionName }}
    azurePowerShellVersion: 'LatestVersion'
    ScriptType: 'InlineScript'
    FailOnStandardError: true
    Inline: |
        $token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token

        $CmdText = @"
            My SQL query
        "@
        
        Invoke-SqlCmd -ServerInstance "${{ variables.sqlServerName }}.database.windows.net" `
                      -Database "DbTest" `
                      -AccessToken $token `
                      -Query $CmdText
                      -Verbose
                      -OutputSqlErrors $true

Unfortunately the same error.

I read the corresponding and similar StackOverflow answer: https://stackoverflow.com/a/71510259/17239546 . Looks like there are some restrictions related to Admin if it is assigned as AAD group. From: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-database-scoped-credential-transact-sql?view=sql-server-ver16#remarks

A database scoped credential is a record that contains the authentication information that is required to connect to a resource outside SQL Server. Most credentials include a Windows user and password.

But I did not find solution for this or missed something in all examples everyone uses a single user/sp as an admin, not a group and it works.

NOTE:

• I even tried manually create users for particular database and login in, nothing changed:

DECLARE @UMIUser NVARCHAR(100) = 'SP name';
IF DATABASE_PRINCIPAL_ID(@UMIUser) IS NULL
BEGIN
  CREATE USER [@UMIUser] FROM EXTERNAL PROVIDER;
  ALTER ROLE db_datareader ADD MEMBER [@UMIUser];
  ALTER ROLE db_datawriter ADD MEMBER [@UMIUser];
  ALTER ROLE db_ddladmin ADD MEMBER [@UMIUser];
END

• SQL Server networking is fine, no restrictions
• Allow Azure services and resources to access this server set as true
• SQL Server name fully qualified name and database is correct cause I can authorize via sql authentication type.

I would be grateful if someone help me with this...

Based on your description, I created an Azure SQL DB with the admin set as an AAD group and succeeded to access to the DB via the YAML pipeline below to run a simple query.

variables:
  ARMSvcCnnName: ARMSvcCnnSubX
  AzureSQLServerName: xxxazsqlserverxxx
  AzureSQLDBName: xxxazsqldbxxx
  system.debug: true

pool:
  vmImage: 'windows-latest'

steps:
- task: SqlAzureDacpacDeployment@1
  displayName: 'Run SQL query'
  inputs:
    azureSubscription: '$(ARMSvcCnnName)'
    AuthenticationType: 'servicePrincipal'
    ServerName: '$(AzureSQLServerName).database.windows.net'
    DatabaseName: '$(AzureSQLDBName)'
    deployType: 'InlineSqlTask'
    SqlInline: 'SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES;'
    IpDetectionMethod: 'AutoDetect'
    DeleteFirewallRule: false

Since the SqlAzureDacpacDeployment@1 task was refencing the Azure Resource Manager service connection to authenticate access to Azure SQL DB against its underlying service principal (app registration), which should be a member of the SQL server admin group, I would also suggest setting the pipeline variable system.debug as true, so that the service principal information was visible in the debug logs for us to double check if the ARM service connection that we selected was correct.

In addition, please also check the sign-in logs in your AAD to see if there is further information on why the service principal sign-ins were failed.