Edit

Microsoft.Data.SqlClient for SQL Server

Download ADO.NET

Microsoft.Data.SqlClient is the supported .NET data provider for SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics, SQL database in Microsoft Fabric, and Warehouse in Microsoft Fabric. It's distributed as a NuGet package, evolves independently of the .NET runtime, and replaces System.Data.SqlClient for new development. Use it to open connections, execute commands, process results, manage transactions, bulk load data, and use SQL Server-specific features from .NET applications.

Choose your starting point

Production baseline for Azure SQL

Use this snippet as a starting point for a production-oriented Azure SQL data access path. It reads the server and database names from IConfiguration, so the values come from the configuration providers the host wires up, such as appsettings.json, environment variables, Azure App Configuration, and Key Vault-backed settings. The configuration combines these features:

  • Transport Layer Security (TLS).
  • Managed identity.
  • Idle-connection resiliency.
  • Initial-connect retry through configurable retry logic (CRL), with structured logging.
  • Command-level retry for transient errors that occur mid-query.
  • Fast failover-group recovery.
  • Asynchronous calls that accept a cancellation token.

For higher security and to support configuration across environments, keep connection information outside your code. In production, store connection information in your application's configuration system, and use Azure Key Vault for sensitive values. For more information, see Protect connection information.

The C# snippet in this article omits using directives and class wrappers for brevity.

public static async Task<string> QueryDatabaseNameAsync(
    IConfiguration config, ILogger logger, CancellationToken cancellationToken = default)
{
    string server = config["Sql:Server"]
        ?? throw new InvalidOperationException("Missing configuration value 'Sql:Server'.");
    string database = config["Sql:Database"]
        ?? throw new InvalidOperationException("Missing configuration value 'Sql:Database'.");

    var builder = new SqlConnectionStringBuilder
    {
        DataSource = server,
        InitialCatalog = database,
        Authentication = SqlAuthenticationMethod.ActiveDirectoryManagedIdentity,
        Encrypt = SqlConnectionEncryptOption.Strict, // TDS 8.0 encryption (SqlClient 5.0 and later versions; server must support it)
        ConnectTimeout = 30,                         // per-attempt connect timeout in seconds
        // Retry transient failures during Open() and reconnect a dropped idle connection.
        // The configurable provider below adds another policy around Open().
        ConnectRetryCount = 3,
        ConnectRetryInterval = 10,
        MultiSubnetFailover = true,                  // recommended for TCP endpoints; enables parallel connect
        // ApplicationIntent = ApplicationIntent.ReadOnly, // uncomment to route to a readable secondary
    };

    // Add exponential backoff and jitter around Open().
    // TransientErrors is null, so the provider uses the driver's built-in transient error list.
    var openRetry = SqlConfigurableRetryFactory.CreateExponentialRetryProvider(
        new SqlRetryLogicOption
        {
            NumberOfTries = 5,
            DeltaTime = TimeSpan.FromSeconds(3),
            MaxTimeInterval = TimeSpan.FromSeconds(60),
        });
    openRetry.Retrying += (_, args) =>
    {
        Exception last = args.Exceptions[^1];
        logger.LogWarning(
            last,
            "Retrying SQL connection to {Server}/{Database} (attempt {Attempt}) after {Delay}",
            server, database, args.RetryCount, args.Delay);
    };

    // Retry commands that hit deadlocks, lock timeouts, or common Azure SQL transient errors
    // mid-query on an established connection. Only attach this provider to commands whose
    // effect is safe to repeat.
    var commandRetry = SqlConfigurableRetryFactory.CreateExponentialRetryProvider(
        new SqlRetryLogicOption
        {
            NumberOfTries = 4,
            DeltaTime = TimeSpan.FromSeconds(5),
            MaxTimeInterval = TimeSpan.FromSeconds(30),
            // Deadlock victim, lock-request timeout, and common Azure SQL transient errors.
            TransientErrors = new[] { 1205, 1222, 10928, 10929, 40197, 40501, 40613, 49918 },
        });
    commandRetry.Retrying += (_, args) =>
    {
        Exception last = args.Exceptions[^1];
        logger.LogWarning(
            last,
            "Retrying SQL command (attempt {Attempt}) after {Delay}",
            args.RetryCount, args.Delay);
    };

    await using var connection = new SqlConnection(builder.ConnectionString)
    {
        RetryLogicProvider = openRetry,
    };
    await connection.OpenAsync(cancellationToken);

    await using var command = new SqlCommand("SELECT DB_NAME()", connection)
    {
        RetryLogicProvider = commandRetry,
        CommandTimeout = 30,
    };

    var name = (string)await command.ExecuteScalarAsync(cancellationToken);
    logger.LogInformation("Connected to database {DatabaseName}", name);
    return name;
}

This snippet targets any SQL Database Engine endpoint configured for Microsoft Entra authentication: Azure SQL Database, Azure SQL Managed Instance, SQL database in Microsoft Fabric, and SQL Server 2022 and later versions on Azure Virtual Machines or enabled by Azure Arc.

Encrypt = SqlConnectionEncryptOption.Strict selects TDS 8.0 encryption. It requires Microsoft.Data.SqlClient 5.0 and later versions and a server that supports TDS 8.0 (SQL Server 2022 and later versions, Azure SQL Database, Azure SQL Managed Instance, and SQL database in Microsoft Fabric). Fall back to SqlConnectionEncryptOption.Mandatory when you connect to older servers.

ConnectRetryCount and ConnectRetryInterval apply during initial connection establishment and idle connection recovery. When ConnectRetryCount is greater than zero, the driver retries qualifying transient failures during Open(). After Open() succeeds, the driver also uses these settings to reconnect a dropped idle connection on the next command. The openRetry provider assigned to SqlConnection.RetryLogicProvider adds a configurable exponential-backoff policy around Open(). Account for both retry layers when you set the retry counts and connection timeout. The driver stops its internal retries when ConnectTimeout expires, so each provider attempt takes at most about ConnectTimeout seconds. With these values, the worst case before OpenAsync throws is five attempts of up to 30 seconds each, plus the provider's backoff delays.

The snippet creates the retry providers on every call to keep the example self-contained. In an application, create them once and reuse them for every connection and command. The method doesn't catch exceptions. After the providers exhaust their retries or hit a nontransient error, the exception propagates to the caller, which logs it once with the rest of the request context. When a provider exhausts its retries, it throws AggregateException, which holds the exception from each attempt. A nontransient error propagates as the original exception.

The snippet uses the asynchronous methods and passes a CancellationToken. Cancellation stops the provider's wait between retries. It doesn't interrupt the driver's own ConnectRetryInterval wait inside a single Open attempt. For more information, see Asynchronous programming.

ActiveDirectoryManagedIdentity works only where a managed identity is available, such as Azure App Service, Azure Functions, or an Azure virtual machine. For local development, use SqlAuthenticationMethod.ActiveDirectoryDefault, which tries several credential sources, including the Azure CLI and Visual Studio sign-in. Keep ActiveDirectoryDefault out of production because probing each credential source slows the connection. For more information, see Use default authentication.

The Retrying event on each provider fires before each retry attempt and carries the retry count, the delay before the next attempt, and the exceptions observed so far. Route it to ILogger or your telemetry pipeline to keep the retry loop visible in production.

Set MultiSubnetFailover = true when the target is Azure SQL Database, Azure SQL Managed Instance, SQL database in Microsoft Fabric, an availability group listener, or a failover cluster instance. It selects a parallel-connect code path that attempts TCP connections to all resolved IP addresses in parallel and uses the first connection that succeeds, avoiding the slow sequential per-IP walk that can otherwise stall those connects. On single-IP targets, the setting is safe. MultiSubnetFailover isn't supported when you connect to a named instance, over a protocol other than TCP, or to an instance configured with more than 64 IP addresses. You also can't use it with database mirroring, which is deprecated in all supported versions of SQL Server. Use Always On availability groups instead. For more information, see High availability and disaster recovery and Disabling Transparent Network IP Resolution.

If the target is Azure SQL Database serverless with auto-pause enabled, raise ConnectTimeout to at least 60 seconds. An auto-paused database resumes on the first Open(), and that first Open() can fail with error 40613 while the database resumes. Error 40613 is in the built-in transient error list, so openRetry retries it. Client-side timeouts surface as error -2, which isn't in that list, so openRetry won't rescue an Open() that times out mid-resume. The individual connect attempt must be long enough to cover the resume. For more information, see Auto-pause and auto-resume.

Command-level retry is the caller's decision, per command. Attach commandRetry to SqlCommand.RetryLogicProvider only when replaying the command is safe: reads, MERGE guarded by a natural key, upserts through a stored procedure, and other idempotent operations. The built-in command provider skips retry when a transaction is active, so multi-statement transactions must be retried by application code that can reopen the transaction. Setting TransientErrors replaces the driver's built-in error list; to extend the built-in baseline instead, use SqlConfigurableRetryFactory.BaselineTransientErrors (Microsoft.Data.SqlClient 7.0 and later).

For more information about each part of this configuration, see:

Learn the core data-access tasks

After you connect, use these articles in order:

  1. Execute a command to choose a result shape and run Transact-SQL (T-SQL).
  2. Configure parameters to match the database contract.
  3. Retrieve data with SqlDataReader to process rows and multiple results.
  4. Modify data with stored procedures to use output parameters and return values.
  5. Use asynchronous programming and cancellation to add caller cancellation and bounded timeouts.
  6. Handle errors and messages before you add retries or production diagnostics.

Key features

  • Modern .NET support: Runs on current .NET and .NET Framework versions. For the per-version breakdown, see Support lifecycle.
  • Encrypted by default: TLS-encrypted connections with Encrypt=true as the default. Set Encrypt=Strict for TDS 8.0 encryption on Microsoft.Data.SqlClient 5.0 and later.
  • Microsoft Entra ID authentication: Passwordless connections with managed identity, service principal, interactive, integrated, default credential chain, and access-token flows.
  • Kerberos and NTLM: Integrated Windows authentication for on-premises Active Directory and legacy scenarios.
  • Always Encrypted: Client-side encryption for sensitive columns, with optional secure enclaves for in-place operations.
  • Bulk copy: High-throughput inserts with SqlBulkCopy.
  • Connection resiliency: Built-in connection retries (ConnectRetryCount and ConnectRetryInterval) plus opt-in configurable retry logic for connections and commands.
  • Rich SQL Server data types: datetimeoffset, sql_variant, JSON, vector, spatial, XML, and table-valued parameters.
  • Diagnostics: Event source tracing, diagnostic counters, provider statistics, and a dedicated troubleshooting guide.

Get started

Article Description
Get started with the SqlClient driver Set up a project, create a database, connect, query, and add connection resiliency.
ADO.NET architecture with Microsoft.Data.SqlClient Learn how Microsoft.Data.SqlClient implements connected, disconnected, and provider-independent ADO.NET access.
Install, update, and deploy Microsoft.Data.SqlClient Install NuGet packages, choose a release, update the driver, and prepare deployment output.
Support lifecycle Review supported driver versions and support dates.
Migrate from System.Data.SqlClient to Microsoft.Data.SqlClient Update package references, namespaces, configuration, and changed driver behavior.
Microsoft.Data.SqlClient namespace and compatibility Understand the driver's relationship to ADO.NET, System.Data.SqlClient, .NET, and SQL Server.
What's new in Microsoft.Data.SqlClient Find current releases, major upgrade changes, and upstream release notes.

Configure and connect

Article Description
Connect to a data source Open and manage connections to SQL Server and Azure SQL.
Connection strings Configure server, database, authentication, encryption, and connection behavior.
Encryption and certificate validation Configure encrypted connections and server certificate validation.
SQL Server connection pooling Reuse physical connections efficiently.
Connection events Respond to connection state and informational messages.

Authenticate and secure

Article Description
SQL Server security Review authentication, authorization, and application security guidance.
Microsoft Entra authentication Connect with managed identity, service principal, password, and interactive flows.
Protect connection information Keep credentials and connection settings out of application code.
Always Encrypted Protect sensitive column values from the database system.
Always Encrypted with secure enclaves Run rich operations on encrypted data with a secure enclave.

Retrieve and update data

Article Description
Commands and parameters Execute parameterized SQL statements and stored procedures.
DataAdapters and DataReaders Stream result sets or populate disconnected data structures.
Execute multiple commands with SqlBatch Send an ordered collection of commands and process their results.
Stream data Transfer large binary, text, XML, and JSON values without buffering the complete value.
Transactions and concurrency Use local and distributed transactions and concurrency controls.
Retrieve database schema information Discover schema collections and restrictions.
Bulk copy operations Load large data sets efficiently with SqlBulkCopy.
Table-valued parameters Send multiple rows to a parameterized statement or stored procedure.
Multiple Active Result Sets (MARS) Interleave multiple batches on one connection.

Data types

Article Description
ADO.NET data type mappings Map common language runtime types to provider and SQL Server types.
SQL Server data types Work with SQL Server-specific values and System.Data.SqlTypes types.
JSON data Send and retrieve the SQL Server json data type.
Vector data Send and retrieve vector values.
XML data Read, write, and parameterize XML values.
Binary and large-value data Stream and update binary, FILESTREAM, and large-value data.

Reliability and diagnostics

Article Description
Configurable retry logic Retry transient connection and command failures with bounded policies.
High availability and disaster recovery Connect to availability group listeners and failover partners.
Diagnostic counters Monitor active connections, pooled connections, and other driver metrics.
Enable event source tracing Capture detailed driver events for diagnosis.
Data tracing Trace ADO.NET operations and data access.
SqlClient troubleshooting guide Diagnose common connection and driver problems.
Query notifications Receive notifications when query results change.

SQL Server features

Article Description
SQL Server features and ADO.NET Browse SQL Server-specific features available through SqlClient.
LocalDB Connect to SQL Server Express LocalDB instances.
Data discovery and classification Read sensitivity classification metadata from result sets.

Reference and resources

Article Description
Microsoft.Data.SqlClient API reference Browse .NET API reference for the driver.
AppContext switches Configure compatibility and security behavior.
Find additional SqlClient information Find source code, support, and community resources.

Request a feature

To request a feature, open an issue in the Microsoft.Data.SqlClient GitHub repository.

Related content

  • Last updated on