Error trying to automate grants with sql package

[Andersenn]

Hello,

We are experiencing an error when trying to deploy changes to a Fabric Warehouse using sqlpackage from an Azure DevOps pipeline.

The deployment process works or creating schemas, tables, views... on the warehouse, but it consistently fails at the post-deployment step when it attempts to execute a GRANT script targeting resource group.

Error:

The pipeline fails with the following error:

Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 33134, Level 16, State 72, Line 1 Principal '[OUR_GROUP_NAME]' could not be resolved. 
Error message: 'Server identity is not configured. Please follow the steps in "Assign an Azure AD identity to your server and add Directory Reader permission to your identity" (https://aka.ms/sqlaadsetup)'
Error SQL72045: Script execution error.

Pipeline Context:

1. The pipeline runs on an Azure DevOps agent.

2. We authenticate using a Service Principal with: az login --service-principal ...

3. We obtain an access token for Fabric with: az account get-access-token --scope https://database.windows.net/.default

4. We execute sqlpackage to publish the .dacpac using that token: sqlpackage /a:Publish /at:"$(TOKEN)" /sf:"my.dacpac" /tcs:"Server=tcp:my-endpoint.datawarehouse.pbidedicated.windows.net..."

Failing SQL Command:

The SQL script that is failing is a simple GRANT statement included in the .dacpac, for example:

GRANT SELECT ON SCHEMA:: SILVER TO [OUR_GROUP_NAME];
GO

We tried to give 'Directory reader' permissions to the SP that is logging into azure (as suggested in the error log), but the same error still appears.

Could you please advise on the correct procedure on how to work with sqlpackage for 'grant' automation?

Thank you.

[v-nmadadi-msft]

Hi @Andersenn 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[v-nmadadi-msft]

Hi @Andersenn 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[Andersenn]

The information has been reviewed, but the issue remains unresolved

[v-nmadadi-msft · Accepted solution]

Hi  @Andersenn ,
Thanks for reaching out to the Microsoft fabric community forum.

As you are having issues with Grant part please check out this point on SQL Security.

SQL security features must be exported/migrated using a script-based approach. Consider using a post-deployment script in a SQL database project, which you can configure by opening the project with the SQL Database Projects extension available in Visual Studio Code.
Source: Source Control with Fabric Data Warehouse (Preview) - Microsoft Fabric | Microsoft Learn

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[svenchio]

Hey @Andersenn  this is such an interesting question and close to my heart as I worked extensively on this, so much so that I wrote a series in my blog around this topic,  SQL Meets Fabric (Series) , but related to your issue is the topic I presented on  SQL Bits 2025: SQL Meets Fabric: Migrate and version-control your SQL workloads   I used a pipeline and a slighly different autentication method: 

                            Install-Module -Name MSAL.PS -Force -Scope CurrentUser
                            Import-Module MSAL.PS
                            $userAccount = "${{variables.sqlPackageAuthEntityId}}"
                            $targetPsw =  (Get-AzKeyVaultSecret -VaultName "${{variables.azKeyVaultName}}" -Name "${{variables.sqlPackageAuthEntityKVSecret}}" -AsPlainText)
                            Update-AzConfig -EnableLoginByWam $false
                            Write-Host "Calling Get-AzAccessToken to get the auth token for $($tokenResourceUrl)"
                            $result = Get-MsalToken -RedirectUri $uri -ClientId $userAccount -ClientSecret (ConvertTo-SecureString "$targetPsw" -AsPlainText -Force) -TenantId $tenantId -Scopes $tokenResourceUrl
                            $accessToken = $result.AccessToken                        
                            # Execute SqlPackage with the access token
                            & "$SqlPackagePath" /Action:Import /SourceFile:"$destinationPath" /TargetServerName:"$targetServer" /TargetDatabaseName:"$targetDatabase" /AccessToken:$accessToken

Review the code in the article, you'll see that I'm using a SrvPrincipal to authenticate towards the Server, but the thing is that this service principal is Admin of the workspace containing the SQL Database (in my case I deployed the Dacpac to a Fabric SQL Database) ... perhaps is not a direct answer to your question, as I need to test my code to match your conditions (e.g. using a Fabric Warehouse and using a post-deployment script with a GRANT SELECT ON SCHEMA) but perhaps this could get the conversation starting and giving some ideas and alternatives you could try!  Hope it helps ... we can keep in touch as I'm super interested in your issue, SQL is very close to my heart as a Data Engineer. 

[KevinChant]

This is a good solution for performing the deployments, but it looks like the failing issue itself is caused by the fact that the group name has not been added to the database before the GRANT command is executed.

Two easiest solutions are to either add the group to the database first or remove the grant statement from the database project in question.

I hope this helps. If so please either give this kudos or accept as the solution.

[Andersenn]

Hello all,

Thank you for the replies and helpful suggestions.

I should clarify a few constraints from my side which are making this difficult:

1. Workspace admin: Granting Workspace Admin permissions to our Service Principal is not an option. This is due to strict client security policies, and we must operate with the principle of least privilege.

2. Create User command: Regarding the suggestion to "add the group to the database first," is this explicitly supported in Fabric Warehouse? My understanding (and I'm not an expert) is that running "CREATE USER [GroupName] FROM EXTERNAL PROVIDER;" is not permitted here, unlike in a dedicated Azure SQL Database.

3. Grant Requirement: Removing the GRANT statements from the project is also not possible, as managing these permissions is a core requirement of our CI/CD process.

I really appreciate the help and ideas. I'm still stuck on the original error.

If I find a solution, I will definitely post it back here.

Thanks again.

[svenchio]

Hey @Andersenn  like wise, I'm contatnly researching and pushing the boundaries of Fabric  😉 and in particular, with its SQL ecosystem, as I said, it is of particular interest of mine. 

Yes, you are correct with  👇 

CREATE USER [GroupName] FROM EXTERNAL PROVIDER

 "... is not permitted here, unlike in a dedicated Azure SQL Database." 

I glad to help and at least brainstorm on this, and likewhise "If I find a solution, I will reach out to" ... all the best, if you can share a thumbs up for the help, that would be appreciated.