Synapse resource class for managed identity

Question

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

Answer 1

Hi @Ljubo Jurkovic ,

Welcome to Microsoft Q&A platform and thanks for posting your question here.

As per my understanding , you want to know if there is any way to create a user with largerc in dedicated SQL pool in Azure Synapse analytics. Please let me know if my understanding is incorrect.

Resource classes are implemented by assigning users to database roles. It cannot be assigned to managed identity, instead it's applicable to database user.

To create SQL Data Warehouse user with a specific resource class, you need to perform these steps :

• Create a server level login : CREATE LOGIN cloudsaxl WITH PASSWORD = ‘<use a strong password>’;
• Create a database level user : CREATE USER cloudsaxl FOR LOGIN cloudsaxl;
• Add the user to the resource class : EXEC sp_addrolemember 'xlargerc', 'cloudsaxl';
• [Optional] Grant permissions: EXEC sp_addrolemember 'db_owner', 'cloudsaxl';

Kindly check the following document for more details: Creating a SQL Data Warehouse user with a specific resource class

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Answer 2

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.