Sunday, April 29, 2018

Power BI: Error 'Unable to connect' while connecting to SQL database which was working earlier.

Think about a sceniro where you developed a power BI solution and published it. However, all of a sudden your data is not refreshed and you are not able publish with updated data. As long as you click 'refresh' from Power BI desktop it gives error saying as like below fig 1.

Fig 1: Unable to Connect error message

You don't have any clue what has happened since you did not change anything. OK!! Though you did not change anything in your .pbix file, however;  Microsoft changed something in January 2018 version. yes, they have updated the credential type at that version. So before Jan 2018 version of power BI had three options like below:
Fig 2: Credential Type before Jan 2018 version


The credential type after the version Jan 2018 look like fig 3:

Fig 3: Credential after Jan 2018

So if your solution was developed before Jan 2018 and you may likely face this situation.

What we do to fix it?

There are three steps process to resolve the issue as follows:

Step 1: Find data source settings
Open the .pbix by using power BI desktop and then find data source settings, which you will find as like fig 4.

Fig 4: Go to settings of data source

Step 2: Edit permissions
When you click data source setting you will find Edit permissions window will look like fig 5.

Fig 5: Edit permission

                                                 
                                 

Step 3: Change the credential type

If you look at the below fig 6, the credential type has changed from 'organizational account' to 'Microsoft account',  if your .pbix is built before jan 2018 version of Power BI then you need to change the option from 'organizational account' to 'Windows' and then it's should work as it used to.

Fig 6: Credential Type