Today I worked with a customer that were not able to connect to their Azure SQL Database from their Azure environment. They have protected their environment using different appliances and just having the port 1433 for outbound connections.
We found that using Microsoft OLE DB Provider for SQL Server our customer is able to connect but using SQL Server Native Client they don't.
But first, let's try to explain first what is the mechanism used to connect to Azure SQL Database when the client is running inside Azure:
- The first connection will be to the Gateway using the port 1433.
- This gateway will validate the connection and provide to the client another Database Server IP and another port (11000-14000) to connect. This process is called redirection.
In this situation, Microsoft OLE DB Provider for SQL Server aka SQLOLEDB is using a TDS version less than 7.4 and when our Gateway detects the TDS version the redirection is not taking effect, for this reason, our customer is able to connect without problems. Also, I would like to mention that we have a new driver for OLE DB called OLE DB Driver for SQL Server aka MSOLEDBSQL that is compatible with Azure SQL and use the latest TDS version.
And when our Gateway detects that the driver is TDS 7.4 or above, for example, using the driver SQL Server Native Client 11.0 the redirection will take place and if our customer don't have opened the ports (11000-14000) the connection will be not established.
So, in this situation, we fixed the problem, changing the default connection from re-direct/default to proxy to use only the port 1433 as you could see in this URL.
In our YouTube channel you could find more information about it.