Last modified at 1/7/2015 2:17 PM by koen@zomers.eu

​Using Business Connectivity Services (BCS) in SharePoint you can connect to other sources like SQL Servers or OData webservices. SharePoint Online also allows for connecting to other systems using the BCS functionality. If you wish to connect SharePoint Online to a SQL database hosted in Azure, you need to whitelist all IP ranges from which SharePoint Online could be making outbound connections on your Azure SQL Server instance. Since there are 59 ranges, good luck with doing that manually.

I've written a PowerShell script which will do this automatically for you. Open up a PowerShell window, connect to Azure using your preferred way of using credentials or a subscription file, replace the $AzureSqlServerName variable with the name of your Azure SQL Server Database instance and execute the script:

$AzureSqlServerName = "xxxxxxxxxx"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 1" -StartIPAddress "192.159.34.1" -EndIPAddress "192.159.34.30"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 2" -StartIPAddress "42.159.38.0" -EndIPAddress "42.159.39.255"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 3" -StartIPAddress "42.159.162.0" -EndIPAddress "42.159.162.31"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 4" -StartIPAddress "42.159.166.0" -EndIPAddress "42.159.167.255"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 5" -StartIPAddress "65.52.45.0" -EndIPAddress "65.52.45.255"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 6" -StartIPAddress "65.55.22.32" -EndIPAddress "65.55.22.63"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 7" -StartIPAddress "70.37.151.64" -EndIPAddress "70.37.151.95"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 8" -StartIPAddress "94.245.113.160" -EndIPAddress "94.245.113.191"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 9" -StartIPAddress "104.146.0.0" -EndIPAddress "104.146.255.255"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 10" -StartIPAddress "111.221.17.160" -EndIPAddress "111.221.17.191"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 11" -StartIPAddress "111.221.20.128" -EndIPAddress "111.221.20.255"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 12" -StartIPAddress "111.221.22.32" -EndIPAddress "111.221.22.63"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 13" -StartIPAddress "111.221.22.192" -EndIPAddress "111.221.22.223"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 14" -StartIPAddress "111.221.64.160" -EndIPAddress "111.221.64.191"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 15" -StartIPAddress "111.221.67.0" -EndIPAddress "111.221.67.127"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 16" -StartIPAddress "134.170.200.0" -EndIPAddress "134.170.207.255"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 17" -StartIPAddress "134.170.208.0" -EndIPAddress "134.170.215.255"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 18" -StartIPAddress "134.177.0.0" -EndIPAddress "134.177.255.255"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 19" -StartIPAddress "157.55.43.32" -EndIPAddress "157.55.43.63"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 20" -StartIPAddress "157.55.46.128" -EndIPAddress "157.55.46.159"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 21" -StartIPAddress "157.55.56.0" -EndIPAddress "157.55.56.31"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 22" -StartIPAddress "157.55.62.96" -EndIPAddress "157.55.62.127"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 23" -StartIPAddress "157.55.62.128" -EndIPAddress "157.55.62.159"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 24" -StartIPAddress "157.55.103.0" -EndIPAddress "157.55.103.31"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 25" -StartIPAddress "157.55.144.64" -EndIPAddress "157.55.144.127"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 26" -StartIPAddress "157.55.145.192" -EndIPAddress "157.55.145.223"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 27" -StartIPAddress "157.55.147.0" -EndIPAddress "157.55.147.31"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 28" -StartIPAddress "157.55.151.192" -EndIPAddress "157.55.151.223"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 29" -StartIPAddress "157.55.152.128" -EndIPAddress "157.55.152.255"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 30" -StartIPAddress "157.55.153.0" -EndIPAddress "157.55.153.31"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 31" -StartIPAddress "157.55.153.64" -EndIPAddress "157.55.153.127"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 32" -StartIPAddress "157.55.154.64" -EndIPAddress "157.55.154.95"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 33" -StartIPAddress "157.55.225.160" -EndIPAddress "157.55.225.191"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 34" -StartIPAddress "157.55.225.224" -EndIPAddress "157.55.225.255"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 35" -StartIPAddress "157.55.227.128" -EndIPAddress "157.55.227.159"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 36" -StartIPAddress "157.55.229.0" -EndIPAddress "157.55.229.127"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 37" -StartIPAddress "157.55.229.160" -EndIPAddress "157.55.229.191"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 38" -StartIPAddress "157.55.231.32" -EndIPAddress "157.55.231.63"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 39" -StartIPAddress "157.55.232.0" -EndIPAddress "157.55.232.31"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 40" -StartIPAddress "157.55.235.64" -EndIPAddress "157.55.235.95"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 41" -StartIPAddress "157.55.238.128" -EndIPAddress "157.55.238.159"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 42" -StartIPAddress "157.56.24.128" -EndIPAddress "157.56.24.159"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 43" -StartIPAddress "157.56.48.0" -EndIPAddress "157.56.48.31"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 44" -StartIPAddress "157.56.80.128" -EndIPAddress "157.56.80.159"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 45" -StartIPAddress "157.56.81.192" -EndIPAddress "157.56.81.255"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 46" -StartIPAddress "157.56.113.0" -EndIPAddress "157.56.113.31"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 47" -StartIPAddress "157.56.132.128" -EndIPAddress "157.56.132.191"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 48" -StartIPAddress "157.56.150.32" -EndIPAddress "157.56.150.63"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 49" -StartIPAddress "191.232.0.0" -EndIPAddress "191.232.1.255"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 50" -StartIPAddress "191.234.8.0" -EndIPAddress "191.234.15.255"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 51" -StartIPAddress "191.234.76.0" -EndIPAddress "191.234.77.255"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 52" -StartIPAddress "191.234.128.0" -EndIPAddress "191.234.135.255"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 53" -StartIPAddress "191.234.144.0" -EndIPAddress "191.234.159.255"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 54" -StartIPAddress "191.234.148.0" -EndIPAddress "191.234.151.255"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 55" -StartIPAddress "191.234.152.0" -EndIPAddress "191.234.153.255"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 56" -StartIPAddress "191.234.192.0" -EndIPAddress "191.234.223.255"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 57" -StartIPAddress "191.235.0.0" -EndIPAddress "191.235.15.255"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 58" -StartIPAddress "207.46.203.128" -EndIPAddress "207.46.203.159"
New-AzureSqlDatabaseServerFirewallRule -ServerName $AzureSqlServerName -RuleName "SharePoint Online BCS 59" -StartIPAddress "213.199.179.0" -EndIPAddress "213.199.179.31"

If you now go to the Azure Management portal, go to SQL Databases, click on the servers tab at the top, click on your server instance, click on the configure tab at the top, you should see all firewall rules having been added:

SQLAzureFirewallRulesSharePointOnline.png