Client IP access error when connecting to Azure SQL Database from an App Service

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…

Firewall settings Azure SQL DB

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

Azure App Service Outbound IP address

According to Microsoft the set of outbound IP addresses for your app changes when you perform one of the following actions:

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.

Don’t forget to save your changes…

Fix the ‘Your client IP address does not have access to the server.’ error when connecting to an Azure SQL Database from SQL Server Management Studio

If you’ve just created an Azure SQL Database and are trying to connect to it from SQL Server Management Studio you may get an access error saying…

Your client IP address does not have access to the server.

To resolve this you need to amend the firewall settings on the server.  To do this navigate to your new database and click ‘Set server firewall’…

Go to the new Azure SQL Database

 

… and then on the Firewall settings page click ‘Add client IP‘ to have Azure populate a firewall rule for you as shown below. Don’t forget to click Save afterwards.

 

Add client IP in the firewall settings page

How to select more than 1000 rows by default in SQL Server Management Studio

In SQL Server Management Studio when we right-click a table we have an option to ‘Select Top 1000 Rows’ and ‘Edit Top 200 Rows’ as shown below…

Select Top 1000 Rows

SQL Server Management Studio chooses these relatively low defaults to prevent unresponsive GUIs however we can increase these if we wish through SSMS options…

Configure how many records to view/edit

How to set scripting defaults in SQL Server Management Studio

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.

How to set scripting defaults in SQL Server Management Studio

After setting these defaults I can now bypass the advanced settings screen on almost all occasions which is a nice little time saver.