Unable to connect on premise sql server from Sql Azure db I have two SQL Server databases:


On-Premise : See the image below.



We have a static IP associated with this server in order to access remotely from other development machines. Machine is deployed in Location A and is accessible from Location B or C which are geographically separated. 

To connect using SSMS, from Location B or C we are using IP Address/UserName/Password. Neither instance name is required nor FQDN. Only IP.
On Azure : Another database is on Azure. Hosted as a Service there. No VM. Its migrated from one of our On-Premise database, which was earlier accessible from #1. We are planned to move our all database to azure , but as of now, we need a cross communication for few months, until we migrate completely.


Problem

We are using a linked server in our queries, and they are failing as Azure does not support linked servers. So in order to access on premise from Azure db we are using External/Table and data-sources but still its giving me connectivity issues.

Error


  Msg 46823, Level 16, State 1, Line 1
  Error retrieving data from XXX.XXX.157.236.TableBackup.
  The underlying error message received was: '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)'.


Kindly help me fixing this issue.
My suggestion is to gradually migrate databases from SQL Server on-premises to SQL Azure but keeping the original databases on-premises and have them synchronize with Azure using SQL Data Sync as explained here. You then will be able to access your databases on premises if you need without the latency of linked server or you can access them on SQL Azure if you want. 

Alternatively you can use replication as explained here.

Hope this helps.

Regards,

Alberto Morillo