Forum Discussion
automate object level tsql permissions in fabric data warehouse
Hi albosta ,
It’s failing because when you try to give a user or service principal access inside Fabric Warehouse, the Warehouse itself needs to look up that account in Entra ID. It does this using its own Managed Identity, not the service principal you’re calling from DevOps.
If that Managed Identity doesn’t have permission to read Entra ID, the lookup fails, and you get the “Principal could not be resolved” error.
You can try fixing it with below steps:
Turn on the Managed Identity for the Workspace that has your Warehouse.In Entra ID, give that Managed Identity the Directory Readers role.Also give it Microsoft Graph – Directory.Read.All (Application permission) and grant admin consent.Wait 15–20 minutes for the permissions to take effect.
Run your ALTER ROLE ... ADD MEMBER ... from DevOps again - no need to manually create the user, Fabric will do it automatically.
Points to remember:
The Warehouse’s Managed Identity is the “middleman” that talks to Entra ID.Your DevOps service principal just needs permission to run SQL - the lookup permissions belong to the Warehouse’s identity.
Here are some Microsoft Documentations for your reference:
Workspace identity - Microsoft Fabric | Microsoft Learn
Thank you.
7 Replies
v-venuppuCommunity Support
Hi albosta ,
Thank you for reaching out to Microsoft Fabric Community.
To automate object-level T-SQL permissions in Fabric Warehouse, use GRANT SELECT ON SCHEMA::schema_name TO [workspace_identity] - Fabric auto-creates the user. Avoid using CREATE USER, as it's not supported. Ensure the Fabric workspace identity is assigned to the warehouse and has Directory Reader permission in Entra ID. Run SQL via a principal that can resolve Entra objects - typically the workspace identity, not the DevOps service principal. If using Invoke-Sqlcmd in pipelines, make sure the identity has Graph API access to avoid resolution errors. This setup supports schema-level isolation across departments.
You can go through the below Microsoft documentation for your reference:
SQL Granular Permissions - Microsoft Fabric | Microsoft LearnThank you.
v-venuppu thanks for getting back on this. Here are some screenshots about me setup
The workspace KDP-Dev has identity enabled and the app registration has directory.read permission granted
api permissions over the Workspace Managed Identity
In Workspace KDP-Dev I have a warehouse called 'kdp-dev-dwh' andf the SP used by DevOps is added as workspace admin, i.e. it will be allowed to run the necessary sql commands
DevOps SP is workspace admin
I use the code below to simulate running the sql command "alter role [role_read_meta] add member [kdp-dev-pyfunc]" from DevOps. where kdp-dev-pyfunc is the managed identity of a azure function app I want to give access over a specific schema in the Warehouse
Import-Module SqlServer $userContext = Get-AzContext $devopsSP = Get-AzADServicePrincipal -DisplayName "kdp-devops-sp-test" $warehouseObject = Get-KAzFabricWarehouse -WorkspaceName "KDP-Dev" -WarehouseName "kdp-dev-dwh" $tenant = $userContext.Tenant.Id $appKey = Read-Host -AsSecureString -Prompt "DevOps SP client secret" $appId = $devopsSP.AppId $Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $appId, $appKey $devopsContext = Connect-AzAccount -ServicePrincipal -TenantId $tenant -Credential $Credential -ContextName "DevOpsSP" $sqlArgs = @{ ServerInstance = $warehouseObject.properties.connectionString Database = $warehouseObject.displayName AccessToken = (Get-AzAccessToken -ResourceUrl "https://database.windows.net/" -DefaultProfile $devopsContext).Token } #NOTE: Adding user [kdp-dev-pyfunc] to the SQL role should create the user but instead it fails $sqlcmd = "alter role [role_read_meta] add member [kdp-dev-pyfunc]" Invoke-SqlCmd @sqlArgs -Query $sqlcmdI would expect the code above to run without any issues but instead I get the error
Invoke-Sqlcmd: Line | 8 | Invoke-SqlCmd @sqlArgs -Query $sqlcmd | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | Principal 'kdp-dev-pyfunc' 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)' Msg 33134, Level 16, State 72, Procedure , Line 1.If I run the SQL command with an access token generate with my own user account, then the users gets created and the role assigned.
$sqlArgs = @{ ServerInstance = $warehouseObject.properties.connectionString Database = $warehouseObject.displayName AccessToken = (Get-AzAccessToken -ResourceUrl "https://database.windows.net/" -DefaultProfile $userContext).Token } $sqlcmd = "alter role [role_read_meta] add member [kdp-dev-pyfunc]" Invoke-SqlCmd @sqlArgs -Query $sqlcmdSo, as far I can tell I am doing everything as per documentation
- v-venuppu
Hi albosta ,
It’s failing because when you try to give a user or service principal access inside Fabric Warehouse, the Warehouse itself needs to look up that account in Entra ID. It does this using its own Managed Identity, not the service principal you’re calling from DevOps.
If that Managed Identity doesn’t have permission to read Entra ID, the lookup fails, and you get the “Principal could not be resolved” error.
You can try fixing it with below steps:
Turn on the Managed Identity for the Workspace that has your Warehouse.In Entra ID, give that Managed Identity the Directory Readers role.Also give it Microsoft Graph – Directory.Read.All (Application permission) and grant admin consent.Wait 15–20 minutes for the permissions to take effect.
Run your ALTER ROLE ... ADD MEMBER ... from DevOps again - no need to manually create the user, Fabric will do it automatically.
Points to remember:
The Warehouse’s Managed Identity is the “middleman” that talks to Entra ID.Your DevOps service principal just needs permission to run SQL - the lookup permissions belong to the Warehouse’s identity.
Here are some Microsoft Documentations for your reference:
Workspace identity - Microsoft Fabric | Microsoft Learn
Thank you.
