OLE DB and SQL Server (SQL Server 2000)
Initialization and Authorization Properties

SQLOLEDB interprets OLE DB initialization and authorization properties as follows.

Property ID Description
DBPROP_AUTH_CACHE_AUTHINFO SQLOLEDB does not cache authentication information.

SQLOLEDB returns DB_S_ERRORSOCCURRED on an attempt to set the property value. The property structure dwStatus member indicates DBPROPSTATUS_NOTSUPPORTED.
DBPROP_AUTH_CACHE_AUTHINFO SQLOLEDB uses standard Microsoft® SQL Server™ 2000 security mechanisms to ensure password privacy.

SQLOLEDB returns DB_S_ERRORSOCCURRED on an attempt to set the property value. The property structure dwStatus member indicates DBPROPSTATUS_NOTSUPPORTED.
DBPROP_AUTH_INTEGRATED If DBPROP_AUTH_INTEGRATED is set to a NULL pointer, a null string, or 'SSPI' VT_BSTR value, SQLOLEDB uses Windows Authentication Mode to authorize user access to the SQL Server database specified by the DBPROP_INIT_DATASOURCE and DBPROP_INIT_CATALOG properties.

If it is set to VT_EMPTY (the default), SQL Server 2000 security is used. The SQL Server 2000 login and password are specified in the DBPROP_AUTH_USERID and DBPROP_AUTH_PASSWORD properties.
DBPROP_AUTH_MASK_PASSWORD SQLOLEDB uses standard SQL Server 2000 security mechanisms to ensure password privacy.

SQLOLEDB returns DB_S_ERRORSOCCURRED on an attempt to set the property value. The property structure dwStatus member indicates DBPROPSTATUS_NOTSUPPORTED.
DBPROP_AUTH_PASSWORD Password assigned to a SQL Server 2000 login. This property is used when SQL Server Authentication is selected for authorizing access to a SQL Server database.
DBPROP_AUTH_PERSIST_ENCRYPTED SQLOLEDB does not encrypt authentication information when persisted.

SQLOLEDB returns DB_S_ERRORSOCCURRED on an attempt to set the property value. The property structure dwStatus member indicates DBPROPSTATUS_NOTSUPPORTED.
DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO SQLOLEDB persists authentication values, including an image of a password, if requested to do so. No encryption is provided.
DBPROP_AUTH_USERID SQL Server login. This property is used when SQL Server Authentication is selected for authorizing access to a SQL Server database.
DBPROP_INIT_ASYNCH SQLOLEDB does not support asynchronous initiation.

SQLOLEDB returns DB_S_ERRORSOCCURRED on an attempt to set the property value. The property structure dwStatus member indicates DBPROPSTATUS_NOTSUPPORTED.
DBPROP_INIT_CATALOG Name of an existing SQL Server database to which to connect.
DBPROP_INIT_DATASOURCE Network name of a server running an instance of Microsoft® SQL Server™. If there are multiple instances of SQL Server 2000 running on the computer, then to connect to a specific instance of SQL Server, the value DBPROP_INIT_DATASOURCE is specified as \\ServerName\InstanceName. The escape sequence \\ is used for backslash itself.
DBPROP_INIT_HWND Window handle from the calling application. A valid window handle is required for the initialization dialog box displayed when prompting for initialization properties is allowed.
DBPROP_INIT_IMPERSONATION_LEVEL SQLOLEDB does not support impersonation level adjustment.

SQLOLEDB returns DB_S_ERRORSOCCURRED on an attempt to set the property value. The property structure dwStatus member indicates DBPROPSTATUS_NOTSUPPORTED.
DBPROP_INIT_LCID SQLOLEDB validates the locale ID and returns an error if the locale ID is not supported or is not installed on the client.
DBPROP_INIT_LOCATION SQLOLEDB returns DB_S_ERRORSOCCURRED on an attempt to set the property value. The property structure dwStatus member indicates DBPROPSTATUS_NOTSUPPORTED.
DBPROP_INIT_MODE SQLOLEDB returns DB_S_ERRORSOCCURRED on an attempt to set the property value. The property structure dwStatus member indicates DBPROPSTATUS_NOTSUPPORTED.
DBPROP_INIT_PROMPT SQLOLEDB supports all prompting modes for data source initialization. SQLOLEDB uses DBPROMPT_NOPROMPT as its default setting for the property.
DBPROP_INIT_PROTECTION_LEVEL SQLOLEDB does not support a protection level on connections to instances of SQL Server.

SQLOLEDB returns DB_S_ERRORSOCCURRED on an attempt to set the property value. The property structure dwStatus member indicates DBPROPSTATUS_NOTSUPPORTED.
DBPROP_INIT_PROVIDERSTRING See SQLOLEDB Provider String later in this topic.
DBPROP_INIT_TIMEOUT SQLOLEDB returns an error on initialization if a connection to the instance of SQL Server cannot be established within the number of seconds specified.

In the provider-specific property set DBPROPSET_SQLSERVERDBINIT, SQLOLEDB defines these additional initialization properties.

Property ID Description
SSPROP_AUTH_REPL_SERVER_NAME Type: VT_BSTR
R/W: W
Default: NULL
Description: Replication server name connect option.
SSPROP_INIT_APPNAME Type: VT_BSTR
R/W: Read/write
Description: The client application name.
SSPROP_INIT_AUTOTRANSLATE Type: VT_BOOL
R/W: Read/write
Default: VARIANT_TRUE
Description: OEM/ANSI character conversion.

VARIANT_TRUE: SQLOLEDB translates ANSI character strings sent between the client and server by converting through Unicode to minimize problems in matching extended characters between the code pages on the client and the server:

Client DBTYPE_STR data sent to an instance of SQL Server char, varchar, or text variable, parameter, or column is converted from character to Unicode using the client ANSI code page (ACP), and then converted from Unicode to character using the ACP of the server.

SQL Server 2000 char, varchar, or text data sent to a client DBTYPE_STR variable is converted from character to Unicode using the server ACP, and then converted from Unicode to character using the client ACP.

These conversions are performed on the client by SQLOLEDB. This requires that the same ANSI code page (ACP) used on the server be available on the client.

These settings have no effect on the conversions that occur for these transfers:

Unicode DBTYPE_WSTR client data sent to char, varchar, or text on the server.

char, varchar, or text server data sent to a Unicode DBTYPE_WSTR variable on the client.

ANSI DBTYPE_STR client data sent to Unicode nchar, nvarchar, or ntext on the server.

Unicode char, varchar, or text server data sent to an ANSI DBTYPE_STR variable on the client.

VARIANT_FALSE:  SQLOLEDB does not perform character translations.

SQLOLEDB does not translate client ANSI character DBTYPE_STR data sent to char, varchar, or text variables, parameters, or columns on the server. No translation is performed on char, varchar, or text data sent from the server to DBTYPE_STR variables on the client.

If the client and the instance of SQL Server 2000 are using different ACPs, extended characters can be misinterpreted.
SSPROP_INIT_CURRENTLANGUAGE Type: VT_BSTR
R/W: Read/write
Description: A SQL Server language name.
Identifies the language used for system message selection and formatting. The language must be installed on the computer running an instance of SQL Server or data source initialization fails.
SSPROP_INIT_ENCRYPT Type: VT_BOOL
R/W: Read/Write
Default: VARIANT_FALSE
Description: To encrypt the data going over the network, SSPROP_INIT_ENCRYPT property is set to VARIANT_TRUE.

Error occurs if the Enable Protocol Encryption is set to ON on the client, and the SSPROP_INIT_ENCRYPT is set to VARIANT_FALSE.

If Enable Protocol Encryption is set to OFF on the client side, and SSPROP_INIT_ENCRYPT is set to VARIANT_TRUE, encryption will be enabled on that particular connection.
SSPROP_INIT_FILENAME Type: VT_BSTR
R/W: Read/write
Description: Specifies the primary file name of an attachable database. This database is attached and becomes the default database for the connection. To use SSPROP_INIT_FILENAME, you must specify the name of the database as the value of the initialization property DBPROP_INIT_CATALOG. If the database name does not exist, then it looks for the primary file name specified in SSPROP_INIT_FILENAME and attaches that database with the name specified in DBPROP_INIT_CATALOG. If the database was previously attached, SQL Server does not reattach it. This option is valid only when connected to SQL Server 2000. 
SSPROP_INIT_NETWORKADDRESS Type: VT_BSTR
R/W: Read/write
Description: The network address of the server running an instance of SQL Server specified by the DBPROP_INIT_DATASOURCE property.
SSPROP_INIT_NETWORKLIBRARY Type: VT_BSTR
R/W: Read/write
Description: The name of the Net-Library (DLL) used to communicate with an instance of SQL Server 2000. The name should not include the path or the .dll file name extension.

The default is provided by the SQL Server Client Network Utility.
SSPROP_INIT_PACKETSIZE Type: VT_I4
R/W: Read/write
Description: A network packet size in bytes. The packet size property value must be between 512 and 32,767. The default SQLOLEDB network packet size is 4,096.
SSPROP_INIT_TAGCOLUMNCOLLATION Type: BOOL
R/W:W
Default: FALSE
Description: Is used during a database update when server-side cursors are used. This property tags the data with collation information obtained from the server instead of the code page on the client. Currently, this property is used only by the distributed query process because it knows the collation of destination data and converts it correctly.
SSPROP_INIT_USEPROCFORPREP Type: VT_I4
R/W: Read/write
Default: SSPROPVAL_USEPROCFORPREP_ON
Description: SQL Server stored procedure use.
Defines the use of SQL Server temporary stored procedures to support the ICommandPrepare interface. This property is meaningful only when connecting to SQL Server 6.5. The property is ignored for later versions.

SSPROPVAL_USEPROCFORPREP_OFF: A temporary stored procedure is not created when a command is prepared.

SSPROPVAL_USEPROCFORPREP_ON: A temporary stored procedure is created when a command is prepared. The temporary stored procedures are dropped when the session is released.

SSPROPVAL_USEPROCFORPREP_ON_DROP: A temporary stored procedure is created when a command is prepared. The procedure is dropped when the command is unprepared with ICommandPrepare::Unprepare, or when a new command is specified for the command object with ICommandText::SetCommandText, or when all application references to the command are released.
SSPROP_INIT_WSID Type: VT_BSTR
R/W: Read/write
Description: A string identifying the workstation.

In the provider-specific property set DBPROPSET_SQLSERVERDATASOURCEINFO, SQLOLEDB defines the following additional properties.

Property ID Description
SSPROP_COLUMNLEVELCOLLATION Type: VT_BOOL
R/W: Read
Default: VARIANT_TRUE
Description: Used to determine if column collation is supported.

VARIANT_TRUE: Column level collation is supported (in case of SQL Server 2000)

VARIANT_FALSE: Column level collation is not supported.

SQLOLEDB Provider String

SQLOLEDB recognizes an ODBC-like syntax in provider string property values. The provider string property is provided as the value of the OLE DB initialization property DBPROP_INIT_PROVIDERSTRING when a connection is established to the OLE DB data source. This property specifies OLE DB provider-specific connection data required to implement a connection to the OLE DB data source. Within the string, elements are delimited by using a semicolon. The final element in the string must be terminated with a semicolon. Each element consists of a keyword, an equal sign character, and the value passed on initialization. For example:

Server=London1;UID=nancyd;

With SQLOLEDB, the consumer never needs to use the provider string property. The consumer can set any initialization property reflected in the provider string by using either OLE DB or SQLOLEDB-specific initialization properties.

SQLOLEDB recognizes the following keywords in the provider string property.

Keyword PropertyID Description
Address SSPROP_INIT_NETWORKADDRESS Network address of an instance of SQL Server in the organization.
APP SSPROP_INIT_APPNAME String identifying the application.
AttachDBFileName DBPROP_INIT_PROVIDERSTRING Name of the primary file (include the full path name) of an attachable database. To use AttachDBFileName, you must also specify the database name with the provider string DATABASE keyword. If the database was previously attached, SQL Server does not reattach it (it uses the attached database as the default for the connection).
AutoTranslate SSPROP_INIT_AUTOTRANSLATE Configures OEM/ANSI character translation. Recognized values are "yes" and "no."
Database DBPROP_INIT_CATALOG Database name.
Encrypt SSPROP_INIT_ENCRYPT Specifies if data should be encrypted before sending it over the network.
Language SSPROPT_INIT_CURRENTLANGUAGE SQL Server language record name. 
Network SSPROP_INIT_NETWORKLIBRARY Net-Library used to establish a connection to an instance of SQL Server in the organization. 
PWD DBPROP_AUTH_PASSWORD SQL Server login password.
Server DBPROP_INIT_DATASOURCE Name of an instance of SQL Server in the organization. 
Trusted_Connection DBPROP_AUTH_INTEGRATED Accepts the strings "yes" and "no" as values.
UID DBPROP_AUTH_USERID SQL Server login record name.
UseProcForPrepare SSPROP_INIT_USEPROCFORPREP Accepts 0, 1, and 2 as values. This keyword is meaningful only when connecting to SQL Server 6.5. It is ignored for any newer versions.
WSID SSPROP_INIT_WSID Workstation identifier.

Page view tracker