When we connect to Azure SQL Databases or Managed Instances using the “Proxy” connection policy, idle sessions are closed after 30 minutes of inactivity. However, with the “Redirect” policy, idle sessions are not closed after 30 minutes.

In Azure SQL we have two connections policies:

Proxy: This is the default option for connecting from on-premises without ExpressRoute or a Site-to-Site VPN. It requires port 1433 to be open to connect to the Azure SQL Database gateway.

Redirect: This is the default option for connections within Azure or from on-premises if you’re using ExpressRoute or a Site-to-Site VPN. It needs port 1433 for the gateway and ports 11000 to 11999 to connect directly to the database node. If you’re connecting via a Private Endpoint (Private Link), the port range is 1433 to 65535.

We have six different scenarios, with this information:

Within Azure:

• Connection Policy: PROXY – The session will be closed by Azure SQL Gateway after about 30 minutes of inactivity.
• Connection Policy: REDIRECT – The session will not be closed by Azure SQL Gateway.
• Connection Policy: DEFAULT (REDIRECT) – The session will not be closed by Azure SQL Gateway.

From On-Premises (No VPN/ExpressRoute):

• Connection Policy: PROXY – The session will be closed after about 30 minutes of inactivity.
• Connection Policy: REDIRECT – The session will not be closed by Azure SQL Gateway.
• Connection Policy: DEFAULT (PROXY) – The session will be closed after about 30 minutes of inactivity.

When using “Private Links” (Private Endpoint Connections) with Azure SQL Server, the default connection policy (even within Azure, Site-to-Site VPN, or ExpressRoute) is set to “Proxy,” meaning idle sessions will be closed after about 30 minutes. However, you can choose the “Redirect” policy to avoid this. In the “Private Endpoint” with “Redirect” scenario, the required port range is 1433 to 65535.

If you’re connecting from on-premises without Site-to-Site VPN or ExpressRoute, you can also force the “Redirect” policy. This will allow the connections to be handled directly by the database node, and your session won’t be closed 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:

Monitor the idle session on your SSMS

Use below query to monitor:

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'

Mohammad Gufran Jahangir