From ADF connect to Azure SQL using AAD with Allow Azure services and resources access disabled

Question

I am working on ADF for ETL/ELT project, currently I am using Azure SQL database to load source data. To connect to Azure SQL database from ADF pipeline I have to enable "Allow Azure services and resources to access this server" whereas this has security threat anyone can connect to this server in Azure environment.

Hence I wanted to disable this option and but also I would like to connect to Azure SQL database from ADF pipeline. Is Azure AAD user may help in this case? I know about whitelisting some IP's but ADF's IP's are dynamic for everyrun hence it is very hard for me to whitelist few of them.

Hi CSharpDev, If my answer is helpful for you, hope you can accept(mark) it as answer. This can be beneficial to other community members. Thank you. — Leon Yue

Leon Yue Leon Yue · Accepted Answer · 2020-07-03T08:50:50

Azure we know, if we don't set enable "Allow Azure services and resources to access this server" in, the only way to access the SQL database is that we must set the Data factory client IP to database firewall.

Or we will get the error like bellow:

As you said "ADF's IP's are dynamic for everyrun hence it is very hard for me to whitelist few of them", a good news is that Azure Data Factory support static IP range as the firewall role.

Data Factory product team provides the workaround for us:

Great news – static IP range for Azure Integration Runtime is now available in all ADF regions! You can whitelist specific IP ranges for ADF as part of firewall rules. The IPs are documented here: https://docs.microsoft.com/en-us/azure/data-factory/azure-integration-runtime-ip-addresses#azure-integration-runtime-ip-addresses-specific-regions. Static IP ranges for gov cloud and China cloud will be published soon!

Please refer to this blog post on how you can use various mechanisms including trusted Azure service and static IP to secure data access through ADF: https://techcommunity.microsoft.com/t5/azure-data-factory/azure-data-factory-now-supports-static-ip-address-ranges/ba-p/1117508

Service tag support will be made available in next few weeks. Please stay tuned!

If your network security requirement calls for ADF support for VNet and cannot be met using Trusted Azure service (released in Oct 2019), static IP range (released in Jan 2020), or service tag (upcoming), please vote for VNet feature here: https://feedback.azure.com/forums/270578-data-factory/suggestions/37105363-data-factory-should-be-able-to-use-vnet-without-re

Please reference this feedback: Static IP ranges for Data Factory and add ADF to list of Trusted Azure Services

You could get the data factory static IP ranges and add the list to Azure SQL database firewall roles.

Data Factory static IP list example:

{
      "name": "DataFactory",
      "id": "DataFactory",
      "properties": {
        "changeNumber": 6,
        "region": "",
        "platform": "Azure",
        "systemService": "DataFactory",
        "addressPrefixes": [
          "13.66.143.128/28",
          "13.67.10.208/28",        
          ...,
          ...
               
        ]
      }
    }

Hope this helps.

From ADF connect to Azure SQL using AAD with Allow Azure services and resources access disabled

1 Answers