Search code examples
sql-serverazure-sql-databaselinked-server

Azure Linked Tables Requires Firewall Rule?

DatabaseA and DatabaseB are both on the same Azure server: myhost.database.windows.net. I'm implementing linked tables to access tables from DatabaseA within DatabaseB.

As follows:

CREATE EXTERNAL TABLE ext.Customers
(   
  [CustomerID] int NOT NULL,
  [CustomerName] varchar(50) NOT NULL,
  [Company] varchar(50) NOT NULL
)
WITH
( 
  DATA_SOURCE = MyElasticDBQueryDataSrc,
  SCHEMA_NAME = N'dbo',  
  OBJECT_NAME = N'Customers'  
)

However, when I first tried to access the linked table I got a firewall access violation for a 40.x.x.x address. Strange, but OK, I'll cooperate. I added the rule. The following evening I get a different firewall access violation for a different 40.x.x.x address. Hmm, this is becoming unworkable. I need these linked tables to be available 24x7 and I can't keep manually adding these firewall rules. So a few questions -

  1. Why is a firewall rule necessary in this first place? Shouldn't the cross-database access use the internal 10.x.x.x VNet?

  2. Is there something special I need to do to configure the link to use the internal VNet?

  3. If it's not possible to use the internal VNet is there a defined list of 40.x.x.x addresses which comprehensively covers the entire range of 40.x IP addresses I need to set to keep this link up and running?

Solution

  • It seems like you have not enabled access to Azure services on your database server.

    You can enable this from the "Firewall and virtual networks" blade.

    enter image description here

    It is not possible to use the internal VNet IP address to configure access.

    If you are not comfortable allowing access to all Azure services, you can configure the firewall to selectively allow the IP address ranges of AzureSQL for the region your database is provisioned in.

    A list Azure IP ranges and service tags by region is available for download here

    Please post here if you face any further issues.