0

I've been asked to set up a list on SharePoint Online that's populated from an Azure SQL database via BCS.

I have found some fairly extensive guides on how to do this, such as this one: http://blogs.msdn.com/b/steve_fox/archive/2011/11/12/leveraging-wcf-services-to-connect-bcs-with-sharepoint-online.aspx

but the one detail all of them leave out is how to determine to which IP addresses the Azure SQL firewall should be opened. The ones that do address the firewall at all say to open up the range 0.0.0.0-255.255.255.255 as a "temporary" solution, but don't give any mention of what a non-temporary solution would involve.

How would one go about this? Are the SharePoint Online machines even guaranteed to use a consistent set of IPs? Is leaving the firewall wide open the only option here?

Improve this question

2 Answers 2

Reset to default
2

You can limit to the SharePoint online services IP address ranges: https://support.office.com/en-US/Article/SharePoint-Online-URLs-and-IP-Addresses-99b11563-c42d-4c20-87d2-502e66d62164?ui=en-US&rs=en-US&ad=US

All Office 365 services address ranges are documented here: http://technet.microsoft.com/en-us/library/hh373144.aspx

Improve this answer
2

I've created a PowerShell script to add all the 59 IP ranges mentioned in the SharePoint Online URLs and IP Addresses document as Jim pointed out. Connect to your Azure subscription using PowerShell, replace the $AzureSqlServerName variable so it contains the name of your SQL Azure database instance and run the following PowerShell 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"

More information can be found in my knowledge article at https://knowledge.zomers.eu/SharePoint/Pages/Add-Firewall-Rules-to-SQL-Azure-for-SharePoint-Online-BCS.aspx

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.