When trying to fetch data from a data source, be it an XLS, SQL, or Excel file, Power BI fails to connect. In this post, we will discuss this issue and see what can be done if Power BI is unable to connect and encounters an error while trying to connect. So, if you are facing this issue, this post is for you.
What is the gateway connection error in Power BI?
If you encounter a gateway error in Power BI, it means that the information of your data source does not match the underlying dataset. To resolve this issue, ensure that the server and database are accurate and consistent between the data source defined on the on-premises data gateway and the one you specified in the Power BI Desktop.
Power BI Unable to connect, We encountered an error while trying to connect
If Power BI cannot connect to the data source and has encountered an error while trying to connect, you might be facing one of the following issues.
- Power BI fails to register data sources for any gateway instances for that particular cluster even though the gateway is mostly online
- Power BI refuses to connect to any data source
- Power BI refuses to authorize the source, stating unauthorized access
- Power BI: Connection to SQL Server from Excel failed
Let us talk about them in detail.
1] Power BI fails to register data sources for any gateway instances for that particular cluster even though the gateway is mostly online
When Power BI cannot register a data source for a gateway instance, it will show you the following error message in the Data Source field.
Unable to connect: We encountered an error while trying to connect to filename.xlsx. Details: We could not register this data source for any gateway instances within this cluster.
If you get that error, you must try giving the full UNC Path. And the UNC Path is nothing but the name of the server. So, for example, if the name of my server is FS1, my UNC Path should be \\FS1. If you are giving a string that contains your domain name as well, it will most probably not work. Therefore, contact your IT admin and ask them to provide you with the exact server name. Once you have the server, enter the aforementioned format, and it will work.
2] Power BI refuses to connect to any data source
Another instance of this error is when Power BI refuses to connect to connect to a data source. Some of us face this issue when trying to connect to an HTML source and encounter the following error message.
Whereas, some are facing this problem when connecting to an HTML source and get this error.
We encountered an error while trying to connect.
Details: “The downloaded data is HTML, which isn’t the expected type. The URL may be wrong or you might not have provided the right credentials to the server.”
When connecting to an Excel workbook, one gets the following error.
Details: “Excel Workbook: The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine.
In the case of an HTML file, we still use an Excel file as a source, so why not create an offline copy of the same file and use it as the data source? However, if you still want to import it from an online platform like OneDrive, you must make the following changes.
- Open the Power BI Desktop.
- From the top, Home > Get data > Web.
- In From Web, select Basic and paste the link in the given field.
- If Power BI Desktop asks for credentials, choose Windows for on-premises SharePoint or Organizational Account for Microsoft 365 or OneDrive for work or school sites.
You will then be presented with a dialog box where you can enter all the details, where you can navigate by following the on-screen instructions, and you will be good to go
However, if you cannot import an Excel file, there is no need to indulge in all these shenanigans; you can just do what the error message says. That is, download the client software from the mentioned link. However, we would recommend you contact your IT admin and ask them for the appropriate link to download the tools, as there might be some company policy against downloading something from an external source.
Read: Microsoft Power Query for Excel helps with data discovery
3] Power BI refuses to authorize the source, stating unauthorized access
Power BI refuses to authorize the data with the following error messages.
Unable to connect
We encountered an error while trying to connect.
Details: “You do not have access to this resource.”
Unable to connect
We encountered an error while trying to connect.
Details: “Access to this resource is forbidden.”
To resolve this issue, there are a few things we can do. First of all, make sure that you have logged in using the correct account. One of our acquaintances logged in using their Powerdobs account and tried to access a Power BI, which was destined to fail. So, make sure your account is correct.
If the account is correct, you are supposed to edit credential settings. It’s actually pretty easy. You need to go to Edit Queries > Data Source Settings, and you can get to all the remembered credentials. To resolve the issue, you need to clear the unwanted passwords that may have been saved to the SharePoint directory in question. The error message you received was generic and can be resolved just by cleaning the redundant and unwanted passwords.
Read: How to share Excel Insights with Power BI Publisher for Excel
4] Power BI: Connection to SQL Server from Excel failed
When trying to import tables or data from SQL Server into Power BI, we are able to connect to SQL, but when trying to access the database, it says that the connection failed. We get the following error when trying to do so.
Unable to connect
We encountered an error while trying to connect.
Details: An error happened while reading data from the provider: ‘Could not load file or assembly. Either a required impersonation level was not provided, or the provided impersonation level is invalid. (Exception from HRESULT:0x80070542)
If you get this error, first import SQL DB tables to Excel, you need to go to Home > From Other Sources > From SQL Server. Now, you must connect to SQL DB using Windows or SQL Authentication.
If that doesn’t work, keep the following points in mind.
- Check the login details and server name
- Login to SQL Server Management Studio with the same details
- Turn off the proxy.
- Turn off the firewall.
- Check if all TCP\IP configurations in the configuration manager are enabled.
- Check all set to port 1433
That’s it.
Read: Power BI unable to connect, Please delete your credentials and try again later
How do I fix SSL provider error 0?
When encountering SSL Provider error 0, you can resolve the issue by selecting Trust Server Certificate = True to accept the certificate provided, which is usually caused by a Certificate Validation issue. Hopefully, this will do the job for you.
Also Read: Fix Power BI Desktop login issues.
