If you need to connect to an Azure SQL Database from an App Service and are getting a ‘Client with IP address ‘XXX’ is not allowed to access the server.’ error you need to amend the server firewall settings in one of two ways…
1 – Allow access for ALL Azure resources
Switch the ‘Allow Azure services and resources to access this server‘ button on in the server firewall settings page. This allows all resources in the Azure boundary to access the server (not the DB).
2 – Allow access for certain IPs
Add IP based rules for the App Services outbound IP addresses in the DB server firewall settings page.
Each app will have a set number of outbound IPs at any time and the specific IP is selected randomly at runtime so you need to grant access to all outbound IP addresses listed. To find the addresses go to your App Service in the portal and navigate to Settings -> Properties…
Delete an app and recreate it in a different resource group (deployment unit may change).
Delete the last app in a resource group and region combination and recreate it (deployment unit may change).
Scale your app between the lower tiers (Basic, Standard, and Premium) and the Premium V2 tier (IP addresses may be added to or subtracted from the set).
The ‘Outbound IP addresses’ section above shows the current IP addresses based on your current tier but ‘Additional Output IP Addresses’ shows all possible outbound IPs regardless of tier. Therefore when you’re adding your IP whitelist, I recommend using the additional list just in case you change SKU in the future.
In SQL Server Management Studio when you use the DB (right click) -> Tasks -> Generate Scripts option to export a DBs schema you’ll see that by default SSMS does not script either indexes or triggers (among other items).
For a long time I found this quite annoying as every single time I wanted to export a schema I had to go into the advanced settings and explicitly tell SSMS to export indexes and sometimes triggers depending if they were used or not.
Thankfully I have since found out that it is possible to set defaults for all scripting options…
Example 1 below (click for a larger view in a new window) shows the default advanced scripting options with neither indexes or triggers set to be scripted.
Example 2 below shows how we can set new defaults for these and other scripting options.
After setting these defaults I can now bypass the advanced settings screen on almost all occasions which is a nice little time saver.