Hello,
I posted this issue in the Power BI community but to answer at all. I hope someone can give me some light.
I am developing a report for a customer. The source is a on-premises SQL Database. The customer gave me credentials and open the SQL Database just for my Static IP. From my computer or any other physical computer through my VPN works fine.
The customer doesn't want to install a Gateway, so as alternative, I thought using a Azure Virtual Server that I can automatically start and stop once a day just for the update of the report.
This Azure VM is connected with a OpenVPN Client to my VPN. So it's the same static IP (I've tested it).
When I open the report in the VM and go to Power Query, all the queries against the SQL Database work fine but the ones that have some Merge step. It breaks when the merge is expanded:
DataSource.Error: Microsoft SQL: 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Details:
DataSourceKind=SQL
DataSourcePath=app.sciomac.com;SciomacProd
Message=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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
ErrorCode=-2146232060
Number=53
Class=20
The source of the queries are:
QUERY1
Source = Sql.Databases("app.sciomac.com"),
SciomacProd = Source{[Name="SciomacProd"]}[Data],
dbo_Calls = SciomacProd{[Schema="dbo",Item="Calls"]}[Data]
QUERY2
Source = Sql.Databases("app.sciomac.com"),
SciomacProd = Source{[Name="SciomacProd"]}[Data],
dbo_Videos = SciomacProd{[Schema="dbo",Item="Videos"]}[Data]
Individually both queries connect to the SQL Database without any problem, but when I make a merge step the query breaks when expanded.
I have the same issue if I tried to connect to a PostgreSQL db
Any idea how to solve it?
Thank you for your help.
Miguel
