Set Datasource Credential with API - Fabric SQL Endpoint issue

Question

Hi,&nbsp;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 :&nbsp;{&nbsp;&nbsp;"error":&nbsp;{&nbsp;&nbsp;&nbsp;&nbsp;"code":&nbsp;"DM_GWPipeline_Gateway_InvalidConnectionCredentials",&nbsp;&nbsp;&nbsp;&nbsp;"pbi.error":&nbsp;{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"code":&nbsp;"DM_GWPipeline_Gateway_InvalidConnectionCredentials",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"parameters":&nbsp;{},&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"details":&nbsp;[&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"code":&nbsp;"DM_ErrorDetailNameCode_UnderlyingErrorCode",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"detail":&nbsp;{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"type":&nbsp;1,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"value":&nbsp;"-2147467259"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;},&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"code":&nbsp;"DM_ErrorDetailNameCode_UnderlyingErrorMessage",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"detail":&nbsp;{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"type":&nbsp;1,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"value":&nbsp;"The&nbsp;credentials&nbsp;provided&nbsp;for&nbsp;the&nbsp;SQL&nbsp;source&nbsp;are&nbsp;invalid.&nbsp;(Source&nbsp;at&nbsp;ad......-7s2tvmzlt6oevooga2lpcmwpwa.datawarehouse.pbidedicated.windows.net;TestLakehouse.)"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;},&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"code":&nbsp;"DM_ErrorDetailNameCode_UnderlyingHResult",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"detail":&nbsp;{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"type":&nbsp;1,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"value":&nbsp;"-2147467259"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;},&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"code":&nbsp;"Microsoft.Data.Mashup.CredentialError.DataSourceKind",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"detail":&nbsp;{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"type":&nbsp;1,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"value":&nbsp;"SQL"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;},&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"code":&nbsp;"Microsoft.Data.Mashup.CredentialError.DataSourcePath",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"detail":&nbsp;{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"type":&nbsp;1,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"value":&nbsp;"ad......-7s2tvmzlt6oevooga2lpcmwpwa.datawarehouse.pbidedicated.windows.net;TestLakehouse"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;},&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"code":&nbsp;"Microsoft.Data.Mashup.CredentialError.Reason",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"detail":&nbsp;{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"type":&nbsp;1,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"value":&nbsp;"AccessUnauthorized"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;},&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"code":&nbsp;"Microsoft.Data.Mashup.MashupSecurityException.DataSources",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"detail":&nbsp;{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"type":&nbsp;1,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"value":&nbsp;"[{\"kind\":\"SQL\",\"path\":\"ad......-7s2tvmzlt6oevooga2lpcmwpwa.datawarehouse.pbidedicated.windows.net;TestLakehouse\"}]"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;},&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"code":&nbsp;"Microsoft.Data.Mashup.MashupSecurityException.Reason",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"detail":&nbsp;{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"type":&nbsp;1,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"value":&nbsp;"AccessUnauthorized"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"exceptionCulprit":&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;}&nbsp;&nbsp;}}&nbsp;This in my body for the REST API (patch datasources) :{&nbsp;&nbsp;"credentialDetails":&nbsp;{&nbsp;&nbsp;&nbsp;&nbsp;"credentialType":&nbsp;"OAuth2",&nbsp;&nbsp;&nbsp;&nbsp;"credentials":&nbsp;"{\"credentialData\":[{\"name\":\"accessToken\",\"value\":\"eyJ0eXAiOi......\"}]}",&nbsp;&nbsp;&nbsp;&nbsp;"encryptedConnection":&nbsp;"Encrypted",&nbsp;&nbsp;&nbsp;&nbsp;"encryptionAlgorithm":&nbsp;"None",&nbsp;&nbsp;&nbsp;&nbsp;"privacyLevel":&nbsp;"Private"&nbsp;&nbsp;}}&nbsp;To retrieve the token, I used this url :https://login.microsoftonline.com/common/oauth2/authorize?display=&amp;client_id={client_id}&amp;redirect_uri={redirect_uri}&amp;resource=https%3A%2F%2Fdatabase.windows.net%2F&amp;response_type=code&amp;scope=.default%20openid&amp;state={state}&amp;prompt=select_account&nbsp;My app in AAD has the API "Azure SQL Database" and "Azure Storage" authorization.&nbsp;Any idea why this isn't working (again this works for Dataflows) ? Is my scope / resource not the correct one for Fabric?&nbsp;Thanks for your help and guidance, let me know if you need more information !&nbsp;

anonymous · Answer

Hi&nbsp; IAmStandard&nbsp;,
&nbsp;
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
&nbsp;
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 · Answer

Hi Anonymous ,&nbsp;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 ?

Forum Discussion

Set Datasource Credential with API - Fabric SQL Endpoint issue

2 Replies

Related content

Service principal for dataset datasource credential

Custom Endpoints - Eventstream

Refresh SQL Endpoint API

Gateway Datasource with SPN credentials

sql endpoint connection