cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
THEG72 Member
Member

Credential Error for Excel File hosted on One Drive

Hi Power BI Support Team,

I am getting a credential errors on an Excel files stored on One Drive when i try to add the data source to the gateway.

Here is my detailed environment:
Windows 10 Pro,Version 10.0.1734 Build
Office 365 Business Premium
Power BI Professional

When i log into the laptop i simply enter my email address such as garry@xyz.com. I have a personal domain name associated with subscription.

My local PC where the gateway is installed shows the following in regards to the system information

Test1.PNGLocal PC

The Excel file is located in the root directory of the onedrive.

When i open the Excel file in the desktop App from the one drive online and copy the url from within Excel it returns the result below:

https://xyz-my.sharepoint.com/personal/myfirstname_xyz_com/Documents/MyTestSpreadsheet.xlsx?web=1

I understand i need to remove the "?web=1" from the url stated above when entering the path for the Web data source.

I tested the url by pasting it to a browser and it opens the file in Excel on line with the ?web=1 or without in Excel desktop app.

Note i am not using the File Data source but Web as the data source.

Test2.PNGData Source Add screen
What is the correct syntax for entering  the Windows User Name for gateway laptop NOT in a business network domain and without an on premise active directory?

 

I have tried several options and referred to previous comments in this area but I cannot get this to work and get credential error messages as shown below:

 

Unable to connect:
We encountered an error while trying to connect to .
Details: "We could not register this data source for any gateway instances within this cluster.
Please find more details below about specific errors for each gateway instance."Hide details
Activity ID:
5a804452-9dc7-4031-902f-132fbdf1baae
Request ID:
6487d495-7748-8d53-17bd-a6c1799ba603
Cluster URI:
https://wabi-australia-southeast-redirect.analysis.windows.net
Status code:
400
Error Code:
DMTS_PublishDatasourceToClusterErrorCode
Time:
Fri Jul 19 2019 13:16:51 GMT+0800 (W. Australia Standard Time)
Version:
13.0.10117.179
MyGATEWAY:
Invalid connection credentials.
Underlying error code:
-2147467259
Underlying error message:
The credentials provided for the Web source are invalid. (Source at https://xyz-my.sharepoint.com/personal/myfirstname_xyz_com/Documents/MyTestSpreadsheet.xlsx.)
DM_ErrorDetailNameCode_UnderlyingHResult:
-2147467259
Microsoft.Data.Mashup.CredentialError.DataSourceKind:
Web
Microsoft.Data.Mashup.CredentialError.DataSourcePath:
https://xyz-my.sharepoint.com/personal/myfirstname_xyz_com/Documents/MyTestSpreadsheet.xlsx
Microsoft.Data.Mashup.CredentialError.Reason:
AccessForbidden
Microsoft.Data.Mashup.MashupSecurityException.DataSources:
[{"kind":"Web","path":"https://xyz-my.sharepoint.com/personal/myfirstname_xyz_com/Documents/MyTestSpreadsheet.xlsx"}]
Microsoft.Data.Mashup.MashupSecurityException.Reason:
AccessForbidden

 

Tried various versions of the username but no luck, appreciate any other options to try and get this working!

 

A one drive connector would be real useful.

1 ACCEPTED SOLUTION

Accepted Solutions
THEG72 Member
Member

Re: Credential Error for Excel File hosted on One Drive

I ended up solving this by making sure the Data source credentials were set in the Power BI file before uploading.


Selecting Data Source Setting as organistational will then give you the OAUTH 2 option in the data source Add screen in the service.

 

3 REPLIES 3
THEG72 Member
Member

Re: Credential Error for Excel File hosted on One Drive

I ended up solving this by making sure the Data source credentials were set in the Power BI file before uploading.


Selecting Data Source Setting as organistational will then give you the OAUTH 2 option in the data source Add screen in the service.

 

GladIQ New Member
New Member

Re: Credential Error for Excel File hosted on One Drive

Hello,
 
we started receiving this error. Everything was working fine for about 1 year, no changes in system nothiing. I even changed credentials in report and published it again, autorefresh just doesnt work, connection to Gateway and datasources is showing as its ok... very weird:
 
Underlying error code-2147467259 Table: B2B.
Underlying error messageThe key didn't match any rows in the table.
DM_ErrorDetailNameCode_UnderlyingHResult-2147467259
Microsoft.Data.Mashup.ValueError.Key[Schema = "dbo", Item = "B2Breport"]
Microsoft.Data.Mashup.ValueError.ReasonExpression.Error
Cluster URIWABI-WEST-EUROPE-redirect.analysis.windows.net
Activity ID8a71bee7-d489-4d96-ae04-94f44dd09cf4
Request IDc38dc306-3dc0-868b-77e5-bd61955b8def
Time2019-07-19 10:35:07Z
THEG72 Member
Member

Re: Credential Error for Excel File hosted on One Drive

@GladIQ 


Sounds like its the table data with the problem B2B table..."Underlying error messageThe key didn't match any rows in the table."..I dont see its a credential error from the error message you submitted.

 

Have your checked the columns of your B2B table data in the query editor for errors? I would step through each step of the transformations in your queries to see if its causing an issue. A sample image of the table data would be useful.

 

Perhaps submit more information about the data source so we can trouble shoot it.

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 45 members 1,057 guests
Please welcome our newest community members: