automate object level tsql permissions in fabric data warehouse

[albosta]

Hello everyone,

I am trying to automate the management of object-level permisisons in a Fabric Warehouse as mentioned in the article data-warehouse entra-id-authentication (the scenario Assign a user or SPN to a specific item)

Context: Our Project has a central Warehouse for reporting, and we want to allow different departments to build their own reports based on data from the Warehouse. We have foreseen different sql schemas per department and we expose data to reports through views. i.e. data required by procurement will be exposed in views in a 'procurement' schema.

each department then get a separate fabric Workspace with workspace identity enabled, to store their reports.

At the moment, in order to allow the identities of the reporting workspaces to read data only from their dedicated schema in the Warehouse we:

- manually add the entra id identity as user in the warehouse, without granting any additional permissions as described in the article liked above

- manually grant select over the appropriate schema for the entra id identity (e.g. `GRANT SELECT ON SCHEMA::pbi_procurement TO [REP-Procurement]`)

This setup works fine in manual mode. But we are at a point where we now want to automate as many things as possible so that the entire solution is defined as code.

Unfortunately trying to run the SQL commands from an Azure DevOps pipeline ends up with an error: Invoke-Sqlcmd: Principal 'REP-Procurement' 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)'

Based on our experience with Azure SQL Databases and Azure Synapse SQL Pools, this error is typically raised because when the `create user xxx from external provider` command is submitted by a service principal (i.e. azure devops service connection) the entra id object ID of the UPN/SPN  is actually resolved by the SQL Server. Therefore the SQL server needs a managed identity, and it also needs GraphAPI Directory.Read permission.

However, granting Directory.Read permisison to the Managed Identity of the Fabric Workspace hosting the warehouse object does not solve the issue!

Does anyone know which 'server identity' is being used in such scenarios in Fabric?

[v-venuppu]

Hi @albosta ,

I hope the information provided is helpful.I wanted to check whether you were able to resolve the issue with the provided solutions.Please let us know if you need any further assistance.

Thank you.

[v-venuppu]

Hi @albosta ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-venuppu]

Hi @albosta ,

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[v-venuppu · Accepted solution]

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

Directory Readers Role in Microsoft Entra ID for Azure SQL - Azure SQL Database & Azure SQL Managed ...

Managed identity in Microsoft Entra for Azure SQL - Azure SQL Database & Azure SQL Managed Instance ...

Thank you.

[v-venuppu]

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 Learn

Thank you.

[albosta]

@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