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.
As the source in the connection string is having asazure so I assume that it's an Azure Analysis server so I would recommend you to import SqlServer module in your Azure Automation account as explained here and then use Invoke-ASCmd cmdlet in your runbook.
Related references: