question

PuneetMithun-9527 avatar image
0 Votes"
PuneetMithun-9527 asked singhh-msft commented

Pause/Resume Dedicated SQL pool using Power Shell

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."

}

azure-automation
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@PuneetMithun-9527, thank you for reaching out to us. Since you are getting error message as "Logging in to Azure...Connection not found.", I suspect that value of $servicePrincipalConnection is NULL. Please share how you are creating $servicePrincipalConnection in the script to help you further. You can also try connecting using Certificate file:

 $securePassword = $plainPassword | ConvertTo-SecureString -AsPlainText -Force
 $TenantId = 'yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyy'
 $ApplicationId = 'zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzz'
 Connect-AzAccount -ServicePrincipal -ApplicationId $ApplicationId -TenantId $TenantId -CertificatePath './certificatefortest.pfx' -CertificatePassword $securePassword
    
 Account                     SubscriptionName TenantId                        Environment
 -------                     ---------------- --------                        -----------
 xxxxxxxx-xxxx-xxxx-xxxxxxxx Subscription1    yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyy AzureCloud


0 Votes 0 ·
PuneetMithun-9527 avatar image
0 Votes"
PuneetMithun-9527 answered singhh-msft commented

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!

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

singhh-msft avatar image
0 Votes"
singhh-msft answered singhh-msft edited

@PuneetMithun-9527, 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.










· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@PuneetMithun-9527 , just checking in to see if you got a chance to check my last response.

0 Votes 0 ·
PuneetMithun-9527 avatar image
0 Votes"
PuneetMithun-9527 answered singhh-msft commented

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,

· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@PuneetMithun-9527, you need to have Microsoft.Sql/servers/databases/pause/action on your database. I would recommend you to verify your permissions on the database once. Ideally, Contributor has permissions to create and manage resources of all types. Check out this to manage RBAC. Since, you mentioned Synapse services as well, I would recommend you to check out this thread around the same (in case you might face the same issue as the user).


0 Votes 0 ·

@PuneetMithun-9527 , just checking in to see if you got a chance to check my last response.

0 Votes 0 ·

Yes, thank you for the details again. I verified that it is not the user access issue. However, I am able to execute the Suspend-AzSqlDataBase command and it stops the synapse services.
I am able to achieve this by manually entering to https://microsoft.com/devicelogin and entering the code.
But through the PowerShell scripting unable to do this activity in automated way.


If you could help me with the below piece on what would be my login script? Looks like using the managed identity it is not helping me..

try
{

"Logging in to Azure..."
# Write your login script here.
}
catch {
# Write your error handling script here.
}


Thanks,

0 Votes 0 ·

@PuneetMithun-9527, Sure, no problem. Login without pop-up can be achieved using Service Principal. Please follow the accepted answer of this question on Stack Overflow. This provides a good and detailed answer about this use case.



0 Votes 0 ·

@PuneetMithun-9527 , just checking in to see if you got a chance to check my last response.

0 Votes 0 ·

@PuneetMithun-9527 , just checking in to see if you got a chance to check my last response.

0 Votes 0 ·
PuneetMithunManojKumarCognizant-4476 avatar image
0 Votes"
PuneetMithunManojKumarCognizant-4476 answered singhh-msft commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Sure, please take your time. But, don't forget to update this thread accordingly, with your observations.

0 Votes 0 ·