Fabric Warehouse - Service Principal permissions on Data warehouse

[arnefromaus]

Good morning,

We have a design where we have an existing orchestration framework on-premise, and starting to look at migrating our workloads to Fabric. Part of our roadmap is to still keep orchestration in the on-premise system until our POC has been proven.

For our requirement now, we need to execute Stored Procedures on the Fabric Datawarehouse.

We are using a Service Pricniple to connect to the Fabric Datawarehouse and execute the Stored Procedure.

All is working as expected, however - we want to trim down the permissions or access the service principle has on the data warehouse, but can't find a way to do this.

I've used the following, but got this error :

 GRANT EXECUTE ON [staging].[myStoredProc] TO [1111-SERVICE-PRINCIPLECLIENTID-1111]

Error:Principal '1111-SERVICE-PRINCIPLECLIENTID-1111' could not be found or this principal type is not supported.

I don't know if there is another way to assign specific permissions to the SERVICE PRINCIPLE.

[arnefromaus · Accepted solution]

Thank you for the response. 

I met up with the Fabric team, and they sorted me out.  To set permissions like this, one needs to use the service principle name, for example 'fabric-poc' instead of the Service Principle ID, or the name that appears when running USER_NAME() .(GUID)

A simple solution, however for some reason I did not test this during my various methods I tried.

[v-cboorla-msft]

Hi @arnefromaus 

Thanks for using Microsoft Fabric Community.

Managing permissions for a Service Principal in Microsoft Fabric Data Warehouse can be crucial for security and access control.

For more details please refer to the below documentations:

Link 1 : GRANT (Transact-SQL) - SQL Server | Microsoft Learn

Link 2 : Grant Permissions on a Stored Procedure

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

Thank you.

[arnefromaus]

Thanks for the feedback. 

I am quite confident I have my GRANT statement correct, but have trouble getting the reference to the Entra ID Service Principle correct.   

What "USER_NAME()" do I user to refer to the service principle ?  

Currently I use the "Service Principle Client ID" as copied out of Entra ID as the User_NAME () (also got this when connecting to the DW using SSMS and using USER_NAME())

Currently getting the error message below : 

Principal 'a873f4e8-****-4fb7-a2a5-73256c0cf2d8@37c9a82a-****-4420-****-****cf33c594' 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)

[v-cboorla-msft]

Hi @arnefromaus 

Apologies for the inconvenience.

Please reach out to our support team to gain deeper insights and explore potential solutions. It's highly recommended that you reach out to our support team. Their expertise will be invaluable in suggesting the most appropriate approach.

Please go ahead and raise a support ticket to reach our support team:

https://support.fabric.microsoft.com/support

After creating a Support ticket please provide the ticket number as it would help us to track for more information.

Thank you.