In FM cannot open SQL Server data source--DS on Connection and ODBC both working

[cogtrack]

Hello Everyone,

I am having a issue in FM when I try to import table from SQL Server Database.
I have created data source on Cognos connection with sign on information --tested.
created ODBC on my local computer(where FM is installed) -- tested
Connect to SQL Server Database from my local machine using QueryExPlus(Free query tool) -- tested.

In FM, after I selected the data source, got prompt for User ID and Password,
even I type in the correct ID and PSWD, still get re-prompt. just cannot connect to the database,

I use same ID in data source and ODBC.

Has anyone seen this before?

[MFGF]

Hello Everyone,

I am having a issue in FM when I try to import table from SQL Server Database.
I have created data source on Cognos connection with sign on information --tested.
created ODBC on my local computer(where FM is installed) -- tested
Connect to SQL Server Database from my local machine using QueryExPlus(Free query tool) -- tested.

In FM, after I selected the data source, got prompt for User ID and Password,
even I type in the correct ID and PSWD, still get re-prompt. just cannot connect to the database,

I use same ID in data source and ODBC.

Has anyone seen this before?

Are you creating a model in Dynamic Query Mode? If so, it will be attempting to use a JDBC connection defined on the Cognos server.

MF.

[cogtrack]

Hi MFGF,

I am not using DQM, unchecked it when I start the new project.

thanks

[MFGF]

Hi MFGF,

I am not using DQM, unchecked it when I start the new project.

thanks

Ok. Just a sanity check. When you created the data source connection on the Cognos server, did you create it as a SQL Server Native Client connection or as an ODBC connection?

MF.

[cogtrack]

As an ODBC connection. the data source name is same as odbc name on my laptop(where FM installed)thank you.

[MFGF]

As an ODBC connection. the data source name is same as odbc name on my laptop(where FM installed)thank you.

Do you have the same ODBC drivers on your laptop that are on the server? Is the ODBC connection defined on your laptop in exactly the same way? Have you made sure that the ODBC is defined in the 32-bit ODBC Administration console (rather than the 64-bit)? Have you made sure it is a System DSN not a User DSN?

MF.

[cogtrack]

finally I have all the information to answer your questions:
Do you have the same ODBC drivers on your laptop that are on the server? - on server use data direct, on laptop use odbc driver 6.01.7601
Is the ODBC connection defined on your laptop in exactly the same way? --yes.
Have you made sure that the ODBC is defined in the 32-bit ODBC Administration console (rather than the 64-bit)? --yes, we are using 32-bit
Have you made sure it is a System DSN not a User DSN?--yes, also tried user DSN

[cogtrack]

The problem is solved.
Installed new odbc driver on laptop.