Question 104392 in Azure Question posted in
The official documentation to get you started can be found here.

Running SQL script on Azure Synapse via Powershell or CLI

I am trying to give a service principal SELECT access on my Azure Synapse SQL data.

CREATE USER [MY_SERVICE_PRINCIPAL] FROM EXTERNAL PROVIDER WITH DEFAFULT_SCHEMA=[dbo]
GO
GRANT SELECT ON DATABASE :: MyDB TO [MY_SERVICE_PRINCIPAL];

This works fine, but it requires me logging into the workspace to do this for every single new service principal. Is it possible to automate this? I automate the creation of the service principal via Azure CLI. Is it possible to run this script from a

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    The best solution I found was to add a whole Azure AD group as a user on the database manually, then for each new user I'm creating, I automate their addition to the group with some basic Azure CLI commands on a DevOps pipeline rather than try with a SQL Script that adds them individually.


  2. 0

    CREATE AUTOMATION ACCOUNT

    1. Open Azure Portal and search for the Automation Account

    enter image description here
    Select Automation Account, and in another screen. Click on Create and fill in the required attributes.

    enter image description here

    1. After creating the Automation Account, the option of Runbook is in the left menu. by opening this, it has default/tutorial Runbooks of different types. To Automate the Process of Synapse Analytics, install some required modules in the Automation Account. In the left menu, find and click on the Modules, search for Az.Accounts and import this module to the Automation Account.

    enter image description here

    After this import, follow the same process to import Az.Synapse, another required module for this automation task.

    1. After importing the required modules, create a Runbook.

    enter image description here
    4. After clicking on Create a Runbook, an editor will be opened, paste the following code, save the Runbook, and publish it.

    [CmdletBinding()]

param (

   [Parameter(Mandatory=$true)]

   [string]$ResourceGroupName ="rg_ResourceGroup",

   [Parameter(Mandatory=$true)]

   [string]$WorkspaceName = "wp_WorkSpaceName",

   [Parameter(Mandatory=$true)]

   [string]$Operation = "op_Pause"

)

Begin    {

Write-Output "Connecting on $(Get-Date)"

#Connect to Azure using the Run As Account

Try{

$servicePrincipalConnection=Get-AutomationConnection -Name "AzureRunAsConnection"

Connect-AzAccount  -ServicePrincipal -TenantId $servicePrincipalConnection.TenantId -ApplicationId $servicePrincipalConnection.ApplicationId -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint

}

Catch {

if (!$servicePrincipalConnection){

$ErrorMessage = "Connection $connectionName not found."

throw $ErrorMessage

} else{

Write-Output -Message $_.Exception

throw $_.Exception

}

}

# Validation parameters

$ArrayOperations = "Pause","Start","Restart"

If ($Operation -notin $ArrayOperations)

{

Throw "Only Pause, Start, Restart Operations are valid"

}

# Start

Write-Output "Starting process on $(Get-Date)"

Try{

$Status = Get-AzSynapseSqlPool –ResourceGroupName $ResourceGroupName -WorkspaceName $WorkspaceName | Select-Object Status | Format-Table -HideTableHeaders | Out-String

$Status = $Status -replace "`t|`n|`r",""

Write-Output "The current status is "$Status.trim()" on $(Get-Date)"

}

Catch {

Write-Output $_.Exception

throw $_.Exception

}

# Start block

# Start

Write-Output "Starting $Operation on $(Get-Date)"

if(($Operation -eq "Start") -and ($Status.trim() -ne "Online")){

Write-Output "Starting $Operation Operation"

try

{

Write-Output "Starting on $(Get-Date)"

Get-AzSynapseSqlPool –ResourceGroupName $ResourceGroupName -WorkspaceName $WorkspaceName | Resume-AzSynapseSqlPool

}

catch

{

Write-Output "Error while executing "$Operation

}

}

# Pause block

if(($Operation -eq "Pause") -and ($Status.trim() -ne "Paused")){

write-Output "Starting $Operation Operation"

try

{

Write-Output "Pausing on $(Get-Date)"

Get-AzSynapseSqlPool –ResourceGroupName $ResourceGroupName -WorkspaceName $WorkspaceName | Suspend-AzSynapseSqlPool

}

catch

{

Write-Output "Error while executing "$Operation

}

}

# Restart block

if(($Operation -eq "Restart") -and ($Status.trim() -eq "Online")){

Write-Output "Starting $Operation Operation"

try

{

Write-Output "Pausing on $(Get-Date)"

Get-AzSynapseSqlPool –ResourceGroupName $ResourceGroupName -WorkspaceName $WorkspaceName | Suspend-AzSynapseSqlPool

Write-Output "Starting on $(Get-Date)"

Get-AzSynapseSqlPool –ResourceGroupName $ResourceGroupName -WorkspaceName $WorkspaceName | Resume-AzSynapseSqlPool

}

catch

{

Write-Output "Error while executing "$Operation

}

        }

}

End

{

# Exit

Write-Output "Finished process on $(Get-Date)"

}

    enter image description here

    1. After publishing, click on the start button and enter Synapse Analytics values, and a job will be created.

    2. there are various options on the job page like its Status, Errors, Exceptions, etc. After completing the job, the Synapse will Resume/Pause through this Runbook.

    3. Now add a schedule for Runbook to completely Automate the process on schedule. On the Runbook page, click on the link to Schedule button in the ribbon and add Schedule and Configure the required parameters.

    Login or Signup to reply.
  3. 0

    You can use the same tools to query dedicated or serverless pools, for example, invoke-sqlcmd with PowerShell, even sqlcmd. See also Connection strings for Synapse SQL.

    As for azure cli, see az synapse and for powershell, see Az.Synapse.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search