Using Odata feed on Azure client whose Data Source is configured against DMG on Azure VM
Recently we have come across many requests where people wanted to install DMG gateway on Azure VM and then use Odata feed exposed by those data sources in Excel client.
This is possible, not recommended and needs little careful settings regarding which ports should be open and which are not needed.
Supportability of this scenario might change in future as this is rapidly changing technology.
To start with steps need to be taken f we want this scenario to work,
Add TCP\IP endpoint for TCP port 1433 (SQL port) on Azure VM Dashboard
Public Port:1433
Private Port: 1433
Data Refresh from Excel workbook sheet:
DataSource.Error: OData: Request failed: Unable to connect to the remote server
Details:
https://microsoft891.hybridproxy.powerbi.com/ODataService/v1.0/Azure
Test Connection from PowerBI admin on client box:
Login timeout expired A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. Named Pipes Provider: Could not open a connection to SQL Server [53].
Added TCP\IP endpoint for TCP port 8051 on Azure VM Dashboard
Public Port:8051
Private Port: 8051
Data Refresh from Excel workbook sheet:
Data Comes back
Test Connection from PowerBI admin on client box:
Successful.
One very important thing to take care when DMG is installed on Azure VM or any cloud VM , we need to add one entry in host file where we will map public IP of VM to netbios name of VM
If we can't ping netbios name of VM from client box then when we refresh connection from excel client it will give error similar to,
DataSource.Error: OData: Request failed: The remote name could not be resolved: 'sql2014dk'
Details:
https://microsoft891.hybridproxy.powerbi.com/ODataService/v1.0/Azure
So edit host file
<Public IP of VM> <netbios name of VM>
And after this follow other instructions mentioned above.
So not many steps but to summaries
n Host file should have entry pointing public IP of Azure VM to netbios name of Azure VM
n Need to add two endpoints for VM 8051 (port on which you have configured DMG) and port 1433 (SQL port)
n Both this ports need to be part of Azure VM firewall rules exception
If even after this steps if you do not get needed data try adding TCP port 80 and 443 into firewall exception and see if that makes any different.
Let me know how this goes and if you have any questions for me.