Executing SSRS report via a console application though a SQL Server Agent job

Question

I have a SSRS solution that contains 20 reports all of which utilise a shared data source called "DataWarehouse". This datasource has been configured to use Windows Authentication. These reports have been deployed to a server.

I have a requirement where I need to be able to automatically run these reports once a data warehouse has completed loading overnight. Sometimes the warehouse load might take 5 hrs, sometimes it might take 10 and so I don't want schedule these reports at a particular time using reporting services, I want the warehouse load to determine when its done and trigger the reports.

Now, I have a mechanism to do this:

1. I have written a c# console application that accepts a report name and a file path which runs the report and saves it do wherever it needs to.
2. I have an SSIS package that executes the console application from (i). I use an SSIS package because the DBA where I work will not enable xp_cmdshell.
3. I have an agent job that runs the SSIS package when the warehouse load completes.

Now if I run the SSIS package on my local machine (as me) the package executes successfully and the report is generated and saved. When I deploy the SSIS package to the server and try and run it through the agent job it fails with the following error:

System.Web.Services.Protocols.SoapException: The permissions granted to user 'NT SERVICE\SQLSERVERAGENT' are insufficient for performing this operation. ---> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user 'NT SERVICE\SQLSERVERAGENT' are insufficient for performing this operation.
   at Microsoft.ReportingServices.Library.ReportExecution2005Impl.LoadReport (String Report, String HistoryID, ExecutionInfo2& executionInfo)
   at Microsoft.ReportingServices.WebServer.ReportExecutionService.LoadReport (String Report, String HistoryID, ExecutionInfo& executionInfo)

My initial guess was that this means the SSIS job gets executed as 'NT SERVICE\SQLSERVERAGENT' and this user does not have access to run reports on the report server. So I jumped on to the report server and tried adding NT SERVICE\SQLSERVERAGENT as a user who could run reports but I was still getting errors. Can anybody help me figure how I can get this working? Thanks

Answer 1

I edited the console application to connect to the SSRS server as a specific user. I've added what I did below incase its useful to somebody else.

To do this I created a file called account.config which just contained the username on the first line and password on the secondline:

MyUser
MyPassword

Then in my console application I have the following code:

// Read in the config file and add the lines to a list
List<string> accountDetails = File.ReadAllLines(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "account.config")).ToList();

ReportExecutionService myReportExecutionService = new ReportExecutionService();

// Add the credentials
// accountDetails[0] is the username, accountDetails[1] is the password
myReportExecutionService.Credentials = new NetworkCredential(accountDetails[0], accountDetails[1]);