Power BI doesn't allow connection with OAuth2 to SQL Source

[Anonymous]

Hi all,

I met frustrating problem recently for which I'm seeking a help:

The situation:

I have a dataset in load regime with connection to a single source - SQL for which I use the Sql.Database connector in Power Query. The connection worked just fine until the last month when I had to change my password for the corporate account. Since then, the connection fails. Obvious solution would be to edit the connection settings with the new password. Unfortunately, this doesn't work, because whenever I try to edit the connection settings in the Power BI desktop (or online Service), the process gets interrupted - I'm prompted to select the account I want to use for the connection, but the after selection a message appears informing that I'm being taken to my organization's sign-in page (we use MFA). At that moment the login window just disappears and error message "The credentials provided for the SQL source are invalid" appears in the Power Query. The problem is that I'm actually not able to provide the correct credentials in the first place as the sign-in process never finishes.

I tried this multiple times, I deleted the source from the settings (local and global), tried to connect to source in blank new PBI file, in Power BI online service and even in Dataflows. Each time with the same result of login window disappearing before I'm able to input password or go through the MFA.

I have the access to the underlying source, which I can confirm by connecting the same SQL server and database in SQL Server Management Studio with no problem (and being able to login there through the MFA) and to see the tables which I want to see in the Power BI dataset. The problem is prevailing only in Power BI. 

The already tried solutions:

• Delete the source from the local and global settings.
• Reinstall Power BI desktop (to newest version).
• Try to set the credentials in Power BI Service after publishing the dataset (in Edge and in Chrome browsers).

Is there any way how to force the Power BI to enable me to go through the whole login including MFA so I can reinstate my credentials for the source?

Thank you.

Boris.

[Anonymous]

Hi! Can you fix the issue with OAuth2.0? I have the same issue, i change my O365 password and is impossible to change on connetions, even if i created a new one, i can't change it.

[BA_Pete]

Hi @Anonymous ,

In Power BI Desktop, go to File > Option & Settings > Options, then check this box:

This will open the authentication window in a separate instance of your default browser, rather than failing in PBI.

Pete

[Anonymous]

Helo Pete,

thank you for reply and suggestion. I used this options settings as you advised, however the actual sign-in is still skipped. When I select the account, this message shows up (with no password form or MFA action):

Although it looks like success, Power BI still shows error:

I tried again to use the same login to connect via SSMS and it works there just to be sure that my credentials are correct and was still able to see the database and the tables there, so there must be some problem between the Power BI and authentication service.

If you have any oter suggestions, please let me know.

Boris.

[BA_Pete]

Hi Boris,

The only thing I can think might be the issue is the authentication method. Have you checked what method you're using to connect via SSMS and used the same method in PBI? It sounds like it should be Windows Authentication as the ssue started when you updated your password, but just check the type in SSMS and match it.

Pete

[Anonymous]

Hi Pete,

I'm using the Microsoft account (my corporate key ad we use Active Directory in Azure to manage user accesses in my organization).

below the sign-in menus in SSMS (left) and Power BI desktop (right) to the same SQL server.  The SSMS asks me for the password and then for authentication in the mobile app, PBI doesn't do either of these and returns error (could not autheticate...). This is the same setup I used before the password change when the PBI used to connect correctly.

I also tried to solve this with our helpdesk but they believe it is Power BI issue which they can't help with - as my access to the database as such works.

Boris.

[BA_Pete]

Did you try using Windows Auth in PBI instead?

It may be worth asking your helpdesk to set up a database identity for you, if possible, so you can see if an alternative auth method works or not.

Pete

[Anonymous]

Hello,

thank you for suggestion. This is not a possibility for me. The database is managed by a different busienss unit than I'm located in and we use Azure Active Directory for the access management across the organization.

Kind regards,

Boris.

[BA_Pete]

I think the only thing I can suggest at this point is to raise a ticket with MS PBI Support (free for Pro/Premium licence holders):

https://powerbi.microsoft.com/en-us/support/ 

They'll be able to read the diagnostic logs that they'll likely get you to collect and give a definitive answer on where the issue lies.

Sorry I can't be more help.

Pete