credential in reporting service

Question

hi everyone... i have a problem about the credential in reporting service.. when i choose option 'windows authentication' for a report in SQL Server Reporting Services, just administartor that can view the report and the other users can't view the report. but when i set 'credential stored securely in the report server' and enter the username and password, all users can view the report but some data of the report is not showing. fyi, i have created the user in SQL Server and i have set the role and user mapping.

what should i do? please advice..

thank you

Answer 1

Based off of what you are saying I would guess that you have not added the users windows credentials to the proper group on the reporting server. The admin would have access as by default that role is already given permission in the proper groups.

Here is an article from Microsoft that explains this process.

http://msdn.microsoft.com/en-us/library/aa274425%28SQL.80%29.aspx

This is a good article on setting up role based security.

http://odetocode.com/articles/215.aspx

Answer 2

If I understand your question properly, you want to use your end-users' windows credentials from end-to-end with your reports. You do not want to specify a specify set of windows or SQL credentials to connect to your data source, you want to use the end users' credentials instead.

In order to accomplish this, you will need to grant the end-users rights to access your reports in SSRS as well as granting them rights to run the needed SQL on the underlying database.

Finally, if your SSRS instance is not running on the same server as your database, you may run into security delegation issues. You can read more about this topic by visiting http://support.microsoft.com/default.aspx?scid=kb;en-us;810572