Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
eddiehartman
Frequent Visitor

I was connecting just fine to Azure using OAuth2 but am now getting Authentication problems

To do this, I have a this function code for use to authenticate:

 let
    AzureToken = () =>
        let
            body = [
                grant_type = "client_credentials",
                client_id = {{AzureClientId}}{0}{0}, // literal value
                client_secret = {{AzureClientSecret}}{0}{0}, // literal value
                resource = "https://management.azure.com/"
            ],
            oathUrl = Text.Combine({"https://login.microsoftonline.com/", {{AzureTenantId}}{0}{0}, "/oauth2/token"}),
            httpBody = Uri.BuildQueryString(body),
            
            token = Json.Document(
                        Web.Contents(oathUrl, 
                            [
                                Headers=[
                                    ContentType="application/x-www-form-urlencoded",
                                    Accept="application/vnd.api+json"
                                ],
Content = Text.ToBinary(httpBody) ])) in token[access_token] in
AzureToken

This function can be invoked manually to request a token and it always works just fine using anonymous authentication.

 

However, I use this function in the actual data feed that returns (in the included example) usage costs. Although it was working previously with anonymous auth, it now refuses to. All I get is the error: We couldn't authenticate with the credentials provided. I have tried various authentication options, but to no avail 😞

 

I may have altered something to cause this, and have tried going back to earlier versions in my backup. No joy.

 

As things stand, I can save my attempts to fix the m code, but not apply changes to the Power BI project - which means it still returns the original feed without my later improvements.

 

Here is the code for the feed:

let 
    BaseUrl         = "https://management.azure.com/subscriptions/"
                        & {{AzureSubscriptionId}}{0}{0} // Newbie coding? This is how I got literal blank queries to work :)
                        & "/providers/Microsoft.Consumption/usageDetails"
                        & "?$expand=properties/additionalProperties"
                        & "&api-version=2021-10-01"
                        & "&" & filter,
    ApiKey           = AzureToken(), // function call
    EntitiesPerPage = 1000,

    GetJson = (Url) =>
          let Options = [
               Headers = [
                  #"accept" = "application/json",
                  #"Authorization" = "Bearer " & ApiKey
               ]
           ],

RawData = Web.Contents(Url, Options), Json = Json.Document(RawData) in Json, GetEntityCount = () => let Url = BaseUrl & "?$count=true&$top=0", Json = GetJson(Url), Count = Json[#"@odata.count"] in Count, GetPage = (Index as number) as list => let Skip = "?$skip=" & Text.From(Index * EntitiesPerPage), Top = "$top=" & Text.From(EntitiesPerPage), Url = BaseUrl & Skip, Json = GetJson(Url), Value = Json[#"value"] in Value, WaitAndRun = (seconds as number, Index as number) as list => let retVal = if (List.Count(List.Generate(() => DateTimeZone.LocalNow() + #duration(0,0,0,seconds), (x) => DateTimeZone.LocalNow() < x, (x) => x) ) = 0) then null else GetPage(Index) /**/ in retVal, EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
// I am using a literal value for testing since the real page count can be huge //RealPageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageCount = NumOfPages, // Generate list of numbers up to PageCount waitList = {0 .. (PageCount - 1)}, // Make all the requests Pages = List.Transform(waitList, each if Number.Mod(_ + 1, 5) = 0 then WaitAndRun(5, _) else GetPage(_) ), Entities = List.Union(Pages), TheTable = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(TheTable, "Column1", {"kind", "id", "name", "type", "tags", "properties"}, {"Column1.kind", "Column1.id", "Column1.name", "Column1.type", "Column1.tags", "Column1.properties"}), BetterHeaders = ReplaceColumnsWithFunction(#"Expanded Column1", each Text.Replace(_, "Column1.", "")), #"Expanded tags" = Table.ExpandRecordColumn(BetterHeaders, "tags", {"Prosjektnr"}, {"tags.Prosjektnr"}), #"Expanded properties" = Table.ExpandRecordColumn(#"Expanded tags", "properties", {"additionalInfo", "billingAccountId", "billingAccountName", "billingCurrencyCode", "billingPeriodEndDate", "billingPeriodStartDate", "billingProfileId", "billingProfileName", "chargeType", "consumedService", "costInBillingCurrency", "costInPricingCurrency", "costCenter", "date", "exchangeRate", "exchangeRateDate", "instanceName", "invoiceId", "invoiceSectionId", "invoiceSectionName", "isAzureCreditEligible", "meterCategory", "meterId", "meterName", "meterRegion", "meterSubCategory", "previousInvoiceId", "pricingCurrencyCode", "product", "productIdentifier", "productOrderId", "productOrderName", "publisherName", "publisherType", "quantity", "resourceGroup", "resourceLocation", "resourceLocationNormalized", "serviceFamily", "serviceInfo1", "serviceInfo2", "servicePeriodEndDate", "servicePeriodStartDate", "subscriptionGuid", "subscriptionName", "unitOfMeasure", "unitPrice", "customerTenantId", "customerName", "partnerTenantId", "partnerName", "resellerMpnId", "resellerName", "publisherId", "reservationId", "reservationName", "frequency", "term", "payGPrice", "costInUSD", "paygCostInBillingCurrency", "paygCostInUSD", "exchangeRatePricingToBilling", "partnerEarnedCreditRate", "partnerEarnedCreditApplied"}, {"properties.additionalInfo", "properties.billingAccountId", "properties.billingAccountName", "properties.billingCurrencyCode", "properties.billingPeriodEndDate", "properties.billingPeriodStartDate", "properties.billingProfileId", "properties.billingProfileName", "properties.chargeType", "properties.consumedService", "properties.costInBillingCurrency", "properties.costInPricingCurrency", "properties.costCenter", "properties.date", "properties.exchangeRate", "properties.exchangeRateDate", "properties.instanceName", "properties.invoiceId", "properties.invoiceSectionId", "properties.invoiceSectionName", "properties.isAzureCreditEligible", "properties.meterCategory", "properties.meterId", "properties.meterName", "properties.meterRegion", "properties.meterSubCategory", "properties.previousInvoiceId", "properties.pricingCurrencyCode", "properties.product", "properties.productIdentifier", "properties.productOrderId", "properties.productOrderName", "properties.publisherName", "properties.publisherType", "properties.quantity", "properties.resourceGroup", "properties.resourceLocation", "properties.resourceLocationNormalized", "properties.serviceFamily", "properties.serviceInfo1", "properties.serviceInfo2", "properties.servicePeriodEndDate", "properties.servicePeriodStartDate", "properties.subscriptionGuid", "properties.subscriptionName", "properties.unitOfMeasure", "properties.unitPrice", "properties.customerTenantId", "properties.customerName", "properties.partnerTenantId", "properties.partnerName", "properties.resellerMpnId", "properties.resellerName", "properties.publisherId", "properties.reservationId", "properties.reservationName", "properties.frequency", "properties.term", "properties.payGPrice", "properties.costInUSD", "properties.paygCostInBillingCurrency", "properties.paygCostInUSD", "properties.exchangeRatePricingToBilling", "properties.partnerEarnedCreditRate", "properties.partnerEarnedCreditApplied"}), removedTagsInName = ReplaceColumnsWithFunction(#"Expanded properties", each Text.Replace(_, "tags.", "")), betterColumnNames = ReplaceColumnsWithFunction(removedTagsInName, each Text.Replace(_, "properties.", "")), expensesTable = Table.Sort(betterColumnNames,{{"date", Order.Descending}}) in expensesTable

Hoping someone can help me get this working again! 😄

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @eddiehartman ,

 

Try clearing the cache and updating your credentials.

 

 

 

Best Regards,

Stephen Tao

 

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

View solution in original post

1 REPLY 1
v-stephen-msft
Community Support
Community Support

Hi @eddiehartman ,

 

Try clearing the cache and updating your credentials.

 

 

 

Best Regards,

Stephen Tao

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors