IP whitelisting in SQL server

Question

Hello,
The Allow Azure service property has been disabled at the server level. Because of which we need to whitelist the IP range of the region in which Azure data factory belongs for ADF to connect to Azure SQL DB.

        "addressPrefixes": [
          "13.69.230.96/28",
          "13.74.108.224/28",
          "20.38.80.192/26",
          "20.38.82.0/23",
          "20.50.68.56/29",
          "52.138.229.32/28",
          "2603:1020:5:1::480/121",
          "2603:1020:5:1::500/122",
          "2603:1020:5:1::700/121",
          "2603:1020:5:1::780/122",
          "2603:1020:5:402::330/124",
          "2603:1020:5:802::210/124",
          "2603:1020:5:c02::210/124"
        ],

This is the Address range for the North Europe ADF.
Can some help me as to how to convert this values into the format of startIp and EndIP to get it added under firewall setting of server for 2 different scenarios:

1) "13.69.230.96/28"
2) 2603:1020:5:1::480/121",

Answer 1

Hi @Nandan Hegde , welcome to Microsoft Q&A forum.

Anything after '/' represents the subnet mask of network and there is way to determine the IP range based on it. IP address is basically a 32 bit number divided into 4 bytes. few Examples below:

255.255.255.255 -> 11111111 11111111 11111111 11111111  
255.255.255.128 -> 11111111 11111111 11111111 10000000  
0.0.0.0 -> 00000000 00000000 00000000 00000000  

When we add a subnet to it, it will represent it in below binary formats:

/28 -> 11111111 11111111 11111111 11110000. This means we have range from '0000' to '1111' for this subnet mask (16)  
/27 -> 11111111 11111111 11111111 11100000. This means we have range from '00000' to '11111' for this subnet mask (32)  

And so on and so forth.

Now if we calculate the IP range of your example (13.69.230.96/28), it will be as below.

Network Address: 13.69.230.96/28  
Broadcast: 13.69.230.111  
Range of usable IP: 13.69.230.97 to 13.69.230.110  

Another example of 13.74.108.224/28:

Network Address: 13.74.108.224/28  
Broadcast: 13.74.108.239  
Range of usable IP: 13.74.108.225 to 13.74.108.238  

You can use below calculator for more conversions:
http://jodies.de/ipcalc?

Second type of IP example you provided was related to IPv6 which is not supported to be added to Azure SQL firewall yet. If we try to add it, error message 'IP Address must be a valid IPv4 address' pop ups.

Please let me know if this helps or else we can discuss further.

----------

If answer helps, please select 'Accept Answer' as it could help other community members looking for similar query.