Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Connect SharePoint List using Service Principal from Power BI service

Hi,

I'm using SharePoint list as data source in my Power BI report and I've to connect the SharePoint List through Service Principal in the Power BI Service for the connection configuration.

 

Following Pre-requisites are already taken care:

1) We have an Azure App registration in Entra ID

2) having the API permission site.selected with full control access

3)Service principal is created

4) Cloud security group is created (Security group in Entra ID) and assigned Service Principal inside this security group.

5) Enabled allow service Principal API in the Power BI admin portal tenant setting and the cloud security group is added under specific security group option.

 

But I'm facing "Failed to update datasource credentials . Credential of the sharepoint source are invalid." issue.

 

Can anyone pls. help me on solving this issue? 

 

Regards,

Selvi

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    Hi Anonymous - To connect to SharePoint from Power BI using a Service Principal Name (SPN) instead of personal credentials, follow these steps. It seems you've already registered the Microsoft Entra (Azure AD) app and created a service principal, so the issue might lie in permissions or configuration. 

     

    Please chck the steps below:

    1. Ensure Microsoft Entra App Permissions are Configured Correctly
    Application Permissions:

    Open the Azure portal.
    Go to Azure Active Directory > App registrations > Select your app.
    Navigate to API Permissions.
    Add permissions for SharePoint:
    API: Microsoft Graph
    Permissions:
    Sites.Read.All (to read all site collections and subsites)
    Sites.Manage.All (if you need write permissions)
    Click Grant Admin Consent for these permissions.
    Set Up Roles on SharePoint Online:

    Go to the SharePoint admin center: https://<yourtenant>-admin.sharepoint.com.
    In Advanced Settings, grant the App-only access by assigning the service principal to the desired site.
    Site Permissions:

    Assign permissions directly to the app on the specific SharePoint site or library:
    Go to the site > Settings > Site Permissions.
    Add the service principal as a user (use the app ID).
    2. Configure Power BI Service to Use Service Principal
    Ensure you configure the data source correctly in Power BI:

    Install Power BI Desktop and Test with Service Principal:

    In Power BI Desktop, when connecting to SharePoint Online:
    Use the Organizational Account option.
    Provide the Client ID and Client Secret of your registered app.
    Enable SPN in Power BI Service:

    In Power BI Service, go to Settings > Admin Portal > Tenant Settings.
    Enable Service principal authentication under Developer Settings.
    3. Test SharePoint Connection in Power BI
    In Power BI Service, follow these steps to set up the dataset connection:

    Set Up the Dataset:

    In Power BI Desktop:
    Connect to SharePoint Online using the app's credentials (Client ID and Secret).
    Publish the report to Power BI Service.
    Configure the Dataset Credentials:

    In Power BI Service:
    Go to the Workspace where your dataset is published.
    Select Settings for the dataset.
    Under Data Source Credentials, choose OAuth2.
    Use the Service Principal credentials (Client ID and Secret).
    4. Troubleshooting Common Issues
    Here are common causes of connection failures and their solutions:

    Issue: Missing Admin Consent

    Make sure admin consent has been granted for the app permissions in the Azure portal.
    Issue: SharePoint Permissions Not Granted

    Ensure that the service principal (app) has the required permissions on the SharePoint site or library.
    Issue: Dataset Not Refreshing in Power BI Service

    Check the gateway (if using one) and ensure that service principal authentication is enabled in the gateway settings.
    Issue: Authentication Error

    Ensure that you’re using the correct Tenant ID, Client ID, and Client Secret.
    Verify the app registration details and ensure the client secret has not expired.

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous,

    Thanks for reaching out to the Microsoft fabric community forum.

     

    It looks like you are facing issue while connecting your SharePoint data source with your PowerBi. The solution has been provided by rajendraongole1. If your issue is resolved then please accept it as solution.

     

    I would also take a moment to personally thank rajendraongole1, for his active participation and the solutions you’ve been sharing in the community forum. Your contributions makes a real difference.

     

    If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

    Best Regards,
    Hammad.
    Community Support Team

     

    If this post helps then please mark it as a solution, so that other members find it more quickly.

    Thank you.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi rajendraongole1 Thanks for your valuable input! I'm able to connect sharepoint from power bi desktop and same works in power bi service as well. Now in Power BI service, due to data security, we are trying to connect the sharepoint source using service principal instead of our mail id. Register a Microsoft Entra app and create a service principal - Microsoft identity platform | Microsoft Learn this was already taken care but still we are facing an issue to connect the sahrepoint using SPN. Could you pls. check on this and share your solution.

       

      Regards,

      Selvi

      • rajendraongole1's avatar
        rajendraongole1
        Super User
        1 year ago

        Hi Anonymous - To connect to SharePoint from Power BI using a Service Principal Name (SPN) instead of personal credentials, follow these steps. It seems you've already registered the Microsoft Entra (Azure AD) app and created a service principal, so the issue might lie in permissions or configuration. 

         

        Please chck the steps below:

        1. Ensure Microsoft Entra App Permissions are Configured Correctly
        Application Permissions:

        Open the Azure portal.
        Go to Azure Active Directory > App registrations > Select your app.
        Navigate to API Permissions.
        Add permissions for SharePoint:
        API: Microsoft Graph
        Permissions:
        Sites.Read.All (to read all site collections and subsites)
        Sites.Manage.All (if you need write permissions)
        Click Grant Admin Consent for these permissions.
        Set Up Roles on SharePoint Online:

        Go to the SharePoint admin center: https://<yourtenant>-admin.sharepoint.com.
        In Advanced Settings, grant the App-only access by assigning the service principal to the desired site.
        Site Permissions:

        Assign permissions directly to the app on the specific SharePoint site or library:
        Go to the site > Settings > Site Permissions.
        Add the service principal as a user (use the app ID).
        2. Configure Power BI Service to Use Service Principal
        Ensure you configure the data source correctly in Power BI:

        Install Power BI Desktop and Test with Service Principal:

        In Power BI Desktop, when connecting to SharePoint Online:
        Use the Organizational Account option.
        Provide the Client ID and Client Secret of your registered app.
        Enable SPN in Power BI Service:

        In Power BI Service, go to Settings > Admin Portal > Tenant Settings.
        Enable Service principal authentication under Developer Settings.
        3. Test SharePoint Connection in Power BI
        In Power BI Service, follow these steps to set up the dataset connection:

        Set Up the Dataset:

        In Power BI Desktop:
        Connect to SharePoint Online using the app's credentials (Client ID and Secret).
        Publish the report to Power BI Service.
        Configure the Dataset Credentials:

        In Power BI Service:
        Go to the Workspace where your dataset is published.
        Select Settings for the dataset.
        Under Data Source Credentials, choose OAuth2.
        Use the Service Principal credentials (Client ID and Secret).
        4. Troubleshooting Common Issues
        Here are common causes of connection failures and their solutions:

        Issue: Missing Admin Consent

        Make sure admin consent has been granted for the app permissions in the Azure portal.
        Issue: SharePoint Permissions Not Granted

        Ensure that the service principal (app) has the required permissions on the SharePoint site or library.
        Issue: Dataset Not Refreshing in Power BI Service

        Check the gateway (if using one) and ensure that service principal authentication is enabled in the gateway settings.
        Issue: Authentication Error

        Ensure that you’re using the correct Tenant ID, Client ID, and Client Secret.
        Verify the app registration details and ensure the client secret has not expired.