Web Content Service Principle Authentication issue

[ThomasGebets]

Hi,

i'm trying to authenticate with a Azure Data Lake via M Query. I would do via the official connector but i didn't find a way to parametrize my credentials so i ended up here. 

I want to set the url/guids via parameters with as script in the Power BI Service (app.powerbi.com). I have these queries:

AccessToken as a function:

let
    GetAccessToken = () =>
    let
        tenantId = TenantID,
        tokenEndpoint = "https://login.microsoftonline.com/",

        // Create the body for the token request
        tokenBody = [
            client_id = ServicePrincipleClientID,
            client_secret = ServicePrincipleClientSecret,
            scope = "https://storage.azure.com/.default",
            grant_type = "client_credentials"
        ],

        tokenRequest = Web.Contents(tokenEndpoint, [
            Content = Text.ToBinary(Uri.BuildQueryString(tokenBody)),
            Headers = [#"Content-Type" = "application/x-www-form-urlencoded"],
            //Timeout=#duration(0, 0, 0, 2),
            RelativePath = TenantID & "/oauth2/v2.0/token"
        ]),

        source = tokenRequest,
        tokenSource = Json.Document(tokenRequest),
        result = tokenSource[access_token]
    in
        result
in
    GetAccessToken

Data Query:

let 
    accessToken = GetAccessToken,
    UtcTimeZone = DateTimeZone.UtcNow(),
    UtcTime = DateTimeZone.RemoveZone(UtcTimeZone),
    UtcTimeText = DateTime.ToText(UtcTime, [Format="r"]),
    headers = [
            #"x-ms-version" = "2023-08-03",
            #"x-ms-date" = UtcTimeText,
            #"Authorization" = "Bearer " & accessToken
        ],
    response = Web.Contents (DataLakeUrl, [
        Headers = headers,
        RelativePath = ConsumableStorage&ConsumableFileName
        ]),
    source = Text.FromBinary(response)
in 
    source

The thing is, the data will load and show in report and after refresh locally, it won't show any errors. When i upload those reports to the Power BI Service, it won't connect an throw this error: 

"400): Value for one of the query parameters specified in the request URI is invalid." 

On refresh in PBI Service, i get this error: [Unable to combine data] Section1/DataSource/source references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.. The exception was raised by the IDbCommand interface.

The thing is, we use hierarchical namespace (dfs) and not blob, but i could access the files via Postman and also locally with my M Query.

I would be really thankful for any idea! 🙂 

[ThomasGebets]

Thanks, yes that helped! 

Then i noticed, that with updating the parameters directly, i would put the credentials / secrets in our sync git repository, which doesn't seem like good practice.

Is there a better way to handle dynamic data lake connections based on environment (dev/stag/prod) including credentials? 

As far as i understood, i can't use the PBI API and Update Datasources for that ("

• Only these data sources are supported: SQL Server, Azure SQL Server, Azure Analysis Services, Azure Synapse, OData, SharePoint, Teradata, and SAP HANA. For other data sources, use the Update Parameters API call.")

Do you have any ideas, how i could achieve that? Thanks for your help! 

[Anonymous]

Hi @ThomasGebets 

Based on the error message, it is about private problem, you can consider to set the private level to none, in data source settings in power bi desktop

Or you can set the privacy, click the option settings->Current file->privacy->"Ignore the privacy...'

Then save the report and republish it , then try to refresh the data source.

You can refer to the following link about private setting.

Understand Power BI Desktop privacy levels - Power BI | Microsoft Learn

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.