Lesson Learned #519: Reusing Connections in Azure SQL DB: How Connection Pooling Caches Your Session

A few days ago, I was working on a case where a customer reported an unexpected behavior in their application: even after switching the connection policy from Proxy to Redirect, the connections were still using Proxy mode.

After investigating, we found that the customer was using connection pooling, which caches connections for reuse. This meant that even after changing the connection policy, the existing connections continued using Proxy mode because they had already been established with that setting. The new policy would only apply to newly created connections, not the ones being reused from the pool.

To confirm this, we ran a test using .NET and Microsoft.Data.SqlClient to analyze how the connection pool behaves and whether connections actually switch to Redirect mode when the policy changes.

How Connection Pooling Works

Connection pooling is designed to reuse existing database connections instead of creating a new one for every request. This improves performance by reducing latency and avoiding unnecessary authentication handshakes.

However, once a connection is established, it is cached with the original settings, including:

• Connection policy (Proxy or Redirect)
• Authentication mode
• Connection encryption settings

This means that if you change the connection policy but reuse a pooled connection, it will retain its original mode. The only way to apply the new policy is to create a new physical connection that does not come from the pool.

Testing Connection Pooling Behavior

For Testing the connection pooling behavior, I developed this small code in C# that basically, opens the connection, provides information about the port using and close the connection. Repeating this process 10000 times. 

The idea was to track active connections and check if the port and connection policy were changing after modifying the connection policy.

Initially, I attemped to use netstat -ano to track active connections and monitor the local port used by each session. Unfortunately, in Azure SQL Database, local port information is not reported, making it difficult to confirm whether a connection was truly being reused at the OS level.

Despite this limitation, by analyzing the session behavior and connection reuse patterns, we were able to reach a clear conclusion.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;

namespace InfoConn
{
    using System;
    using System.Data;
    using System.Diagnostics;
    using System.Text.RegularExpressions;
    using System.Threading;
    using Microsoft.Data.SqlClient;

    class Program
    {
        static void Main()
        {
            string connectionStringProxy = "Server=tcp:servername.database.windows.net,1433;Database=db1;User Id=user1;Password=..;Pooling=True;";
            Console.WriteLine("Starting Connection Pooling Test");
            for (int i = 0; i < 10000; i++)
            {
                using (SqlConnection conn = new SqlConnection(connectionStringProxy))
                {
                    conn.Open();
                    ShowConnectionDetails(conn, i);
                }
                Thread.Sleep(5000); 
            }
            Console.WriteLine("Test complete.");
        }

        static void ShowConnectionDetails(SqlConnection conn, int attempt)
        {
            string query = "SELECT session_id, client_net_address, local_net_address, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID;";
            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"[Attempt {attempt + 1}] Session ID: {reader["session_id"]}");
                        Console.WriteLine($"[Attempt {attempt + 1}] Client IP: {reader["client_net_address"]}");
                        Console.WriteLine($"[Attempt {attempt + 1}] Local IP: {reader["local_net_address"]}");
                        Console.WriteLine($"[Attempt {attempt + 1}] Auth Scheme: {reader["auth_scheme"]}");
                    }
                }
            }
            RetrievePortInformation(attempt);
        }

    static void RetrievePortInformation(int attempt)
        {
            try
            {
                int currentProcessId = Process.GetCurrentProcess().Id;
                Console.WriteLine($"[Attempt {attempt + 1}] PID: {currentProcessId}");

                string netstatOutput = RunNetstatCommand();

                var match = Regex.Match(netstatOutput, $@"\s*TCP\s*(\S+):(\d+)\s*(\S+):(\d+)\s*ESTABLISHED\s*{currentProcessId}");

                if (match.Success)
                {
                    string localAddress = match.Groups[1].Value;
                    string localPort = match.Groups[2].Value;
                    string remoteAddress = match.Groups[3].Value;
                    string remotePort = match.Groups[4].Value;

                    Console.WriteLine($"[Attempt {attempt + 1}] Local IP: {localAddress}");
                    Console.WriteLine($"[Attempt {attempt + 1}] Local Port: {localPort}");
                    Console.WriteLine($"[Attempt {attempt + 1}] Remote IP: {remoteAddress}");
                    Console.WriteLine($"[Attempt {attempt + 1}] Remote Port: {remotePort}");
                }
                else
                {
                    Console.WriteLine($"[Attempt {attempt + 1}] No active TCP connection found in netstat.");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"[Attempt {attempt + 1}] Error retrieving port info: {ex.Message}");
            }
        }

        static string RunNetstatCommand()
        {
            using (Process netstatProcess = new Process())
            {
                netstatProcess.StartInfo.FileName = "netstat";
                netstatProcess.StartInfo.Arguments = "-ano";
                netstatProcess.StartInfo.RedirectStandardOutput = true;
                netstatProcess.StartInfo.UseShellExecute = false;
                netstatProcess.StartInfo.CreateNoWindow = true;
                netstatProcess.Start();

                string output = netstatProcess.StandardOutput.ReadToEnd();
                netstatProcess.WaitForExit();

                return output;
            }
        }
     }
    }