Sql Server Reporting Services Ssrs 2008

Troubleshooting SSRS Scheduled Reports Not Working

If you’re using SQL Server Reporting Services (SSRS) and facing issues with scheduled reports not running, you’re not alone. Many users encounter this frustrating problem where their scheduled reports simply fail to execute or return an error message during scheduled jobs. One common error is related to the login issues, specifically with the message:

‘EXECUTE AS LOGIN’ failed for the requested login ‘NT AUTHORITY\NETWORK SERVICE’. The step failed.

In this post, we’ll explore the potential reasons behind this issue and provide a step-by-step solution to help you get your scheduled reports back on track.

Understanding the Issue

When scheduled reports fail to run, it often indicates a problem with the job execution context or the security configurations for the account under which the report server runs. In this case, the error message suggests that the login for ‘NT AUTHORITY\NETWORK SERVICE’ is not functioning as expected. Here are some key points to consider when diagnosing the problem:

  • SQL Authentication: The user is using SQL authentication for reports, so you might think this isn’t a permissions issue. However, the account under which SSRS runs can still affect job execution.

  • Service Account Issues: The ‘NT AUTHORITY\NETWORK SERVICE’ account is a built-in service account that may have been previously configured incorrectly or its permissions have been altered.

Steps to Resolve the Issue

1. Check Service Account Configuration

Start by verifying that the ‘NT AUTHORITY\NETWORK SERVICE’ account is set up correctly:

  • Go to SQL Server Management Studio (SSMS).
  • Check the properties of the SQL Server Agent service; ensure that it is running under the ‘NT AUTHORITY\NETWORK SERVICE’ account.

2. Verify SQL Server Agent Jobs

You need to check the jobs that are responsible for executing the scheduled reports:

  • In SSMS, expand the SQL Server Agent node, then find the jobs associated with your reports.
  • Review the job steps, especially the security context they’re set to execute under.

3. Permissions and Access Right Confirmation

Ensure that the ‘NT AUTHORITY\NETWORK SERVICE’ account has the necessary permissions to execute the jobs:

  • Navigate to Security > Logins in SSMS.
  • Verify that the ‘NT AUTHORITY\NETWORK SERVICE’ has login permissions and that it can access the databases related to the reports.

4. Edit the Job and Change the Owner (if necessary)

If all configurations appear valid, but the problem persists:

  • Consider changing the owner of the job to a different user account that is known to have the appropriate permissions.
  • Right-click the job, go to Properties, and under the General tab, change the owner to an account that is not a built-in service account.

5. Review and Test

Once you’ve made these changes, be sure to test if the scheduled report runs successfully:

  • Execute the job on demand through SSMS to ensure it works without any issues.
  • Monitor for any error messages in the logs, adjusting your configurations as needed.

Conclusion

Facing issues with scheduled reports in SSRS can be a daunting task, especially when they refuse to run due to login problems. By systematically checking the service account configuration, verifying job setups, confirming permissions, and potentially adjusting job ownership, you can resolve these issues effectively.

For additional assistance, you can refer to helpful resources available online, such as the solution found at The MSS Forum, which outlines similar troubleshooting steps. If you follow these steps and do not find a resolution, consider reaching out to IT or DB management to confirm if any broader system permissions or policies might be interfering.

Now, you’re equipped to tackle most challenges with your SSRS scheduled reports. Happy reporting!