SQL Reporting Services and the Unattended Execution Account


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 (http://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. 

Comments (10)

  1. Anonymous says:

    Thanks for this post.  I wish I read it before I wasted a few hours trying to troubleshoot the ‘logon failed’ problem.

    I had specified an invalid account for ‘Unattended Execution Account’.  Didn’t bother to double check it thinking it’s used only for ‘Unattended’ jobs and the error I was getting was for on demand reports. Was I wrong!

  2. Anonymous says:

    Thanks a lot solved my problem after a couple of hours searching. My problem started when I changed my passowrd.

  3. wandasoozq says:

    I’m wondering if you could point me to any documentation regarding how best to automate the execution of a SQL Report based on data being modified on a data-source (ie: a due-date is entered in a table triggering a report to be run)…

    Thanks!

  4. Hi There. I don’t know if there are any articles/and or walk-thrus that focus on exactly what you want, but in essence, here’s what you’d have to do:

    • Create a subscription that sends out your report to whomever it needs to go out to.

    • Expire the schedule used by this subscription so that it never fires on its own

    • Add a trigger and/or leverage ADO.NET query notification so that you know when a change has been made to your data

    • The trigger and/or managed code sitting on the other side the query notification calls a SSRS webmethod called "FireEvent" to execute the schedule which you expired: The report gets created!

  5. Anonymous says:

    Thank you for posting this! We had an expired password on our unattended execution account that ended up being the culprit for issues we were having when running reports on demand. Thanks.  

  6. Anonymous says:

    Thanks for the post, but don’t you think you could possibly have a better error message for this scenario!  Jeez…

  7. Anonymous says:

    Definately move this tidbit from Semi-Useful to VERY-Useful. Only improvement would have been to have it magically pop up on my screen 2 hours ago 😉

  8. Anonymous says:

    Same here – thanks for the post. I had an old password set for my execution account, and could not figure out why I was getting this error when my data source was using an entirely different account.  Your post pointed me in the right direction!

  9. zackariya says:

    My data source is an assembly file. The data is returned by a DLL to the report to render. I am using an unattended execution account. But when I run the report I am getting the exception

    An error has occurred during report processing. (rsProcessingAborted)

    Cannot set the command text for dataset 'DataSet1'. (rsErrorSettingCommandText)

    Error during processing of the CommandText expression of dataset ‘DataSet1’. (rsQueryCommandTextProcessingError)

    Can anybody help..?