Forum Discussion

sciomac's avatar
sciomac
Frequent Visitor
5 years ago

Unable to refresh Power BI from Azure Virtual Machine connected to an on-premises SQL database

Hello,

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.

 

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.xxxxxxxxx.com;RxxxxxPxxx
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

 

 

 

Any idea how to solve it?

 

Thank you for your help.

 

Miguel

Need Help

2 Replies

  • jaideepnema's avatar
    jaideepnema
    Solution Sage
    5 years ago

    Hi sciomac ,

    Are you merging tables from two separate databases from two separate servers ? or its only a single server having a single DB Instance ?

    • sciomac's avatar
      sciomac
      Frequent Visitor
      5 years ago

      Hi jaideepnema 

       

      I am not an expert in SQL but here it is the source of both queries:

       

      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],

       

      Thank you