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:
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
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]);