Set Datasource Credential with API - Fabric SQL Endpoint issue

[IAmStandard]

Hi,

I'm working on a project where I need to update the credentials of some Dataset programatically. I got it working for SharePoint, Dataverse and Dataflow.

Now i'm trying to do the same for Fabric SQL Endpoint, however I recieve the error below :

{

  "error": {

    "code": "DM_GWPipeline_Gateway_InvalidConnectionCredentials",

    "pbi.error": {

      "code": "DM_GWPipeline_Gateway_InvalidConnectionCredentials",

      "parameters": {},

      "details": [

        {

          "code": "DM_ErrorDetailNameCode_UnderlyingErrorCode",

          "detail": {

            "type": 1,

            "value": "-2147467259"

          }

        },

        {

          "code": "DM_ErrorDetailNameCode_UnderlyingErrorMessage",

          "detail": {

            "type": 1,

            "value": "The credentials provided for the SQL source are invalid. (Source at ad......-7s2tvmzlt6oevooga2lpcmwpwa.datawarehouse.pbidedicated.windows.net;TestLakehouse.)"

          }

        },

        {

          "code": "DM_ErrorDetailNameCode_UnderlyingHResult",

          "detail": {

            "type": 1,

            "value": "-2147467259"

          }

        },

        {

          "code": "Microsoft.Data.Mashup.CredentialError.DataSourceKind",

          "detail": {

            "type": 1,

            "value": "SQL"

          }

        },

        {

          "code": "Microsoft.Data.Mashup.CredentialError.DataSourcePath",

          "detail": {

            "type": 1,

            "value": "ad......-7s2tvmzlt6oevooga2lpcmwpwa.datawarehouse.pbidedicated.windows.net;TestLakehouse"

          }

        },

        {

          "code": "Microsoft.Data.Mashup.CredentialError.Reason",

          "detail": {

            "type": 1,

            "value": "AccessUnauthorized"

          }

        },

        {

          "code": "Microsoft.Data.Mashup.MashupSecurityException.DataSources",

          "detail": {

            "type": 1,

            "value": "[{\"kind\":\"SQL\",\"path\":\"ad......-7s2tvmzlt6oevooga2lpcmwpwa.datawarehouse.pbidedicated.windows.net;TestLakehouse\"}]"

          }

        },

        {

          "code": "Microsoft.Data.Mashup.MashupSecurityException.Reason",

          "detail": {

            "type": 1,

            "value": "AccessUnauthorized"

          }

        }

      ],

      "exceptionCulprit": 1

    }

  }

}

This in my body for the REST API (patch datasources) :

{

  "credentialDetails": {

    "credentialType": "OAuth2",

    "credentials": "{\"credentialData\":[{\"name\":\"accessToken\",\"value\":\"eyJ0eXAiOi......\"}]}",

    "encryptedConnection": "Encrypted",

    "encryptionAlgorithm": "None",

    "privacyLevel": "Private"

  }

}

To retrieve the token, I used this url :

https://login.microsoftonline.com/common/oauth2/authorize?display=&client_id={client_id}&redirect_ur...{redirect_uri}&resource=https%3A%2F%2Fdatabase.windows.net%2F&response_type=code&scope=.default%20openid&state={state}&prompt=select_account

My app in AAD has the API "Azure SQL Database" and "Azure Storage" authorization.

Any idea why this isn't working (again this works for Dataflows) ? Is my scope / resource not the correct one for Fabric?

Thanks for your help and guidance, let me know if you need more information !

[v-yangliu-msft]

Hi  @IAmStandard ,

According to your requirement description, it seems that you need to update credentials for SharePoint and other Power BI dataset datasources, according to my view of the permissions requirements for this corresponding rest API in the official documentation, it seems that it needs at least the Power BI side of the permission scope: Dataset.ReadWrite. .All, would it be convenient for you to add this to the corresponding application before trying it out?

Datasets - Update Datasources - REST API (Power BI Power BI REST APIs) | Microsoft Learn

Best Regards,

Liu Yang

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

[IAmStandard]

Hi @v-yangliu-msft ,

Thanks for your reply. The API I use if actually this one. And it works very well for SharePoint and Dataflows, my only issue is with the SQL Endpoint of a Fabric Lakehouse.

I think I don't have the correct documentation to get the correct AccessToken for this specific datasource. Do you have any info on that ?