How create external table in Azure SQL to access a table in local SQL Server on same subnet. Getting error on select

Question

I have a number of Azure SQL databases that I would like to have access an external table in a local SQL Server database (not named instance). The local SQL Server table has a trigger that can send mail that I want to use since Azure SQL databases don't have mail capabilities that could send an email. Stored procedures in the Azure SQL databases would write to the local database and trigger the event.

I have done the following:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xxxx';
CREATE DATABASE SCOPED CREDENTIAL [SignalRxCredential] 
    WITH IDENTITY='normalLogon',SECRET='normalPassword';  -- SQL Server authentication

CREATE EXTERNAL DATA SOURCE [SignalRxDataSource]    
WITH (
    TYPE = RDBMS,  
    LOCATION = 'sqlserver://10.0.0.6',  
    DATABASE_NAME = 'staging',  
    CREDENTIAL = [SignalRxCredential]
);

CREATE EXTERNAL TABLE [dbo].[EmailNotifications_ext]
(
    [MessageID] [int] NOT NULL,
    [Subject] [varchar](255) NULL,
    [Recipient] [varchar](255) NULL,
    [SendFrom] [varchar](255) NULL,
    [MessageText] [nvarchar](max) NULL
) 
WITH (DATA_SOURCE=[SignalRxDataSource],SCHEMA_NAME=N'dbo',OBJECT_NAME=N'EmailNotifications')

It successfully creates the scoped credential and the external table but when I try to select records from it, I get this error:

An error occurred while establishing connection to remote data source: [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53].
[Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired[Microsoft][ODBC Driver 17 for SQL Server]
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

I've double-checked the credentials and I'm connected to both the Azure SQL server and the local SQL Server is the same SSMS... so both databases are accessible via TCP/IP with SQL Server authentication. Named pipes is enabled and allow remote connections is checked on the local server.

What have I missed and what do I need to do to get this working?

You have mentioned that you are able to connect to both Azure SQL and local SQL Server using SSMS. But is connectivity between Azure and the local SQL Server network enabled? — Shanmugam Sudalaimuthu
– Shanmugam Sudalaimuthu, Commented Nov 10, 2024 at 21:49
Yes, they are on the same subnet. The SQL server is on one of the Azure VMs. — Velocedge
– Velocedge, Commented Nov 11, 2024 at 13:53

Alberto Morillo · Accepted Answer · 2024-11-10 23:17:23Z

1

Azure SQL Database does not support direct connections to SQL Server instances for external tables. This is why you're encountering connection errors.

If you are trying to create that external table on the basis that Azure SQL does not support table triggers, please note that Azure SQL do support triggers as stated on the documentation.

Below you will find how to create a trigger on Azure SQL:

create table testusers  
(userid int,  
[password] varchar(20))  
  
insert into testusers values  
(1,'password1'),  
(2,'password2'),  
(3,'password3')  
  
create table pswlog  
(userid int,  
oldpassword varchar(20),  
newpassword varchar(20),  
updatetime datetime)  

CREATE TRIGGER psw_trigger  
ON testusers  
AFTER  UPDATE  
AS  
IF ( UPDATE ([password]) )  
BEGIN  
insert into pswlog   
select a.userid,c.password,a.password,getdate() from inserted a  
inner join testusers b on a.userid=b.userid  
inner join deleted c on a.userid=c.userid  
END;  

-- Fire the trigger

update  testusers set password='password11' where userid=1  
update  testusers set password='password111' where userid=1  
update  testusers set password='password22' where userid=2 

-- Examine log created by the trigger

select * from pswlog

answered Nov 10, 2024 at 23:17

Alberto Morillo

15.8k3 gold badges29 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Velocedge Over a year ago

Alberto, that is very good information... not so much "does not support direct connections" but at least it answers the question! The reason I wanted to send data to SQL server is so I could send mail from a trigger. So, I was thinking send from Azure SQL to SQL Server (also with a trigger) and send db mail. I've edited the post but I think I'm out of luck and will have to accept your answer.

Alberto Morillo Over a year ago

You can use Azure Logic Apps as trigger. When data on Azure SQL changes the logic apps can trigger send mail using SendGrid. learn.microsoft.com/en-us/archive/blogs/david_burgs_blog/…

Alberto Morillo Over a year ago

learn.microsoft.com/en-us/connectors/sendgrid @Velocedge

Collectives™ on Stack Overflow

How create external table in Azure SQL to access a table in local SQL Server on same subnet. Getting error on select

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related