Authorization for Defender API as a Data Source using a Web App

[JK2]

Hello,

I have a PowerBI dashboard that pulls data from the Microsoft Defender for Endpoint API. Unfortunately the access needs an elevated account so the refresh fails unless I activate my PIM role. I don't want a privileged account just for this dashboard so I have created an Entra app with the appropriate read access to access the API as described here: Use Microsoft Defender for Endpoint APIs

I am now at a loss as to how I authenticate using the app to access the Defender API in PowerBI. I have seen Powershell examples and there is a C# example in the Learn article but I cannot find the correct syntax for Power Query. Does anyone have an example where they have got this to work? I have tried the below with the Client Secret I generated in the app as the Access Token but get an error 'We couldn't authenticate with the credentials provided'.

let
        AccessToken = "your_access_token",
        ApiUrl = "https://api.securitycenter.microsoft.com/api/exposureScore/ByMachineGroups",
   
        Source =  
            let
                Headers = [#"Authorization" = "Bearer " & AccessToken]
            in 
            Web.Contents(ApiUrl, [Headers=Headers])
    in
    Source

Many Thanks,

J

[v-veshwara-msft · Accepted solution]

Hi @JK2 ,
Thank you for reaching out in Microsoft Fabric Community.
It appears you're facing authentication issues while connecting to the Microsoft Defender for Endpoint API in Power BI.

The Client Secret is used in the process to authenticate the application with Azure Active Directory (Azure AD) and request an Access Token, which is required for API calls. If the Client Secret is being used directly as the Access Token, it may lead to the authentication failure you're experiencing.
Steps:

1. You need to request an Access Token from Azure AD using the Client Secret, Client ID, and tenant ID.
2. This token will be used in the Authorization header of the API requests to Microsoft Defender for Endpoint.
   
   

Here is an example Power Query code:

let
    TenantId = "your_tenant_id",
    ClientId = "your_client_id",
    ClientSecret = "your_client_secret",
    TokenUrl = "https://login.microsoftonline.com/" & TenantId & "/oauth2/v2.0/token",

    // Request the access token
    TokenBody = "client_id=" & ClientId & "&scope=https://api.securitycenter.microsoft.com/.default" & 
                "&client_secret=" & ClientSecret & "&grant_type=client_credentials",
    TokenResponse = Json.Document(Web.Contents(TokenUrl, [
        Headers=[#"Content-Type"="application/x-www-form-urlencoded"],
        Content=Text.ToBinary(TokenBody)
    ])),

    AccessToken = TokenResponse[access_token],

    ApiUrl = "https://api.securitycenter.microsoft.com/api/exposureScore/ByMachineGroups",
    Source = Json.Document(Web.Contents(ApiUrl, [
        Headers=[#"Authorization"="Bearer " & AccessToken, #"Content-Type"="application/json"]
    ]))
in
    Source

For further clarification, please refer to the following Microsoft documentation:
Handling authentication for Power Query connectors - Power Query | Microsoft Learn
OAuth 2.0 client credentials flow on the Microsoft identity platform - Microsoft identity platform |...
Microsoft Defender for Endpoint APIs connection to Power BI - Microsoft Defender for Endpoint | Micr...
Create an app to access Microsoft Defender for Endpoint without a user - Microsoft Defender for Endp...

Hope this helps. Please reach out for further assistance.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and a kudos would be appreciated.

Best Regards,
Vinay.

[JK2]

Thank you Vinay, this has worked perfectly!

Your swift response is very much appreciated.

J