Sending SSRS credentials via C# application

Question

I am currently working on a C# application for my company that goes out to our SSRS server, runs specific reports, formats the excel reports in the application, and then spits them out to a location. The application is working as intended and there is just one little annoyance. Every time a specific report type is run the credentials for SSRS must be entered. We are opening the excel file with the report's URL as the file name. This is the code that actually opens the excel file from our application:

Microsoft.Office.Interop.Excel.Workbook excelActiveBook; 
excelActiveBook = excelApp.Workbooks.Open(reportURL, 0, false, 5,     System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, true, false, System.Reflection.Missing.Value, false, false, false);

Is there any way to pass in credentials to SSRS through this application we have written. We are using SSRS 2005. Please let me know if there is any other useful information I can provide.

Thanks Chris

Answer 1

This is a setting for the Data Source on the Reporting Services Server.

Log on to the SSRS server and go to the data sources. Edit the data source in question, and choose the option to use the credentials from the calling program.

Answer 2

There are a number of ways of accessing SSRS programatically - see here for some details.

You'll just need to be careful that your domain does not have a proxy server set up - if a user is trying to pass their windows credentials to the SSRS server, they can do so directly with no problem, but if there is a hop first to a proxy, then the servers will need to be set up to perform Kerberos delegation.

Answer 3

Do you actually NEED to pass credentials back to the report server (e.g. is the user's view of the data based on their credentials)?

If not it is common to create a domain account, grant that account permissions to the source database and then modify the data source properties on the report server (using Report Manager) to connect using "Credentials stored securely in the report server", entering the domain account username and password and selecting "Use as Windows credentials when connecting".

If your data source doesn't support Windows authentication you can achieve the same using a standard login (e.g. SQL Server authentication), obviously you then do not select "Use as Windows credentials.." on the data source properties.

This way the credentials the report server needs to access the data source are stored internally and are not required to call a report - security then needs to be managed through permissions in the front-end app.