IBM Support

How to Use New Microsoft OLE DB Driver in Cognos Analytics

Troubleshooting


Problem

Microsoft recently confirmed that SQL Server Client is deprecated and it will no longer be used with the SQL Server Products for application development. 

Further reading is available here --
https://learn.microsoft.com/en-us/sql/relational-databases/native-client/sql-server-native-client?view=sql-server-ver15
However, creating a connection to SQL Server still defaults to the old provider; SQLNCLI11. This poses a few issues for users who are unable to install the old native client on their OS or users who are on newer versions of SQL server. 

Symptom

When a connection is created to SQL Server DB using the Native Client, the connection string still references Provider=SQLNCLI11, and there is no way to change this provider value in the string. This issue presents a problem on Windows Server 2022 where the Client is not available by Default. 

Cognos displays the error after the connection test is unsuccessful:
 

QE-DEF-0285 The logon failed.

QE-DEF-0322 The connection string is invalid.

RQP-DEF-0068 Unable to connect to at least one database during a multi-database attach to 1 database(s) in:

testDataSourceConnection

UDA-SQL-0031 Unable to access the "testDataSourceConnection" database. Check that the connection parameters to the database are configured correctly. For example, ensure that the data source connection contains the signon information, such as a password, to connect to the database.

UDA-SQL-0534 Invalid connection string.

UDA-SQL-0206 The OLEDB driver returned the following value: HRESULT= -2147221164".

BME-EX-0144 Trace back:

BmtPlayTransaction.cpp(788): QFException: CCL_RETHROW: BmtPlayTransaction::DoAction

BmtActionsHelper.cpp(488): QFException: CCL_RETHROW: BmtActionsHelper::DoAction

QFSSession.cpp(1190): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()

QFSSession.cpp(1188): QFException: CCL_CAUGHT: QFSSession::ProcessDoRequest()

QFSSession.cpp(2082): QFException: CCL_RETHROW: QFSSession::SessionProcessTestConnection()

Source/QE_RsApi.cpp(2112): QFException: CCL_RETHROW: QE

Source/QEI_ConnectionFault.cpp(880): QFException: CCL_THROW: QE


image-20230505141603-2
 

Cause

The error occurs because Cognos is looking for the Native Client SQLNCLI11 rather than the new driver MSOLEDBSQL

Resolving The Problem

The word-around is to create a data source Connection using "Other Type" in the selection list rather than Microsoft SQL server (Native Client). 

This allows the user to manually define the connection string and enforce the usage of the MSOLEDBSQL driver. 
 
  1. Download oledb driver from Microsoft (https://learn.microsoft.com/en-us/sql/connect/oledb/download-oledb-driver-for-sql-server?view=sql-server-ver15) and verify it is present in the %SYSTEMROOT%\system32\ folder on Windows OS.
  2. Create New Connection >Select Other Type
  3. Create Signon with credentials (if needed)
  4. Paste connection string from previous native client failed connection and replace Provider=SQLNCLI11 with Provider=MSOLEDBSQL. No other changes


OLD string with Microsoft SQL Server Native Client:

^User ID:^?Password:;LOCAL;OL;DBInfo_Type=MS;Provider=SQLNCLI11;User ID=%s;Password=%s;Data Source=test1234.zzz.aaa.com;Provider_String=Initial catalog=testDB;@COLSEQ=

NEW String with "Other Type":

^User ID:^?Password:;LOCAL;OL;DBInfo_Type=MS;Provider=MSOLEDBSQL;User ID=%s;Password=%s;Data Source=test1234.zzz.aaa.com;Provider_String=Initial catalog=testDB;@COLSEQ=

image-20230505141332-1

5. Test the Connection and see it succeeds

It is important to note that this workaround will not allow Web-based modeling.

Related Information

SQL Server Native Client

Document Location

Worldwide

[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSTSF6","label":"IBM Cognos Analytics"},"ARM Category":[{"code":"a8m50000000Cl3zAAC","label":"Administration"}],"ARM Case Number":"","Platform":[{"code":"PF033","label":"Windows"}],"Version":"All Versions"}]

Document Information

Modified date:
23 June 2023

UID

ibm16988613

Page Feedback