Driver-Aware Connection Pooling in the ODBC Driver for SQL Server
The ODBC Driver for SQL Server supports Driver-Aware Connection Pooling. This article describes the enhancements made to driver-aware connection pooling in the Microsoft ODBC Driver for SQL Server on Windows:
Whatever the connection properties, connections that use
SQLDriverConnectgo into a separate pool from connections that useSQLConnect.When using SQL Server Authentication and driver-aware connection pooling, the driver doesn't use the Windows user's security context for the current thread to separate connections in the pool. That is, if connections are equivalent in their parameters for Windows impersonation scenarios with SQL Server Authentication, and they're using the same SQL Server Authentication credentials to connect to the backend, different Windows users can potentially use the same pool of connections. When using Windows Authentication and driver-aware connection pooling, the driver uses the current Windows user's security context to separate connections in the pool. That is, for Windows impersonation scenarios, different Windows users don't share connections even if the connections use the same parameters.
When using Microsoft Entra ID (formerly Azure Active Directory) and driver-aware connection pooling, the driver also uses the Authentication value to determine the membership in the connection pool.
Driver-aware connection pooling prevents a bad connection from being returned from the pool.
Driver-aware connection pooling recognizes driver-specific connection attributes. So, if a connection uses
SQL_COPT_SS_APPLICATION_INTENTset to read only, that connection gets its own connection pool.Setting the
SQL_COPT_SS_ACCESS_TOKENattribute causes a connection to be pooled separately
If one of the following connection-attribute IDs or connection string keywords is different between your connection string and the pooled connection string, the driver uses a pooled connection. However, performance is better if all connection attribute IDs or connection string keywords match. (To match a connection in the pool, the driver resets the attribute.) Performance degrades because resetting the following parameters requires an extra network call.
If two or more of the following connection attributes or connection keywords differ, a pooled connection isn't used.
LanguageQuoteIdSQL_ATTR_TXN_ISOLATIONSQL_COPT_SS_QUOTED_IDENT
If there's a difference in any of the following connection keywords between your connection string and a pooled connection string, a pooled connection isn't used.
Keyword ODBC Driver 13+ ODBC Driver 11 AddressYes Yes AnsiNPWYes Yes AppYes Yes ApplicationIntentYes Yes AuthenticationYes No ColumnEncryptionYes No DatabaseYes Yes EncryptYes Yes Failover_PartnerYes Yes FailoverPartnerSPNYes Yes MARS_ConnectionYes Yes NetworkYes Yes PWDYes Yes ServerYes Yes ServerSPNYes Yes TransparentNetworkIPResolutionYes Yes Trusted_ConnectionYes Yes TrustServerCertificateYes Yes UIDYes Yes WSIDYes Yes If there's a difference in any of the following connection attributes between your connection string and a pooled connection string, a pooled connection isn't used.
Attribute ODBC Driver 13+ ODBC Driver 11 SQL_ATTR_CURRENT_CATALOGYes Yes SQL_ATTR_PACKET_SIZEYes Yes SQL_COPT_SS_ANSI_NPWYes Yes SQL_COPT_SS_ACCESS_TOKENYes No SQL_COPT_SS_AUTHENTICATIONYes No SQL_COPT_SS_ATTACHDBFILENAMEYes Yes SQL_COPT_SS_BCPYes Yes SQL_COPT_SS_COLUMN_ENCRYPTIONYes No SQL_COPT_SS_CONCAT_NULLYes Yes SQL_COPT_SS_ENCRYPTYes Yes SQL_COPT_SS_FAILOVER_PARTNERYes Yes SQL_COPT_SS_FAILOVER_PARTNER_SPNYes Yes SQL_COPT_SS_INTEGRATED_SECURITYYes Yes SQL_COPT_SS_MARS_ENABLEDYes Yes SQL_COPT_SS_OLDPWDYes Yes SQL_COPT_SS_SERVER_SPNYes Yes SQL_COPT_SS_TRUST_SERVER_CERTIFICATEYes Yes SSPROP_AUTH_REPL_SERVER_NAMEYes Yes SQL_COPT_SS_TNIRYes No The driver can reset and adjust the following connection keywords and attributes without making an extra network call. The driver resets these parameters to ensure that the connection doesn't contain incorrect information.
These connection keywords aren't considered when the Driver Manager tries to match your connection with a connection in the pool. (Even if you change one of these parameters, an existing connection can be reused. The driver will reset the options, as needed.) These attributes can be reset in the client side without making an extra network call.
Keyword ODBC Driver 13+ ODBC Driver 11 AutoTranslateYes Yes DescriptionYes Yes MultisubnetFailoverYes Yes QueryLog_OnYes Yes QueryLogFileYes Yes QueryLogTimeYes Yes RegionalYes Yes StatsLog_OnYes Yes StatsLogFileYes Yes If you change one of the following connection attributes, an existing connection can be reused. The driver will reset the value, as needed. The driver can reset these attributes in the client without making an extra network call.
Attribute ODBC Driver 13+ ODBC Driver 11 All statement attributes Yes Yes SQL_ATTR_AUTOCOMMITYes Yes SQL_ATTR_CONNECTION_TIMEOUTYes Yes SQL_ATTR_DISCONNECT_BEHAVIOR SQL_ATTR_CONNECTION_TIMEOUTYes Yes SQL_ATTR_LOGIN_TIMEOUTYes Yes SQL_ATTR_ODBC_CURSORSYes Yes SQL_COPT_SS_PERF_DATAYes Yes SQL_COPT_SS_PERF_DATA_LOGYes Yes SQL_COPT_SS_PERF_DATA_LOG_NOWYes Yes SQL_COPT_SS_PERF_QUERYYes Yes SQL_COPT_SS_PERF_QUERY_INTERVALYes Yes SQL_COPT_SS_PERF_QUERY_LOGYes Yes SQL_COPT_SS_PRESERVE_CURSORSYes Yes SQL_COPT_SS_TRANSLATEYes Yes SQL_COPT_SS_USER_DATAYes Yes SQL_COPT_SS_WARN_ON_CP_ERRORYes Yes