IBM Support

Getting error "XQE-DS-0006 Unable to logon to data source" while making a JDBC connection to SQL Server in Cognos 10.1.1

Troubleshooting


Problem

While trying to make a datasource connection to SQL Server 2005/2008 using JDBC option and also having selected 'IBM Cognos Software Service credentials' as signon you end up getting error "XQE-DS-0006 Unable to logon to data source" in Cognos 10.1.1.

Symptom

We receive the following error message while testing the JDBC datasource connection to SQL Server

XQE-DS-0006 Unable to logon to data source
This driver is not configured for Integrated Authentication. Client ConnectionId:<Random_number>
sqljdbc_auth(Not found in java.library.path)

Screenshot of the error is also attached "error.zip"

Cause

DLL files not copied to the right location

Environment

Windows, SQL Server

Resolving The Problem

The file requirements for the IBM Cognos Dynamic Query Mode(JDBC connection) are dependant on the data source security strategy.  For non-integrated security connections that pass the saved signon information, the IBM Cognos 10 Dynamic Query Mode only requires access to the Microsoft Type 4 JDBC driver.  For integrated security connections that use the service credentials to connect to the data source, IBM Cognos Dynamic Query Mode requires access to both the Microsoft Type 4 JDBC driver and its associated 32 or 64 bit authentication dynamic linked library (DLL). 

The following table lists the type of IBM Cognos BI data source authentication types and the files required to establish successful connection.



To configure Microsoft SQL Server integrated security connectivity for use within IBM Cognos 10 follow these instructions:

1.  Download and install the Microsoft SQL Server JDBC driver from [http://www.microsoft.com/downloads/en/details.aspx?FamilyID=a737000d-68d0-4531-b65d-da0f2a735707&displaylang=en] . From this link you can simply download 1033\sqljdbc_3.0.1301.101_enu.tar.gz and extract it. When you unzip you will find sqljdbc4.jar and enu\auth\x64\sqljdbc_auth.dll files.

2.  Copy sqljdbc4.jar file to <Install Dir>\v5dataserver\lib and the <Install Dir>\p2pd\web-inf\lib directory.

3.  If your Cognos install is 64-bit you will need to copy sqljdbc_auth.dll from x64 directory. Copy enu\auth\x64\sqljdbc_auth.dll to <Install Dir>\v5dataserver\lib and the <Install Dir>\p2pd\web-inf\lib directory.
If your Cognos install is 32-bit you will need to copy sqljdbc_auth.dll from x86 directory.

4.  Locate the <Install Dir>\v5dataserver\databaseDriverLocations.properties.sample file and make a backup copy of the same having name as Copy_databaseDriverLocations.properties.sample file. Rename Copy_databaseDriverLocations.properties.sample it to databaseDriverLocations.properties file.

5.  Open databaseDriverLocations.properties file in a text editor.

6.  Set the databaseJNIPath to the location of the sqljdbc_auth.dll file. For this example the databaseJNIPath will be <Install Dir>\v5dataserver\lib.  The completed entry would represent the following text:
databaseJNIPath=<Install Dir>\\v5dataserver\\lib [For Windows only]
databaseJNIPath=/<Install Dir>/v5dataserver/lib [For Unix only]

7.  Set the databaseClassPath specifying the complete path of the files. The completed entry would represent the following text:
databaseClassPath=<Install Dir>\\v5dataserver\\lib\\sqljdbc_auth.dll;<Install Dir>\\v5dataserver\\lib\\sqljdbc4.jar [For Windows only]
databaseClassPath=<Install Dir>/v5dataserver/lib/sqljdbc_auth.dll:<Install Dir>/v5dataserver/lib/sqljdbc4.jar [For Unix only]

Note:- Regarding the <Install Dir> path mentioned in point(6) and (7) above, space in Installation Directory paths (as most default windows installs will have) may cause an issue in locating for the dlls. Alternatively you can copy the sqljdbc_auth.dll and sqljdbc4.jar into a folder such as C:\MSSQL_AUTH and reference those in the databaseDriverLocations.properties file.

8.  Save the changes and close the file.

9.  In order for the Microsoft SQL Server driver to be picked up by IBM Cognos10, the IBM Cognos 10 service will need to be restarted.

Note : If you have deployed Cognos on other Application server then Tomcat eg: Websphere/Weblogic/Oracle Application server then you will have to rebuild and redeploy p2pd.ear file and then restart Cognos services.

10. Now test if you are able to make successful connection using JDBC. Just incase if it still fails it does suggest that you need to install MS SQL Server (32-bit) client on all the application servers. Note the client should be 32-bit as all connection made through client are 32-bit only.

[{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Install and Config","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.2.2;10.2.1;10.2;10.1.1;10.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 June 2018

UID

swg21584451