Yesterday's connection works until a client driver upgrade exposes a certificate problem. TrustServerCertificate can hide that problem, but it also changes how the server's identity is checked. Read the connection settings as security and availability decisions, not decorative punctuation.
Read the Settings in the Client's Dialect
Connection-string keywords belong to a particular client provider. Identical-looking settings do not guarantee identical defaults across providers and versions. Check the provider loaded by the application before copying a connection string from another application.
For the SQL Server .NET client Microsoft.Data.SqlClient, version 4.0 changed the Encrypt default to True. Version 5.0 added Mandatory, Optional, and Strict values. Mandatory corresponds to True, while Optional corresponds to False in that provider.
SSMS 20 and later also default to mandatory encryption. A connection failure after an upgrade can therefore expose an existing certificate deployment problem. The database server does not need to change for stricter client defaults to matter.
I record the provider name and version before troubleshooting an upgraded application. I also inspect the effective settings rather than relying on an old deployment note. Defaults have a habit of changing without asking the connection string's permission.
Separate Encryption From What TrustServerCertificate Skips
Encrypt=True or Mandatory requires encrypted communication using the supported negotiation path. With certificate validation enabled, the client also verifies the certificate's trust chain and server name. A trusted issuer alone cannot fix a certificate naming the wrong host.
TrustServerCertificate=True bypasses normal server-certificate validation in these ordinary encryption modes. Traffic can remain encrypted, but the client has weakened its assurance about the peer's identity. That distinction matters when diagnosing an untrusted issuer or hostname mismatch.
Use a certificate trusted by the client and containing the connection hostname in its supported names. Prefer the real listener or server DNS name used by the application. An IP address or informal alias needs appropriate certificate coverage too.
The following C# fragment shows a deliberate connection string rather than relying on encryption defaults. Run it within an application already referencing the indicated SQL Server client assembly. Replace the example hostname and database with your environment's values.
// C#
using Microsoft.Data.SqlClient;
string connectionString =
"Server=tcp:sql.example.internal,1433;Database=AppDb;" +
"Integrated Security=True;Encrypt=True;TrustServerCertificate=False;" +
"Connect Timeout=30;Application Name=AppDbConnectionCheck;";
using var connection = new SqlConnection(connectionString);
connection.Open();
using var command = new SqlCommand("SELECT DB_NAME();", connection);
command.CommandTimeout = 60;
Console.WriteLine(command.ExecuteScalar());The example contains no password and does not print the connection string. Keep authentication secrets outside source code and diagnostic output. Integrated authentication still requires the intended Windows identity to have authorized database access.
Know Why Strict Mode Ignores TrustServerCertificate
Strict mode uses TDS 8.0 and requires an endpoint and provider supporting that protocol. SQL Server 2022 and later support the relevant server capability. Azure SQL also supports TDS 8.0 connections, subject to the client configuration.
In Strict mode, TrustServerCertificate is ignored and treated as False. Setting the bypass flag cannot rescue an invalid certificate in that mode. Fix the trust chain, certificate name, and protocol support instead.
Do not treat Strict as a spelling change for Mandatory. Its protocol requirements can make a previously compatible endpoint unavailable. Test the full connection route, including listeners and network devices, before changing an application's mode.
A connection-string builder helps validate supported keyword values in the installed provider. The next C# block requires Microsoft.Data.SqlClient 5.0 or later. It constructs an encrypted listener connection without opening it.
// C#
using Microsoft.Data.SqlClient;
var settings = new SqlConnectionStringBuilder
{
DataSource = "tcp:aglistener.example.internal,1433",
InitialCatalog = "AppDb",
IntegratedSecurity = true,
Encrypt = SqlConnectionEncryptOption.Strict,
TrustServerCertificate = false,
ConnectTimeout = 30,
ApplicationName = "AppDbListenerCheck",
MultiSubnetFailover = true
};
using var connection = new SqlConnection(settings.ConnectionString);
Console.WriteLine("Connection settings constructed without opening a connection.");The construction check does not test certificates, permissions, or listener reachability. An actual connection under the deployed application identity must verify those requirements. Keep that distinction clear in your deployment evidence.
Give Connection and Command Timeouts Different Jobs
Connect Timeout limits the connection-opening attempt, including connection establishment behavior defined by the provider. It does not set the timeout for every query. A failed login and a slow command therefore need different troubleshooting paths.
CommandTimeout belongs to the command in the first example. It limits the provider's waiting behavior during command execution and result processing. It is not a guaranteed server execution deadline or proof that every side effect was rolled back.
After a command timeout, inspect transaction and application recovery behavior before retrying. Repeating a write can duplicate an operation that completed before the response was lost. Retry policies need an explicit rule for repeatable effects.
Avoid using unlimited timeouts simply to silence failures. Choose budgets that match the operation and record which budget expired. A timeout message with no operation identity is difficult to turn into a useful diagnosis.
Label Connections and Verify What the Server Sees
Application Name gives monitoring a recognizable workload label. It becomes program_name in the session view, but it is client-supplied information. Use it for attribution and grouping, not authorization or proof of identity.
SELECT s.session_id, s.program_name, s.host_name,
s.client_interface_name, s.login_name,
c.net_transport, c.encrypt_option, c.auth_scheme,
c.client_net_address, c.connect_time
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_connections AS c
ON c.session_id = s.session_id
WHERE s.is_user_process = 1
ORDER BY s.session_id;For your own connection, filter on @@SPID. Broader visibility requires the documented monitoring permission for your version. SQL Server 2022 and later use VIEW SERVER PERFORMANCE STATE for the connection view.
The server can report whether the connection is encrypted and its authentication scheme. It cannot prove that the client validated the certificate correctly. An encrypted connection can still have been opened with the validation bypass enabled.
The session views also do not disclose each command's timeout or the complete original connection string. Inspect application configuration for those values. Combining server evidence with client configuration gives a fuller explanation than either source alone.
Test Listener Failover as an Application Behavior
MultiSubnetFailover=True improves connection behavior for supported availability-group listeners, especially across subnets. Use the listener address rather than substituting an individual replica's address. The listener name also needs to match the certificate validation contract.
The option does not retry failed business transactions or make every failover transparent. Test connection opening, pool recovery, and command retry behavior separately. Record which operations can safely repeat after a connection breaks.
I compare a normal connection and a planned failover using the deployed application identity. I also check the resulting program_name and encryption state from SQL Server. That catches configuration differences between a developer's test and the actual service.
Does your application fail because it cannot reach the endpoint, validate its identity, or finish a command? Answer that before changing timeouts. Keep TrustServerCertificate disabled in the final validated configuration unless a consciously accepted exception defines its use.
Related reading on this blog: Fix: Error: The certificate chain was issued by an authority that is not trusted and Connection Retry Logic for Cloud Databases.
A connection string is not harmless boilerplate, it is an application security and availability contract.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
