Synapse resource class for managed identity

Question

Synapse resource class for managed identity

Ljubo Jurkovic 66

Hi,
Is there a possibility to assign the resource class to the Synapse managed identity when it manipulates the dedicated SQL pool data through its pipelines? Since the managed identity is a member of the Synapse Administrator role by default and cannot be removed from there, it always uses the smallrc resource class and cannot be changed. It looks like the option is to use the SQL authentication (which I would rather not use if possible), but even so we would need to use Stored procedure activity from the General group rather than SQL pool stored procedure from the Synapse group and also Azure Synapse Analytics integration dataset instead of Azure Synapse dedicated SQL pool integration dataset. I thought the Synapse specific ones would be more performant than the general ones, but the inability to assign the resource class seems fairly big drawback.
Any suggestions?

Thanks

Brandon Duncan 0 Reputation points

2023-04-19T17:59:45.32+00:00

I was able to get this working by configuring a classifier on the resource workload group by specifying the role db_datawriter. For whatever reason, I could not get it to work with the managed identity name (synapse workspace name), or with a custom role. This solves my issue for now and allows me to dedicate more resources to my Synapse pipelines.

2 answers

Your answer

Brandon Duncan 0 Reputation points

2023-04-19T17:59:45.32+00:00

I was able to get this working by configuring a classifier on the resource workload group by specifying the role db_datawriter. For whatever reason, I could not get it to work with the managed identity name (synapse workspace name), or with a custom role. This solves my issue for now and allows me to dedicate more resources to my Synapse pipelines.

Answer 1

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

4 deleted comments

Comments have been turned off. Learn more

Answer 2

OlgaBo 6 Microsoft Employee

Here is what you should do in this situation. Since you use a stored procedure, you can specify wlm_context inside it EXEC sys.sp_set_session_context @Key = 'wlm_context', @value = 'etl' After that you need to create a classifier on workload management page of the dedicated sql pool. Make sure to use dbo member.

Note that we do not use user/password credentials and you can also use activities from Synapse group.

0 comments