Automation with service principals

Service principals are an Azure Active Directory application resource you create within your tenant to perform unattended resource and service level operations. They're a unique type of user identity with an application ID and password or certificate. A service principal has only those permissions necessary to perform tasks defined by the roles and permissions for which it's assigned.

In Analysis Services, service principals are used with Azure Automation, PowerShell unattended mode, custom client applications, and web apps to automate common tasks. For example, provisioning servers, deploying models, data refresh, scale up/down, and pause/resume can all be automated by using service principals. Permissions are assigned to service principals through role membership, much like regular Azure AD UPN accounts.

Analysis Services also supports operations performed by managed identities using service principals. To learn more, see Managed identities for Azure resources and Azure services that support Azure AD authentication.

Create service principals

Service principals can be created in the Azure portal or by using PowerShell. To learn more, see:

Create service principal - Azure portal
Create service principal - PowerShell

Store credential and certificate assets in Azure Automation

Service principal credentials and certificates can be stored securely in Azure Automation for runbook operations. To learn more, see:

Credential assets in Azure Automation
Certificate assets in Azure Automation

Add service principals to server admin role

Before you can use a service principal for Analysis Services server management operations, you must add it to the server administrators role. Service principals must be added directly to the server administrator role. Adding a service principal to a security group, and then adding that security group to the server administrator role is not supported. To learn more, see Add a service principal to the server administrator role.

Service principals in connection strings

Service principal appID and password or certificate can be used in connection strings much the same as a UPN.

PowerShell

Note

This article has been updated to use the new Azure PowerShell Az module. You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. For Az module installation instructions, see Install Azure PowerShell.

Using Az.AnalysisServices module

When using a service principal for resource management operations with the Az.AnalysisServices module, use Connect-AzAccount cmdlet.

In the following example, appID and a password are used to perform control plane operations for synchronization to read-only replicas and scale up/out:

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

# Connect using Az module
Connect-AzAccount -Credential $Credential -SubscriptionId "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx"

# Syncronize a database for query scale out
Sync-AzAnalysisServicesInstance -Instance "asazure://westus.asazure.windows.net/testsvr" -Database "testdb"

# Scale up the server to an S1, set 2 read-only replicas, and remove the primary from the query pool. The new replicas will hydrate from the synchronized data.
Set-AzAnalysisServicesServer -Name "testsvr" -ResourceGroupName "testRG" -Sku "S1" -ReadonlyReplicaCount 2 -DefaultConnectionMode Readonly

Using SQLServer module

In the following example, appID and a password are used to perform a model database refresh operation:

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

Invoke-ProcessTable -Server "asazure://westcentralus.asazure.windows.net/myserver" -TableName "MyTable" -Database "MyDb" -RefreshType "Full" -ServicePrincipal -ApplicationId $AppId -TenantId $TenantId -Credential $Credential

AMO and ADOMD

When connecting with client applications and web apps, AMO and ADOMD client libraries version 15.0.2 and higher installable packages from NuGet support service principals in connection strings using the following syntax: app:AppID and password or cert:thumbprint.

In the following example, appID and a password are used to perform a model database refresh operation:

string appId = "xxx";
string authKey = "yyy";
string connString = $"Provider=MSOLAP;Data Source=asazure://westus.asazure.windows.net/<servername>;User ID=app:{appId};Password={authKey};";
Server server = new Server();
server.Connect(connString);
Database db = server.Databases.FindByName("adventureworks");
Table tbl = db.Model.Tables.Find("DimDate");
tbl.RequestRefresh(RefreshType.Full);
db.Model.SaveChanges();

Next steps

Sign in with Azure PowerShell
Refresh with Logic Apps
Refresh with Azure Automation
Add a service principal to the server administrator role
Automate Power BI Premium workspace and dataset tasks with service principals