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; } } } }
When using proxy on localhost teams fails to establish TCP connection for login
This is a strange problem, and it took us quite a while to get to the bottom of it. We tested on Windows 10 and Windows 11 with the latest Teams app. What is the problem? We are working on a solution the monitors network traffic for a safe school environment. As part of that we install a local proxy on Windows devices. The proxy is configured as manual proxy in the proxy settings. When requesting to sign into Teams, Teams establishes a connection to `login.microsoftonline.com`. Without the proxy that works without problems. Teams establishes a TCP connection, sends a CONNECT, TLS handshake and then encrypted data. When activating the local proxy any connection is fine (e.g. `teams.events.data.microsoft.com`, `nav.smartscreen.microsoft.com` are established just fine), but the one to `login.microsoftonline.com` is not established. In Teams this results in an error page that the login page can't be reached (it reports a 404, but it's not a 404). We ensured that nothing was blocked and there are no lower level connection errors, then we dug deeper with Wireshark. Once accessing the login page (which would trigger connection making for `login.microsoftonline.com`) we see a `SYN` being sent to establish the TCP connection, but there is never a `SYN/ACK`. The local proxy never receives this `SYN` (no connection is ever established), somehow it never reaches the destination but is "dropped". We can see that Teams tries to re-transmit the SYN, but it never arrives at the destination. A tcp `SYN` not reaching it's destination points to it being blocked somewhere, so we: - Turned the firewall off (anything that can be turned off is turned off) - Added specific allow rules for any other device control that could block. This did not help, the problem keeps persisting. (Note: We were able to reproduce this problem with a different proxy solution as well. Once we configure the proxy on localhost we run into this issue.) We also tried: - Configure the local proxy with the IP address of the machine within the local network (i.e. 10.12.128.2) instead of `127.0.0.1`: Leads to the same problem. - Run the local proxy on another Windows machine, turn off firewall and connect to the proxy on the other machine: No problems with this solution, the connection is established fine. We then tried to configure the proxy via PAC file (without a DIRECT fallback), which result in different weird behavior. In the browser the behavior is as expected - pages that are blocked by the proxy report a connection failure, pages that are allowed can be accessed. For Teams login we don't run into problems with the PAC file based settings, but Teams does not route the connection to `login.microsoftonline.com` through the proxy! The connection to `login.microsoftline.com` somehow implicitly bypasses the system wide proxy settings when using a PAC file (i.e. I see the connection in Wireshark, but it's directly established, not going through the proxy even though it is configured via PAC file). Other connections related to Teams are established through the proxy as expected. I don't think this is expected behavior. I have Wireshark traces that show all the different behaviors. If needed I'm happy to add them to this ticket. It would be great to get help with a better understanding what could be the problem. What could cause this behavior (in Teams / in Windows)? With a better understanding of the behavior we could work on overcoming this problem.
Adding Custom Header to Microsoft Teams Login Requests Using mitmproxy Causes HTTP 404 Error
Hi Team, We are using mitmproxy to add a custom header to all requests during the login process in the Microsoft Teams Windows application. The purpose of adding this custom header is to verify that the request is coming from a specific device by checking this header on our sso server. Unfortunately, we are unable to bypass the login URLs. Adding the custom header is a crucial part of our verification process. However, we are encountering the following issue: When using mitmproxy to modify requests, the Microsoft Teams app fails to connect and shows the error: "We can't connect you. HTTP 404 login.microsoftonline.com" Is there a way to add a custom header to requests from the Teams app without breaking the connection to the Microsoft login endpoint? We appreciate any guidance on how to resolve this issue or insights into how Teams validates its login requests to avoid potential interference. Looking forward to your reply. ThanksDefender for Endpoint and disconnected environments. Which proxy configuration wins?
This article is a follow-up to a previous one discussing conflicting proxy configurations and how Microsoft Defender for Endpoint behaves in these situations. The first article can be found in here. In this article we'll explore how Defender for Endpoint network traffic flows depending on which proxy configuration is in use, as well as what network traffic looks like when all three proxy configurations are set.Lesson Learned #431: Determining Connection Type to Azure SQL Database: Proxy or Redirect
When connecting to Azure SQL Database, it's important to understand the type of connection established. Azure offers two primary connection policies: Proxy and Redirect. Knowing which one you're using can be crucial for performance considerations and troubleshooting.Clearing WPAD cache and settings (solution)
Hi all, I want to share the solution to clear all WPAD cache and settings on a Windows 10 or 11 machine. Step 1: Regedit Computer\HKEY_USERS\S-1-5-19\SOFTWARE\Microsoft\Windows\CurrentVersion\Internet Settings\Wpad: delete all underlying keys Computer\HKEY_CURRENT_USER\SOFTWARE\Microsoft\Windows\CurrentVersion\Internet Settings\Connections: delete the keys "DefaultConnectionSettings" and "SaveLegacySettings" Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\WinHttpAutoProxySvc: in the value "start", change the data value from "3" to "4 Step 2: Internet Options Advanced tab > Reset Internet Explorer Settings (incl Delete personal settings) > Reset Step 3: CMD as admin ipconfig /flushdns Step 4: reboot the machine twice Clearing the WPAD can be useful when getting this output from dsregcmd /status "Auto Detect PAC Status : Failed to auto detect the Proxy Auto-Configuration (PAC) script using WPAD. code: 0x80072f94" (Hybrid Azure AD Join Failure)
WPAD File Cache
We are soon to be moving away from using our WPAD/PAC file for web browsing access on our clients and select servers. We are aware that the WPAD file is cached on the machine locally so I was hoping someone could help me understand a couple of things: 1. Where is the wpad.dat file cached on client machines? 2. How long is the wpad.dat file cached for once the client machine loses access to the hosting server? We noticed when we remove the auto-detect checkbox from the browser that some system processes are still using the WPAD file whilst the browser starts routing direct. Hope you can help!Add SOCKS5 (+ Authentication) support to Edge insider browser
Hi, Please consider adding SOCKS5 proxy support and Authentication (username and password) to the new Edge browser. currently only Firefox has that capability (without Auth). Microsoft Edge insider is using the same networking settings as google chrome which is not ideal and heavily relies on extensions and 3rd party programs. please create a separate proxy settings page that only effects Edge browser (like Firefox) and Not the whole Windows OS, and add SOCKS5 support to it. Thanks
