Excel Reporting : Can't connect to calendars with OData anymore

[Romain_Brunel]

Hello there,

A few days ago, some of the Excel reportings I'm maintaining for a client stopped working because they get an "access denied" error when accessing data. I'm using OData connections to get relevant informations from both the data sources below :

https://xxx.sharepoint.com/sites/pwa/_api/projectdata

https://xxx.sharepoint.com/sites/pwa/_api/projectserver/calendars('calendarid')

The first data source is the usual one to get all the projects-related data. This one seems to still work fine when using the Admin account (authenticated using the "Organizational account" authentication type).

The second one I used to get the calendar exception list from the Standard calendar. It worked fine too until a few days ago and now I can't access it anymore, even with the Admin credentials.

When I try to reconnect to the data source I meet a message that says "We can't authenticate you with the given credentials. Try again.".

Did the authentication method for calendar access changed these last days ? Has anyone encountered this issue ?

Thank you very much

[#2]

Update: When I try to create a new OData feed connection, while it won't work with the URL https://xxx.sharepoint.com/sites/pwa/_api/projectserver/ it will work with https://xxx.sharepoint.com/sites/pwa/_api/ps_projectserver/.
Then, I can see my Calendar tables in Power Query, but still can't open it (with the same error). I can access other tables just fine (CustomFields, EnterpriseProjectTypes, Events, Projects...). Another table I cannot access is the Settings one, even with Admin credentials.

Edit : The weird thing is that if I try going straight to the xxx.sharepoint.com/sites/pwa/_api/ps_projectserver/calendars('xxx')/BaseCalendarExceptions URL in the same browser I use for PWA, it instantly works and shows me the list of exceptions, but accessing it through Excel is still impossible.

[#3]

@Romain_Brunel We are experiencing the same error when connecting via dataflows, or Power BI desktop and I have opened a ticket with Microsoft.  It tired the workaround you suggested, https://XXX.sharepoint.com/sites/PWA/_api/PS_ProjectServer/, but get the same error 

[#4]

@RodFromm Thanks for the info, that's somehow good to know that the issue isn't only on my client's side. Let's hope it's just an issue from the last update and would get resolved soon.

[#5]

Having the same issue. Going straight to the endpoint works (as per Romain's update) but pulling the OData feed fails. I am a global admin so I know it's not actually permissions-related. Hopefully we can figure this one out soon.

[#6]

Has anyone figured anything out on this? I'm having the same issues...

[#7]

Hi @DanMarkoff,
I don't think we can do anything on our end. The connection works when established directly in a browser but fails when asked through OData (or Excel web data source).
I'm mostly waiting to see @RodFromm 's ticket results.

[#8]

@Romain_Brunel - I'm actively working with Microsoft on this issue and will provide updates as  

I'm actively working with Microsoft on this issue and will provide updates as I receive them.

[#9]

@RodFromm Thank you for the update, that's very reassuring. Good luck !

[#10]

I'm facing the same issue. Have you gotten any response from Microsoft?

[#11]

@otgerpeidro Not yet, but I am actively working with Micrsoft to resolve this issue.

[#12]

@RodFromm I'm facing the exact same issue while I'm a Global Admin. Have you got any response from Microsoft?

[#13]

@KMathijs Microsoft has duplicated the issue and it is isolated to Project Online PWA sites.  They didn't provide and ETA, but they are actively working on a fix and will provide updates as the team makes progress, which I will share as I receive them.

[#14]

@RodFromm Any news from MS? It has been almost 2 weeks...

[#15]

@KMathijs I got an update from Microsoft.  They've identified the issue to a change in the permissions required to access this table.  They are working to restore the original permissions but did not give an ETA.

[#16]

@KMathijs @Romain_Brunel @otgerpeidro - I got a reply from Microsoft to test the fix they implemented for this issue!  I am now able to access the ProjectServer/Calendar table data using Power Query in Excel and Power BI desktop.  It still does not work if queried from a dataflow.  I replied to Microsoft with my findings, and they are working on getting the query working with dataflows.  Can you try querying the Calendar table and report back your results, which I will pass onto Microsoft?

[#17]

@RodFromm @Romain_Brunel @otgerpeidro 

I just tested it with PowerBI Desktop and still no luck. Also refreshing the report from a published dataset doesn't work but we kind of expected that

Which fix did they implement?

[#18]

@KMathijs That is strange.  In our tenant, I can query the Calendar table from Power BI desktop or Excel, but I get the same credentials error refreshing a published dataset or dataflow.

[#19]

@KMathijs - Microsoft has resolved this issue.  I am able to refresh Calendar table data using Power BI desktop, Power Query within Excel, published datasets or a dataflow.  Hopefully this is also resolved in your tenant.

[#20]

Hello, Same as @KMathijs, it still won't work on my end when trying to create a new OData connection to the Calendars table. Let's hope they roll it on all tenants soon.