Crystal Reports Authentication for reports

[Former Member]

Hi

For a while now I have been trying to get my head around how Crystal Reports handles authentication for report refreshing and am wondering if anyone can provide a simple explanantion of how it is done?

For all our reports we run them against SQL databases. I design the reports through Crystal Reports XI which is installed on my PC. I create an ODBC connection to the SQL database and use SQL authentication and use the SQL SA username and password.

When I run the report it then always prompts me for the database name and credentials even though I have set the datasource location to use the ODBC connection that I created (which also includes the credentials).

The only way that I can get it to run the report without it prompting me for this information is to type nothing in the username and password boxes and tick the trusted connection box. When I do this each time I open the report and hit F5 it runs the report without asking for credentials etc.

The end users run the reports from Infoview and in the database tab for each report it is set for "Use original database logon information from the report". This allows the users to run the report without it prompting them for any credentials. I think I also had to create the same ODBC connection on the server on which Crystal Reports server is installed.

With the above in mind can anyone explain what authentication processes are happening? If I have to set the option for trusted connection does that mean it is using my windows username and password for authentication against the SQL database? How are the users able to run the reports from infoview without it prompting them for credentials.

Hope this makes sense and I just want to understand how authentication works in Crystal Reports and Crystal Reports Server.

Thanks

[Former Member]

Thanks

But can anyone tell me definitively what Crystal is using when the Trusted connection" setting is selected?

[Former Member]

Trusted Authentication means that it's using the user's Windows login to access the database.

-Dell

[Former Member]

Hi

After working on this over the weekend alot of what you have said does make sense.

I agree that it does seem that when you create the ODBC connection, in terms of the credentials side of setting this up does seem simply to verify database connectivity.

When you run the report in Crystal Reports XI it still prompts for credentials even though these have been set in the ODBC connection. As I said I tick the trusted connection box and then it seems to work - what I would like to know is what credentials the report is therefore using if this box is ticked.

With this particular report I was working on when I published it to Crystal Reports Server it asked for credentials when the report was run from infoview. I created a user account in AD and gave it the db_datareader permission on the SQL database. I then set this in the CMC Admin Console on the database tab for the report object and the report then ran ok without asking for credentials.

The strange thing is though that there are other reports run against another SQL database and on the databsse tab for these reports the option is set to "Use original database logon information from the reportu201D. With this option set I dont understand how then the reports are runnig without prompting for credentials. The users that are using these reports log into infoview with a user account that has been setup in the CMC admin console - it isnt a AD account or anything. Is this then still using their Windows credentials and their accounts have been given permissions on the SQL database to which the reports are being run?

Certainly getting closer to getting my head round this anyway!

[Former Member]

I'm not sure why the "Use original database..." setting is not working for you if it works for the other reports. We don't use that setting - we always manually set the password for the report in the CMC Database Configuration.

-Dell

[Former Member]

You have to set the password Database Configuration for the report in the CMC in addition to selecting "Use same logon as when report is run."

When you set the password in an ODBC connection, I believe that password is really just used to verify that the connection is working. Anything that uses that connection, including reports, still has to authenticate to the database. So, in order for a reporin in InfoView or the CMC to know how to connect to the database, you still have to set the password.

BTW, it is NOT a good idea to use the SA ID in your reports because of the damage that can be done if someone gets the password. Best practice is to have a separate "reporting" user ID set up in the database that has it's own password and has been granted Read Only access to the data along with the rights to execute just the functions or stored procedures might be used in your reports. This way if anyone gets the password for the account, they can't really do anything that will change data or the structure of the database.

-Dell