Crystal Reports forgot SQL database login

We are running Crystal Reports 7 with a SQL database. Recently, it seems that CR has forgotten the database login information for the reports.  This seems to affect only certain users.  If I login as myself (an administrator) the reports work just fine.  But other users get asked for the database login information when refreshing the report.  It seems to be only certain users and it doesn't matter which computer they use.  How can I correct this?  Any ideas would be greatly appreciated.  Thanks.

~bruno71
bruno71Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SweetsGreenCommented:
Sombody must of messed with the users/permissions on your DB server.

The CR probably uses Integrated Security to log in and since you are an admin the SQL Server probably has you as full access.

You have to add the users ( or the group they are in) to have permission to access the databases/tables that the report uses as wells as access to the default database specified in the report (probably master)
GJParkerCommented:
You ned to supply a lot more information than this

how are the users accessing the reports ?
waht type of security are you using on SQL - Trusted connections or SQL security.

Like sweetsgreen I would supect that something has changed on teh sqlserver but without technical information we're just guessing.

HTH

Gary
Mike McCrackenSenior ConsultantCommented:
Agree.

Are you running from an application?

mlmcc
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

bruno71Author Commented:
The users access the reports either through Crystal Reports itself, or by using a compiled report (to free up CR licenses).  Also, I believe they are using SQL security (username/password).  Unfortunately, I wish I knew more about the inner workings of SQL, but I know just enough to get me by.  Where should I check for the correct permissions on the SQL server (SQL Enterprise Manager, etc...)?  Thanks for all the help.

~bruno71
SweetsGreenCommented:
to look at the permissions thru Enterprise manager either look under the servername->security-> logins (this will give you all the users for the server)
or ServerName->Databasename->Users (only shows you who has access to that specific databse)

this will show you both SQL users and Windows users/groups
The users should only need public access ...but you can try giving them "Owner" just to see if thats the problem.

Also you should really look at the dataconnection properties in the Report itself to make sure that it is SQL security and not Intergrated (windows) as well as check what user you should be looking for in enterprise manager.

But I'm pretty sure that if you are using the Crystal Reports Designer to view reports then it should always prompt for a username and password if using SQL Security the only way to prevent that is to use Intergrated security.

Another thing that could cause the login prompt (when Integrated security is set) is if you have a default database specified other than the databse used for the report  (ie Master).  If the user does not have permission to access this database CR will prompt for a user that can access the default even though the report is based off of another database.  You can fix this by giving the user access to that databse or simply changing the default database to the same one that the report is run off of.
bruno71Author Commented:
The correct logins are setup under Security > Logins as well as DatabaseName > Users.  In the ODBC settings, the database connection is setup with the correct Server Name, Login, Default Database, etc...  Nothing has changed on the local workstation settings and I don't know of anything that has changed on the SQL server.

Here is something else...
When I logon to a workstation as a regular user, I can open a report in the CR Designer.  When I click the Refresh button, it asks for the SQL server login.  It has the correct Data Source, Database, and Login ID.  All I do is click 'OK' and it refreshes the report.

However, after I compile that same report and try to run it, it does not work.  The report dialog comes up and I choose 'Print to Window' and click 'Print'.  An error message that says "ODBC error: [Microsoft][ODBC Driver Manager] Connection not open" pops up.  I click 'OK'.  Then the Logon Parameters box pops up.  All the information is the same as it appeared in the CR Designer.  Except this time when I hit 'OK', nothing happens.  The login dialog box just keeps coming back.

Any more ideas or detailed information are greatly appreciated.  This is becoming more and more urgent.  Thanks.

~bruno71
Mike McCrackenSenior ConsultantCommented:
Does the report have several subreports?

mlmcc
bruno71Author Commented:
No, there are no subreports.  Only serveral tables linked together from the same database.  Also, this happens on all reports, that I know of.

~bruno71
Mike McCrackenSenior ConsultantCommented:
bruno71Author Commented:
I had to contact tech support.  They directed me to change some permissions in the registry to fix it.  This question can be closed.

~bruno71

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GJParkerCommented:
Suggest PAQ and refund

Gary
Mike McCrackenSenior ConsultantCommented:
Closing as suggested.

PAQ and refund

mlmcc
Crystal Reports Page Editor
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.