Pause/Resume Dedicated SQL pool using Power Shell

Question

Hi Team, Need help in identifying the issue with below script. I am trying to Pause the Synapse services using below code but getting error as " Logging in to Azure...Connection not found." However, I am able to pause the services if I manually Login to the Azure account by signing in through email id.

try
{

"Logging in to Azure..."
Connect-AzAccount `
    -ServicePrincipal `
    -TenantId $servicePrincipalConnection.TenantId `
    -ApplicationId $servicePrincipalConnection.ApplicationId `
    -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint 

}

catch {
if (!$servicePrincipalConnection)
{
$ErrorMessage = "Connection not found."
write-output $ErrorMessage
} else{
Write-Error -Message $.Exception
throw $.Exception
}
}

$database = Get-AzSqlDatabase ResourceGroupName "ABCD" ServerName "XYZ" DatabaseName "BCB"
if($database){

if($database.Status -eq 'online'){
    $database | Suspend-AzSqlDatabase
    Write-Output "The Data Warehouse was Active and paused now."
}else{
    Write-Output "The Data Warehouse has already been paused." 
}

}else{

Write-Output "The Data Warehouse does not exist."

}

Answer 1

Thank you for getting back!
Yes I have not created/supplied the ServicePrincipalConnection.
I am new to this Azure automation platform. So, wanted to know what are the mandatory attributes required to execute the Suspend-AzSqlDatabase command through automated way. I wanted to achieve this only through Power Shell without creating any Automation account in Azure portal.

One more detail : I am able to connect Azure account using Identity method, not sure how to execute the above commands post that.

Thank you!

Answer 2

Thanks again for the details.

I executed these steps in PowerShell 6.0.2

Step 1: Connect-AzAccount -Identity - (Able to connect with
Step 2: Executed Suspend-AzSqlDatabase -ResourceGroupName "ABCD" -ServerName "XYZ" -DatabaseName "BCB"

Getting error :

Suspend-AzSqlDatabase : AuthorizationFailed: The client 'xxxx' with object id 'xxx' does not have authorization to perform action 'Microsoft.Sql/servers/databases/
3b/resourceGroups/xxxx/providers/Microsoft.Sql/servers/yyyy/databases/vvv' or the scope is invalid. If access was recently granted, please refresh your credentials.

May be I will ask my Admin to run it if this is related to any access issue. However, the user I executed is having Contributor role though.

Thanks,

Answer 3

Hey I am really Sorry!, Caught up with few other things couldn't check this post.

I did not try this yet, but I installed the new module "AZ" in the Powershell 7.0X and it is throwing a different error like connection not found. I shall try to hard code the values and see if it works.

Thank you again.

Answer 4

@Puneet, Mithun , thank you for reaching out to us. Since you are getting error message as "Logging in to Azure...Connection not found." from your script, I suspect that value of $servicePrincipalConnection is NULL. You can try connecting using a service principal account OR a Managed Service Identity OR Managed Service Identity login and ClientId OR certificate file depending on the host you are using. Further, to login without pop-up, using Service Principal, please follow the accepted answer of this question on Stack Overflow. This provides a good and detailed answer about the use case.

You can find all the parameters and their definitions/descriptions in the corresponding links.

Further, to execute the Suspend-AzSqlDatabase to suspend a SQL Data Warehouse database, you can use below cmdlet in your script:

 Suspend-AzSqlDatabase -ResourceGroupName "ABCD" -ServerName "XYZ" -DatabaseName "BCB"  

But, there is another way to suspend which retrieves the database into the $database object. It then pipes the object to Suspend-AzSqlDatabase. The results are stored in the object resultDatabase. The final command shows the results.:

$database = Get-AzSqlDatabase –ResourceGroupName "myResourceGroup" `  
–ServerName "sqlpoolservername" –DatabaseName "mySampleDataWarehouse"  
$resultDatabase = $database | Suspend-AzSqlDatabase  
$resultDatabase  

So, to sum it up, you can use below example for your use case:

try  
{  
  
 "Logging in to Azure..."  
 # Write your login script here.   
}   
catch {  
# Write your error handling script here.  
}  
  
$database = Get-AzSqlDatabase ResourceGroupName "ABCD" ServerName "XYZ" DatabaseName "BCB"  
if($database){  
 if($database.Status -eq 'Online'){  
     $database | Suspend-AzSqlDatabase  
     Write-Output "The Data Warehouse was Active and paused now."  
 }else{  
     Write-Output "The Data Warehouse has already been paused."   
 }  
}else{  
 Write-Output "The Data Warehouse does not exist."  
}  

Pls let me know if you have any follow-up questions.

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

Please "Accept the answer" and upvote if the information helped you. This will help us and others in the community as well.