June 16, 2015 at 6:56 am
Hello,
I have created a report using SSRS and deployed it to the report server. Within the report if I right click on the Data Source and Select Properties from the Report Data Window in Visual Studio, the properties windows displays the "use shared data source reference" which I can edit. After selecting Edit and selecting Credentials it shows Use Windows Authentication (integrated security). The report is simple and uses no parameters. I can then build and deploy the report to the report server, which creates two folders. One for the Visual Studio project and one entitled Data Sources which has the shared data source within the folder. If I select the folder with the Visual Studio Project I can then see the project which I want to manage. After selecting Manage from the drop down, on the next window I can select Data Source and then it shows the shared data source as being selected, however I can't test the connection until I select Windows Integrated Security as one of the options below. Then the Test Connection box illuminates and displays that the Connection was successful. I can then select Apply. Then I can select Subscriptions and I have tried to create both a regular subscription and a data driven subscription, but there is always a prompt that says that "Subscriptions cannot be created because credentials used to run the report are not stored …"
My Windows authentication credentials are admin on the server for everything (SSMS, SSRS, etc.). So I'm not sure why I can't create subscriptions. I have tried everything from creating credentials, took shared data sources out, and pretty much read every thread on this topic with no luck. Any assistance would be much appreciated with this problem. More importantly has anyone been able to create a subscription with a shared data source that uses Windows credentials? Thanks for the help.
Kevin
June 16, 2015 at 8:16 am
I believe it is generally a good idea to set up SQL Server authentication with a service account specifically for SSRS. Then use that account to connect to your data source. It will ask for username and password which will then be stored with the data source. Once that is done, you should not have that error any longer.
June 16, 2015 at 8:32 am
Subscriptions are generally unattended, i.e. the report is executed in your absence. Therefore, you need to provide credentials for the time it runs off the subscription's schedule.
June 16, 2015 at 8:33 am
Could you perhaps explain how to set up the account specifically for SSRS? I'm not entirely familiar with the process. If I go this route, I currently use Windows Authentication in SSMS, would that affect that connection string?
Kevin
June 16, 2015 at 8:35 am
abd2303,
Could I not just pass it Windows credentials to do this?
Kevin
June 16, 2015 at 12:44 pm
Hi,
Yes, in the datasource, enter domain credentials of an account that has permissions to read data from the database the report's querying. This will have to be static credentials on the data source for subscriptions to work
Thanks,
Andrew
June 16, 2015 at 1:53 pm
To create a subscription, the report must use stored credentials.
Subscriptions and Delivery (Reporting Services)
Joie Andrew
"Since 1982"
June 16, 2015 at 10:34 pm
Creating subscriptions is a topic with many scenarios. I have mostly looked at Windows Authentication as it is the most easiest to look at.
I did work out most of the scenarios for both SQL Server 2008 and 2012, the differences as far as subscriptions related are few.
Chapter 4 of my book deals with Working with Report Manager and consists of the following which may be relevant to you.
Hands-on exercise 4.10: Creating an event-driven report subscription for delivery by an email
Hands-on exercise 4.11: Creating an event-driven report subscription for delivery to a file share
Hands-on exercise 4.12: Creating data-driven report subscription for delivery to a file share
The last one is somewhat more involved but works like magic.
Book link is here:
http://hodentekmsss.blogspot.com/2013/06/do-you-want-to-learn-sql-server.html
These references (very exhaustive) may be useful also:
http://hodentekmsss.blogspot.com/2013/06/links-in-book-learn-sql-server.html
Here is a link to my 2008 book:
June 17, 2015 at 7:26 am
Finally it worked for me. What I have done is
1. report manager, right click on the report , select manage, click on data source tab on the left
2. select Credentials supplied by the user running the report
select Use as Windows credentials when connecting to the data source
3. run the report from visual studio and give a username and password when prompted.
4. come back to report manager, right click on the report , select manage, click on data source tab on the left
5. select Credentials stored securely in the report server
provide the same username and password you used to run the report
6. select Use as Windows credentials when connecting to the data source
7. test the connection and then apply.
hope it helps.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply