If you’ve ever opened a packet capture for an Azure SQL Database connection, you’ve seen a very “SQL-shaped” conversation riding on top of TCP: Tabular Data Stream (TDS). TDS is the application-layer request/response protocol used by SQL clients to talk to SQL Server family services—including Azure SQL Database. It’s the on-the-wire format that carries login/authentication, encryption negotiation, SQL batches / RPC calls, and result sets back to the client.
This blog breaks TDS down from a networking-layer perspective (what connects to what, and which ports) and from a protocol-layer perspective (what messages and phases exist, and why you see certain patterns like “connect → redirect → connect again”). Along the way, we’ll connect the theory to real-world troubleshooting patterns you’ll recognize from Azure SQL support cases.
Reference: Connectivity architecture - Azure SQL Database and SQL database in Fabric | Microsoft Learn
1) First principles: what TDS is (and what it’s not)
At a high level, TDS is an application-layer protocol that “facilitates interaction with a database server,” including authentication and channel encryption negotiation, the ability to send SQL requests (including bulk insert), invoke RPC/stored procedures, and return data and transaction manager requests.
Reference: [MS-TDS]: Tabular Data Stream Protocol | Microsoft Learn
Two important clarifications:
- TDS is not “the connection string.” A connection string is a client-side concept—your driver parses it and then sends the relevant information over the wire via TDS during login.
-
TDS is not tied to one specific transport. TDS requires that a network transport connection is established first (typically TCP); the choice of transport is not important to TDS itself.
2) Azure SQL connectivity architecture: why the “gateway” matters
In Azure SQL Database, clients don’t initially connect straight to a “database VM.” Instead, the public entry point is a gateway that listens on TCP 1433. The official flow is:
- Client connects to the gateway (public IP, port 1433)
- Based on the effective connection policy, the gateway either proxies traffic or redirects the client to the correct database cluster
- Inside the cluster, traffic is forwarded to the appropriate database
This is where many “it works here but not there” mysteries come from—because the first TCP connection (to 1433) may not be the last TCP connection (especially in Redirect mode).
3) Proxy vs Redirect (and the port story your firewall cares about)
Azure SQL logical servers support three effective policies: Default, Proxy, and Redirect.
Proxy
- The TCP session is established via the gateway on 1433, and all subsequent packets continue to flow through the gateway (simpler firewall story, higher latency/less throughput).
Redirect (recommended for performance)
- The client first connects to the gateway on 1433.
- Then the client is redirected to the node hosting the database, and traffic flows directly to the database node, bypassing the gateway for the rest of the session (lower latency / improved throughput).
-
Networking requirement: the client must allow outbound traffic not only to gateway IPs on 1433, but also to Azure SQL addresses in the region on ports 11000–11999.
Default
- Default behavior depends on where the client is:
- Inside Azure → Redirect by default
- Outside Azure → Proxy by default
That “inside/outside Azure” default is a frequent root cause for intermittent connectivity issues when a corporate firewall or appliance allows 1433 but not the Redirect port range.
4) Connection lifecycle: what happens on the wire (step-by-step)
Let’s walk through the typical Azure SQL Database login flow, from the first SYN to the first query result.
This diagram summarizes the "classic" TDS login as: Prelogin -> TLS handshake -> Login -> Login ack.
Step A — TCP handshake (transport layer)
Your client establishes a TCP connection (often to the Azure SQL gateway on 1433 as the first hop).
Step B — TDS PRELOGIN (application layer “setup”)
Before authentication happens, a Pre-Login handshake occurs to set up context like encryption and MARS (Multiple Active Result Sets).
Reference: [MS-TDS]: Pre-Login | Microsoft Learn
In the protocol spec, PRELOGIN is described as a message sent by the client to set up context for login; the server replies with a PRELOGIN response containing a PRELOGIN structure.
The PRELOGIN options include fields/tokens for things like VERSION, ENCRYPTION, and MARS, among others.
Step C — TLS handshake (encryption)
In TDS 7.x, TLS negotiation is handled within the TDS layer, and encryption is negotiated during PRELOGIN; if encryption is agreed/required, an SSL/TLS handshake follows.
The diagram explains that PRELOGIN exchanges non-sensitive options, then TLS happens, and then LOGIN follow with sensitive data (username/password/database names) protected by encryption.
Step D — LOGIN / authentication
After TLS is established, the client sends the login message and receives a login acknowledgement.
From here, the connection is a long-lived session for request/response exchanges (queries, stored procedures, results).
Reference: TDS 8.0 - SQL Server | Microsoft Learn
5) The “Redirect dance”: why you sometimes see two TCP connections
In Redirect mode, it’s normal to see a pattern like:
- Connect to gateway on 1433
- Receive redirect information
- Immediately connect again to a different port on the destination (for example, 6188 in a real support case) Lesson Learned #520: Troubleshooting Azure SQL Database Redirect Connection over Private Endpoint | Microsoft Community Hub
A Microsoft support “lesson learned” shows exactly this in practice: the client first connects to 1433, then attempts a second connection to a dynamic port (example 6188), and it was the second leg that timed out due to outbound firewall restrictions.
Why it matters:
If your network only allows 1433, the initial handshake might succeed but the session fails on the redirected connection—leading to confusing errors like “server not found/not accessible” even though DNS and 1433 look fine.
6) TDS 7.x vs TDS 8.0: what changed, and why strict encryption matters
Historically, TDS could be configured in ways where encryption was optional or disabled. To align with “mandatory encryption” expectations, TDS 8.0 was introduced.
Key changes:
- TDS 7.x: TCP handshake → TDS PRELOGIN in cleartext → TLS handshake → authentication encrypted → data exchange could be encrypted or unencrypted (depending on config).
-
TDS 8.0: TCP handshake → TLS handshake first → then TDS messages (including PRELOGIN) are inside TLS → authentication and data exchange are encrypted.
Microsoft Learn summarizes this as: “The TLS handshake now precedes any TDS messages, wrapping the TDS session in TLS to enforce encryption.
TDS 8.0 is positioned as compatible with TLS 1.3 (and also TLS 1.2) and is associated with Encrypt=strict behavior in drivers.
Practical implication: if you’re troubleshooting handshake failures, you need to consider both the SQL driver’s encryption defaults and the OS/TLS capabilities. The TDS 8.0 doc includes a compatibility matrix tying TDS version, TLS version, OS version, and encryption options together.
7) Troubleshooting guide: what to look for in traces (and what to check first)
A. If connections fail only from certain networks / gateways
Start with the policy and ports story:
- Is the server set to Redirect, Proxy, or Default? (Default varies based on client location.)
-
If Redirect is in effect, does the network allow outbound 11000–11999 in addition to 1433?
B. If you see “Redirected: …,” in errors
Treat it as a strong hint you’re failing on the second leg of Redirect mode. The support example shows the second port attempt (6188) timing out because outbound dynamic ports were blocked.
C. If you need to explain “what happens first” to customers
A simple way to describe it (accurate and non-hand-wavy) is the Microsoft Learn sequence:
- Client connects to the gateway on 1433
- Gateway proxies or redirects traffic
- Inside the cluster, traffic is forwarded to the database
That short narrative often resolves confusion when customers assume “1433 must be the database port” in every scenario.
8) Wrap-up: mental model you can keep
When you troubleshoot Azure SQL connectivity, always separate the problem into two layers:
- Network path & policy layer: Gateway entry point, Proxy vs Redirect, ports and firewall rules, and how “Default” behaves by client location.
-
Protocol & security layer: TDS phases (PRELOGIN → TLS → LOGIN → data exchange) and which TDS generation/encryption mode you’re effectively using (TDS 7.x vs TDS 8.0).
Once you train yourself to spot the “two-connection Redirect pattern” and to map failures to either port reachability or handshake/protocol issues, a huge number of real-world cases become much faster to triage and explain.
References
Azure SQL connectivity architecture (gateway, Proxy/Redirect, port requirements): Connectivity architecture - Azure SQL Database and SQL database in Fabric | Microsoft Learn
TDS Open Specification (protocol overview): [MS-TDS]: Tabular Data Stream Protocol | Microsoft Learn
PRELOGIN details and encryption negotiation (TDS 7.x): [MS-TDS]: PRELOGIN | Microsoft Learn
TDS 8.0 (TLS before TDS messages, encrypt=strict): TDS 8.0 - SQL Server | Microsoft Learn
Real-world Redirect troubleshooting example (second port failure): Lesson Learned #520: Troubleshooting Azure SQL Database Redirect Connection over Private Endpoint | Microsoft Community Hub
Microsoft