1

I have two SQL Server databases:

  • On-Premise : See the image below.

    enter image description here

    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.

closed as off-topic by LowlyDBA, Philᵀᴹ, Colin 't Hart, mustaccio, Marco Jun 18 '18 at 14:03

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – LowlyDBA, Philᵀᴹ, mustaccio, Marco
If this question can be reworded to fit the rules in the help center, please edit the question.

  • I don't think you mention that, but you can connect to that Azure db using SSMS? – Renato Afonso Jul 27 '17 at 7:53
  • It seems you are trying to connect using : provider: Named Pipes Provider instead of tcp connection. – McNets Jun 17 '18 at 11:08
0

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.