Which SQL Server Client Driver to Use Now

The server upgrade is ready, but the application still uses an old provider. Your client driver choice decides authentication, encryption, data types, and support.

Choose the Client Driver for the Application Stack

There is no single best driver for every program. Start with the language and framework the application uses. A .NET application normally belongs with Microsoft.Data.SqlClient. Java uses Microsoft’s JDBC driver. Native or cross-language tools commonly use ODBC. Components built around OLE DB need the supported Microsoft OLE DB driver.

I ask the application owner which library actually opens the connection. A server inventory cannot answer that. A program name in a SQL session is a hint, not a driver certificate. Look at the deployed application package and connection configuration.

What feature does the client need: integrated authentication, encryption, Always Encrypted, bulk copy, or a specific data type? Check the supported driver feature matrix for that requirement before choosing.

ODBC Fits Broad Tooling

Microsoft’s ODBC Driver for SQL Server serves applications and tools that use the ODBC API. It is also used by the ODBC version of sqlcmd and by bcp through the command line utilities. Choose a supported driver release for your operating system and SQL Server target.

A driver major update can change encryption defaults and certificate validation behavior. Test connection strings on a staging machine. A successful connection from an old ODBC driver does not prove a newer driver will accept the same certificate setup.

On Windows, list installed ODBC drivers locally. The list tells you what is installed on that machine, not what a specific process has loaded. Check the application’s data source configuration too.

# PowerShell
Get-OdbcDriver |
    Where-Object Name -Like '*SQL Server*' |
    Select-Object Name, Platform, Attribute

Use the Current OLE DB Provider

OLE DB remains relevant for components that use the OLE DB API, including some SSIS packages and linked server configurations. Microsoft’s current OLE DB Driver for SQL Server uses the MSOLEDBSQL19 provider name for version 19. Older SQLNCLI and SQLOLEDB providers are not the place to start new work.

A provider name is part of the configuration. Installing a current provider does not rewrite every SSIS connection manager or linked server. Inventory those references and test the migration. Encryption and property behavior can differ across major provider versions.

I treat OLE DB changes as application changes. The server can be healthy while a package fails to acquire a connection. Test the exact package path and credentials, not just a manual SELECT.

Use Microsoft.Data.SqlClient for .NET

Microsoft.Data.SqlClient is the supported .NET data provider for SQL Server and related Microsoft SQL services. It ships as an application package, independently of the SQL Server engine. Updating the server does not update every deployed application library.

Check the supported release line, target framework, and feature support before changing it. Review encryption and authentication defaults. Test the application’s actual connection string and transaction behavior. A build that compiles is not proof that the deployed service connects.

I inspect the application dependency list rather than guessing from the server version. An older System.Data.SqlClient reference and a newer Microsoft.Data.SqlClient package are different choices. Plan the move with the application team.

Use JDBC for Java

Java applications use the Microsoft JDBC Driver for SQL Server when they choose Microsoft’s driver. Match a supported driver release to the Java runtime and database features. Package the driver with the application through its normal deployment process.

Check TLS, certificate trust, integrated authentication options, and time zone behavior in staging. A Java trust store can differ from the Windows certificate store used by a DBA tool. A successful Management Studio login does not prove the Java service will connect.

The driver lives with the client. Inventory it where the application runs. A database team that only checks SQL Server binaries will miss an old JDBC jar in an application deployment.

Do Not Guess the Client Driver From a Session Name

SQL Server exposes connection and session metadata. It can show transport, encryption, authentication scheme, host, and program name for established sessions. It does not provide a complete, trustworthy inventory of every client driver and version. Some client fields are supplied by the application.

Use a query as a cross-check. Run it from the application session if possible. Compare the result with the deployed package record. The query is useful for confirming encryption and endpoint behavior, while the application package identifies the driver.

I have seen a connection called “Application” that belonged to a test tool. Treat labels as clues.

SELECT
    s.program_name,
    s.host_name,
    c.net_transport,
    c.encrypt_option,
    c.auth_scheme
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_connections AS c
  ON c.session_id = s.session_id
WHERE s.session_id = @@SPID;

Test the Client Driver and Server as a Pair

A driver and server form a pair. Test the exact driver release against the exact SQL Server build. Include login, a representative query, transaction handling, date and decimal values, bulk operations, and application error paths. Read both release notes.

If a driver update fixes a security or compatibility issue, plan its rollout to every application host. An installer run on one DBA workstation does not update the application fleet. Track versions in the application inventory.

Keep a return plan for the client deployment. A server rollback and a driver rollback have different owners and data consequences. Coordinate them before the production window.

Retire Old Providers Deliberately

Search connection strings, SSIS packages, linked servers, scheduled tasks, and vendor tools for provider names. Replace old providers in a controlled order. Confirm the current provider is installed on every execution host. Keep a record of which application still depends on an exception.

Do not remove an old driver immediately after one successful test. Some monthly job can be the last user. Observe a full relevant cycle, then retire the dependency under change control.

The right client driver is the supported one that fits the application API and passes its real workload. The server version is only half the conversation.

Related reading on this blog: Network Protocol and IP Address and How to Find IP Address of All SQL Server Connection? Interview Question of the Week #280.

A client driver is not a server accessory, it is part of the application contract.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.