SQL Reporting Services and the Unattended Execution Account

  • Article

Thanks to John Gallardo and Radu Grama for engaging in a very interesting conversation from which I’m cribbing the following information!

Scenario:

After changing the password on the user you are using for SSRS’s unattended execution account, all of your reports stop rendering. Regardless of whether report data sources leverage stored Windows / SQL credentials or they dynamically pick up the user’s identity, no reports will run. Instead, the error below is returned:

“Logon failed (rsLogonFailed) Logon failure: unknown user name or bad password.”

BOL (https://msdn2.microsoft.com/en-us/library/ms156302.aspx) says the following about the unattended execution account:

SQL Server 2005 Reporting Services requires a special account to use for unattended report processing. The account is used in the following ways:

· Connect to external report data sources that do not require or use authentication.

· Send connection requests over the network for reports that use database authentication. For more information, see Specifying Credential and Connection Information in SQL Server Books Online.

· Retrieve external image files that are used in report. For more information about the unattended execution account is used for retrieving images, see Adding an Image to a Report in SQL Server Books Online.

Unattended report processing refers to any report execution process that is triggered by an event (either a schedule-driven event or data refresh event) rather than a user request. A special account must be configured for unattended report processing only when the external report data source does not require or use credentials, or when a report data source connection requires credentials to be specified in the connection string (not recommended).

It’s pretty much common knowledge that the unattended execution account is used in scenarios where reports are executed on a schedule, but as you can see, we use it for other things, too.

It also turns out that if you have specified a userid/password for the unattended execution account, SSRS will always impersonate it when you render a report, even if you are doing on-demand execution. This nugget was sure news to me!

If you have specified an unattended execution account, SSRS basically flip-flops between impersonating it and the identity specified in your data source during the rendering process. Most of the “work” happens under the context of the data source identity, but at some point, we will always flip over to the unattended execution account.

So, the moral of the story is that if you use an unattended execution account, you’ve gotta keep the password up-to-date. If you aren’t doing things mentioned in the BOL list (above), you could also choose not to use the unattended execution account at all, which would also solve your problem.