SharePoint Authentication Issues

[JillHenninger]

Hi all

I have a dataset that refreshes nightly and taps into 6 SharePoint based Excelf files and one OData (connected to a SharePoint list). Way more often than not, the dataset won't refresh due to a credentialing issue. 

Error: Your data source can't be refreshed because the credentials are invalid. Please update your credentials and try again.

Within Data Source Credentials, when I click "Edit Credentials", this is the error:

Failed to update data source credentials: Access Denied. Before opening files in this location, you must first add the web site to your trusted sites list, browse to the web site, and select the option to login automatically. 

But - not possible in Chrome and I can't add specifics sites to the safelist in IE due to company policy (and I can navigate to those sites just fine)

Here is how the site is referenced:

\\mastersite.sharepoint.com@SSL\DavWWWRoot\sites\NAME\Shared Documents\General\Folder\File1.xlsx

To try and fix it, I've:

Closed and reopened Chrome (I can't get into PowerBI in IE)

Made sure I'm on VPN

Navigated to the folder in Chrome where those files are stored

Visisted https://myaccount.microsoft.com/ to reauthenticate (I read about that somewhere, I swear it worked last week)

I'm at a loss! Any tips? 

[cmorin]

Hi, I think I resolved the issue. At least in my case, I disabled some features:

File - Options and Settings - Options - Preview Features: I disabled them all.  The problem disappeared for me (at this time). 

Hope this helps!

[AlonaOnline]

I found a solution by adding a correct URL to the Sharepoint document - Open the Document in the Desktop Ap -> Information -> Copy path ( I am not sure about wording, since I am using a German Version) You need to copy only a part of the path, for me it looks like https://companyxy.sharepoint.com/sites/XXX/ 

and when you connect the source you need to make sure that no Gateway is selected and your authentification is set to organisation

[AlonaOnline]

Hi, I have an alike issue with a sharepoint Document. It is added as a dataflow. Everything was fine until yeasterday, and suddenly the data does not upload. I tried to open dataflow in cloud and login with my organisation account. Unfortunately, it tells - unexpected error and try again later.  

What I tried so far: delete and installed gateway again, tried to update data through the desktop variant.

I am clueless. 

[StefanoGrimaldi]

hey, this happend often around here, try setting the source to the root sharepoint instead of the specific site also you need to include the entire adrees: including the httpl:\\ from the begining. try those 2 part and let us know, 

if this solved your question give some kudos and mark as solution for others to find. 

[Anonymous]

I am having same issue regarding this. Does it have any solution?

[bwag2023]

What finally worked for me was going to Transform Data, and changing the source of the query from:

= Excel.Workbook(File.Contents("\\sharepoint.com@SSL\DavWWWRoot\sitespath\excelfile.xlsx"), null, true)

to:

= Excel.Workbook(Web.Contents("https://sharepointsite.com/sitepath/filepath/excelfile.xlsx"), null, true)