DOCSEARCHbyAlgolia
Submit Docs

Microsoft.Data.SqlClient Documentation

repository·main·Indexed 21 days ago

https://github.com/dotnet/sqlclient

The modern .NET data provider for Microsoft SQL Server and Azure SQL databases, superseding System.Data.SqlClient. It supports .NET Framework 4.6.2+ and .NET 8.0+. The library features a decoupled extension architecture to minimize dependencies, offering optional packages for Azure Authentication, Attestation, and Key Vault via Microsoft.Data.SqlClient.Extensions.Azure. Documentation includes guidance on Entra ID authentication plumbing, SNI package configuration for Windows, and migration paths to version 7.0.0.

Tokens
36.6K
Snippets
81
Records
137
Agent score
71%

What's inside Microsoft.Data.SqlClient

  1. Overview of the PackageCompatibility Tool

    main

    The PackageCompatibility tool is a minimal console application designed to verify that various SqlClient packages can coexist without transitive dependency conflicts, API surface mismatches, or runtime failures.

    Unlike standard unit or functional tests that run against the current source code, this tool allows you to test heterogeneous package versions (e.g., testing a new AKV provider against an older published SqlClient release) and locally-built packages before they are published to NuGet.org.

    It works by:

    1. Forcing the SqlColumnEncryptionAzureKeyVaultProvider to load, which pulls in the AKV provider and its transitive dependencies.
    2. Opening a SqlConnection using a provided connection string to exercise the full package graph against a real SQL Server instance.

    Note: This tool requires a live SQL Server or Azure SQL instance to function.

  2. Async-Friendly Always Encrypted Support

    main

    Starting with version 7.1.0-preview2, Microsoft.Data.SqlClient introduces async-friendly support for Always Encrypted (AE). This feature eliminates 'sync-over-async' by providing asynchronous counterparts to all key decryption, signature verification, and enclave attestation calls.

    When using async methods like SqlCommand.ExecuteReaderAsync, the driver now uses asynchronous paths for AE operations (such as Azure Key Vault HTTP calls or enclave attestation) instead of blocking ThreadPool threads with synchronous calls. This is particularly beneficial for applications using Azure Key Vault (AKV) or secure enclaves, as it prevents thread starvation during I/O-bound cryptographic operations.

  3. Compare PackageValidator with other public tools

    main

    PackageValidator is a dependency-light, cross-platform CLI tailored specifically to the SqlClient package family. While it performs tasks covered by other tools, it is distinct because it uses metadata-only reading (allowing inspection of net462 assemblies on Linux without loading them), performs symbol matching via both debug GUID and portable-PDB checksums, and supports inter-package version-match assertions for entire package families.

    AreaPublic toolsRelationship
    Package inspection & health rulesNuGet Package Explorer, dotnet validateClosest all-round analogs: inspect .nupkg metadata, layout, symbols, and run package-hygiene rules.
    Package/assembly signaturesdotnet nuget verify, signtool, sn.exeVerify NuGet, Authenticode, and strong-name signing state. PackageValidator reports the same state inline.
    API surface & compatibilityMicrosoft.DotNet.ApiCompat, SDK PackageValidation (<EnablePackageValidation>), PublicApiAnalyzersCatch breaking API/TFM changes across versions — complementary to, not overlapping with, this tool's version checks.
    Assembly/metadata inspectionILSpy / ilspycmd, dotnet-ildasm, AsmSpyMetadata-only inspection; AsmSpy specifically reports version conflicts across binaries.
    Symbols / Source Linksourcelink, Microsoft.SourceLink.*Verify portable-PDB checksums and Source Link — the same verification this tool performs against the sibling .snupkg.

  4. Key Features of the Azure Key Vault Provider

    main

    The Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider provides several key capabilities for secure data handling:

    • Azure Key Vault Integration: Enables storing and managing Column Master Keys (CMKs) in Azure Key Vault.
    • Entra ID Authentication: Integrates with Azure.Identity for seamless authentication.
    • Key Caching: Includes built-in caching of Column Encryption Keys (CEKs) to improve performance.
    • Multiple Authentication Methods: Supports various credentials including DefaultAzureCredential, ClientSecretCredential, and ManagedIdentityCredential.

  5. Identify Microsoft.Data.SqlClient test projects

    main

    The repository contains three primary test projects located under src/Microsoft.Data.SqlClient/tests:

    • Unit tests: Microsoft.Data.SqlClient.UnitTests.csproj - Unit tests and tests against simulated servers.
    • Functional tests: Microsoft.Data.SqlClient.FunctionalTests.csproj - Functional tests for public and internal behavior (some use simulated servers or local infrastructure).
    • Manual tests: Microsoft.Data.SqlClient.ManualTests.csproj - Integration tests requiring a configured SQL Server or Azure SQL target.

  6. Handling Warmup Failures and Error States

    main

    Warmup is designed to be resilient and should not cause application crashes.

    Error Handling Rules:

    • Silent Absorption: If a connection fails to open during a background warmup, the failure is logged/traced and absorbed by the warmup loop. It is never surfaced as an unhandled exception to the caller.
    • Blocking-Period Error State: Because warmup uses the same creation path as user requests, a genuine connection open failure will trigger the pool's 'blocking-period error state'.
    • User Impact during Errors: If a user attempts to open a connection while the pool is in this error state (during the blocking window), the request will fast-fail with the cached exception. Once the blocking period expires, the pool will attempt to create connections on demand normally.
    • Error State Protection: If the pool is already in an error state (driven by a failing user request), the warmup loop will 'stand down' and skip replenishment to avoid adding more load to a struggling server.

  7. Manage connection failure behavior with PoolBlockingPeriod

    main

    The PoolBlockingPeriod feature allows the connection pool to enter an error state after a connection creation failure (e.g., when the server is unreachable). This enables 'fast-fail' behavior, where subsequent requests immediately return the cached error instead of waiting for a timeout, preventing cascading failures.

    Blocking Modes

    | Mode | Behavior | | :--- | : | | AlwaysBlock | A blocking period is always applied after a creation failure. | | NeverBlock | No blocking period is applied; every request independently attempts connection creation. | | Auto | Behavior depends on the endpoint: <br> - Azure SQL: Acts like NeverBlock (no blocking). <br> - On-premises SQL Server: Acts like AlwaysBlock (blocking is applied). |

    Error State and Recovery

    • Fast-Fail: While in the error state, requests fail immediately with the cached error.
    • Exponential Backoff: The pool uses exponential backoff to recover, starting at 5s and doubling each time (5s → 10s → 20s → 30s → 60s), capped at 60s.
    • Recovery: Once a connection attempt succeeds after a backoff period, the error state is cleared, the cached exception is removed, and the backoff interval resets.
    • Monitoring: The ErrorOccurred property returns true when the pool is in an error state.

  8. Understand the purpose of Microsoft.Data.SqlClient.Extensions.Abstractions

    main

    This package provides the abstraction interfaces for Microsoft.Data.SqlClient extensions. It defines core contracts that allow for extensibility without requiring direct dependencies on specific implementation packages.

    Use this package if you are:

    • A Library Author: Building extensions that integrate with Microsoft.Data.SqlClient.
    • A Framework Developer: Creating custom authentication or logging implementations.
    • An Enterprise Developer: Implementing organization-specific security or monitoring requirements.

    Common use cases include implementing:

    • Custom authentication providers
    • Logging and diagnostics integration
    • Extensibility points for third-party integrations.

  9. Select manual test sets via TestSet parameter

    main

    The manual test project is divided into compile-time sets to allow for parallelization of large test runs. You can control which tests are run using the -p:TestSet parameter.

    TestSetCoverage
    1Smaller SQL connectivity and command scenarios.
    2Broad data access coverage (adapters, bulk copy, retry logic, data reader, schema, DNS caching, etc.).
    3Integration coverage (LocalDB, pooling, parameters, transactions, JSON, Kerberos, UDT, vector, etc.).
    AEAlways Encrypted tests.

    Usage Notes:

    • If TestSet is omitted, all sets are compiled and run.
    • You can combine sets by concatenating values (e.g., -p:TestSet=23 or -p:TestSet=12AE).
    dotnet build -t:TestSqlClientManual -p:TestSet=2

  10. How Background Pool Warmup works

    main

    When using ChannelDbConnectionPool with a Min Pool Size greater than 0, the pool automatically performs a background warmup to ensure connections are ready for application requests.

    Key behaviors:

    • Asynchronous & Non-blocking: Warmup runs on a background task. It does not block the application startup or connection returns.
    • Serial Creation: Connections are created one at a time (serially) rather than in parallel to avoid overwhelming the server.
    • Rate-Limited: Warmup uses the same shared rate-limiting mechanism as user-initiated requests. If the rate limiter is saturated, the warmup pass ends gracefully rather than bypassing the limiter or spinning.
    • Automatic Replenishment: The pool monitors its size. If the connection count drops below the Min Pool Size (due to connection destruction, idle-timeout eviction, or other removals), the pool automatically triggers a new background replenishment pass.
    • Coalesced Requests: To prevent redundant work, the pool ensures only one warmup/replenishment loop runs at a time using an internal guard.

  11. Replenishment Triggers for Min Pool Size

    main

    The pool automatically restores connections to the Min Pool Size floor whenever the count drops below that threshold. Replenishment is triggered by the following events:

    • Connection Destruction: When a connection is destroyed upon being returned to the pool (e.g., due to being broken or reaching its lifetime limit).
    • Idle-Timeout Eviction: When an expired connection is removed during a liveness check upon retrieval.
    • Pruning: While the pruning cycle itself respects the Min Pool Size floor and won't trigger replenishment, any removal that crosses the floor via the RemoveConnection choke point will trigger it.

    Replenishment follows the same serial, rate-limited path as the initial startup warmup.

  12. Understand the MDS Extension Architecture

    main

    MDS uses a decoupled architecture to minimize dependency bloat. The core components are:

    1. Microsoft.Data.SqlClient (MDS): The main driver. It depends on Abstractions but does not depend on any specific extension packages.
    2. Microsoft.Data.SqlClient.Extensions.Abstractions: A base package containing common types, enums, and delegates (e.g., Azure Authentication/Attestation/Key Vault types). Both MDS and its extensions depend on this.
    3. Microsoft.Data.SqlClient.Extensions.Azure: An extension package containing implementations for Azure Authentication, Attestation, and Key Vault.
    4. Extension Providers (e.g., AKV Provider): Specialized packages that depend on the Azure extension package.

    This design allows applications to pick and choose only the extensions they need without pulling in the entire Azure SDK dependency tree.