Hey guys!
In this post, I would like to share with you a situation that I have seen in about 4 clients in the last 3 months and I always have to explain the same thing when users access the bank using an external IP or hostname, created so that users can connect to these data sources outside the company network and without the need for a VPN, but the server only recognizes the internal addresses (IP/Hostname) and it cannot configure the Power BI Gateway correctly because it gives an error when it tries to add the data source.

Therefore, I decided to create this article, with two extremely simple solutions, so that it can be useful to people in the community, and not just my BI clients.

Simulating and explaining the problem

Click to view content
To understand this scenario, imagine the following:
  • Within the same network as the server or using a VPN, access to the instance is made using an internal hostname (dirceu-vm) or IP (192.168.0.27, for example)
  • Outside the network, there are some virtual machines with a fixed IP that have direct access to the company's network and access the server through a public IP, directing ports and releasing these IPs in the Firewall. In other words, access outside the network to this server is done using the public IP (Ex: 189.123.111.222) or public host (acessoexterno.dirceuresende.com)
  • Some people who develop reports in Power BI are inside the network and others access it via public IP
  • Power BI Gateway is installed and configured on the database server, with the data source already created

The user who accesses externally can access the database and create the report normally within Power BI Desktop:

When a user inside the network publishes the report, it works normally. But when a user with external access publishes a report, this error message occurs:

There is no gateway to access the DW data source

And then when you click on “show details”, you will see this error message:

Let's try to add the data source to the Gateway then:

I configure the connection data normally:

But when I try to add the data source, we come across a very common message, which is shown when it is not possible to locate the requested instance:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)

And now ?

Solving the connection problem

In this case, the first test I do to understand exactly what is happening before blaming the Gateway is to try to access this address logged on the server where the Gateway is installed:

As expected, the server is not recognizing this external address that these users are accessing, and therefore, the connection to the Gateway is not working. The same thing would happen if the external access was to IP 189.123.123.123, for example, and the bank server's internal IP was 192.168.0.27, for example.

To be able to make the gateway server recognize this hostname or external IP, we can use these 2 solutions that I will demonstrate below (if you know others, leave them here in the comments):

Solution 1 – Alias ​​in SQL Server Configuration Manager

Click to view content
One way to be able to do a “From X To” on SQL Server IPs or hostnames is by using the SQL Server Configuration Manager Alias ​​feature:

Don't forget to create the alias in x64 settings as well:

It is important to configure the Native Client driver in both the 32-bit (x86) and 64-bit (x64) versions, as some software that accesses the database only exists in the 32-bit version (e.g. SSMS) and others are usually used in the 64-bit version (e.g. Power BI Desktop).
Attention: Pay attention to the protocol and port of the SQL Server instance where the connection will be pointed. If this information is wrong, the connection will not be made. An advantage of this approach is precisely choosing what will be affected by this note and it works with both external hostname and external IP. The downside of this solution is that it is specific to SQL Server

After this note, the connection now works normally within the server where the Gateway is located (and in this case, the bank as well):

A simple test assures me that this note only affects SQL Server:


Solution 2 – Hosts file

Click to view content
This second alternative is an operating system-level solution and consists of changing the Windows hosts file so that it understands that a certain hostname responds through an IP chosen by you. On Windows, this file is located in C:\Windows\System32\drivers\etc while in Linux, the path is /etc/hosts.

For this note to work, you will need to enter the IP where the chosen host should point, leave a blank space and then enter the hostname that will be affected by this manual note:

And when doing this, any software that tries to use this hostname, for any type of protocol or port, will try to connect to the IP informed in the hosts file (192.168.31.230, in the case of the example).

Just like SQL Server Management Studio (SSMS), Power BI and many others:

Attention: The hosts file only allows you to assign a hostname to an IP. If you access via a public IP only, you will have to use the Alias ​​solution in SQL Server Configuration Manager, start accessing via Hostname instead of IP or use another pointing solution.

Testing the solution on Power BI Gateway

Now that I have demonstrated 2 ways to make this note, let's test whether they will actually work for my original objective, which is to use this data source in my Power BI report published in the cloud.

Connection successful:

Running gateway and report dataset associated with the data source:

And as a consequence of this, the report works both for those who access using the internal address of the network and the external address 🙂

I hope you liked this tip, a big hug and see you in the next article!