Cloud Recommendation - SQL Server Connection String Changes

Last updated: 2026-08-21 18:26:59

Overview
Recommended Settings
Availability Groups Failover
.NET
Java or Python
Reference Links

Overview

This document recommends connection string changes for applications that connect to SQL Server with .NET, Java, or Python drivers. You can make these changes on-premises, before you migrate to a cloud infrastructure. These changes do not affect application performance.

NOTE: Any changes must go through the SDLC first, before they go into production.

Recommended Settings

We recommend TRUE (or your driver's equivalent value) for the following three settings.

MultiSubnetFailover: this gives the fastest Availability Group failover detection. See Availability Groups Failover.
TransparentNetworkIPResolution: this is the driver default. Keep it enabled as a fallback for a connection that does not target an Availability Group listener or Failover Cluster Instance. See .NET Client Drivers or Java or Python Client Drivers.
TrustServerCertificate: when set, the driver skips certificate chain validation. The channel is still encrypted, but this setting gives no protection against a man-in-the-middle attack on an untrusted network path. Use it only when the remote server's certificate is not known or pinned in advance. This is okay on a private VPN or cloud network, for example a VPC connection. Where the certificate is known and CA-signed, set this to FALSE instead, since that also validates the certificate.

Availability Groups Failover

Microsoft introduced Availability Group Listeners as a simple way for client applications to connect to an Availability Group. Like a DNS CNAME record, an Availability Group Listener is a list of IP addresses, 1 per subnet, where an Availability Group can reside.

RECOMMENDED – LATEST DRIVERS: Always use the latest driver version, to help client applications react more quickly to a failover. See Recommended Settings for the connection string settings that also help with this.

.NET

.NET Client Drivers

Microsoft provides connection modules/drivers for .NET. Use the latest C# driver, where possible, before you migrate to AWS. This document assumes Microsoft.Data.SqlClient, the actively maintained driver package. The older System.Data.SqlClient package is in maintenance mode and does not receive new features. These drivers include the recent security patches and allow faster recovery from an Availability Group failover.

NOTE: Engineer client applications to be database restart aware, so they retry connections.

Setting	Description
MultiSubnetFailover`=TRUE`	Microsoft's primary recommendation for an Availability Group with replicas in multiple subnets. The driver attempts every listener IP address at the same time, in parallel, not one at a time. This gives the fastest failover detection.

FALLBACK – FOR A CONNECTION THAT DOES NOT TARGET AN AVAILABILITY GROUP LISTENER OR FAILOVER CLUSTER INSTANCE

Setting Description

TransparentNetworkIPResolution=TRUE When set to true, the driver retrieves all IP addresses for a particular DNS entry and attempts to connect to the first one in the list. If the driver does not establish the connection within 0.5 seconds, it tries to connect to all the other addresses in parallel. When one address answers, the driver establishes the connection with that address. Microsoft's driver documentation states that TransparentNetworkIPResolution is ignored whenever MultiSubnetFailover is set to true.

Setting	Description
TransparentNetworkIPResolution`=TRUE`	When set to `true`, the driver retrieves all IP addresses for a particular DNS entry and attempts to connect to the first one in the list. If the driver does not establish the connection within 0.5 seconds, it tries to connect to all the other addresses in parallel. When one address answers, the driver establishes the connection with that address. Microsoft's driver documentation states that TransparentNetworkIPResolution is ignored whenever MultiSubnetFailover is set to true.

.NET Connection Pooling

Connection pooling avoids the cost of a new physical connection for every request. Microsoft.Data.SqlClient enables pooling by default.

Setting	Description
Pooling`=TRUE`	The default value. The driver returns an existing connection from the pool. It does not open a new connection for every request.
Max Pool Size	The upper limit on connections in one pool. The default value is 100. Set this value to match the application's expected concurrent connection count.
Min Pool Size	The number of connections the pool keeps open at all times. The default value is 0, so the pool closes idle connections after a period of inactivity. Set a value above 0 only if a latency-sensitive application benefits from a pre-warmed pool.
Load Balance Timeout (`Connection Lifetime`)	The maximum age, in seconds, for a pooled connection. The pool destroys an older connection. It does not return that connection to a caller. The default value is 0, which disables this limit. Set this value in a multi-subnet Availability Group, so connections rebalance across replicas after a failover.

NOTE: Close or dispose of every connection so the driver returns it to the pool. An open, unused connection stays out of the pool and reduces its effective capacity. Wrap connection use in a using block in C#.

NOTE: The pool for a connection string is exact-match. A different connection string, a different Windows identity, or a different transaction context each gets a separate pool.

.NET TLS Connectivity

Transport Layer Security is a cryptographic protocol. It provides privacy and data integrity between two applications. SQL Server has supported TLS 1.2 since January 2016, and SQL Server 2022 and later versions support TLS 1.3. SQL Server 2019 and earlier versions do not support TLS 1.3. To implement TLS connectivity to SQL Server, apply the following connection changes.

Setting	Description
Encrypt`=TRUE`	If the server has a certificate installed, use SSL/TLS encryption for all data sent between the client and server. Starting with `Microsoft.Data.SqlClient` version 4.0, the driver default for this setting is `TRUE`.
TrustServerCertificate`=TRUE`	The channel is encrypted, but the driver does not validate the certificate chain. See Recommended Settings for when this is appropriate.

.NET ApplicationName/WorkstationID

In a cloud environment, the name of a host is often not configurable. The name is generic and can change at any time. For example, the host name can look like IP-###-###-###. Use the following connection strings to identify which applications connect to the database. NOTE: This helps audit connections and troubleshoot connectivity issues from specific applications and hosts.

Setting	Description
ApplicationName`=<APPNAME>-<CLIENT_HOSTNAME>`	Set the name of the application associated with the connection string. NOTE: Specify the name of the application and the client hostname.
WorkstationID`=<APPNAME>-<CLIENT_HOSTNAME>`	Sets the name of the workstation that connects to SQL Server. NOTE: Specify the name of the application and the client hostname.

Java or Python

Java or Python Client Drivers

Microsoft provides connection modules/drivers for Java and Python. Use the latest Microsoft JDBC Driver for Java, or the latest mssql-python driver for Python, where possible, before you migrate to AWS.

Java (JDBC)

Setting	Description
`multiSubnetFailover=true`	Same behavior as .NET's `MultiSubnetFailover`. The default is `false`. The driver attempts every listener IP address at the same time, in parallel, not one at a time.

FALLBACK – FOR A CONNECTION THAT DOES NOT TARGET AN AVAILABILITY GROUP LISTENER OR FAILOVER CLUSTER INSTANCE

Setting	Description
`transparentNetworkIPResolution=true`	Same behavior as .NET's `TransparentNetworkIPResolution`. The default is `true`. The driver ignores this setting whenever `multiSubnetFailover` is set to `true`.

Python (mssql-python)

Setting	Description
`MultiSubnetFailover=yes`	Same behavior as .NET's `MultiSubnetFailover`. The default is `no`. The driver attempts every listener IP address at the same time, in parallel, not one at a time.

NOTE: mssql-python has no transparentNetworkIPResolution equivalent. Always set MultiSubnetFailover=yes for a Python Availability Group connection.

Java or Python Connection Pooling

Java (JDBC)

The JDBC driver does not include its own connection pool. It relies on a Java EE application server, or a third-party library such as HikariCP, for pooling. Use SQLServerXADataSource as the pooled connection factory. Microsoft recommends this class over SQLServerConnectionPoolDataSource, since it supports the pooling interface and the XA transaction interfaces.

NOTE: The JDBC driver has no minimum or maximum pool size setting of its own. Your chosen pooling library sets these values. For example, HikariCP calls them minimumIdle and maximumPoolSize. HikariCP's default minimumIdle matches maximumPoolSize, so the pool holds a fixed number of connections unless you lower minimumIdle.

Python (mssql-python)

The driver enables connection pooling by default when you create the first connection. Configure it with the mssql_python.pooling() function before you create any connection. A call to pooling() after that point has no effect.

Parameter	Default	Description
`max_size`	100	Maximum pooled connections per unique connection string.
`idle_timeout`	600 seconds	Seconds before the driver closes an idle pooled connection.
`enabled`	`True`	Set to `False` to disable pooling.

NOTE: mssql-python has no minimum pool size setting, and no ClearPool/ClearAllPools equivalent.

NOTE: Close every connection so the driver or pool can reuse it. In Java, use a try-with-resources block, or the finally construct, to guarantee this even if an exception occurs. In Python, use a with block for the same guarantee.

Java or Python TLS Connectivity

Transport Layer Security is a cryptographic protocol. SQL Server has supported TLS 1.2 since January 2016, and SQL Server 2022 and later versions support TLS 1.3. SQL Server 2019 and earlier versions do not support TLS 1.3.

Java (JDBC)

Setting	Description
`encrypt=true`	Same behavior as .NET's `Encrypt`. The default is `true` since driver version 10.2, and `false` in version 9.4 and earlier.
`trustServerCertificate=true`	Same behavior as .NET's `TrustServerCertificate`. The driver does not validate the certificate chain. The default is `false`. See Recommended Settings for when this is appropriate.

Python (mssql-python)

Setting	Description
`Encrypt=yes`	Enables TLS encryption. The default is `yes`. Set `Encrypt=strict` to enforce TDS 8.0 and mandatory TLS 1.3.
`TrustServerCertificate=yes`	The driver does not validate the certificate chain. The default is `no`. See Recommended Settings for when this is appropriate.

Java or Python ApplicationName/WorkstationID

In a cloud environment, the name of a host is often not configurable. Use the following connection strings to identify which applications connect to the database, where the driver supports it.

Java (JDBC)

Setting	Description
`applicationName=<APPNAME>-<CLIENT_HOSTNAME>`	Same behavior as .NET's `ApplicationName`. If you do not set this, the default is "Microsoft JDBC Driver for SQL Server".
`workstationID=<APPNAME>-<CLIENT_HOSTNAME>`	Same behavior as .NET's `WorkstationID`. If you do not set this, the default is an empty string.

Python (mssql-python)

NOTE: mssql-python does not support a custom application name or workstation ID. The driver reserves the APP keyword and always sets it to "MSSQL-Python". There is no WorkstationID equivalent. To identify a specific application or host in profiling and logging tools, use a different mechanism. Examples are a SQL comment in each query, or an audit column in your application's own logs.

Search This Blog

DarkSide Foundation Musings