How to execute a DMV query in SSAS through a powershell runbook from Azure Automation?

Question

I am trying to open a connection with a ssas server and execute a dmv query in order to extract table metadata, through a powershell runbook from an Azure Automation Account.

I have already written and tested a powershell script that seems to work fine on my local machine, but when I run the same script on the cloud as an azure runbook ** it seems **impossible to open a connection.

I used this code to open the connection with ssas:

$connectionString = "Provider=msolap;Data Source=asazure://westeurope.asazure.windows.net/servername;User Id={0};Password={1}; Initial Catalog=DataModel" -f $ssasUser, $ssasPassword;

## Connect to the data source and open SSAS
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$connection.Open()

I can't seem to be able to open a connection, as I run this code I receive the following error:

System.Management.Automation.MethodInvocationException: Exception calling "Open" with "0" argument(s): "The .Net Framework Data Providers require Microsoft Data Access Components(MDAC).  Please install Microsoft Data Access Components(MDAC) version 2.6 or later."

Searching on the web, the only solution I found seems to be to simply download and install the MDAC sdk but this can't be done while running on the cloud.

I need help to fix this error or find an alternative solution.
Thank you.

Answer 1

This script is aim to SSAS on-prem? It seems this need to be fixed from Azure automation side. It seems that not much can be done from SSAS or PowerShell perspectives.
You could try get help from Azure side to see if any other users have done such job. And I would suggest to start fixing and Automation settings and the $connection.Open() function.