OLE DB: The connection failed because user credentials are needed and Sign-In UI is not allowed

[Anonymous]

Hi everyone, 

I'm hoping someone can help me with this problem. I'm trying to use the connection string from an existing PBI dataset to import certain tables into another report (without using DQ). I managed to do this using an OLE DB connection. The reason for utilizing this method is that I can use subsets of data from existing PBI datasets in my report. The first week it worked perfectly, but now it's started giving me the following error when I refresh the dataset (in PBI desktop): 

OLE DB: The connection failed because user credentials are needed and Sign-In UI is not allowed

I have tried:

• Clearing (local and global) permissions and connecting again.
• Re-adding my user id when prompted.
• Clearing the cache.
• Re-installing Power Bi.
• Re-doing the data import in a new Power Bi report.

The same error happens when I try to download the dataset in Python, even though that worked too before. Does anybody know what causes this error when downloading a dataset using the connection string? Thanks in advance for your help.

[Anonymous]

So after another week of trial and error I have yet to find a solution. The absence of a response here makes me think we are dealing with a bug in the PBI Service (in the Analyze in Excel function specifically) or in Azure Analytics Services. I am going to log an issue as suggested by @amitchandak. If anyone comes across a solution in the meantime, they can post it here and I will highlight it. 

[Syndicate_Admin]

Can you help me find out how you made that connection?

[travisg_06]

I am having this exact same issue, and I have tried everything under the sun to fix it.  There is no good solution out there, and the error itself is so ambiguous that Google doesn't yield any meaningful results to help. 

I'm having this issue when trying to refresh the Power BI data in an Excel workbook.  Like you, this was working for weeks and then suddenly I'm getting this error and can't shake it.

[Syndicate_Admin]

Hello, how did you get that connection? If you help me??

[amitchandak]

@Anonymous , try this setting, if this does not work log an issue

Go to File-> Options and settings -> Options -> Security Option -> and check : Use My Default browser( Visible after scrolling)

if needed, clear the cache of the default browser

if needed log an issue -https://community.powerbi.com/t5/Issues/idb-p/Issues

[Anonymous]

Hi Amitchandak, sadly that did not work either. I don't think the problem is that I am not properly authenticated. Otherwise it would not happen in Python as well I would think. I will look into logging an issue later today, thanks for the suggestion. I'm still hoping there is some way to make the OLE DB connection work again on my end. 

[Cayshin]

I've discovered a way that resolves the issue for me. In the connection string, remove the sobe_wowvirtualserver- portion.

So for example this:

provider=MSOLAP.8;initial catalog=sobe_wowvirtualserver-xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx;data source=pbiazure://api.powerbi.com;mdx compatibility=1;safety options=2;mdx missing member mode=Error;identity provider=https://login.microsoftonline.com/common, https://analysis.windows.net/powerbi/api, xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx;update isolation level=2

Becomes this:

provider=MSOLAP.8;initial catalog=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx;data source=pbiazure://api.powerbi.com;mdx compatibility=1;safety options=2;mdx missing member mode=Error;identity provider=https://login.microsoftonline.com/common, https://analysis.windows.net/powerbi/api, xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx;update isolation level=2

Once that was gone and the report replublished, I was able to set up a new Gateway connection

For authenticaiton method, choose Basic, but still enter in your Microsoft credentials.

Power BI will now happily refresh the report.