Automate pause/resume Synapse Workspace (Preview) SQL Pool with Powershell

Question

Automate pause/resume Synapse Workspace (Preview) SQL Pool with Powershell

Jim Mauck 106

I have a Synapse Workspace with SQL Pool that I would like to automate resume and suspend with PowerShell. I am following this MS doc as a guide: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/pause-and-resume-compute-powershell

I am using this command after authenticating and setting proper subscription context:

Suspend-AzSqlDatabase –ResourceGroupName "workspacemanagedrg-********-****-****-****-************" –ServerName "syndatawarehouse" –DatabaseName "sqlpooldw"

(The only way I can get this command to work is by referencing the SQL Pool with managed resource group name as the ResourceGroupName parameter. The SQL Pool is not found when referencing it using the primary resource group name.)

With this, the suspend command is able to reference my SQL Pool but I get this error message:

Suspend-AzSqlDatabase: DenyAssignmentAuthorizationFailed: The client '****@******.onmicrosoft.com' with object id '********-****-****-****-************' has permission to perform action 'Microsoft.Sql/servers/databases/pause/action' on scope '/subscriptions/********-****-****-****-************/resourceGroups/workspacemanagedrg-********-****-****-****-************/providers/Microsoft.Sql/servers/syndatawarehouse/databases/sqlpooldw/pause'; however, the access is denied because of the deny assignment with name '********-****-****-****-************' and Id '********************************' at scope '/subscriptions/********-****-****-****-************/resourceGroups/workspacemanagedrg-********-****-****-****-************'.

I can see the deny assignment is placed on the SQL Pool from the managed resource group through Azure Blueprint that was applied during SQL Pool creation.

Do I need to remove the deny assignment?
Or is there another way to reference the SQL Pool database to get around the deny assignment?

Thank you,
Jim

PRADEEPCHEEKATLA 90,651 Reputation points Moderator

2020-07-20T07:53:41.107+00:00

Hey @Jim Mauck ,

Deny assignments block users from performing specific Azure resource actions even if a role assignment grants them access.

Could you please remove the deny assignment and try to run the PowerShell cmdlet?
PRADEEPCHEEKATLA 90,651 Reputation points Moderator

2020-07-22T10:19:28.02+00:00

Hey @JimMauck-2192, Following up to see if the above suggestion was helpful. And, if you have any further query do let us know.
Jim Mauck 106 Reputation points

2020-07-22T14:00:53.467+00:00

Hi Pradeep,

Thanks for following up!

No, I am not able to remove the deny assignment. I cannot find any solid documentation on how to remove deny assignments from Azure Blueprints where I did not create the blueprint itself. I cannot find any where to assign myself RBAC privileges to work with this particular blueprint which contains the deny assignment. In fact, I cannot even read the blueprint itself.

Was the blueprint created by Microsoft Azure administrators that was automatically applied (per design) to my managed resource group upon creation? I ask this because the SQL Pool I created, named "sqlpooldw", within my Synapse workspace was automatically assigned to the managed resource group.

Regards,
Jim
PRADEEPCHEEKATLA 90,651 Reputation points Moderator

2020-07-27T05:39:25.133+00:00
Hello @Jim Mauck ,
If it becomes necessary to modify or delete a resource protected by an assignment, there are two ways to do so.

Updating the blueprint assignment to a locking mode of Don't Lock

Delete the blueprint assignment

When the assignment is removed, the locks created by Azure Blueprints are removed. However, the resource is left behind and would need to be deleted through normal means.

Reference: Understand resource locking in Azure Blueprints

Hope this helps. Do let us know if you any further queries.

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

Do click on "Accept Answer" and Upvote on the post that helps you, this can be beneficial to other community members.

Accepted answer

0 additional answers

Your answer

PRADEEPCHEEKATLA 90,651 Reputation points Moderator

2020-07-20T07:53:41.107+00:00

Hey @Jim Mauck ,

Deny assignments block users from performing specific Azure resource actions even if a role assignment grants them access.

Could you please remove the deny assignment and try to run the PowerShell cmdlet?
PRADEEPCHEEKATLA 90,651 Reputation points Moderator

2020-07-22T10:19:28.02+00:00

Hey @JimMauck-2192, Following up to see if the above suggestion was helpful. And, if you have any further query do let us know.
Jim Mauck 106 Reputation points

2020-07-22T14:00:53.467+00:00

Hi Pradeep,

Thanks for following up!

No, I am not able to remove the deny assignment. I cannot find any solid documentation on how to remove deny assignments from Azure Blueprints where I did not create the blueprint itself. I cannot find any where to assign myself RBAC privileges to work with this particular blueprint which contains the deny assignment. In fact, I cannot even read the blueprint itself.

Was the blueprint created by Microsoft Azure administrators that was automatically applied (per design) to my managed resource group upon creation? I ask this because the SQL Pool I created, named "sqlpooldw", within my Synapse workspace was automatically assigned to the managed resource group.

Regards,
Jim
PRADEEPCHEEKATLA 90,651 Reputation points Moderator

2020-07-27T05:39:25.133+00:00

Hello @Jim Mauck ,
If it becomes necessary to modify or delete a resource protected by an assignment, there are two ways to do so.

Updating the blueprint assignment to a locking mode of Don't Lock

Delete the blueprint assignment

When the assignment is removed, the locks created by Azure Blueprints are removed. However, the resource is left behind and would need to be deleted through normal means.

Reference: Understand resource locking in Azure Blueprints

Hope this helps. Do let us know if you any further queries.

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

Do click on "Accept Answer" and Upvote on the post that helps you, this can be beneficial to other community members.

Answer 1

Jim Mauck 106

Hi Pradeep,

I figured it out I was using wrong product documentation set. I was following "Azure Synapse Analytics (formerly SQL DW)".
I needed to follow "Azure Synapse Analytics (workspaces preview)". They have completely different set of commands. This happened easily because searching for "pause resume Synapse Analytics with PowerShell" in MS documentation sites finds the formerly SQL DW and not the workspaces preview in the results.

Going back through the workspaces preview documentation again I noticed that distinction and the PowerShell command Update-AzSynapseSqlPool.

So, for my instance, these commands work in PowerShell:

Update-AzSynapseSqlPool -WorkspaceName syndatawarehouse -Name sqlpooldw -Resume
Update-AzSynapseSqlPool -WorkspaceName syndatawarehouse -Name sqlpooldw -Suspend

I am also able the schedule these as PowerShell runbooks in Azure Automation.

Regards,
Jim

Jim Mauck 106 Reputation points

2020-07-27T19:12:48.877+00:00

I want to emphasize this was my fault due to not paying enough attention to the differences between SQL DW and Workspaces.
PRADEEPCHEEKATLA 90,651 Reputation points Moderator

2020-07-29T05:09:28.603+00:00

Hi @Jim Mauck ,
Glad to know that your issue has resolved. And thanks for sharing the solution, which might be beneficial to other community members reading this thread.
Hassan Rahamathullah 6 Reputation points

2021-08-29T07:51:58.88+00:00
Hi everyone, now the document has been updated. Please refer the commands here: https://learn.microsoft.com/en-us/powershell/module/az.synapse/?view=azps-6.3.0#synapse-analytics
Below are the updated command to resume/pause synapse workspace:

Suspend-AzSynapseSqlPool -WorkspaceName $servername -Name $databasename Resume-AzSynapseSqlPool -WorkspaceName $servername -Name $databasename

Anyway thanks for sharing the solution @Jim Mauck .

Share via

Automate pause/resume Synapse Workspace (Preview) SQL Pool with Powershell

0 additional answers

Your answer