Speak guys!
In this post, I would like to share with you a situation that I have witnessed in some 4 clients in the last 3 months and 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 are outside the corporate network and without the need for a VPN, but the server only recognizes internal addresses (IP / Hostname) and it cannot configure Power BI Gateway correctly because it gives error when it tries to add the source of data.
So I decided to create this article with two extremely simple solutions so that it can be useful to people in the community, not just my BI clients.
Simulating and explaining the problem
Click to view content- Within the same server network or using a VPN, access to the instance is done using an internal hostname (dirceu-vm) or IP (192.168.0.27, for example)
- Outside the network, there are some virtual machines with fixed IPs that have direct access to the company's network and access the server through a public IP, directing ports and releasing these IP's in the Firewall. In other words, access outside this network to this server is done using the public IP (Ex .: 189.123.111.222) or public host (accessexterno.dirceuresende.com)
- Some people who develop reports on Power BI are within the network and others access via public IP.
- Power BI Gateway is installed and configured on the database server, with the data source already created.
The external user can access the bank and create the report normally within Power BI Desktop:
When a user from within the network publishes the report, it works normally. But when a user doing external access publishes a report, this error message happens:
And then when you click on “view details”, you will see this error message:
Let's try adding the data source to 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 the requested instance cannot be found:
And now ?
Resolving the connection issue
In this case, the first test I do to understand exactly what is going on before blaming Gateway is to try to access this logged in address on the server where Gateway is installed:
Not surprisingly, the server is not recognizing this external address that these users are accessing, so the connection to the Gateway is not working. The same thing would happen if external access were to 189.123.123.123 IP, for example, and the bank server internal IP was 192.168.0.27, for example.
In order to make the gateway server can recognize this hostname or external IP, we can use these 2 solutions that I will demonstrate below (if you know others, leave them in the comments):
Solution 1 - Alias in SQL Server Configuration Manager
Click to view content
Don't forget to create the alias in x64 settings as well:
After this note, the connection now functions 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 affects only SQL Server:
Solution 2 - Hosts file
Click to view contentFor this annotation 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 annotation:
And in doing so, any software that tries to use this hostname, for any protocol or port, will try to connect to the IP given in the hosts file (192.168.31.230, in this case).
As well as SQL Server Management Studio itself (SSMS), Power BI and many others:
Testing the solution on Power BI Gateway
Now that I've demonstrated 2 ways to do this, let's test if they really work for my original goal of using this data source in my cloud-published Power BI report.
Successful connection:
Running gateway and report dataset associated with data source:
And as a result, reporting working for both the accessor using the internal network address and the external address 🙂
I hope you enjoyed this tip, a big hug and see you in the next article!
