Report stops running when published to report server

Hi, I have a report stored locally that has two sub reports within it. The user provides credentials for Server, Database, Username and Password. There parameters get passed to the two sub reports also. When I run the main report from within Visual Studio on my desktop all is fine and all three reports work as expected.

The connection expression is.....
="Data Source=" + Parameters!Server.Value + ";Initial Catalog=" + Parameters!Database.Value + "; user=" + Parameters!Username.Value + ";password=" + Parameters!Password.Value + ";"

When I publish the report to the report server and try to access it through out application I get the error:

'System error attempting to run this report. ErrorCode: rsInvalidDataSourceCredentialSetting
Error Message: The current action cannot be completed. The user data source credentials do not meet the requirements to run this report or shared dataset. Either the user data source credentials are not stored in the report server database, or the user data source is configured not to require credentials but the unattended execution account is not specified.'

Why is it that report runs ok locally (it does not use windows credentials) but not on the report server?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Zac HarrisSystems Administrator Commented:

The error message actually holds a clue as to the issue (surprising for Microsoft, I know :) )

 the unattended execution account is not specified

You have to use a domain user account. To serve its intended purpose, this account should be different than the one used to run the Report Server service. Be sure to use an account that has minimum permissions (read-only access with network connection permissions is sufficient) and limited access to just those computers that provide data sources and resources to the report server.

Here is how to configure that account:

Start the Reporting Services Configuration Utility (rsconfig)
On the Execution Account Page, select Specify an execution account.
Type the account and password, then retype the password to confirm and click Apply.

Then you want to set the following options to use the Unattended Report Processing Account

In Report Manager or Management Studio, select the Credentials are not required option.

FYI: The unattended report processing account is used primarily to connect to external servers and not as a login to database servers. If you plan to use the account credentials to log in to a database you have to specify credentials in the connection string.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
deanlee17Author Commented:
That's perfect, thank you Zac
Zac HarrisSystems Administrator Commented:
Glad I could help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.