This blog won’t be very long and will describe a connection issue I experienced with a customer including the solution and some context why this solution worked. I hope this saves you time when dealing with the same error.
Issue
The customer can connect to the Azure Sql database with Sql Server Management Studio (SSMS) but not with a specific client application.
When digging into the logs (all logs were activated for this database), nothing shows up for the specific login used by the client application. The application itself returns a connection error caused by a time-out.
The application resides outside of Azure and can’t use a VPN connection, the Azure Sql Server has a specific firewall rule to allow incoming traffic from this specific IP address. Not a situation I’m really happy with, but it happens.
Analysis
First we checked different firewall settings and on the outbound firewall from the client, there was a rule with a specific public IP address, linked to the Azure Sql Server. when you check out the list of public IP adresses that Azure can use, you’ll find that there are many available. The first step was to change the firewall rule from IP adress to FQDN (Fully Qualified Domain Name).
But this still didn’t solve the issue. The connection still didn’t work.
Solution
Then we checked out the different connection settings on the Azure Sql Server and found this.
The policy was set to Redirect. Something we do by default as it speeds up data transfer between VM’s and databases. But apparently can kill connections coming from outside of Azure using specific connection strings.
But changing the setting from Redirect to Default made all the difference. The client application could connect and load data into the target database.
Why did this work?
Digging into the documentation presented me with the answer.
The customer only allowed port 1433 to connect to the FQDN. If you’re using the default, this will work. With Redirect, you need to open ports 11000 to 11999 as well to establish the connection. Something that could work against your company security policy.
Reitse's blog 