Start Free Trial

asked on

Grant access to a database using a master user in Crystal reports

We have a newly created database. We are creating Crystal reports using tables from that databaase. I am being asked if we can create the crystal reports with the credentials built in so access would not have to be gramted for the individual users. They want anyone to be able to run the reports. Is there a way to do this? Is there a way to avoid setting up an ODBC connection on everyone's machine that would run the reports?

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

How do the users run reports?

Crystal does NOT store the logon credentials in the report however if you are using CR Server or an equivalent product to run reports you can store the logon credentials when you publish a report.

mlmcc

ASKER

The Database is SQL Server. The reports will be run using Crystal Reports.

ASKER

It sounds like OLE DB Connection is the way to go. I have always used ODBC, but I can try it out.

To be clear, you still can't save the credentials and will need to use the integrated authentication with OLE DB

ASKER

So the only way that OLE DB helps me is that I do not have to setup an ODBC connection on each users machine. I would still need to grant individual authority on the database?

ASKER

Does the 3rd party viewer have to be loaded on each user's machine?

ASKER

I tried setting up an OLE DB Connection. 1st screen was easy, however I had no idea what to do with the 2nd screen. So I did nothing. I tried to use it but it did not show the tables for the database. Any suggestions on setting up the connection?

Yes , OLE DB is better because you do not need to create an ODBC file on each machine. This actually is a big deal because it is not just creating the files , but also maintaining them. How many computers do you have?

3rd part desktop viewer should be installed on each user computer. The same will be valid for Crystal reports, but the 3rd party viewers will be much cheaper. There are some viewers , which will allow you to manage the reports and permissions from one place. Others will require to walk on each machine and set the reports. Consider this before to choose a tool. I know that R-Tag Crystal Viewer (www.r-tag.com) is able to manage all users and reports from one place, so you can add a new report to the system and make it available to certain number or to all users immediately. You can check also if DataLink from Millet software supports some similar options. I have heard good things for this viewer too. Most viewers will require you to walk through all the machines and add each report to each machine. If you have more than 2-3 users this might be a problem especially if you have to add/remove/change reports often. Another option is to find a WEB based solution. It will not require local installations and will allow you to control everything from one place. WEB based systems are usually significantly more expensive and I personally find them slow and with less features.

You can use ODBC for the development and switch the connection later when you deploy.

ASKER

There are several hundred users, so it is a lot. They all have crystal reports installed already. I have one report that I created using ODBC. I tried to switch it to OLE DB but have not had any luck. I tried creating a new connection. But it does not show the tables. see attachment. If there is something I need to add on the 2nd screen I don't know what that is. I have the server, etc, filled out on the 1st screen.OLEDB.docx

Were you able to see the tables with an ODBC connection using the same user ? My guess is that you do not have permissions to work with any table.

I am not getting it . If you have few hundred user, how much did you pay for Crystal licenses ? How are you planning to control user permissions ( which user is able to run which report) ?

Did you click the + next to Information Schema and Sys?

Are you using integrated security now with the database?

mlmcc

ASKER

I am able to access the tables and run the report using ODBC connection and using the same username and password, so permission to the tables is not the issue. They want everyone to be able to run the reports. The Crystal licenses came with other software that we have.I did click the + next to Information Schema and Sys.

After you select the OLE provider for SQL server, you need to set the connection information.
Enter the server name, check the integrated security box and then you should get a list of databases to select. User ID and Password are for SQL server authentication, do not use those. If you can't get a list of databases in the dropdown, then you're not gaining access to the server with your windows credentials.

Double-check the server name and database from your new ADO connection are matching with your working ODBC connection. Does you ODBC use integrated security or a specific SQL user? I suspect there is something that is different between the two but they should both use the same information to get the same access.

ASKER

The odbc connection is setup with sql authority using the same userid and password that I put into the OLE DB connection. On the ole db I checked integrated security. I am not sure if that answers your question. The server name and database are spelled correctly.

ASKER

You said: User ID and Password are for SQL server authentication, do not use those.
So I tried it and now I get the list of tables. But I wanted to be able to store a user id and password so how can I do that?

Ok, can you log in to the sql server with windows authentication. That seems likely to be the problem. On SQL, you'll have to grant some access to your domain user accounts--ideally using a group from active directory. The objects then need to have the appropriate permissions on them too.
You can verify that is the case by setting up the ole connection using your SQL account (so uncheck the integrated security) and see if tables appear. This is only a troubleshooting method because crystal won't save the password.

ASKER

How can I use the userid and password and still get the table list?

You can't. Crystal doesn't save the passwords for SQL users. You have to configure integrated security. It sucks but I know no workaround. It's finding the best feature set and ADO, to me is the winner.

If you use the SQL account, the report will prompt the user for the password (it will show the SQL user name) every time.

ASKER

It is ok if they have to enter a password to get in.

Great, then you can uncheck the integrated security and you should be in business.

ASKER

But I am not. If I uncheck Integrated security and enter the userid and password, then I do not get the tables.

Does the list of databases get populated when you only use the SQL user id and password?

ASKER

I tried refreshing the report that I setup using the OLE DB connection with the Integrated security. I had updated all my tables to the correct tables under the new connection. When I refreshed it could not find the tables.

ASKER

It looks like the user does not have select permissions. I am asking for that. It may resolve the issue.

Have you been able to setup an ODBC connection that will work with this new database using the SQL user name?
The server, database, sql user and password should be exactly the same for ADO as ODBC.

Well, that's definitely going to be needed to pull data...
It sounded like integrated security was working, you don't want to do it that way?

qbjgqbjg, I guess the easiest way to resolve this is to call one of the vendors in this list: http://kenhamady.com/viewerfeaturematrix.pdf and to ask them for a demo on your computer. They will be able to resolve the connection problem and show you how to save passwords ( if necessary) with their software. As it was mentioned Crystal does not support saved passwords so you will need to use another approach. BTW, my guess is that the connection is the smallest problem, there will be other problems when you decide to restrict access for some users to some reports. I cannot imagine that you will allow all your users to run all the reports. Sooner or later you will have some reports for the top management and they will ask you to restrict the access to these reports. Another thing , which might be useful for you is the ability to schedule reports to run automatically. With so many users you may find that it easier to schedule a report to run during the night and save it somewhere on a shared drive or e-mail it instead of each user running the same report and hitting the database.

He said entering the password wasn't a concern. At this point, it sounds like permissions on the database are not configured correctly yet.

ASKER

I got the added permissions for the user I want to use. It appears to be working now. I will look over the answers to see about giving credit.

ASKER

Thanks to all who responded. The reason they don't care who runs the reports is: They are all over Legacy data. It won't be changing and there is nothing sensitive. The data is all public record. I am using the OLE DB connection with a username and password specified.