OLE DB: Connection string

Format

The connection string format is based on ODBC connection strings: it consists of key/value pairs.

Each key/value pair is separated from its neighbouring key/value pair by a semicolon (;).

In a key/value pair, the (name of the) key is separated from its value by an equal sign (=).

A key name is composed of any printable character. An equal sign needs to be escaped by another equal sign.

Thus, a connection string looks like

keyOne=valOne;keyTwo=ValTwo;…

Generic key names

Generic key names are those with the same meaning across all drivers.

Commonly seen key names include:

`Provider`	see below
`Data Source`
`User ID`
`Password`
`Integrated Security`	names the authentication service for TGTs - Ticket Granting Tickets
`Initial Catalog`	Specifies to the name of the database (or schema on Oracle) that is to be connected to
`General Timeout`
`Mode`	for example to open a read-only connection
`Extended Properties`	see below

Microsoft's complete list of generic keys is here.

Key: Provider

An important key is provider. It needs to be set to the value of an OLE DB provider's Version independent Prog ID or version dependent Prog ID (for example Microsoft.ACE.OLEDB.12.0).

The system automatically chooses the most recently installed provider if the version independent Prog ID is chosen.

If the connection string is missing a provider key, the provider defaults to MSDASQL which is the OLE DB Provider for ODBC.

Key: Extended Properties

The value for the key Extended Properties is passed to the respective OLE DB Provider.

If this value itself consists of key/value pairs that are separated by a semicolon, the value must be enclosed in quotes.

For example, the OLE DB provider for Microsoft Access database engine allows to specify a few options when connecting to Excel. In the following connection string, the single quotes around Excel 12.0. XML;HDR=YES;IMEX=1 make sure that the complete value is passed to the provider (rather than parsed by the OLE DB framework).

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=P:\ath\to\workbook.xlsx; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';

I assume that non-standard key/value pairs are passed to a provider anyway (that is, without using the Extended Properties key). The following connection string seems to be equivalent:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=P:\ath\to\workbook.xlsx; Excel 12.0 Xml;HDR=YES;IMEX=1';

Values without a key

Although Microsoft's OLE DB connection string specification states that a connection string consists of key/value pairs, it still seems possible that, at least in the case of the OLE DB Provider for Microsoft Access database engine, it is possible to have a value without a key name.

Consider the following connection string:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=P:\ath\to\workbook.xlsx; Excel 12.0 Xml;HDR=YES;IMEX=1';

Clearly, the value Excel 12.0 does not have a corresponding key value.