BI 4.0 - Report can be viewed, but when scheduled/ran I get DB 18456 error

[Former Member]

Hello everyone,

I have a report deployed to CMC that was migrated from a BOXI R2 environment. In the R2 environment the report was created against oledb driver, deployed and we ran it against an ODBC SQL Server data source. Everything was fine. Report could be previewed and scheduled/ran.

After migrating the report to SAP BI 4.0 using the deployment tool, I can view the report in 4.0 CMC fine which means that the report can establish a connection to the database. I could verify this by changing the data source name in ODBC admin in windows and it didn't work, so I know it was using the connection.

The strangest thing is that when I run the report (Right click > Schedule or Right click Run Now) I get this error:

Error in File ~tmp1bc86f443c2ea80.rpt: Unable to connect: incorrect log on parameters. Details: [Database Vendor Code: 18456 ]

This error seems to suggest that I didn't provide the correct logon parameters or that I am not authorized to access this database (correct me if I'm wrong). However it doesn't make sense because the exact same parameters were used for Viewing the report which works.

I verified a thousand times that the login info in Database Configuration is correct. I'm out of ideas. I expected this migration to be rather smooth since we're going only 2 versions up.

Anyone? Much appreciated.

BI 4.0 is running on Windows 2008 Server 64 bit.

Regards

Bart

[former_member11696]

Hi Bart,

What DB client are you using? And is it 32 or 64 bit?

Try adding the same DSN to both ODBC Admin, one located in \windows\system32 and the 32 bit is in \windows\syswow64 folder.

Don

[Former Member]

Thanks for the reply Don,

I did not have the data source set up on the \windows\system32 admin so I have done that (exact same config as on the 32 bit one).

I'm still getting the erorr when I try to schedule/run the report. Viewing still works as before.

ODBC data source is using SQL Native Client for SQL Server 2005 (which is our database - set up on a different machine).

Is there any reason why it wouldn't work when scheduling the report vs viewing it? I thought the backend process for getting the data would be the same...

Thanks again.

Best regards,

Bart

[former_member11696]

Hi Bart,

What I have discovered is the SQL 2008 Client Tools works for SQL 2005 and it has both a 32 and 64 bit client. See if installing that works on the BOE PC and setting up the ODBC using the SQL Native 10 driver.

You can download the Client Tools from MS.

Don

[Former Member]

Hi Don,

Thanks for the suggestion but I have tried that before. I tried again right now and I still get the problem.

I used the SQL Server Native Client 10.0 (version 2007.100.1600.22, SQLNCLI10.dll) drivers.

I still, however, receive the same error message described above.

Regards,

Bart

[former_member11696]

Hi Bart,

Just to clarify, you create the report off of OLE DB and then publish to BOE and use ODBC to connect correct?

If so the error is likely generated due to the changes in the driver. Use the same connection method in BOE and you do in CR Designer.

Don

[Former Member]

Hi Don,

Correct, we use oledb/ado to connect at design level (easier for the designers) and then publish to BOE and run as odbc. It was done this way for historical reasons and there are thousands of reports done like this. Now, the reason why I expect this to work on version 4 is that it worked in boxi r2 without issues. Did something change? And I still find it a mystery that viewing the report works yet running it does not. Is it a 32bit/64bit issue maybe?

Regards,

Bart

[former_member11696]

Hi Bart,

Yes it did change, MS SQL 2008 does not support the MDAC version of the SQL Client which your report designers are likely using. So converting on the fly can and will generate errors if they are using features unique to SQL Server 2008.

BOE assumes your reports work as is and no conversions are required, designing on OLE DB and running against ODBC is considered a conversion.

I don't see any advantage of doing this.... You still have to enter a Server Name User, Password and Table to use.

It's going to be a pain to set them all to OLE DB but better to do it now than later....

Try one to verify it works...

Don

Oh, and the reports also require being updated to Native 10 driver.

Edited by: Don Williams on Feb 28, 2012 1:53 PM