Azure SQL Direct Query error: Login failed for user '<token-identified principal>'

[thomsog1]

Hi All,

I have recently encountered an error where we have a report which connects to an Azure SQL database using direct query with database authentication.

When both myself and my colleague connect to it in the desktop it works fine and the visuals all work correctly. However when my colleague published the report online the following errors were given.

However when I uploaded the same report with no changes it worked fine. And when went into the Dataset and retyped in the same connection information in Data Source Credentials it worked fine.

The only difference that we can think is that I was the person who created and administered the database... But since it uses database authentication, I don't see why that would matter...? After I have edited the credentials he can even publish the exact same report and it works fine (we assume because the credentials are still working from when I typed them in)

Any suggestions?

[GilbertQ]

Hi @thomsog1 

Can you confirm when setting up the credentials that you are not using Single Sign On (SSO)?

Also it would appear that your credentials are the only ones who works and has access to the database? 

I would suggest adding the other users who you want to setup and configure the data source credentials.

[thomsog1]

Hi @GilbertQ thanks for replying so quickly (apologies for the slow response)!

I would imagine both myself and my colleague use SSO to login to the Power BI online service.

So despite me creating a database login, since I created it, only I can use it in the online service, but on desktop it works fine? Is there a reason for that? 

Also, when you say adding other users. Do you mean within the database itself?

Many thanks for all your help!

[GilbertQ]

Hi @thomsog1 

What you could do is to configure it to use a single login on the data source credentials which would be used for all report consumers?

[thomsog1]

Sorry, I'm not sure I understand... Would this essentially mean between myself and my colleague we had a different company account to log into Power BI online to connect to the database?

TBH, I thought thats what I was trying to do with my database login that I created... avoiding one single person having access.

[GilbertQ]

Hi @thomsog1 

I have done that in the past and it has worked without any issues in the past. I would suggest checking the documentation? 

Azure SQL Database with DirectQuery - Power BI | Microsoft Docs

[thomsog1]

Thanks @GilbertQ for your suggestion and that link!

I'll test this approach out and if successful, accept your result as the solution. It still confuses me why a login is created for that database specifically it won't work...

[GilbertQ]

Yes please let me know how it goes.