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:
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
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.