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. It provides connections, commands, result processing, transactions, bulk copy, and SQL Server-specific features to .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.

The snippet assumes a project that targets .NET 8 or later versions and references the Microsoft.Data.SqlClient and Microsoft.Data.SqlClient.Extensions.Azure 7.1.1 packages. The host, such as the .NET Generic Host or ASP.NET Core, supplies the ILogger and the IConfiguration, and configuration supplies the server name in Sql:Server and the database name in Sql:Database.

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 (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;
}

The method returns the name of the database it connected to and logs Connected to database <database>. When a retry happens, it also logs one warning per retry with the exception that triggered it.

How each part of the snippet works:

  • 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 SQL Server 2022 and later versions, Azure SQL Database, Azure SQL Managed Instance, or SQL database in Microsoft Fabric. Use SqlConnectionEncryptOption.Mandatory for older servers.
  • 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 the Azure CLI, Visual Studio sign-in, and other credential sources. Keep it out of production because probing each source slows the connection. For more information, see Use default authentication.
  • MultiSubnetFailover = true: Attempts TCP connections to all resolved IP addresses in parallel and uses the first connection that succeeds, avoiding the slow sequential per-IP walk. Set it for Azure SQL Database, Azure SQL Managed Instance, SQL database in Microsoft Fabric, availability group listeners, and failover cluster instances. It's safe on single-IP targets. It isn't supported for named instances, protocols other than TCP, instances with more than 64 IP addresses, or database mirroring, which is deprecated. Use Always On availability groups instead. For more information, see High availability and disaster recovery and Disabling Transparent Network IP Resolution.
  • ConnectRetryCount = 3 and ConnectRetryInterval = 10: The driver retries qualifying transient failures during Open(), and reconnects a dropped idle connection on the next command. The default count is 1. Three attempts 10 seconds apart outlast a longer reconfiguration or failover. The driver raises 1 to 2 only when the server name ends in a known Azure SQL domain, such as .database.windows.net, so a custom DNS alias gets 1.
  • openRetry: Assigned to SqlConnection.RetryLogicProvider, it adds exponential backoff around Open(). The driver stops its internal retries when ConnectTimeout expires, so the worst case before OpenAsync throws is five attempts of up to 30 seconds each, plus backoff. Each idle reconnect attempt also runs the full openRetry policy, and when that policy exhausts its retries, idle recovery ends.
  • Serverless auto-pause: For Azure SQL Database serverless, raise ConnectTimeout to at least 60 seconds. The first Open() can fail with error 40613 while the database resumes, and openRetry retries it. A client-side timeout surfaces as error -2, which isn't retried, so each connect attempt must outlast the resume. For more information, see Auto-pause and auto-resume.
  • commandRetry: Attach it 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. It skips retry inside a transaction, so application code must retry multi-statement transactions. ExecuteReader and ExecuteScalar retry only errors returned before the first result set starts. ExecuteNonQuery retries the whole call.
  • Transient error list: Setting TransientErrors replaces the built-in list. To extend it instead, use SqlConfigurableRetryFactory.BaselineTransientErrors (Microsoft.Data.SqlClient 7.0 and later).
  • Retrying event: Retrying fires before each retry with the retry count, the next delay, and the exceptions so far. Route it to ILogger or your telemetry pipeline.
  • Exceptions: The method doesn't catch exceptions. An exhausted provider throws AggregateException with the exception from each attempt. A nontransient error propagates as the original exception. The caller logs it once with the rest of the request context.
  • Cancellation: The CancellationToken stops the provider's wait between retries. It doesn't interrupt the driver's ConnectRetryInterval wait inside a single Open attempt. For more information, see Asynchronous programming.
  • Provider lifetime: The snippet creates the retry providers on every call to stay self-contained. In an application, create them once and reuse them.

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.
  • 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 pooling: On by default. An opt-in V2 pool is in evaluation.
  • 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.
  • Application identity: Set SqlConnection.RegisteredApplication to identify your library or tool to SQL Server (Microsoft.Data.SqlClient 7.1 and later).

Get started

Article Description
Get started with the SqlClient driver Set up a project, create a database, connect, query, and add connection resiliency.
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 how SqlClient implements ADO.NET and its compatibility with 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, and respond to connection state changes.
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.
Handle errors and messages Catch errors and receive informational messages.

Authenticate and secure

Article Description
Security best practices for Microsoft.Data.SqlClient 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
Execute a command 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
SQL Server data type mappings Map SQL Server types to .NET 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.
Event counters in SqlClient Monitor active connections, pooled connections, and other driver metrics.
Enable event source tracing Capture detailed driver events for diagnosis.
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.
Microsoft.Data.SqlClient GitHub repository Read the driver source code, release notes, and samples.
Microsoft.Data.SqlClient samples Browse C# samples for driver APIs.
.NET runtime GitHub repository Read the source for the .NET runtime libraries.

Request a feature

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

Related content

  • Last updated on