I can't seem to get this to work and have to resort to a custom connection for each report in Server Manager. The problem with that is that I have to re-do it every time I re-deploy the reports. Is there a secret to this that I have not found yet
Dick Campbell
Hello Dick,
What kind of errors/problems are you seeing You should be able to create a shared data source (of course, with stored credentials), then be able to create a subscription on that report.
From Report Manager, create a new data source, and store the credentials in it. Modify the connection to one of your reports to use this connection, then create a subscription. It should work then.
Jarret
Hi Jarret,
I didn't reaaly explain very clearly.
I have used a Shared Connection (for the obvious maintenance advantages), with integrated Windows security, from the start and everything worked perfectly until I needed to create subscriptions for some of the reports. As I am sure you know, you must store credentails on the server to do this.
Again, for ease of maintenance, I eleceted to store the ceredentials with the Shared Connection using the SQL Server Management studio to configure the Report Server. Even though I used the same login details, all reports failed on security grounds and the same happened when trying to create subscriptions. Naturally I checked and double checked that the user name and password were correct.
About the only option I didn't try was to simply tell it to use Windows security, which means not actually storing the credntials on the server. While this seems a bit of a contradiction, I guess the oprion is there for a reason and I presume that it allows subscriptions to use Windows secuirty without actually storing credentials on the server. This seems to be a circular argument I must say.
Reading between the lines, the whole "store credentials securely on the server" feature is to avoid embedding the user name and password (in plain text) in the connection string so I guess one work around is to do just that.
Dick Campbell
Hello Dick,
That is correct, if you want to setup subscriptions, it is required that the credentials be stored. Did you check the box for "Use as Windows credentials when connecting to the data source" if your shared connection was using a windows account
My advice is to get a single report working with a shared data source, then determine what is different between that one and the one your other reports are using.
Unfortunately, if you set the data source to use "Windows integrated security", then you won't be able to setup subscriptions (because the credentials won't be stored).
I haven't tried embedding the credentials into the connection string, so I am unsure if it would work, but you could try it.
Jarret
Thanks Jarret,
Your right, there are so many places that you can set credentials (Viusual Studio, SQL Server Management Studio/Report Server and Report Manager) and so many options (Integerated Wondows Security, Windows log in etc) it is difficult to test thiem all when you have limited time (i.e. most of the time).
I did check the "Use as Windows credentials when connecting to the data source" check box at one stage and that seemd to help but if I re-depolyed the report and looked at the Properties/Data tab in the Report Manager, it was overwritten and set back to Integrated Windows Secuirty (the original setting for the Shared Data Source as configured in Visual Studio).
Are you saying that I can't use Integrated Windows Security at all if I want configure subscriptions (I think that is what you are saying) Is the alernative to go to "Credentials" tab for the Shared Data Source and set "Use a specific user name and password" I think that I gave that a try but i was trying lots of different options in a short space of time.
In the end. the main problem was the configuration on the Report Server Propertes/Data tab being changes whenever the report was re-deployed from Visual Studio (sorry the SQL Server BI Development Studio
)
I am beginning to think that i need to do both of the above (paragraohs 2 and 3)
Having d
Thanks John,
That is a big help with the immedaite problem although I would still like to use store the credentials in one place (the Share Data Source) rather than with each subscription so I will still look into that side of things.
Dick Campbell.
Hello,
I ran a little test on this and found that if you create a shared data source in your Visual Studio project and store the credentials (Use a specific user name and password), it assumes that you're using a SQL account. The setting "Use as Windows credentials when connecting to the data source" isn't an option, so if you want to use a Windows account, you will have to edit the data source after you deploy it.
Here's what I think you should do...
If you want to use a Windows account for this data source, your credentials will be different in Report Manager and your Visual Studio project. You could use a SQL account as your data source, then you wouldn't have to modify it when you deploy it to your report server.
Does this make sense
Hope this helps.
Jarret
Thanks,
At the moment I am configuring the connection details for every subsciption which is undesirable.
Configuring the Shared Data Source in the Report Manager is the one thing that I think of, I suppose because Books Online tells you to do this in SQL Server Management Studio (on the Report Server).
I have set "Overwrite Data Sources" to false of course.
Dick Campbell