SSRS shared data source connection does not have credentials and will not allow subscription to be setup?

HudsonMarine
HudsonMarine used Ask the Experts™
on
I have created a report using a shared db connection in visual studio - I have deployed the report to report server 2008 and now i wish to set it up to be emailed as a report but it keeps telling me the credentials are not saved for this db connection type - why cant i set this up to run automatically with my shared db connection?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
You need to save the credentials for the connection. Go into properties on the connection in report manager and enter and save the credentials the connection should be using.

Author

Commented:
what is report manager? visual studio? SQL Server Reporting Services?

Thanks for your help!

Commented:
Report manager is the browser based report display tool distributed with SSRS.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
ok i understand but how do i set this up automatically when i create a report in visual studio or am i always goping to need to do this step if i want to setup a subscription on any future reports?

Author

Commented:
how does this browser based service account differ from the current report server database credentials and the execution account credentials in reporting services configuration manager?

Commented:
In SSBS Dev Studio, I think you can go to properties on the shared connection in  and tell it to save the credentials and redeploy the shared connection...sorry I am not in front of the product at the moment.
Right click the Shared Data Source in Visual Studio and choose open. At the left select the Credentials Tab. I suppose you now have the "Use Windows Authentication.." option selected. In this article:
http://msdn.microsoft.com/en-us/library/ms160330.aspx
it says:
Stored Credentials
You can store the credentials used to access an external data source. Credentials are stored in reversible encryption in the report server database. You can specify one set of stored credentials for each data source used in a report. The credentials you provide retrieve the same data for every user who runs the report.

Stored credentials are recommended as part of a strategy for accessing remote database servers. Stored credentials are required if you want to support subscriptions, or schedule report history generation or report snapshot refreshes. When a report runs as a background process, the report server is the agent that executes the report. Because there is no user context in place, the report server must get credential information from the report server database in order to connect to a data source.

The user name and password that you specify can be Windows credentials or a database login. If you specify Windows credentials, the report server passes the credentials to Windows for subsequent authentication. Otherwise, the credentials are passed to the database server for authentication.
So you need to select the "Use this user name and password" option in the Data Source Properties Windows. Make sure the "OverwriteDataSources" property of your project is set to true, or your original datasource will not be overwritten when you deploy the project.

You can also check the settings in Report Managers (the web based report viewer). Select the shared datasource there and check the properties.

Author

Commented:
Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial