Creating a custom OLEDB Provider string for BPC

Purpose

This article is intended for technical users who wish to modify the database connection criteria which SAP’s Business Planning & Consolidation product uses by default. You should therefore be familiar with BPC, databases and have the administrator rights to modify BPC settings in order to make use of this information.

When SAP Business Planning & Consolidation (BPC) is first installed it will use the information provided during the installation process to construct a default OLEDB Data provider string. This string will be used to connect to the BPC database using these default settings.

If you wish to change the provider, or any of the settings related to that provider’s connection, then this Wiki article will show you how this can be done.

Overview

The following terminology will be used in this article:

BPC uses a %Provider% variable setting to define the connection method to its default SQL Server database (defined during installation). This variable can be over-ridden so that the default provider is no longer used, and instead a custom string can be defined which offers more control over the parameters used to make the connection to the OLEDB data source.

The following sections describe how the settings can be found and the options for changing them.

The BPC Provider information

BPC's AppServer.tblServerInfo table

Contains the field 'Provider' that identifies the Provider type to use in the %Provider% variable. This %Provider% variable is subsequently used to define a connection string in the AppServer database’s table tblServerDefaults.

The default setting is “SQL” to signify that a SQL Server provider type is being used. This setting has been created by BPC, and it tells BPC to use the default installed SQL Server OLEDB  provider type (“SQLOLEDB”). This setting is used to feed into the default “%Provider%” variable.

This “%Provider%” variable is reflected in the BPC Server Manager -> 'Options' -> 'Server Options' settings. It should not be changed in Server Manager. Instead, if you need to override the current provider, then use the OLAPContr and Provider data in the AppServer tables. Details of these parameters are listed in the following sections.

BPC's AppSerer.tblServerDefaults table

In the AppServer database is a table called tblServerDefaults. It contains a field called OLAPContr which contains the connection string definition. This connection string contains a series of parameters which define the provider used to connect to the data source.

Example of the OLAPContr string:

Provider=%Provider%;Persist Security Info=False;Initial Catalog=%DBNAME%;Data Source=%SQLServer%;integrated security=SSPI;Connect Timeout=90

Provider Settings

Let’s look at each of those OLEDB Provider settings in more detail.

1.       Provider

• the application which provides the information from the data source (usually a database)
• expressed as a unique string name, or by default as the variable “%Provider%”
• a list of installed Provider names can be found in the Server Objects section of your SQL Server via Management Studio)

2.       Persist Security Info

• a Boolean value (True or False)
• Microsoft recommends this be left at the default FALSE setting as this is more secure.

3.       Initial Catalog

• Specifies which database to connect to by default
• Good practise is to set this

4.       Data Source

• The NetBios name, FQDN name, or IP Address of the server hosting the database specified in the Initial Catalog parameter.
• A specific port number can be appended if required (e.g. “myserver,1433”)
• This can also be expressed as a Universal Naming Convention path (‘UNC’), e.g. “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\machinename\sharename\folder\file.mdb”

5.       UserId/Pwd or Integrated Security

• If using standard security (e.g. user name and password) then the User Id and Password fields contain this information.
• If using Microsoft’s Integrated Security capability (using the logged on user’s account and password) then “SSPI” can be specified in this parameter. When set in combination with the ‘Persist Security Info = FALSE’ parameter this will provide the best security.

6.       Connect Timeout

• A value specified in seconds. The default is 30 seconds.
• Determines the amount of time that the connection will wait whilst trying to connect to the Data Source.
• NOTE: This does not govern the time allowed for the query from the Data Source to return.

7.       Network Library

SQL Server allows you to use the following network libraries:-

• dbnmpntw - Win32 Named Pipes
• dbmssocn - Win32 Winsock TCP/IP
• dbmsspxn - Win32 SPX/IPX
• dbmsvinn - Win32 Banyan Vines
• dbmsrpcn - Win32 Multi-Protocol (Windows RPC)

The default is “DBNETLIB”, which defaults to the current SQL Server configuration settings.

Examples

Microsoft SQL Server Native Client

The following example defines an OLEDB Provider that uses the Microsoft SQL Server Native Client connectivity. In this specific example it is using the Native Client provider name for SQL Server 2008 (i.e. ‘SQLNCLI11’) and standard security is being used.

Provider=SQLXMLOLEDB.4.0;Data Provider=SQLNCLI11;Data Source=myServerAddress; Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

Related Content

Microsoft article on OLE DB providers - http://msdn.microsoft.com/en-us/library/windows/desktop/ms709836(v=vs.85).aspx

Wikipedia definition of OLEDB: http://en.wikipedia.org/wiki/OLE_DB

Microsoft page discussing the Connection String for OLEDB: http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbconnection.connectionstring(v=vs.110).aspx

Microsoft’s OLEDB Connection String parameter definition page: http://msdn.microsoft.com/en-us/library/ms722656(VS.85).aspx