COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Framework Manager => Topic started by: vandreev on 01 Jun 2016 09:39:47 AM

Title: Unable to to add remote SQL Server as data source
Post by: vandreev on 01 Jun 2016 09:39:47 AM
The Cognos server is installed on CentOs. I try to add remote SQL Server as data source. It`s GOSALES samples.
When test connection it is fails with this message:
=======================================================
QE-DEF-0285 The logon failed.
QE-DEF-0325 The logon failed for the following reason:
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-0106 The function "{0}" is not supported.
BME-EX-0144 Trace back:
BmtPlayTransaction.cpp(788): QFException: CCL_RETHROW: BmtPlayTransaction::DoAction
BmtActionsHelper.cpp(488): QFException: CCL_RETHROW: BmtActionsHelper::DoAction
QFSSession.cpp(1160): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()
QFSSession.cpp(1158): QFException: CCL_CAUGHT: QFSSession::ProcessDoRequest()
QFSSession.cpp(2052): QFException: CCL_RETHROW: QFSSession::SessionProcessTestConnection()
Source/QE_RsApi.cpp(2106): QFException: CCL_RETHROW: QE
Source/QEI_ConnectionFault.cpp(829): QFException: CCL_THROW: QE

Script Stack Trace

Action No=1   Action Name=TestDataSourceConnection
=================================================================
Where I make mistake?

Best Regards,
Vladislav
Title: Re: Unable to to add remote SQL Server as data source
Post by: MFGF on 02 Jun 2016 02:33:15 AM
Quote from: vandreev on 01 Jun 2016 09:39:47 AM
The Cognos server is installed on CentOs. I try to add remote SQL Server as data source. It`s GOSALES samples.
When test connection it is fails with this message:
=======================================================
QE-DEF-0285 The logon failed.
QE-DEF-0325 The logon failed for the following reason:
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-0106 The function "{0}" is not supported.
BME-EX-0144 Trace back:
BmtPlayTransaction.cpp(788): QFException: CCL_RETHROW: BmtPlayTransaction::DoAction
BmtActionsHelper.cpp(488): QFException: CCL_RETHROW: BmtActionsHelper::DoAction
QFSSession.cpp(1160): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()
QFSSession.cpp(1158): QFException: CCL_CAUGHT: QFSSession::ProcessDoRequest()
QFSSession.cpp(2052): QFException: CCL_RETHROW: QFSSession::SessionProcessTestConnection()
Source/QE_RsApi.cpp(2106): QFException: CCL_RETHROW: QE
Source/QEI_ConnectionFault.cpp(829): QFException: CCL_THROW: QE

Script Stack Trace

Action No=1   Action Name=TestDataSourceConnection
=================================================================
Where I make mistake?

Best Regards,
Vladislav

How are you connecting? ODBC? OLE DB? Native Client? JDBC?

MF.
Title: Re: Unable to to add remote SQL Server as data source
Post by: vandreev on 02 Jun 2016 06:41:21 AM
I try with this type Microsoft SQL SERVER (SQL 2012/2014 Native Client),
I follow this tutorial: http://www.cognossource.com/installing-ibm-cognos-samples/2/
The Cognos server is on Centos and I don`t know How to make ODBC connection.
If I check config JDBC connection and add the same parametersq, first connection fails, but jdbc connection is succsseed
Title: Re: Unable to to add remote SQL Server as data source
Post by: MFGF on 02 Jun 2016 11:36:49 AM
Quote from: vandreev on 02 Jun 2016 06:41:21 AM
I try with this type Microsoft SQL SERVER (SQL 2012/2014 Native Client),
I follow this tutorial: http://www.cognossource.com/installing-ibm-cognos-samples/2/
The Cognos server is on Centos and I don`t know How to make ODBC connection.
If I check config JDBC connection and add the same parametersq, first connection fails, but jdbc connection is succsseed

To connect using the SQL Server Native Client, you need to have the SQL Server Native Client libraries installed on the Cognos server. My guess is you don't, which is why you're seeing the error when trying the Native Client connection. A connection via JDBC simply needs the JDBC jar file copying to the correct folder on the Cognos server. If this connection works, you can use this - you need to create your Framework Manager models using Dynamic Query Mode.

Cheers!

MF.
Title: Re: Unable to to add remote SQL Server as data source
Post by: vandreev on 04 Jun 2016 01:32:43 AM
Yes you are right I don`t  have the SQL Server Native Client libraries installed on the Cognos server. I copying   JDBC jar file for sql server, to the correct folder on the Cognos server. What type of connection to select? If I select JDBC type connection an click Next, the list of posible jdbc types connectcions not include SQL Server. Is it right way to make JDBC connection to SQL Server or I  make some mistake?
Title: Re: Unable to to add remote SQL Server as data source
Post by: MFGF on 08 Jun 2016 10:28:14 AM
Quote from: vandreev on 04 Jun 2016 01:32:43 AM
Yes you are right I don`t  have the SQL Server Native Client libraries installed on the Cognos server. I copying   JDBC jar file for sql server, to the correct folder on the Cognos server. What type of connection to select? If I select JDBC type connection an click Next, the list of posible jdbc types connectcions not include SQL Server. Is it right way to make JDBC connection to SQL Server or I  make some mistake?

Hi,

Set it up as a "Normal"  Microsoft SQL SERVER (SQL 2012/2014 Native Client) connection - on the first page put in the correct authentication details but any old thing for the database name. On the next page (JDBC) specify the correct server and database name. Although the native connection will fail if you test it, the JDBC connection should work. You can then create a model in FM based on this if you use Dynamic Query Mode.

MF.
Title: Re: Unable to to add remote SQL Server as data source
Post by: vandreev on 20 Jun 2016 07:54:53 AM
Hi,
after make all this steps and select my new data source this error message is displayed:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QE-DEF-0368 Unable to decrypt user name and password credentials from the content store. Review and save the Cognos Configuration settings to configure your reporting environment.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What is the problem now?
Thanks!
Title: Re: Unable to to add remote SQL Server as data source
Post by: MFGF on 20 Jun 2016 08:07:40 AM
Quote from: vandreev on 20 Jun 2016 07:54:53 AM
Hi,
after make all this steps and select my new data source this error message is displayed:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QE-DEF-0368 Unable to decrypt user name and password credentials from the content store. Review and save the Cognos Configuration settings to configure your reporting environment.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What is the problem now?
Thanks!

It sounds like you have a crypto-key problem in your FM install - might be as easy as opening Cognos Configuration on your FM machine and saving the configuration to regenerate them?

If that doesn't work, take a look at the following:

http://www-01.ibm.com/support/docview.wss?uid=swg21343706

Cheers!

MF.
Title: Re: Unable to to add remote SQL Server as data source
Post by: vandreev on 21 Jun 2016 03:27:00 AM
Hi
we follow all this steps http://www-01.ibm.com/support/docview.wss?uid=swg21343706
but the problem exist yet.
When we save Cognos Configuration on server everything is OK
but when I save Cognos Configuration on my PC where is installed FM
I have warning with this message:
----------------------------------------------------------------------------------------------------------------------------------
The cryptographic information cannot be encrypted. Do you want to save the configuration in plain text?
----------------------------------------------------------------------------------------------------------------------------------
and I click Yes
After this I try again to select my data source and I have the old error message.
I make data source to mysql with jdbc driver. I test it was succeeded but when I select it again same error:

QE-DEF-0368 Unable to decrypt user name and password credentials from the content store. Review and save the Cognos Configuration settings to configure your reporting environment.
Title: Re: Unable to to add remote SQL Server as data source
Post by: MFGF on 21 Jun 2016 03:37:27 AM
Quote from: vandreev on 21 Jun 2016 03:27:00 AM
----------------------------------------------------------------------------------------------------------------------------------
The cryptographic information cannot be encrypted. Do you want to save the configuration in plain text?
----------------------------------------------------------------------------------------------------------------------------------

Hi,

This means the crypto keys your FM instance is using do not match the crypto keys the Cognos server is using. Either you have an incorrect gateway/dispatcher URI defined in your FM configuration, or you need to follow the steps to manually copy over the crypto keys (in the technote I posted in the prior reply).

Cheers!

MF.
Title: Re: Unable to to add remote SQL Server as data source
Post by: vandreev on 21 Jun 2016 04:49:55 AM
Big Thanks !!!
I copy folders csk and certs from server to my FM install dir
and The cryptographic information was encrypted.
I successfully connect to  sql server data source and import DB
but when I try to use data source with connection to mysql database
Metadata Wizard generate error message:
-------------------------------------------------------------------------------------------------------------------------------------------------------------
BMT-IMP-0002 Failed to execute metadata request. java.lang.NullPointerException
CAF-WRN-2082 An error has occurred. Please contact your administrator. The complete error has been logged by CAF with SecureErrorID:2016-06-21-13:39:38.908-#10
-------------------------------------------------------------------------------------------------------------------------------------------------------------
I try this to fix it:
http://www-01.ibm.com/support/docview.wss?uid=swg21624136
but unsuccessfully.
Title: Re: Unable to to add remote SQL Server as data source
Post by: MFGF on 21 Jun 2016 04:56:45 AM
Quote from: vandreev on 21 Jun 2016 04:49:55 AM
Big Thanks !!!
I copy folders csk and certs from server to my FM install dir
and The cryptographic information was encrypted.
I successfully connect to  sql server data source and import DB
but when I try to use data source with connection to mysql database
Metadata Wizard generate error message:
-------------------------------------------------------------------------------------------------------------------------------------------------------------
BMT-IMP-0002 Failed to execute metadata request. java.lang.NullPointerException
CAF-WRN-2082 An error has occurred. Please contact your administrator. The complete error has been logged by CAF with SecureErrorID:2016-06-21-13:39:38.908-#10
-------------------------------------------------------------------------------------------------------------------------------------------------------------
I try this to fix it:
http://www-01.ibm.com/support/docview.wss?uid=swg21624136
but unsuccessfully.

How are you connecting to MySQL? The fact you can import from SQL Server shows you don't have the capabilities problem outlined in the technote you linked to. My guess is your MySQL data source connection isn't properly defined.

This is a different issue from your original post, though...

Cheers!

MF.
Title: Re: Unable to to add remote SQL Server as data source
Post by: vandreev on 22 Jun 2016 08:27:55 AM
Thank a lot again,
I made ​​a mistake when I try to import MySql data source.
Now everything is OK thanks to you.
Best regards.