Power BI cannot connect, an error was encountered while trying to connect

Power BI may encounter difficulties when it cannot connect to a data source for an XLS, SQL, or Excel file. This article will explore possible solutions to help you resolve this issue. This article will guide you on what to do if you encounter errors or connection failures during the connection process. So, if you are facing this problem, keep reading and we will provide you with some useful suggestions.

What is the gateway connection error in Power BI?

Gateway errors in Power BI are often caused by a mismatch between the data source information and the underlying dataset. To solve this problem, you need to ensure that the data source defined on the local data gateway is accurate and consistent with the data source specified in Power BI desktop.

Power BI cannot connect, we encountered an error when trying to connect

If Power BI cannot connect to a data source and encounters an error when trying to connect, you may be facing one of the following issues.

Power BI cannot register a data source for any gateway instance for this specific cluster, even though the gateway is online most of the time

Power BI refuses to connect to any data source

Power BI denies authorized source, stating unauthorized access

Power BI: Connection from Excel to SQL Server failed

Let’s talk about it in detail.

1]Power BI cannot register a data source for any gateway instance for this specific cluster, even though the gateway is online most of the time

When Power BI fails to register a data source for a gateway instance, it displays the following error message in the data source domain.

Unable to connect: An error was encountered while trying to connect to filename.xlsx. Details: We cannot register this data source for any gateway instance in this cluster.

When encountering this error, you must be sure to provide an accurate UNC path. The UNC path only includes the server name. If the server name is fs1, the UNC path should be \fs1. Avoid including the domain name in the string and contact your IT administrator for the exact server name. Entering the correct format will solve the problem.

2]Power BI refuses to connect to any data source

Power BI refusing to connect to a data source is a common problem, especially when trying to connect to HTML source code. In this case, the user may encounter an error message stating that the connection cannot be established.

However, some people have encountered this problem when connecting to HTML sources and got this error.

An error was encountered while trying to connect.

Details: "The downloaded data is Hypertext Markup Language, which is not the expected type. The URL may be wrong, or you may not have provided the correct credentials to the server."

When connecting to an Excel workbook, the following error occurs.

Details: "Excel workbook: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local computer.

In the case of the HTML file, we are still using the Excel file as the 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 have to make the following changes.

Open the Power BI desktop.

Start at the top, Home > Get Data > Network.

Select Basic from Web and paste the link into the given field.

If Power BI Desktop asks for credentials, select Windows for SharePoint on-premises, Organization Account for Microsoft 365, or OneDrive for a work or school site.

You will then see a dialog box where you can enter all your details, you can follow the on-screen instructions to navigate, and you're good to go

But if you can't import an Excel file, there's 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 link mentioned above. However, we recommend that you contact your IT administrator and ask them for the appropriate link to download the tool, as the company may have some policies prohibiting downloading certain content from external sources.

Read: Microsoft Power Query for Excel helps with data discovery

3]Power BI rejects the authorized source and declares unauthorized access

Power BI refuses to authorize the data with the following error message.

unable to connect

An error was encountered while trying to connect. Details: "You do not have permission to access this resource."

unable to connect

An error was encountered while trying to connect. Details: "Access to this resource is prohibited."

To solve this problem, we can do several things. First, make sure you're logged in with the correct account. An acquaintance of ours logged in using their Powerdobs account and tried to access Power BI, which was bound to fail. Make sure your account is correct.

If the account is correct, the credential settings should be edited. This is actually very simple. You can access all remembered credentials simply by going to Edit Query >Data Source Settings. To resolve the issue, you need to clear out unwanted passwords that may have been saved to the problematic SharePoint directory. The error message you receive is generic and can be solved by simply clearing out redundant and unnecessary passwords.

Read: How to share Excel insights with Power BI Publisher for Excel

4]Power BI: Failed to connect to SQL Server from Excel

When trying to import table or data from SQL Server to Power BI, we can connect to SQL but when trying to access the database it shows connection failed. When we try to do this we get the following error.

unable to connect

An error was encountered while trying to connect. Details: Error reading data from provider: 'Cannot load file or assembly. The required impersonation level was not provided, or the impersonation level provided was invalid. (HRESULT exception: 0x80070542)

If this error occurs, please import the SQL DB table to Excel first, you need to go to the home page from SQL Server from other sources. Now you have to connect to SQL DB using Windows or SQL Authentication.

If this doesn't work, here are a few things to keep in mind.

• Check login details and server name
• Log in to SQL Server Management Studio using the same details
• Close the proxy.
• Turn off the firewall.
• Check whether all TCPIP configurations in the configuration manager are enabled.
• Check all ports set to port 1433

that's all

The above is the detailed content of Power BI cannot connect, an error was encountered while trying to connect. For more information, please follow other related articles on the PHP Chinese website!