რედაქტირება

Microsoft OLE DB Driver for SQL Server

Download OLE DB driver

The Microsoft OLE DB Driver for SQL Server is a standalone data access application programming interface (API) that's part of OLE DB. It connects C and C++ applications to the Microsoft SQL Database Engine in Azure SQL Database, SQL database in Microsoft Fabric, Azure SQL Managed Instance, and in supported versions of SQL Server. Microsoft first released it in 2018 as version 18 and included it in SQL Server 2019 (15.x).

MSOLEDBSQL19 is the current driver. It's generally backward compatible with SQL Server Native Client (SNAC), and provides functionality beyond both SNAC and the SQL Server OLE DB provider that Windows Data Access Components (Windows DAC, formerly Microsoft Data Access Components, or MDAC) supplies.

Choose your starting point

Production baseline for Azure SQL

Use this snippet as a starting point for a production-oriented Azure SQL connection. It loads the server name and database name from application configuration, authenticates with a managed identity so that no secret appears in the connection string, and enables Tabular Data Stream (TDS) 8.0 encryption with full certificate validation. It sets a per-attempt connect timeout, and retries transient failures with exponential backoff and jitter.

The C++ snippet in this article omits includes, COM initialization, and the logging helper for brevity.

std::wstring BuildConnectionString(const wchar_t* server, const wchar_t* database) {
    std::wstring cs = L"Provider=MSOLEDBSQL19";
    cs += L";Data Source=tcp:"; cs += server; cs += L",1433";
    cs += L";Initial Catalog="; cs += database;
    cs += L";Authentication=ActiveDirectoryMSI";   // managed identity, no stored secret
    cs += L";Use Encryption for Data=Strict";      // TDS 8.0 with certificate validation
    cs += L";Connect Timeout=30";                  // per-attempt connect timeout, in seconds
    cs += L";Connect Retry Count=3";               // idle connection resiliency, not initial connect
    cs += L";Connect Retry Interval=10";
    return cs;
}

// Transient fault codes documented for Azure SQL, plus the resource governance
// codes. Network termination and timeout errors (64, 233, 258, 10053, 10054,
// 10060) are retried a bounded number of times, which is the documented
// guidance for them. 258 is the code the driver reports for a connect timeout.
// 10053 and 10054 can also mean the encryption handshake failed rather than a
// plain network reset, so read the error text before assuming a network fault.
bool IsTransient(LONG nativeError) {
    switch (nativeError) {
        case 615: case 926: case 4060: case 4221:
        case 10928: case 10929: case 10936:
        case 40197: case 40501: case 40613:
        case 42108: case 42109:
        case 49918: case 49919: case 49920:
        case 40020: case 40143: case 40166: case 40540:   // failover subcodes
        case 64: case 233: case 258:
        case 10053: case 10054: case 10060:
            return true;
        default:
            return false;
    }
}

// Retries only errors that a new connection can clear, with exponential backoff
// plus jitter so that concurrent clients don't retry in lockstep.
HRESULT ConnectWithRetry(IDataInitialize* pDataInit, const std::wstring& connectionString,
                         int maxAttempts, IDBInitialize** ppDbInit) {
    HRESULT hr = E_FAIL;
    *ppDbInit = nullptr;
    for (int attempt = 1; attempt <= maxAttempts; ++attempt) {
        IDBInitialize* pDbInit = nullptr;
        hr = pDataInit->GetDataSource(nullptr, CLSCTX_INPROC_SERVER, connectionString.c_str(),
                                      IID_IDBInitialize, reinterpret_cast<IUnknown**>(&pDbInit));
        if (SUCCEEDED(hr) && SUCCEEDED(hr = pDbInit->Initialize())) {
            Log("INFO", "connected on attempt %d/%d", attempt, maxAttempts);
            *ppDbInit = pDbInit;
            return S_OK;
        }

        // Walks IErrorRecords and returns the first record that carries a real
        // SQL Server error number. Transport and timeout failures report a
        // generic wrapper record first, whose native error is 0. Errors the
        // server returns carry the number on the first record.
        LONG native = LogProviderErrors("connect", hr);
        if (pDbInit) pDbInit->Release();
        if (attempt == maxAttempts || !IsTransient(native)) return hr;

        // Cap the backoff at 64 seconds. This also keeps the shift in range
        // when a caller passes a large maxAttempts.
        int shift = (attempt - 1 < 6) ? attempt - 1 : 6;
        DWORD delayMs = (1UL << shift) * 1000UL + (DWORD)(GetTickCount64() % 500);
        Log("WARN", "retrying in %lu ms (attempt %d/%d)", delayMs, attempt + 1, maxAttempts);
        Sleep(delayMs);
    }
    return hr;
}

Connect Retry Count and Connect Retry Interval enable idle connection resiliency, which transparently restores a connection that dropped while idle. They don't retry the initial connect, which is why this snippet also implements application-level retry. Keep both.

This snippet builds its connection string for IDataInitialize::GetDataSource, which uses the spaced keyword names shown here, such as Use Encryption for Data and Connect Retry Count. IDBInitialize::Initialize and ADO use different names for the same settings, such as Encrypt and ConnectRetryCount.

GetDataSource accepts a name from the wrong set without raising an error, and the setting never takes effect. The connection then uses the driver default, which can weaken it. Encrypt=Strict on this path leaves encryption at Mandatory on MSOLEDBSQL19, so the connection drops TDS 8.0 and negotiates encryption in the cleartext prelogin, and it leaves encryption off entirely on MSOLEDBSQL. Not every case fails open: the wrong-set TrustServerCertificate is dropped the same way, which leaves the property at its default false and keeps certificate validation on.

Don't count on an error to catch the mistake. A name that belongs to no set, such as ZzzNotAKeyword, produces an Invalid connection string attribute record, but a name from the wrong set produces nothing. To confirm that a setting took effect, read the property back with IDBProperties::GetProperties before you connect. For the keyword set that goes with each API, see Using connection string keywords with OLE DB Driver for SQL Server.

OLE DB reports diagnostics through the error object rather than the HRESULT alone, so classify failures before retrying. An authentication or configuration error then fails immediately instead of consuming the whole retry budget.

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

For the catalog of Azure SQL transient errors, see transient fault error codes.

Key features

  • Microsoft Entra ID authentication: Passwordless connections with managed identity, service principal, interactive, and integrated flows.
  • Strict encryption: TDS 8.0 connections with full certificate validation, and TLS 1.3 in version 19.2.0 and later versions.
  • Idle connection resiliency: Transparent restoration of a connection that dropped while idle.
  • Multiple active result sets (MARS): More than one pending request per connection.
  • Bulk copy: High-throughput inserts through the bulk copy interfaces.
  • Table-valued parameters: An entire result set passed to the server as a single parameter.
  • Always On availability groups: Listener support with MultiSubnetFailover for fast failover.
  • UTF-8 and UTF-16 support: Character data in both encodings.
  • Data classification: Sensitivity metadata for classified columns.
  • Asynchronous operations: Nonblocking data source and rowset operations.

Get started

Article Description
When to use OLE DB Driver When to choose OLE DB Driver for SQL Server over the other SQL Server drivers.
Download Installer downloads for every supported driver version.
System requirements Supported operating systems, SQL Server versions, and prerequisites to install first.
Building applications Header and library files, installation layout, and what changes when you upgrade from MDAC.
Creating an application The call sequence an application follows, from connecting to executing a command to reading results.
Support lifecycle Which driver versions are supported, and when each one leaves support.

Configure and connect

Article Description
Connection string keywords Every connection string keyword the driver accepts, with its accepted values.
Data source objects Create and initialize the data source and session objects that a connection is built from.
Using ADO with the driver Reach driver features such as MARS, query notifications, and the xml type from ADO.
High availability and disaster recovery Connect through an availability group listener, and the keywords that control failover behavior.
Idle connection resiliency Automatically restore a connection that dropped while it was idle.
LocalDB support Connect to a LocalDB instance for local development and testing.

Authenticate and secure

Article Description
Using Microsoft Entra ID The Microsoft Entra authentication modes the driver supports, including managed identity and interactive.
Encryption and certificate validation Set Encrypt and TrustServerCertificate, and control how the server certificate is validated.
Changing passwords programmatically Handle an expired password and set a new one without leaving your application.
Service principal name (SPN) support in client connections Set the service principal name on a connection so Kerberos mutual authentication succeeds.
Using data classification Read the sensitivity labels that SQL Server returns for classified columns.

Execute commands and process results

Article Description
Commands The ICommand interface and the command object model that command execution is built on.
Command syntax The mix of ODBC SQL, ISO, and Transact-SQL syntax the driver accepts in command text.
Command parameters Mark parameters in command text, and bind the types the driver supports for each.
Using multiple active result sets (MARS) Keep more than one pending result set open on a single connection.
Performing asynchronous operations Start an operation without blocking the calling thread, and poll or wait for it to finish.
Working with query notifications Register for a notification when the result of a query changes on the server.
Processing results how-to articles Worked examples that execute a stored procedure or function and read return codes, output parameters, and rows.

Work with rowsets and cursors

Article Description
Rowsets The rowset interfaces, and the properties that decide which kind of rowset you get.
Fetching rows Use IRowset to fetch rows sequentially, read column values, and release rows.
Updating data in rowsets Request IRowsetChange or IRowsetUpdate to get a modifiable rowset, and control its locking.
Bookmarks Save a row position and return to it later, instead of refetching sequentially.
Rowsets and SQL Server cursors When the driver uses a default result set and when it opens a server cursor instead.

Bulk copy

Article Description
Performing bulk copy operations Move large volumes of rows into or out of a table through data files or program variables.
Bulk copy data using IRowsetFastLoad Bulk copy data into a SQL Server table with the IRowsetFastLoad interface.
Send BLOB data using IRowsetFastLoad and ISequentialStream Use IRowsetFastLoad to stream varying length BLOB data per row to SQL Server.

Table-valued parameters

Article Description
Table-valued parameters overview How table-valued parameters pass multiple rows of data to the server in a single parameter.
Table-valued parameter reference Parameter rowset creation and parameter type discovery.
Inserting data into table-valued parameters The push model and pull model for supplying table-valued parameter rows.
Use table-valued parameters Create a table-valued parameter and pass its rows to a stored procedure.

Work with large and binary data

Article Description
BLOBs and OLE objects Read and write BLOB columns as streams through ISequentialStream.
Getting large data Retrieve a large column value in chunks instead of one bound buffer.
Setting large data Send a large column value to the server from a consumer storage object.
FILESTREAM support Store large binary values that you can read through SQL Server or through the file system.
FILESTREAM how-to articles Worked examples that read and write FILESTREAM columns with streaming interfaces.

Manage tables, indexes, and stored procedures

Article Description
Tables and indexes Create, alter, and drop tables and indexes through ITableDefinition and IIndexDefinition.
Creating SQL Server tables Define columns and call ITableDefinition::CreateTable to create a table.
Creating SQL Server indexes Define a new index on an existing table with IIndexDefinition::CreateIndex.
Stored procedures Call a stored procedure with ODBC CALL syntax or RPC, and read its return code and output parameters.

Data types

Article Description
Data types overview How SQL Server types map to OLE DB types when you bind parameters and columns.
Data type mapping in rowsets and parameters The full type mapping table for rowset columns and command parameters.
Using large value types Bind the varchar(max), nvarchar(max), and varbinary(max) types.
Using XML data types Store and retrieve XML documents and fragments in an xml column.
Using user-defined types Bind CLR user-defined types, which the driver exposes as binary values with type metadata.
Sparse columns support Driver support for sparse columns, which are optimized for storing null values.
UTF-8 support Work with UTF-8 server collations and UTF-8 client encoding.
UTF-16 support How the driver handles surrogate pairs when it fills a client buffer.
Date and time improvements Bind the date, time, datetime2, and datetimeoffset types, and the conversions they allow.

Transactions

Article Description
Transactions overview Local transaction support, and the Microsoft Distributed Transaction Coordinator for distributed transactions.
Isolation levels Set the isolation level for a session, and what concurrency each level allows.
Working with snapshot isolation Use row versioning to raise read concurrency without blocking writers.
Supporting distributed transactions Enlist a session in a distributed transaction with ITransactionJoin::JoinTransaction.

Diagnose and troubleshoot

Article Description
Errors How the driver reports failures, and which interfaces carry the detail.
Retrieving error information Walk the error interfaces to read message text, SQLSTATE, and the native error number.
Accessing diagnostic information in the extended events log Turn on driver tracing and read the resulting extended events log.
Known issues Open issues in the current driver, with workarounds where one exists.
Release notes What changed in each driver release, newest first.

Migrate to the current driver

There are three generations of Microsoft OLE DB providers for SQL Server. Use MSOLEDBSQL19 for new and existing applications, and convert existing connection strings to it. The OLE DB provider was undeprecated and re-released in 2018.

Generation Provider string Status
Microsoft OLE DB Driver for SQL Server (this article) MSOLEDBSQL19, MSOLEDBSQL Supported. MSOLEDBSQL19 is the current driver and the one to use for new development.
SQL Server Native Client (SNAC) SQLNCLI11, SQLNCLI Removed from SQL Server 2022 and SQL Server Management Studio 19. Not recommended for new development.
Microsoft OLE DB Provider for SQL Server SQLOLEDB Ships in Windows Data Access Components. No longer maintained. Not recommended for new development.
Article Description
MSOLEDBSQL major version differences Breaking changes between OLE DB Driver 19 and version 18, including encryption defaults, property type changes, and migration steps.
Updating an application from MDAC What changed between the old OLE DB Provider for SQL Server and the current driver, and what to check before you update.
Updating an application from SQL Server 2005 Native Client The breaking changes in OLE DB Driver for SQL Server since SQL Server Native Client in SQL Server 2005 (9.x).

Reference

Article Description
OLE DB Driver for SQL Server features Index of the driver-specific features, and where each one is documented.
OLE DB programming The COM API model the driver exposes, and how it talks to SQL Server over TDS.
OLE DB how-to articles Index of the OLE DB how-to articles, grouped by task.
OLE DB interfaces The OLE DB interfaces and methods that exhibit provider-specific behavior in this driver.
Schema rowset support Provider-specific schema rowset behavior, including metadata returned from linked servers.
Finding more information Specifications, samples, and community resources outside this documentation set.

Related content

  • Last updated on