When I try running reports from enterprise (FYI. My database is in a different server and I use ODBC connection to access my DB) ODBC Authentication type should always be WinAD or NT on my BO Enterprise server. Report fails when I select SQL server authentication type even if my SQL server user ID and Password is valid and has sufficient access on the data base server. Appreciate any help.
We are using BO XI r2, Crystal designer XI and SQL server 2008.
Thank you for your quick response. But neither do we use Universes nor Business Views. We access the database directly. Some times we create stored procedures also. It also fails for stand alone reports that are not running from enterprise (we did not load these reports). So we can not use CMC. They are still reside in the main server but fails to run.
These instructions work for reports that are saved out to the Enterprise and run against stored procedures. We have 60-70 reports that report off of stored procedures that we schedule through Business Objects. The instructions in the link above are how we update the login credentials.
If your reports are failing outside of the Enterprise then your problem is more likely with the database end of things. Have you checked with your DBA?
Yes. I did check with my DBA. My ID has all necessary rights to access database. Let me explain my problem in depth. That might help you understand my issue better. We have a couple of reports that are stand alone. Neither do we have them in BO enterprise nor do we use BO enterprise to run them. In fact we have a different application in a different server that accesses these reports. So in short, my application resides in one server. My reports reside in another server and finally my database server is different from the above two. So as to access the database I created one ODBC connection in the server where my reports reside and use it in my report to retrieve data. When I select option it works for me because I log into the server using my NT user ID and the same user ID has access to SQL server (set up as a AD/NT account). But when my users run the same report it fails as their NT account is not set-up on database side. Again when I use SQL server authentication, the application fails to run the report. I am totally confused. I hope you understand. Please let me know if you need more information. Appreciate your help.
This is the problem with your users not being able to run the report. If the users are running the report locally or logging on to the server to run the report like you can, this is why the report is failing. They just don’t have access to the database under their logins like you do.
How are you setting this up? Did you set it up using your own NT account ID or do you have a SQL Server account that your DBA set up for you to use? We have a SQL Server account that we use to access our databases. This way we can set up the ODBC connection on the server to use this login and all of our users can share that access. We control the report security through Business Objects Enterprise but it sounds like you don’t have that option. One solution would be to work with your DBA to get an NT group set up that has read access to the database and then set up all of your users as members of that group. This will allow you to have some control over access.
If the users are running the Crystal Report locally, it will be a different setup. When running a Crystal Report locally you need an ODBC connection set up locally. In short, you need an ODBC connection to the database set up on every computer where you are running the report, not just on the server. You need to make sure that the name of the ODBC connection is the same name as it is on the server also.
Just for your information, this holds true for reports that are stored in Business Objects Enterprise. If a report developer is working on a Crystal Report locally, he must have a local ODBC connection that has the same name as the one on the server. It can point to a different database if the developer desires, but the name must be the same. The report developer would also need to have their own credentials to access the database. If a user runs the report through Business Objects InfoView, then it uses the ODBC connection that is set up on the server.
I really appreciate your quick response. We have a service account that is used in our CMC for all the reports to run. And in production ODBC we seleted NT authentication to retrieve data. When I tried using this ID it did not work. ODBC does not even accept this ID and fires back with the error message “failed to log in”. So when you say we create a SQL server account to set up ODBC and a group of people share this ID what does it mean? Does it mean to create SQL server account with sufficient access and then use it in our server. We did that but users fail to run the report. If no could you please let me know how to create such IDs and what extra rights does it need other than general user rights. I am confused with sharing one SQL server ID by a group of people. Sorry for any inconvenience created.
I think this setup still requires those users that are logged in to the server and running the report need to have access to the database. In Business Objects Enterprise there is an option to set up that will pass the users login credentials through to the database server when using NT Authentication. I think this functions similarly to what you have set up outside of Business Objects Enterprise. I could be wrong though as we don’t set up any reports this way.
This is exactly what we have set up except that we also specify the SQL Server account credentials in Business Objects Enterprise. The sharing takes place through Business Objects Enterprise. This may be the piece that you are missing.
I think your resolution goes back to setting up access to the database in SQL Server for all of your users. Talk with your DBA to find how they would like to set that up.
Sorry I may not be much help. We just don’t use Crystal Reports in this manner here and I don’t have much experience setting them up any other way.
I really appreciate your sincere effort resolving my issue. I am still investigating and I know you will definitely be curious to know the resolution. I will definitely post my resolution that may help you and the group in future. Once again appreciate your quick help and support. A BIG thank you.
(BOB member since 2007-09-02)