Forum Discussion

Andersenn's avatar
Andersenn
Frequent Visitor
10 months ago
Solved

Error trying to automate grants with sql package

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.

CICD
security

8 Replies

  • svenchio's avatar
    svenchio
    Super User
    10 months ago

    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's avatar
      KevinChant
      Super User
      10 months ago

      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's avatar
        Andersenn
        Frequent Visitor
        10 months ago

        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.

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    9 months ago

    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.

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    9 months ago

    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