Unable to connect with On Premise Sql Server from Azure Sql db

Question

I am having two Sql Server Databases:

On-Premise : See the image below. We have a static IPassociated 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.

NOTE

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 linked server in our queries, and they are failing brutally, as Azure do 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.

MORE UPDATE

I am using external tables to connect Sql Azure with my on premise server. See the code below:

--CREATE MASTER KEY ENCRYPTION BY PASSWORD = '##########';
--Needs to be username and password to access SQL database
CREATE DATABASE SCOPED CREDENTIAL SqlUsercred WITH IDENTITY = '####user', SECRET = '########';


CREATE EXTERNAL DATA SOURCE Remote1801
WITH 
( 
    TYPE=RDBMS, 
    LOCATION='XXX.XXX.157.236', 
    DATABASE_NAME='TableBackup', 
    CREDENTIAL= SqlUsercred
); 







CREATE EXTERNAL TABLE oeordhExt(
    [Entity] [varchar](14) NOT NULL,
    [ordnumber] [char](22) NOT NULL,
    [SHIPTO] [char](6) NOT NULL,
    [SHPNAME] [char](60) NOT NULL,
    [SHPADDR1] [char](60) NOT NULL,
    [SHPADDR2] [char](60) NOT NULL,
    [SHPADDR3] [char](60) NOT NULL,
    [SHPADDR4] [char](60) NOT NULL,
    [SHPCITY] [char](30) NOT NULL,
    [SHPSTATE] [char](30) NOT NULL,
    [SHPZIP] [char](20) NOT NULL,
    [SHPCOUNTRY] [char](30) NOT NULL,
    [SHPPHONE] [char](30) NOT NULL,
    [SHPFAX] [char](30) NOT NULL,
    [SHPCONTACT] [char](60) NOT NULL
)    
WITH
(
  DATA_SOURCE = Remote1801,
  SCHEMA_NAME = 'dbo', --schema name of remote table
  OBJECT_NAME = 'oeordh' --table name of remote table
);

select * from         oeordhExt

`

RESULT

Msg 46823, Level 16, State 1, Line 53 Error retrieving data from 180.150.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)'.

is this occuring continously or a one time issue – TheGameiswar Jul 28 '17 at 08:57 — TheGameiswar, Jul 28 '17 at 08:57

score 0 · Answer 1 · answered Jul 27 '17 at 12:10

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

answered Jul 27 '17 at 12:10