I am wanting to use an MSAL access token created for a Power BI access scope in order to query a Power BI dataset. The MSAL access token appears to be generated successfully. However, when I use the following connection string I fail to create a connection using OleDB. Any pointers would be greatly appreciated. It is worth noting that I can use interactive security in my successfully, but not via the App Reg.
// Function to resolve the service:
// ResolveCubeType => function()
public static string ResolveCubeType (
string cubeServerName
){
// Resolve the cube type:
string cubeType = cubeServerName.Contains("powerbi") ? "powerbi":"aas";
// Explicitly define the returned object:
// string => env
return cubeType;
}
// Function to resolve the resource uri:
// ResolveResourceUri => fucntion()
public static string ResolveResourceUri(
string cubeType,
string aasRegion
){
// Derive the resource uri using the cube Type:
string resourceUri = (
cubeType == "powerbi" ? "https://analysis.windows.net/powerbi/api":$"https://{aasRegion}.asazure.windows.net"
);
// Explicitly define the returned object: string scalar => env
return resourceUri;
}
// Function to resolve the authority uri:
// ResovleAuthorityUri => function()
public static string ResolveAuthorityUri(
string tenantId
){
// Derive the uri to log into microsoft: authorityUri => string scalar
string authorityUri = $"https://login.microsoftonline.com/{tenantId}";
// Explicitly define the returned object: string scalar => env
return authorityUri;
}
// Function to resolve authorisation uri:
// ResolveAuthorityUri => function()
public static string ResolveAccessScope(
string resourceUri
){
// Derive the authority uri: authorityUri => string scalar
string authorityUri = $"{resourceUri}/.default";
// Explicitly define the returned object: string scalar => env
return authorityUri;
}
// Function to generate access token:
// GenerateAccessToken => function()
public static async Task<string> GenerateAccessToken(
string accessScope,
string clientId,
string clientSecret,
string authorityUri,
string redirectUri
){
// Create a list of access authority uris
// to add to the access scopes:
// scopeList => list of string scalars
List<string> scopeList = new List<string>();
// Add the access scope to the list:
// scopeList => List of string scalars
scopeList.Add(accessScope);
// Instantiate am authentication request client object:
// confidentialClient => request client object
var confidentialClient = ConfidentialClientApplicationBuilder
.Create(clientId)
.WithClientSecret(clientSecret)
.WithAuthority(new Uri(authorityUri))
.WithRedirectUri(redirectUri)
.Build();
// Instantiate an accessTokenRequest object:
var accessTokenRequest = confidentialClient.AcquireTokenForClient(scopeList);
// Authenticate and store the result:
// authResult
var authResult = await accessTokenRequest.ExecuteAsync();
// Return the access token as string: string scalar => env
return authResult.AccessToken.ToString();
}
// Instantiate a Data Lake Service Client object:
// dataLakeServiceClient => DataLakeServiceClient object
// Derive the path to the Data Lake storage account
string adlsUri = $"https://{adlsStorageAccountName}.dfs.core.windows.net";
// Resolve the cube type from the cube sever string:
// cubeType => string scalar
string cubeType = ResolveCubeType(
cubeServer
);
// Resolve the cubeResourceUri:
// cubeResourceUri => string scalar
string cubeResourceUri = ResolveResourceUri(
cubeType,
"australiaeast"
);
// Resovle the access scope:
// accessScope => string scalar
string accessScope = ResolveAccessScope(
cubeResourceUri
);
// Resolve the authority uri:
// authorityUri => string scalar
string authorityUri = ResolveAuthorityUri(
tenantId.Value.ToString()
);
// Store the value of the redirectUri:
// redirectUri => string scalar
string redirectUri = "urn:ietf:wg:oauth:2.0:oob";
// Get and access token via MSAL: accessToken => string scalar
string accessToken = await GenerateAccessToken(
accessScope,
clientId.Value.ToString(),
clientSecret.Value.ToString(),
authorityUri,
redirectUri
);
string connectionString = $"Provider=MSOLAP.8;" +
$"Data Source={cubeServer};" +
"Update Isolation Level=2;" +
$"Initial Catalog={cubeName};" +
$"User ID=;" +
$"Password={accessToken};" +
$"Persist Security Info=True;" +
$"Impersonation Level=Impersonate";
//Create OLEDB connection: connection => OleDbConnection variable
using (var connection = new OleDbConnection(connectionString))
//using (var connection = new AdomdConnection(connectionString))
{
// Create a flag to be used for flow control: exceptionCaught => boolean scalar (default false)
bool exceptionCaught = false;
// Open the connection: connection => OleDbConnection variable
try
{
connection.Open();
}
// If an exception occurs:
catch(Exception e)
{
// Write out the error message to the console: string scalar => stdout(console)
Console.WriteLine("{0} Error:", e);
// Invert the flag value: exceptionCaught => string scalar
exceptionCaught = true;
// leave the function:
return "";
}
And the MSOLAP driver version:
Edit can't connect to workspace with powershell:
