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?
qbjgqbjgConsultantAsked:
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.

UnifiedISCommented:
You can configure your reports to use an ado.net connection with integrated windows security in the report and grant permissions on the database server to all domain users or some other broad category.  
In XI the connection type is called "OLE DB (ADO)".
0

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
vastoCommented:
Crystal reports and ODBC connections cannot preserve the passwords. The only way to do this with ODBC is to use "Integrated security". In this scenario the database will control users permission ( users should be added to the database with granted at least "select" permissions)
OLE DB Connection will be generally a better choice because it is set inside the report and does not require additional ODBC file on each client machine.
If you use 3rd party viewer you can set a connection inside the viewer , which might use a specific user name and password . In this scenario all the users will use one account and there sill be no need to set permissions inside the database.
What is your database type and what kind of software are you using to run the reports ?
0
mlmccCommented:
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
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

qbjgqbjgConsultantAuthor Commented:
The Database is SQL Server. The reports will be run using Crystal Reports.
0
qbjgqbjgConsultantAuthor Commented:
It sounds like OLE DB Connection is the way to go. I have always used ODBC, but I can try it out.
0
UnifiedISCommented:
To be clear, you still can't save the credentials and will need to use the integrated authentication with OLE DB
0
qbjgqbjgConsultantAuthor Commented:
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?
0
qbjgqbjgConsultantAuthor Commented:
Does the 3rd party viewer have to be loaded on each user's machine?
0
qbjgqbjgConsultantAuthor Commented:
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?
0
vastoCommented:
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.
0
vastoCommented:
You can use ODBC for the development and switch the connection later when you deploy.
0
qbjgqbjgConsultantAuthor Commented:
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
0
vastoCommented:
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) ?
0
mlmccCommented:
Did you click the + next to Information Schema and Sys?

Are you using integrated security now with the database?

mlmcc
0
qbjgqbjgConsultantAuthor Commented:
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.
0
UnifiedISCommented:
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.
0
UnifiedISCommented:
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.
0
qbjgqbjgConsultantAuthor Commented:
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.
0
qbjgqbjgConsultantAuthor Commented:
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?
0
UnifiedISCommented:
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.
0
qbjgqbjgConsultantAuthor Commented:
How can I use the userid and password and still get the table list?
0
UnifiedISCommented:
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.
0
UnifiedISCommented:
If you use the SQL account, the report will prompt the user for the password (it will show the SQL user name) every time.
0
qbjgqbjgConsultantAuthor Commented:
It is ok if they have to enter a password to get in.
0
UnifiedISCommented:
Great, then you can uncheck the integrated security and you should be in business.
0
qbjgqbjgConsultantAuthor Commented:
But I am not. If I uncheck Integrated security and enter the userid and password, then I do not get the tables.
0
UnifiedISCommented:
Does the list of databases get populated when you only use the SQL user id and password?
0
qbjgqbjgConsultantAuthor Commented:
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.
0
qbjgqbjgConsultantAuthor Commented:
It looks like the user does not have select permissions. I am asking for that. It may resolve the issue.
0
UnifiedISCommented:
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.
0
UnifiedISCommented:
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?
0
vastoCommented:
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.
0
UnifiedISCommented:
He said entering the password wasn't a concern. At this point, it sounds like permissions on the database are not configured correctly yet.
0
qbjgqbjgConsultantAuthor Commented:
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.
0
qbjgqbjgConsultantAuthor Commented:
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.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.