Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CDavies
Resolver I
Resolver I

OData issue using Power BI API

Hi

[January 2020 Report Server and Desktop]

 

I'm working on the Report Server itself and have created a Report Server contents type of dashboard in the using several OData queries connecting to the Power BI API such as:

 

http://<MyServer>/PBIReports/api/v2.0/CatalogItems

http://<MyServer>/PBIReports/api/v2.0/Folders

 

These work with no issues in the Desktop on the server, but when I save the dashboard to the Power BI Report Server, when entering in my windows credentials I get a 'Cannot Connect, Login Failed...' type of error message.

 

I have local admin rights on the server, so it's not my login details.  Strangely though if I paste the OData into the browser address it downloads a .json file as expected, so whats the RS doing to block my login credentials in the Data Sources area?

 

Are there any OData settings to check on the server somewhere?

 

Thanks in advance.

1 ACCEPTED SOLUTION

Final managed to solve this issue.

 

I had to delete and re-add the SPNs for the ReportServer Service account.  The OData connectivity now works.

 

Why?  Don't know but it works.

View solution in original post

14 REPLIES 14
Anonymous
Not applicable

he following “brick walls” were encountered:

The PowerBI Zendesk plugin only works if you install the 32-bit version of Power BI desktop client. If you don’t use it, the authentication dialogue box doesn’t work.
No Zendesk plug-in for Excel Power Query.
I am therefore forced to use JSON calls in Power Query for Excel instead and decided to use the same code for PowerBI desktop client. That is, I didn’t use the Zendesk plugin in Power BI.
I now discover that auto refresh of datasets in PowerBI.com does not support the JSON call methods employed.
I now realize that I have no choice to use different queries in PowerBI and Excel. One that uses the Zendesk plugin (PowerBI) and one that uses JSON calls (Excel).
I’m back to using a 32-bit version of Power BI Desktop Client because I’m using the Zendesk plugin. Guess what? It crashes all the time. 32-bit means less memory to play with and with PowerBI modelling, you want as much memory as possible.
It seems that if I load too much data with the timesheet data queries, then PowerBI crashes. In order to publish the PowerBI report, I need to limit the timesheet queries to 2 timesheet periods. However, I want to load almost 4 months’ worth, but more on that later.
My Power Query employs “dynamic variables” to calculate the two most recent reporting periods and these are passed to the $filter query option in the odata query.

 

I hope this information helps!

Regards,

Lewis

developer

apps4rent | o365cloudexperts | clouddesktoponline

@Anonymous did you maybe post this reply to the wrong thread? It looks like your issues related to the ZenDesk connector on the cloud service. This thread is about connecting to the Power BI Report Server API on-prem.

d_gosbell
Super User
Super User


@CDavies wrote:

 

These work with no issues in the Desktop on the server, but when I save the dashboard to the Power BI Report Server, when entering in my windows credentials I get a 'Cannot Connect, Login Failed...' type of error message.

 


What format are you using for your username. Have you tried entering it as <domain>\<username> ? 

Hi

Yes: Domain\Username

Username has db_datareader on the PBIReportServer DB and local admin rights on the server itself.


@CDavies wrote:

Hi

Yes: Domain\Username

Username has db_datareader on the PBIReportServer DB and local admin rights on the server itself.


So if you are using the OData url that user does not need any driect rights on the PBIReportServer DB or the server itself. 

 

It's not entirely clear where you are getting is error from. I'm assuming that you have successfully uploaded your report to the server and you are getting this error in the Manage > Data Sources page - is this correct?

 

What authentication option have you chosen - it should be "Windows Authentication".

 

I just double checked this on my server and using Windows Authentication and the Domain\Username format worked for me and I was able to schedule a refresh against the http://<MyServer>/PBIReports/api/v2.0/Folders endpoint

Thanks for looking/taking an interest...

 

Yup, using Windowss Authentication, and getting the error from the Manage > Data Sources > Test Connection. (I'm copying and pasting the password from our password manager which works successfully with SQL queries...)

 

Curiously though if I copy the OData url directly into the browser address bar, it correctly gives me the .json file to download.

 

Hmm, when I use an OData data source with Windows auth and domain\user the test button (and scheduled refresh) works for me. If I remove the "domain\" prefix it does not work, but I'm assuming that the server was trying to authenticate the user as a local account instead of a domain one.

 


@CDavies wrote:

Curiously though if I copy the OData url directly into the browser address bar, it correctly gives me the .json file to download.

 


When you paste the URL in the browser are you logged in as the same user which you are trying to test? Or are you running the browser under your account? To test this properly you would either need to log in as the user you are trying to save against the data source or use "runas" to run the browser as this user.

 

If this fails it probably means this users has no rights to the folders in the PBIRS portal and you would need to go into the manage folder options and then into the Security tab and make sure this user has at least "Browser" rights.

Yeah, it's a puzzle...

 

I get the same error using my domain/user login credentials in the test connection box, which is why it's confusing that the .json file downloads from the browser.

 

My user account is in an AD Group with Content Manager rights on the whole PBIReport Server.

May have to get the SQL team to SSIS these queries into a db rather than using the api... which would be a shame.

I'm struggling to think what else we can try.

 

There is a long shot that this might just be a bug with the "Test" button, so maybe try saving your credentials anyway as a temporary test (since we know your account definitely gets the expected json result) and see if you can then run a scheduled refresh.

 

And the other thing that is worth checking are the PBIRS logs, maybe they might have more information that will give a hint as to why this is not working.

 

It's also worth checking how the service account for PBIRS is configured - is it using a domain account or something like NetworkService which would have permission to authenticate accounts against AD?

Hi

I've tried saving the connection and scheduling - same result

Checked the log files - nothing conclusive in there.

Developer tools (F12) in IE (yeah we're stuck with IE) gives this error in the response body:

{
  "@odata.context":"http://<MyServer>/PBIReports/api/v2.0/$metadata#Model.DataSourceCheckResult","IsSuccessful":false,"ErrorMessage":"Log on failed. Ensure the user name and password are correct."
}

Good point about the service account.  It's set up with a Domain/Username account not a Network Service account.  Not sure how to check the permission to authenticate part...

 

Thanks again for being a second set of eyes on this... 

 


@CDavies wrote:

It's set up with a Domain/Username account not a Network Service account. 

 


That should not be the issue then. We normally use domain\username for our service accounts too. This means that the account should definitely be able to connect to your domain. (I think the service account would only be an issue if it was setup with LocalSystem or something like that).

 

Maybe you need to ask your domain admins, see if they can see authentication failures and help figure out what is going on here.

Ok, took a step back.

Test 1:
•Cleared All Permissions, and restarted PBI Desktop
•Odata connection to http://<myserver>/PBIReports/api/v2.0/Folders
•Windows Credentials: Use my current credentials
•Result: Success.  Data Returned

Test 2:
•Cleared All Permissions, and restarted PBI Desktop
•Odata connection to http://<myserver>/PBIReports/api/v2.0/Folders
•Windows Credentials: Use alternate credentials.  Used my Windows Domain\Username and password (should be the same as in test 1 right?)
•Result: Fail. 'We can't authenticate with the credentials provided'

Log files are no help, Event view doesn't capture anything, may bounce the serve

Final managed to solve this issue.

 

I had to delete and re-add the SPNs for the ReportServer Service account.  The OData connectivity now works.

 

Why?  Don't know but it works.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors