How to correctly set firewall rules for RDMS external data sources

Question

When setting a RDMS extenal data source and table like the following:

CREATE EXTERNAL DATA SOURCE preview
WITH 
(
    TYPE=RDBMS,
    LOCATION= 'xxxxxxxx.database.windows.net',
    DATABASE_NAME = 'preview',
    CREDENTIAL= preview
)
GO
CREATE SCHEMA preview;
GO

GO
CREATE EXTERNAL TABLE preview.entity_types
(
    entity_type_id int NOT NULL,
    entity_type_name varchar(128) NOT NULL
)
WITH (DATA_SOURCE=preview);
GO

If you execute a SELECT statement like the following:

SELECT *
FROM preview.entity_types

You receive an error like this:

Error retrieving data from xxxxxxxx.database.windows.net.preview. The underlying error message received was: 'Cannot open server 'xxxxxxxx' requested by the login. Client with IP address 'xxx.xxx.xxx.xxx' is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.'.

I know how to set a firewall rule to enable access for client ip address 'xxx.xxx.xxx.xxx'. But I think that IP is dynamic, it may change, therefore some day will stop working.

So, Which is the correct way to allow access to the client SQL Azure database?

Client IP could be a range.Do you know you the range of the dynamic IP, and can it achieve your purpose？ — Leon Yue
@LeonYue, I don't know the range. It's assigned by Azure and I don't know how to figure out the range. — Jesús López

Alberto Morillo Alberto Morillo · Accepted Answer · 2019-02-18T11:57:38

If you are configuring elastic queries (cross database queries) in Azure SQL Database, it should work if you set "Allow access to Azure Services" to "On" on Azure SQL Database firewall rules. This way elastic queries won't fail when Azure SQL Databases change their IP address.

A more restricted way to configure Azure SQL Database firewall to allow elastic queries currently does not exist. You can vote for this feature here so it can be considered in the future by Azure SQL Database team.

How to correctly set firewall rules for RDMS external data sources

1 Answers