Solved

ssrs migration from 2005 to 2008. Execution account error.

Posted on 2013-11-13
3
1,000 Views
Last Modified: 2013-11-14
Hello,

We have migrated about 300 rdl files from SSRS 2005 to SSRS 2008 .For each RDL file had its own seperate Dynamic datasource with datasource credentials in SSRS 2005 but after migration on SSRS 2008 all rdl files lost their datasource credentials.
So if we run rdl file in report manager it shows following error :
"The report server has encountered a configuration error. Logon failed for the unattended execution account. (rsServerConfigurationError) Log on failed. Ensure the user name and password are correct. (rsLogonFailed) Logon failure: unknown user name or bad password " .

To avoid error we need to go for each rdl file and save datasource credentials manually, but its not feasible for us as we have about 300 rdl file needs to manuaaly update .

Is there any another way to add execution account credentials for all reports ?

Error :
report error
Manual update need for each report  :
manual setting
0
Comment
Question by:vikasbapat
  • 2
3 Comments
 
LVL 1

Accepted Solution

by:
FriedTyGuy earned 500 total points
ID: 39645438
Just a suggestion for future development, use shared data sources whereby the data source is saved on the reports server.  This way in instances such as this, you only need to update a couple data sources.  For our recent upgrade from SharePoint 2007 and 2005 SSRS to SharePoint 2013 integrated 2012 SSRS I pulled all our reports into a single VS2012 solution and with multiple projects based on report types (we had to do this as the old .rdl files wouldn't work on 2012 SSRS).  Each project had anywhere from 10-15 to over 50 reports.  The magic in this though is I could set the properties for each project (location, data sources, etc) and then do a mass deploy to our reports server.  Any new reports that are created are managed through the single solution so that for our next upgrade if Microsoft wants to throw another curve at us we'll be able to convert the entire solution and then after updating the project properties mass deploy it to our new environment.

It may sound like a lot of work initially, but it's much easier to maintain.  Also, it's not like you have to recreate all the reports as you can just copy the xml of the old report into blank reports in your VS solution/projects.

As far as manually changing all your individual data source credentials, I wish I could offer an easier solution.  I can only suggest using shared data sources in future to make these types of issues easier to maintain in the future.
0
 

Author Comment

by:vikasbapat
ID: 39647074
I appreciate your reply .
But we have multiple  databases in our application and DB name should be dynamically passed as parameter to report.
So the Connection string should be dynamic mentioned as expression in datasource.
Can we Write Dynamic connection string in shared data source ? we have tried this in SSRS 2008 , but it doesn't allow to create dynamic connection string for shared database.
0
 
LVL 1

Expert Comment

by:FriedTyGuy
ID: 39647593
I see your dilemma, if you have access to the data base server you could take a look into this:

http://blog.softartisans.com/2011/04/19/how-to-create-a-dynamic-reporting-services-shared-data-source-using-linked-servers/ 

Another, extremely painful option, as mentioned in the link is to deploy a report for each data base, but having over 300 reports; doesn't seem like an option.

You could also consider:

http://www.codeproject.com/Articles/355461/Dynamically-Pointing-to-Shared-Data-Sources-on-SQL
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question