Automation of Azure Analysis Services with Service Principals and PowerShell

存储架构 2017-08-31 阅读原文

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 asAzure 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 Servicescmdlets 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 inSSMS.

app:@

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 Servicescmdlets 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 aboutcredential assets andcertificate assets in Azure Automation.

Microsoft Azure Blog

责编内容by:Microsoft Azure Blog阅读原文】。感谢您的支持!

您可能感兴趣的

Powershell Script – Backup SQL databases to ... Question:Do you have a powershell script to backup SQL databases on a single SQL Server Instance , and direct them to a...
Handling Permissions Changes With Powershell Drew Furgiuele has a process to store and then re-run rights grants on SQL Server databases : Permission requir...
Managing the VMware datastore with PowerCLI (Power... In this article, I will go over the basics of managing the VMware datastore with PowerCLI cmdlets.
Microsoft Revamps Approach to DevOps on Azure Microsoft this week unfurled a broad range of DevOps services for the Azure cloud that are intended to make it easier fo...
PowerShell脚本执行策略 Powershell默认是不允许执行PS1脚本的,这就需要我们自己使用 Set-ExecutionPolicy 来修改默认策略。 使用 Get-ExecutionPolicy 可以获取当前的策略: PS C:...