I am completely new to xmla and attempting to use the 19.22.0.1 version of Microsoft.AnalysisServices.SdomdClient.NetCore.retail.amd64 to connect to a dataset in a Powerbi premium workspace. I lifted the connection string from the excel file generated by the analyze in excel option but attempting to use it results in a the following AdomdConnectionException. Updating the connection string based upon the documentation guidance results in the same beahvior. The excel spreadsheet works fine which I believe means the xmla endpoints settings are set appropriately. I am able to access the dataset and linked reports via app.powerbi.com which suggest that I have the necessary permissions.
var tenantId = "<AAD Tenant Guid>";
var credential = new DefaultAzureCredential(new DefaultAzureCredentialOptions
{
VisualStudioTenantId = tenantId,
VisualStudioCodeTenantId = tenantId,
SharedTokenCacheTenantId = tenantId,
InteractiveBrowserTenantId = tenantId,
});
var token = await credential.GetTokenAsync(new TokenRequestContext(scopes: new[] { "https://analysis.windows.net/powerbi/api" }));
// https://learn.microsoft.com/en-us/analysis-services/instances/connection-string-properties-analysis-services?view=asallproducts-allversions
var connectionString = @$"Provider=MSOLAP.8;Integrated Security=ClaimsToken;Persist Security Info=True;Initial Catalog=<DataSet Name>;Data Source=powerbi://api.powerbi.com/v1.0/myorg/<workspace>;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Password={token.Token}";
Exception 1 - bad schema in the URL
Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException: The remote server returned an error: (401) Unauthorized. ---> System.Net.WebException: The remote server returned an error: (401) Unauthorized. at System.Net.HttpWebRequest.GetResponse() at Microsoft.AnalysisServices.AdomdClient.ASAzureUtility.PutHttpJsonData[TRequest,TResult](Uri uri, Dictionary`2 headers, TRequest requestObject, Int32 timeoutMs)
at Microsoft.AnalysisServices.AdomdClient.ASAzureUtility.ResolvePowerBICluster(Uri discoverUrl, String token, TimeLeft timeLeft, AsInstanceType asInstanceType) --- End of inner exception stack trace --- at Microsoft.AnalysisServices.AdomdClient.ASAzureUtility.ThrowConnectionException(WebException ex, AsInstanceType asInstanceType) at Microsoft.AnalysisServices.AdomdClient.ASAzureUtility.ResolvePowerBICluster(Uri discoverUrl, String token, TimeLeft timeLeft, AsInstanceType asInstanceType) at Microsoft.AnalysisServices.AdomdClient.ConnectionInfo.ValidateAndSetPbiDatasourceAndLocation() at Microsoft.AnalysisServices.AdomdClient.ConnectionInfo.HandleAsAzureconnections() at Microsoft.AnalysisServices.AdomdClient.ConnectionInfo.ValidateAndCompleteConnectionConfiguration() at Microsoft.AnalysisServices.AdomdClient.ConnectionInfo.SetConnectionString(String cs) at Microsoft.AnalysisServices.AdomdClient.ConnectionInfo..ctor(String connectionString) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.set_ConnectionString(String value) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.set_ConnectionString(String value) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection..ctor(String connectionString)
Update #1
A couple of tweaks to get further.
- the connection string I got from excel used the schema
azurebiinstead ofpowerbi - shifted the token scope to
https://analysis.windows.net/powebi/api
This allows the AdomdConnection to be instantiated but now when attempting to Open() the connection it throws exception #2 below.
Exception 2
Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException: The remote server returned an error: (401) Unauthorized.
Technical Details: RootActivityId: 025c6866-1776-48e0-a43f-677cdde76a20 Date (UTC): 7/8/2021 11:36:14 PM ---> System.Net.WebException: The remote server returned an error: (401) Unauthorized. at System.Net.HttpWebRequest.GetResponse()
at Microsoft.AnalysisServices.AdomdClient.HttpRequestResponseExtensions.GetResponseWithErrorHandling(HttpWebRequest httpWebRequest, Boolean isPbiPremium) at Microsoft.AnalysisServices.AdomdClient.HttpStream.WaitForAndUpdateHttpResponseContainingXmlaPayload() at Microsoft.AnalysisServices.AdomdClient.HttpStream.GetResponseStream() --- End of inner exception stack trace --- at Microsoft.AnalysisServices.AdomdClient.ASAzureUtility.ThrowConnectionException(WebException ex, AsInstanceType asInstanceType) at Microsoft.AnalysisServices.AdomdClient.HttpStream.GetResponseStream() at Microsoft.AnalysisServices.AdomdClient.HttpStream.GetResponseDataType() at Microsoft.AnalysisServices.AdomdClient.CompressedStream.GetResponseDataType() at Microsoft.AnalysisServices.AdomdClient.XmlaClient.EndRequest(Boolean useBinaryXml) at Microsoft.AnalysisServices.AdomdClient.XmlaClient.EndRequest() at Microsoft.AnalysisServices.AdomdClient.XmlaClient.CreateSession(ListDictionary properties, Boolean sendNamespaceCompatibility, String sessionToken)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.CreateSession(ListDictionary properties, Boolean sendNamespaceCompatibility) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.CreateSession(Boolean sendNamespaceCompatibility) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()
