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.
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 $sqlcmd
I 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 $sqlcmd
So, as far I can tell I am doing everything as per documentation
