cancel
Showing results for 
Search instead for 
Did you mean: 

Failed to retrieve data from the database.

Former Member
0 Kudos

Having a bear of a time getting a report into the daily routine for scheduling.  It is based off of a SQL server stored procedure, which has two databases that it is accessing to pull information from.  One database is local to the same server, the other is a linked server on a different box. 

when i go to the report within Crystal Server, and right click select Database Configuration, i see prompts for both login information for databases.    If this helps at all, often for this one the database login information is yellow background - this was the first i saw it and seems to correlate to my problems.  Other reports have that background to be white

In Crystal reports, the report runs fine - i get prompted for user information for one database (the remote, linked one), i don't for the other because it is windows authentication, and my ID authenticates fine. 

In Crystal Server, i have two database authentication screens, and i'm 100% positive i'm entering the information right.  each has "use same database logon as when report is run" selected.    I have done this on a previous version of this report, but for the last 2 weeks, since recent edits, it will just not run via the scheduler. 

When i try to view the report, i get prompted for just one database (the remote linked one), not two, but then get this msg:

The viewer could not process an event. The database logon information for this report is either incomplete or incorrect.

Within Crystal Reports, i have used Verify Database to make sure the latest version of the Stored Procedure are being used, and again, it runs fine within Crystal Reports.

Any help would be greatly appreciated - especially maybe where in some log files could i see more info for what is being passed. 

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

I finally got it to work, mainly by switching my ODBC connection to use SQLServer Native Client, vs the generic SQLServer ODBC, but i'm still having it fail on one out of 4 possible combinations.  but its really confusing as to why it only works one way and not another (details to follow). 

I know that this info can be confusing to try and lay out in a post - hopefully it makes sense what im tryign to do.  What would be helpful is if someone could try and help me figure out what might be different credentials wise about the server running something vs running it manually in Crystal Report.  I couldnt even find the place that shows what creds Crystal Server service runs with, if at all?

Here is what i am trying to do and what i am seeing:

SQL Server 2008R2

Crystal Reports 2011 v 14.0.4.738

Crystal Server 2011 v 14.1.0

All Windows Server 2008

Data resides on remote SQLserver, where it is two linked tables within two databases on that server.

Data is produced using a Stored Procedure that summarizes data from Table A, Database A, linked to Table B in Database B via the primary key, table_id in B.B.  The rights to read the information is explicitly granted to a database service account that i supply credentials for.

This Stored Procedure can reside on this remote server in Database A, or on my local SQLserver that i have development rights to (and that holds the Crystal Server).  In other words, i can use either the remote server's SP or my local SQLServer's SP to generate the appropriate output and build a Crystal Report from that info. 

What works:

   Crystal Reports running the report manually using the Local version of the Stored Procedure.  All permissions work (i am prompted for credentials for the remote ODBC connection, but the local server automatically accepts my personal credentials)

     Crystal Reports running the report using the remote version of the Stored Procedure - same credentials are prompted for

     Crystal Server running a scheduled version of the report using the remote Stored Procedure - the scheduler only requires the remote creds to be supplied.

What doesn't work:

     Crystal Server running a scheduled version of the report using a local version of the Stored Procedure.   In the database configuration, it asks for two sets if creds - both the remote and the local  SQL server.  Both should work - and we seem to be failing on logging into the Database B tables, because in another test, i simplifed the Stored Procedure to only be pulling data from Table A - that seemed to work ok in the scheduler.   as soon as i try to access the table on Database B, it fails and says incorrect login information.

Hope that's clear enough to help you offer guesses as to whats going on and maybe some solutions to try

thanks!

former_member292966
Active Contributor
0 Kudos

Hi John,

When a report is scheduled, it's running using the service account assigned to Crystal Server.  If you are getting prompted twice, that usually means the report can't find the database to connect to.

If you can go onto the server Crystal Server is running on, go into the ODBC administrator and see if you can create an ODBC connection to your local database and test the connection.

You are correct that if it works on one database and the second database is configured the same, the report should work.  Because the report is running on the Application server and not your local machine, I'm curious to see if the problem is a simple issue of the two machines not being able to communicate with each other.

Not to overlook the obvious but make sure the SQL Server account you are using in your database configuration is active in your local database as well.

Is your local database set to use SQL Server Authentication or Windows Authentication?  It should be SQL Server Authentication to make sure the account you are passing is being used and not the Crystal service account.

Good luck,

Brian

Former Member
0 Kudos

Hi Brian,

Thanks for the info and suggestions.   I guess there are two basic questions that i have  that are related:

1) Why would the report work fine when i run it manually from Crystal Reports application while on a terminal services session on the same server as Crystal Server (so the ODBCs are exactly the same),  but not when i run it from Crystal Server?

2) What is the service account Crystal is using?  I don't see anything within the services.msc  Windows services console (unless i'm blind)  Can you point me to where i can locate the /Crystal Server configurations that show which service account it is using (BTW, i think i know what it is supposed to be, and i have already given that account full read permissions on both of the databases i am trying to reach.   I was thinking that if i can modify that account to be my own, maybe it will be able to run and we can have a clue that there's something not quite right with the service account)

thanks!

former_member292966
Active Contributor
0 Kudos

Hi John,

1)  There are several reasons why a report works in one but not the other.  Permissions is the most common.  Difference in versions is another.  If both environments are similar, ODBC is using the same driver and ODBC test connection is fine then permissions is usually the next thing I look at but it does not rule out a problem with runtime files.

2)  When you look at the services Crystal uses, if the log on tab is empty then that service is using the local Windows service account.  Mine is empty but you could try using your account, just make sure your account does have full admin permisssions on the server.

Something to try if you can is to install Crystal Reports on the application server and run the report in Crystal.  If it fails to logon here as well then we should get a better idea as to what's happening.

Good luck,

Brian

Former Member
0 Kudos

Hi Brian,

Its confusing to me because as you suggested in the third paragraph, i actually am running Crystal Reports on the application server, and that is the behavior i'm seeing - it runs fine on my account running Crystal Reports, but not when the server does it.  I only have this one environment.  I guess i can try to login with the service account and see if it fails to run it from Crystal Reports too, and then i can be sure its the service account permissions...

But in regards to #2,I see no place in the windows services tab where you can see a Crystal Server or SAP labeled service that i can check or modify service accounts.  The only ones that are related are the SQL Server services, and all of them are using the windows service account that i said has explicit permissions on the remote database. 

Where else would you configure the Crystal Server services, if not the windows services control panel?

thx

former_member292966
Active Contributor
0 Kudos

Hi John,

Well now that definitely leads me to believe its permissions.  The service to look for is Server Intelligence Agent, go figure.

You mentioned the Database Configuration lists 2 datasources.  When you open the report in Crystal and run it against the local database, go to Database | Database Expert, does it show the report using 2 connections or just 1?

The confusing thing is that it's having a problem with your local database.  My problems usually are with a remote database.

If you are using ODBC, make sure you are using the 32 bit ODBC Administrator.  Crystal is 32 bit and can't use the 64 bit drivers.

Thanks,

Brian

Former Member
0 Kudos

thanks Brian,

yes, that Server Intelligence Agent is running as local system account.  This may or may not have anything to do with it, cause...

But i do believe i found the error.  its kinda weird cause the remote system is setup in an odd way that probably has permissions in this other way, but not standard.   When i logged in to the console with the service account, i was unable to run the stored procedure as expected from SMSS.

When i looked at the fully qualified 4 part name, what i was using for the Stored Procedure was server1.database.dbo.tablename.  This is a direct link to the actual data, and i gather that my personal account has permissions on it, so running it from SMSS works fine.  When i logged in with the services account, this same query doesn't run. 

However, what i think confused us is the dba actually setup an alias to that catalog in the same database2 that the service account does have permissions on, and when i change my stored procedure to use server2.database.dbo.tablename, it does work.   I think there are also matching ODBC connections to server1 and server2, but i think its all within the stored procedures and linked servers, not ODBC

I still have to confirm that it works from the Crystal Server scheduler, but i think it should be ok...it certainly look so.  Murphy just reared his head and the Crystal server is not working for some other reason...i will get back later when i have more info

thx again.

Former Member
0 Kudos

Yes, that worked in the scheduler too.  Definitely odd combo of permissions crossing.  Logging in with the service account allowed me to reproduce the behavior in something other than the scheduler, helping me isolate the problem. thanks for your help, Brian. 

former_member292966
Active Contributor
0 Kudos

Wow.  Great work tracking down the problem.

former_member292966
Active Contributor
0 Kudos

Hi John,

When you schedule the report, is the report using the same SQL Server account as you do in Crystal?

If you are using ODBC, go into the ODBC Administrator and test the database connections.  Make sure they are able to connect properly.

Make sure you are using the same database drivers as in Crystal.

I'm a bit confused, if the stored procedure is using tables from 2 different databases why Crystal is prompting for the two connections.  SQL Server should be handling the login to the second database.

Are there subreports in the report?  Could it be the subreports that is returning the message?

If you create a test report with a couple of fields and run it in Crystal Server, does it run or do you get the same message?

Good luck,

Brian

Former Member
0 Kudos

Thanks Brian,

i don't think its the same account - there is a service account that is used for the Server, whereas its my account when i run the report in Crystal reports...my login to Crystal Server isnt running the reports, right?  

You may have provided a clue though - in my connections screen i have two connections with the same name, one says trusted connection, the other shows userid= service account.  Im trying to trim it to one, and retesting is not quite right, but im getting further than the immediate fail.