Automation of Azure Analysis Services with Service Principals and PowerShell
Posted on
2 min read
Azure Analysis Services presents opportunities for the automation of administrative tasks including server provisioning, scale up/down, pause/resume, model management, data refresh, deployment, among others. This can leverage cloud efficiencies and helps ensure the repeatability and reliability of mission-critical systems.
Such tasks can be performed in the Azure cloud using PowerShell in unattended mode. Services such as Azure Automation exist to support these processes. They should be executed using service principals for enhanced security and ease of management. Service principals are similar to on-premises service accounts, but for Azure. They use credentials in the form of an application ID along with a password or certificate. Model permissions are assigned to service principals through role membership like normal Azure Active Directory UPN accounts.
The remainder of this post shows how to create a service principal to use with the Analysis Services cmdlets available in the SqlServer PowerShell module.
Creation of service principals
Learn more about how to create service principals in the Azure Portal, and how to create service principals in PowerShell either with a password or certificate.
Role membership
Once the service principal is created, its application ID can be assigned permissions in the Azure Analysis Services server or model roles using the following syntax. The example below adds a service principal to the server administrators group in SSMS.
![clip_image002[5]](https://azurecomcdn.azureedge.net/mediahandler/acomblog/media/Default/blog/9bf2046c-d104-4523-9c3b-2c6bca6a0d2d.jpg "clip_image002[5]")
The application can be selected in SSMS using the account picker by searching for its name.
Execution of administrative tasks with PowerShell and service principals
A pre-requisite to this section is to install the latest Azure.AnalysisServices and SqlServer PowerShell modules.
Install-Module -Name Azure.AnalysisServices
Install-Module -Name SqlServer
The following example shows how to log in using a service principal application ID and password, and how to process (data refresh) a table in a model.
Param (
[Parameter(Mandatory=$true)] [String] $AppId,
[Parameter(Mandatory=$true)] [String] $PlainPWord,
[Parameter(Mandatory=$true)] [String] $TenantId
)
$PWord = ConvertTo-SecureString -String $PlainPWord -AsPlainText -Force
$Credential = New-Object -TypeName “System.Management.Automation.PSCredential” -ArgumentList $AppId, $PWord
Login-AzureAsAccount -Credential $Credential -ServicePrincipal -TenantId $TenantId -RolloutEnvironment “southcentralus.asazure.windows.net”
Invoke-ProcessTable -Server “asazure://southcentralus.asazure.windows.net/myserver” -TableName “MyTable” -Database “MyDb” -RefreshType “Full”
Note that the login cmdlet used is Login-AzureAsAccount, not Login-AzureRsAccount. The former should be used for Azure Analysis Services database-level operations such as those enabled by the Analysis Services cmdlets in the SqlServer PowerShell module. The latter should be used for Azure resource management operations such as those enabled by the AzureRM.AnalysisServices PowerShell module.
The following example shows how to log in using a service principal application ID and self-signed certificate, and how to process (data refresh) a table in a model.
Param (
[Parameter(Mandatory=$true)] [String] $AppId,
[Parameter(Mandatory=$true)] [String] $CertThumbprint,
[Parameter(Mandatory=$true)] [String] $TenantId
)
Login-AzureASAccount -RolloutEnvironment “southcentralus.asazure.windows.net” -ServicePrincipal -ApplicationId $AppId -CertificateThumbprint $CertThumbprint -TenantId $TenantId
Invoke-ProcessTable -Server “asazure://southcentralus.asazure.windows.net/myserver” -TableName “MyTable” -Database “MyDb” -RefreshType “Full”
Storing credentials and certificates in Azure Automation
Credentials and certificates can be securely stored in Azure Automation and extracted for use in runbooks. Learn more about credential assets and certificate assets in Azure Automation.