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.
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.
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.