When we connect to our Azure SQL Databases or Azure SQL Managed Instances databases using the "Proxy" connection policy and the session is still open without any activity, the Azure SQL Gateway will kill the session after being idle for more than 30 minutes.
When we use the connection policy "Redirect" the Gateway won't kill the idle sessions after about 30 minutes.
In Azure SQL we have two connections policies:
Azure SQL Database connectivity architecture - Azure SQL Database and Azure Synapse Analytics | Microsoft Docs
- Proxy: the default option when you connect from on-premises without any ExpressRoute or Site-to-Site VPN. This policy requires port 1433 to open to connect to the Azure SQL Database gateway.
- Redirect: the default option when you connect within Azure or from On-premises if you have implemented an ExpressRoute or Site-to-Site VPN. This policy requires ports 1433 to connect to the Azure SQL Database gateway and 11000 to 11999 to establish connections directly to the node hosting the database. If you are connecting through a Private Endpoint Connection (Private Link), the port range is 1433 to 65535.
With this information, we have six different scenarios:
| WITHIN AZURE: | |
| Connection Policy: PROXY | The session will be killed by the Azure SQL Gateway after about 30 minutes of being inactive (idle). |
| Connection Policy: REDIRECT | The session won't be killed by the Azure SQL Gateway. |
| Connection Policy: DEFAULT (REDIRECT) | The session won't be killed by the Azure SQL Gateway. |
| FROM ON-PREMISES (No VPN/ExpressRoute): | |
| Connection Policy: PROXY | The session will be killed after about 30 minutes of being inactive (idle). |
| Connection Policy: REDIRECT | The session won't be killed by the Azure SQL Gateway. |
| Connection Policy: DEFAULT (PROXY) | The session will be killed after about 30 minutes of being inactive. |
In addition to these scenarios, we need to consider another variable "Private Links" (Private Endpoint Connections). If a "Private endpoint connection" is enabled on our Azure SQL Server, the default connection policy within Azure, Site-to-Site VPN, and ExpressRoute is set to "Proxy", so all sessions will be killed after approximately 30 minutes of being inactive (idle). However, you have the option to select the "Redirect" to force this connection policy. In the "Private EndPoint Connection (Private Link)" + "Redirect" scenario the port range is 1433 to 65535.
When you connect from on-premises without Site-to-Site VPN and ExpressRoute you can force it to use "Redirect", and the connections will be handled directly by the node that hosts the database, and then your session won't be killed by the Azure SQL Gateway.
These scenarios are easy to test.
You can open a Command Prompt (cmd) and run the following command on your Azure SQL Server:
And then monitor the idle session on your SSMS for example:
-- Run this script in your user database
DECLARE program varchar(20) = '';
DECLARE @msg varchar(50) = '';
DECLARE @session int = 57; -- type the correct spid
Set program = (SELECT [program_name]
FROM sys.dm_exec_sessions
WHERE session_id=@session and program_name='SQLCMD');
WHILE program is not null
BEGIN
Set program = ( SELECT [program_name]
FROM sys.dm_exec_sessions
WHERE session_id=@session and program_name='SQLCMD'
);
SELECT @msg = program + ' ' + convert(varchar(20),getdate());
RAISERROR(@msg,10,1) WITH NOWAIT
-- Wait for 1 minute
WAITFOR DELAY '00:01:00';
END
print '*** END TIME: ' + convert(varchar(20),getdate());
SELECT [program_name],session_id, DATEDIFF(minute,last_request_start_time,GETDATE()) [idle_minutes], last_request_start_time
FROM sys.dm_exec_sessions
WHERE session_id=@session and program_name='SQLCMD'
Finally, the session was killed after 32 minutes:
Let's see how it works when we connect to the Azure SQL Database through a Private Endpoint Connection (Private Link)
I have enabled "Private Access" at the Azure SQL Server level:
The private IP address is 172.170.0.6
And then we force "Redirect" connection policy:
Now, we will connect from an Azure Virtual Machine (VM):
We will connect to the Azure SQL Server through SSMS and then run the following networking commands:
nslookup srv-sqldb-test.database.windows.net
With this command we observe that the Azure DNS (168.63.129.16) is resolving the private IP address of the Private Endpoint Connection instead of the Public IP address of the Public Endpoint.
netstat -ano | findstr 172.17.0.6
With this command we observe that the Azure VM's private IP address(172.17.0.4) is connecting to the Private Endpoint Connection's private IP address (172.17.0.6) and port 22906 from the ports range 1433 to 65535.
Let's run the test T-SQL code:
We are going to check if the idle spid 78 will be killed after 30 minutes without any activity:
After 30 minutes we observe that the Azure SQL Gateway doesn't kill the idle session 78:
What happen when we select "Default" Connection Policy at Azure SQL Server level? in this case all connections will use the "Proxy" option, therefore the Gateways will kill idle sessions after 30 minutes:
We run again the T-SQL test (idle spid = 82):
The Azure SQL Gateway killed the session after 30 minutes:
Microsoft
