Automate analysis services cube refresh using service principal

Swathi Sudheer 1

We are using azure automation account to refresh our cubes but when we view the query sessions we see the queries using our login id's in the database. We want to use the service principal account to refresh the cube via azure automation account. Is there a way to override the credentials used to publish the cubes using power shell commands or scripts or do we have to do this manually by publishing the cube with the service principal account? Also, do we need to create a database login for the service principal account so it can be used to process and publish the cube?

We are using this command in our daily refresh runbook -
Invoke-ProcessASDatabase -DatabaseName

Nandan Hegde 29,896 Reputation points MVP

2022-09-06T16:11:56.18+00:00

Hey,
Can you please clarify your ask?
Is it to leverage service principal to establish the connection with the Azure SQL database within AAS?
Can you provide the screenshot of the current connection(scrubbing sensitive data)
Prateek Vermani 1 Reputation point

2022-09-06T18:07:15.92+00:00

Hello Nandan,

A part of this is similar to what you mentioned. We do need to know whether we need to create a login for service principal within the AAS database or only giving the contributor access to the resource should be sufficient.
Also, we want to know whether there is a via scripting to override the existing login details in the cube in Azure with the help of runbooks. We are currently publishing the cube using our credentials via visual studio.

1 answer

Nandan Hegde 29,896 Reputation points MVP

2022-09-07T13:34:44.213+00:00

Hey
For any deployment/updates over AAS, we need to provide admin access on server to a service principal

You can update the datasource details by executing TMSL scripts on the basis of powershell
https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-ascmd?view=sqlserver-ps
Please sign in to rate this answer.
Prateek Vermani 1 Reputation point

2022-09-07T14:02:10.95+00:00

Hi,

Thanks for the answer. Yes sure we will do the same.
Let us go through the link and we will revert if this solution works for us.

Nandan Hegde 29,896 Reputation points MVP

2022-09-07T14:29:24.607+00:00

Data Source update:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/07dcf89c-0a6f-4194-ad15-8e9c6f422504/updating-the-connection-details-in-azure-analysis-services-using-powershell?forum=AzureAnalysisServices

Prateek Vermani 1 Reputation point

2022-09-20T18:14:02.737+00:00

Hi Nandan,

We are using the same logic as mentioned in below link. Our Analysis service db is using the service principal for authentication but the source db from which we have pulled data into our cubes which we have authenticated using our own account when working with the cubes in visual studio code.

https://learn.microsoft.com/en-us/azure/analysis-services/analysis-services-refresh-azure-automation

Now when we check the session queries in the source db for every cube refresh it is showing sessions running using our id. We want to change this credential with a service principal with the help of powershell script for automating this authentication.
Any idea how to change the source authentication.
We are also attaching a screenshot of our BIM file which also shows we are using service principal connection for datasource but still the queries in source db are running using our id. The reason is when we authenticate the cube in visual studio we are using our microsoft account credentials.
Can we change credentials in visual studio also to authenticate source db using Service Principal ?
To change our source credentials and then refresh successfully do we have to first run "Invoke-ASCmd" and then run
Invoke-ProcessASDatabase -DatabaseName $Par1 ( our analysis service cube db) -RefreshType $Par2 -Server $Par3 ( our analysis server ) -Credential $_Cred1 -ServicePrincipal

Prateek Vermani 1 Reputation point

2022-09-20T18:15:28.633+00:00

Refresh BIM File :

{
"name": "zzzzz",
"compatibilityLevel": 1500,
"model": {
"culture": "en-US",
"dataSources": [
{
"type": "structured",
"name": "zzzzzzzzzzz",
"connectionDetails": {
"protocol": "tds",
"address": {
"server": "zzzzzzzzz",
"database": "zzzzzzzzz"
},
"authentication": null,
"query": null
},
"options": {},
"credential": {
"AuthenticationKind": "UsernamePassword",
"Username": "ServicePrincipalAccount",
"EncryptConnection": true,
"PrivacySetting": "Organizational"
}
}
Sign in to comment