When SQL Server TLS/TDS Handshake Errors Cause “Invisible” Data Loss — and How to Prevent It

If you’ve been running SQL Server in production for a while, you may have seen cryptic alerts like these pop up in your email or logs:

Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library.
A valid TLS certificate is not configured to accept strict (TDS 8.0 and above) connections. The connection has been closed.

If you’re getting these SQL Server alerts — especially with “Severity 20” — there’s a good chance the problem is happening during the TLS/TDS handshake phase between the client and SQL Server.

On the surface, they might just look like technical warnings, but in reality, these can cause real data loss if your applications don’t handle them correctly.

---

What’s Happening Here?

SQL Server uses the Tabular Data Stream (TDS) protocol to communicate with clients. When a client connects, it negotiates encryption and packet size before any actual SQL statements run.

Two common failure points are:

1. Malformed TDS packet — The packet header says “I’m X bytes long,” but SQL Server reads a different length. This can happen with outdated client drivers, mismatched packet sizes, or middleboxes (firewalls, SSL offloaders) tampering with packets.
2. TLS certificate mismatch with TDS 8.0+ — Modern SQL Server clients (ODBC, ADO.NET, Microsoft.Data.SqlClient) default to encrypted connections with strict validation. If the SQL Server instance doesn’t have a proper TLS cert bound, the handshake fails.

When these issues happen, SQL Server terminates the connection — sometimes before a query even starts, sometimes mid-transaction.

---

Why This Can Lead to Data Loss

SQL Server’s ACID compliance means no partial or corrupt writes: uncommitted transactions are rolled back completely.

The risk is not corruption — it’s missing work.

If your application or job doesn’t retry the transaction after the connection drops:

• The intended insert/update/delete never happens.
• The database stays in its previous state.
• You only notice later when data is missing.

Example:

• An ETL job starts inserting 10,000 rows.
• Connection drops at row 8,000 because of a TLS/TDS handshake problem.
• SQL Server rolls back everything.
• Without retries, those 10,000 rows are never loaded.

---

How to Fix the Root Cause

1. Install and bind a valid TLS certificate
   • CN/SAN must match the server name clients use.
   • Store it in Local Computer → Personal.
   • Give SQL Server’s service account read access to the private key.
   • Bind it via SQL Server Configuration Manager → Protocols → Certificate tab.
2. Update all client drivers
   • Use the latest Microsoft ODBC driver or Microsoft.Data.SqlClient.
   • Remove old SQL Native Client, jTDS, or FreeTDS unless updated.
3. Reset packet size to default (4096) in both SQL Server and client connection strings.

---

Add Retry Logic to Avoid Data Gaps

Even with a fixed TLS cert and updated drivers, transient errors can still happen. Adding retries ensures you don’t lose transactions if a drop occurs.

---

T-SQL TRY/CATCH Retry

DECLARE @RetryCount INT = 0, @MaxRetries INT = 3, @Success BIT = 0;

WHILE @RetryCount < @MaxRetries AND @Success = 0
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
        INSERT INTO Sales (CustomerID, Amount) VALUES (123, 99.99);
        COMMIT TRANSACTION;
        SET @Success = 1;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        SET @RetryCount += 1;
        PRINT CONCAT('Retrying... Attempt ', @RetryCount);
        WAITFOR DELAY '00:00:02';
    END CATCH
END

C# Retry Example

int maxRetries = 3, retries = 0;
bool success = false;

while (!success && retries < maxRetries)
{
    try
    {
        using (var conn = new SqlConnection("Your_Connection_String"))
        {
            conn.Open();
            using (var cmd = new SqlCommand("INSERT INTO Sales (CustomerID, Amount) VALUES (123, 99.99)", conn))
            {
                cmd.ExecuteNonQuery();
            }
        }
        success = true;
    }
    catch (SqlException ex)
    {
        retries++;
        Console.WriteLine($"Retry {retries} after error: {ex.Message}");
        Thread.Sleep(2000);
    }
}

if (!success) Console.WriteLine("Operation failed after retries.");

SQL Agent Jobs

If this is a scheduled job:

• In SQL Server Agent, go to Job Properties → Steps.
• Set Retry Attempts and Retry Interval.
• This is the easiest safeguard for ETL and batch jobs

Extended Events Monitoring Script

If you want to confirm this TLS/TDS handshake issue is happening — and track it after you apply fixes — set up an Extended Events session.

-- 1) Create the XE session
DECLARE @log_path nvarchar(260) = N'C:\XE\TLS_TDS_Watch';  -- change to a valid path

IF NOT EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = 'TLS_TDS_Watch')
BEGIN
    DECLARE @sql nvarchar(max) = N'
    CREATE EVENT SESSION [TLS_TDS_Watch] ON SERVER
    ADD EVENT sqlserver.error_reported(
        ACTION(
            sqlserver.client_app_name,
            sqlserver.client_hostname,
            sqlserver.client_ip,
            sqlserver.session_id,
            sqlserver.database_id,
            sqlserver.sql_text
        )
        WHERE
        (
            severity >= 20
            OR [message] LIKE ''%Length specified in network packet payload did not match number of bytes read%''
            OR [message] LIKE ''%valid TLS certificate is not configured%''
            OR [message] LIKE ''%TDS%''
            OR [message] LIKE ''%handshake%''
        )
    ),
    ADD EVENT sqlserver.login(
        ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.client_ip, sqlserver.session_id)
    ),
    ADD EVENT sqlserver.logout(
        ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.client_ip, sqlserver.session_id)
    ),
    ADD EVENT sqlserver.disconnect(
        ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.client_ip, sqlserver.session_id)
    ),
    ADD EVENT sqlserver.errorlog_written(
        ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.client_ip, sqlserver.session_id)
        WHERE (
            [message] LIKE ''%Length specified in network packet payload%''
            OR [message] LIKE ''%valid TLS certificate is not configured%''
            OR [message] LIKE ''%TDS%''
            OR [message] LIKE ''%handshake%''
        )
    )
    ADD TARGET package0.event_file(
        SET filename = ''' + @log_path + N'\TLS_TDS_Watch.xel'',
            max_file_size = 100,
            max_rollover_files = 10
    )
    WITH (STARTUP_STATE = ON);';
    EXEC sys.sp_executesql @sql;
END
GO

-- Start if not already running
IF NOT EXISTS (
    SELECT 1
    FROM sys.dm_xe_sessions
    WHERE name = 'TLS_TDS_Watch'
)
    ALTER EVENT SESSION [TLS_TDS_Watch] ON SERVER STATE = START;
GO

Query the event file

;WITH x AS
(
    SELECT
        CAST(event_data AS xml) AS x
    FROM sys.fn_xe_file_target_read_file(
        N'C:\XE\TLS_TDS_Watch\TLS_TDS_Watch*.xel', NULL, NULL, NULL
    )
)
SELECT
    x.value('(/event/@name)[1]', 'sysname')            AS event_name,
    x.value('(/event/@timestamp)[1]', 'datetime2(3)')  AS utc_time,
    x.value('(/event/data[@name="message"]/value)[1]', 'nvarchar(max)')   AS message,
    x.value('(/event/action[@name="client_ip"]/value)[1]', 'nvarchar(48)') AS client_ip,
    x.value('(/event/action[@name="client_hostname"]/value)[1]','nvarchar(256)') AS client_hostname,
    x.value('(/event/action[@name="client_app_name"]/value)[1]','nvarchar(256)') AS client_app_name
FROM x
WHERE
    message LIKE '%Length specified in network packet payload%'
    OR message LIKE '%valid TLS certificate is not configured%'
    OR message LIKE '%TDS%'
    OR message LIKE '%handshake%'
    OR message LIKE '%severity%'
ORDER BY utc_time DESC;

Stop and remove when done

IF EXISTS (SELECT 1 FROM sys.dm_xe_sessions WHERE name = 'TLS_TDS_Watch')
    ALTER EVENT SESSION [TLS_TDS_Watch] ON SERVER STATE = STOP;
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = 'TLS_TDS_Watch')
    DROP EVENT SESSION [TLS_TDS_Watch] ON SERVER;

SQL Agent job: nightly import of TLS/TDS XE events

1) Create a table to store events (with a unique key on file + offset)

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'Monitoring')
    EXEC('CREATE SCHEMA Monitoring');

IF OBJECT_ID('Monitoring.XE_TlsTdsEvents') IS NULL
BEGIN
    CREATE TABLE Monitoring.XE_TlsTdsEvents
    (
        event_name           sysname         NOT NULL,
        event_time_utc       datetime2(3)    NOT NULL,
        error_number         int             NULL,
        severity             int             NULL,
        message              nvarchar(max)   NULL,
        client_ip            nvarchar(48)    NULL,
        client_hostname      nvarchar(256)   NULL,
        client_app_name      nvarchar(256)   NULL,
        session_id           int             NULL,
        database_id          int             NULL,
        sql_text             nvarchar(max)   NULL,
        file_name            nvarchar(260)   NOT NULL,
        file_offset          bigint          NOT NULL,
        load_time_utc        datetime2(3)    NOT NULL DEFAULT SYSUTCDATETIME(),

        CONSTRAINT PK_XE_TlsTdsEvents PRIMARY KEY CLUSTERED (file_name, file_offset)
    );

    -- Handy indexes for reporting
    CREATE INDEX IX_XE_TlsTdsEvents_Time ON Monitoring.XE_TlsTdsEvents(event_time_utc);
    CREATE INDEX IX_XE_TlsTdsEvents_Client ON Monitoring.XE_TlsTdsEvents(client_ip, client_app_name);
END
GO

2) Make a stored procedure that imports new events from .xel files

Update @XeFolder to match the folder you used in the XE session (the same path as filename = 'C:\XE\TLS_TDS_Watch\TLS_TDS_Watch.xel').

IF OBJECT_ID('Monitoring.usp_ImportTlsTdsXe') IS NOT NULL
    DROP PROCEDURE Monitoring.usp_ImportTlsTdsXe;
GO
CREATE PROCEDURE Monitoring.usp_ImportTlsTdsXe
    @XeFolder nvarchar(260) = N'C:\XE\TLS_TDS_Watch'
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @pattern nvarchar(400) = @XeFolder + N'\TLS_TDS_Watch*.xel';

    ;WITH src AS
    (
        SELECT
            CAST(event_data AS xml) AS x,
            file_name,
            file_offset
        FROM sys.fn_xe_file_target_read_file(@pattern, NULL, NULL, NULL)
    )
    INSERT INTO Monitoring.XE_TlsTdsEvents
    (
        event_name, event_time_utc, error_number, severity, message,
        client_ip, client_hostname, client_app_name, session_id, database_id, sql_text,
        file_name, file_offset
    )
    SELECT
        x.value('(/event/@name)[1]', 'sysname')                          AS event_name,
        x.value('(/event/@timestamp)[1]', 'datetime2(3)')                AS event_time_utc,
        x.value('(/event/data[@name="error_number"]/value)[1]', 'int')   AS error_number,
        x.value('(/event/data[@name="severity"]/value)[1]', 'int')       AS severity,
        x.value('(/event/data[@name="message"]/value)[1]', 'nvarchar(max)')      AS message,
        x.value('(/event/action[@name="client_ip"]/value)[1]', 'nvarchar(48)')   AS client_ip,
        x.value('(/event/action[@name="client_hostname"]/value)[1]','nvarchar(256)') AS client_hostname,
        x.value('(/event/action[@name="client_app_name"]/value)[1]','nvarchar(256)') AS client_app_name,
        x.value('(/event/action[@name="session_id"]/value)[1]', 'int')   AS session_id,
        x.value('(/event/action[@name="database_id"]/value)[1]', 'int')  AS database_id,
        x.value('(/event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)')   AS sql_text,
        s.file_name,
        s.file_offset
    FROM src AS s
    CROSS APPLY (SELECT s.x) AS CA(x)
    WHERE
        (
            x.value('(/event/data[@name="message"]/value)[1]','nvarchar(max)')
                LIKE '%Length specified in network packet payload%'
            OR x.value('(/event/data[@name="message"]/value)[1]','nvarchar(max)')
                LIKE '%valid TLS certificate is not configured%'
            OR x.value('(/event/data[@name="message"]/value)[1]','nvarchar(max)')
                LIKE '%TDS%'
            OR x.value('(/event/data[@name="message"]/value)[1]','nvarchar(max)')
                LIKE '%handshake%'
            OR x.value('(/event/data[@name="severity"]/value)[1]','int') >= 20
        )
        -- de-dup: only load events we have not seen yet
        AND NOT EXISTS
        (
            SELECT 1
            FROM Monitoring.XE_TlsTdsEvents d
            WHERE d.file_name = s.file_name
              AND d.file_offset = s.file_offset
        );
END
GO

3) Create the SQL Agent job and nightly schedule

This runs the import at 1:05 AM every day. Adjust times as you like.

DECLARE @job_id uniqueidentifier;
DECLARE @jobName sysname = N’Import TLS/TDS XE Events’;

— Clean up if it already exists
IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = @jobName)
BEGIN
EXEC msdb.dbo.sp_delete_job @job_name = @jobName;
END

— Add job
EXEC msdb.dbo.sp_add_job
@job_name = @jobName,
@enabled = 1,
@notify_level_eventlog = 0,
@description = N’Imports TLS/TDS handshake errors from XE into Monitoring.XE_TlsTdsEvents nightly.’,
@owner_login_name = N’sa’, — change owner if needed
@job_id = @job_id OUTPUT;

— Add job step (T-SQL)
EXEC msdb.dbo.sp_add_jobstep
@job_id = @job_id,
@step_name = N’Import XE files’,
@subsystem = N’TSQL’,
@database_name = N’master’,
@command = N’EXEC Monitoring.usp_ImportTlsTdsXe @XeFolder = N”C:\XE\TLS_TDS_Watch”;’,
@retry_attempts = 3,
@retry_interval = 2; — minutes

— Nightly schedule at 01:05
EXEC msdb.dbo.sp_add_schedule
@schedule_name = N’Nightly 1:05 AM’,
@freq_type = 4, — daily
@freq_interval = 1,
@active_start_time = 010500; — 01:05:00

— Attach schedule to job
EXEC msdb.dbo.sp_attach_schedule
@job_id = @job_id,
@schedule_name = N’Nightly 1:05 AM’;

— Target the local server
EXEC msdb.dbo.sp_add_jobserver
@job_id = @job_id,
@server_name = N'(LOCAL)’;

4) Quick reports you can run anytime

See the latest events

SELECT TOP (200)
    event_time_utc,
    event_name,
    severity,
    client_ip,
    client_app_name,
    LEFT(message, 200) AS message_snippet
FROM Monitoring.XE_TlsTdsEvents
ORDER BY event_time_utc DESC;

Who is the noisiest client

SELECT
    client_ip,
    client_app_name,
    COUNT(*) AS hits,
    MIN(event_time_utc) AS first_seen_utc,
    MAX(event_time_utc) AS last_seen_utc
FROM Monitoring.XE_TlsTdsEvents
GROUP BY client_ip, client_app_name
ORDER BY hits DESC;

Trend by day

SELECT
    CONVERT(date, event_time_utc) AS [day],
    COUNT(*) AS events
FROM Monitoring.XE_TlsTdsEvents
GROUP BY CONVERT(date, event_time_utc)
ORDER BY [day] DESC;

Notes

• Make sure the SQL Server Service account can read the XE folder.
• Keep the XE session path and the proc’s @XeFolder identical.
• De-duplication uses the unique (file_name, file_offset) pair from the XE files, so re-runs are safe.
• If you rotate the XE folder location later, update both the XE session and the proc.

Best Practices

• Always log failures and retries so you know they’re happening.
• Make retry logic idempotent so it’s safe to run the same operation twice.
• Apply retries only to transient network/TLS errors, not logical data errors.
• Keep the Extended Events session running for at least a week after fixing the root cause to confirm it’s resolved.

---

Bottom Line

If you’re getting these SQL Server alerts in your inbox or error logs, they could be signs of a TLS/TDS handshake problem.
That problem alone doesn’t corrupt data — but without retry logic, it can silently skip transactions and leave your data incomplete.

Fixing the certificate and client drivers solves the root cause. Adding retries ensures that even if a drop happens, your work still gets done.
Monitoring with Extended Events lets you verify the problem is gone and prove your fix worked.