Monday, March 19, 2012

Cannot create new or change existing subscriptions

We are having problems when creating new subscriptions or when trying to edit existing subscriptions. When editing an existing subscription, the report manager displays "An Internal error occured" message and when I look in the log the error says
Only members of sysadmin role are allowed to update or delete jobs owned by a different login

When trying to create a new subscription the report manager displays "An internal error occured" and the log says
System.Data.SqlClient.SqlException: The schedule was not attached to the specified job. The schedule owner and the job owner must be the same or the operation must be performed by a sysadmin.

We are running sql reporting services 2005 sp1. The report server database server is on a different machine to the reporting server. The existing subscriptions were set up previously on another report server which was originally our dev server. These subscriptions are working just can't change them, or create new ones.
Any help is much appreciated.

I had a similar issue only 2 days ago:
There seems to be a problem with the owner of the SQL Server Agent Jobs. In my case ( I couldn't change existing subscriptions but create new ones) the "old" subscriptions had "System" as owner and the new ones had "ASPNET". So I changed all owners to ASPNET and editing subscriptions worked again.
I got the same error as you when I tried to edit subscriptions, though System is also in the sysadmin-role of the server?!

Maybe somebody of microsoft can give some information whats the problem with the Agent Job Owners..

ps: I also moved the database from one server to another..
Calling RSKeyMgmt -a -f keyfile\key.snk -p report creates the Agent-Jobs but with the wrong owner..|||

Thanks for that. I played around with the owner of the jobs but still had the same problem. What I've done for now is delete the encryption key and create a user with sysadmin role on the database server and use that user to connect to database server from report server. I don't recall if the original user I set jobs up as had sysadmin role or if this is a prerequisite for subscriptions to work. I can deploy reports and users can view reports just have the issue with subscriptions. Can anyone let me know if running subscriptions requires a user with sysadmin role?

Thanks

|||

I had the same kind of problem !
Just open a newly created job (created by the reportmanager). Lookup the account, at my case 'NT AUTHORITY\NETWORK SERVICE' and replace the SYSTEM account with this one. (just replace it by hand not via the browse button).
From now on you can delete or update any report or its subscriptions etc

|||

I had a similar problem.

I guess only subscriptions created on a dev server were impacted. Subscriptions created on the prod server are fine.

Since the dev server subscriptions were only a few test made on dev that got transfered to prod when I restored the ReportServer db, I simply deleted them from the subscription table.

I also deleted as many As I could of the associated jobs but the UID makes this un-easy.

They sure is a way to create a query that gives you a list of subscriptions, there ID and the correpsonding job ID and the other way around also.

I always found very aggravating to have the job list filled with these reporting jobs. That is un-manageable and noisy.

Does anybody can share such nice to have management tool?

Thanks,

Philippe

|||

Just encountered the same problem with RS 2005 and this resolved my problem.

It is curious why one job out of four was running with the System account. Changed it to Network Service as you suggested and my changes worked! Thanks for the post.

|||This is a known issue with the RS subscriptions. Jobs are created with the owner set to the account specified for Database Connection setting (Windows account or RS service account). If this account is changed, then the problem occurs for existing subscriptions because SQL Server does not allow editing another owner's subscriptions without special rights. This issue is planned to be fixed in the next RS version.|||

I have a report that I would like to send out to users automatically every day at 10AM. For some reason on reporting server under the report I would to email, I see 3 folders...Data Source, History & Subscription but I am not able to click on "New Subscription" option in order to create new subscription.

First I created "Shared Schedule" specifing day, time etc information. And then I am tring to create Subscription but the option is disabled.

Is there any service pack I am missing or I am following the wrong procedure.

Please advice.

- Payal

|||Hi,
This looks like a permission issue. Couple checks:
- Do you want your users (browser role) being allowed right to create subscriptions
- Is your userid linked to the correct role, i.e. Built in administrators or some other role with access to subscriptions.
Check the permissions, your problem should be there.
Philippe|||

Thanks Phillippe. I have logged in as admin and tring to create new subscription, the option is diabled for me. Is there anything I missed out while installation?

Please advice

|||Did you enabled Scheduled events and report delivery in the surface area configuration for Reporting services?
Philippe|||

Now I get error "Credentials used to run this report are not stored"

I am logged in as admin, what changes i need to make in order to create the subscription?

Please help

|||

Finally I was able to create a subscription, but still under Properties --> general folder I dont see any email options, what am i missing?

Please guide

|||Hi,
Getting closer.
Is it a data-driven subscription? I would guess it is not.
Now, how did you set-up the execution account, is it a (or your) windows account?
On my side, not able to get the thing to work as I initially thought it should with a Windows app account, I finally opted for the following model.
- Local SQL Server login "ReportingServices" with adequate read and execute rights
- All reports datasources use this local SQL server login (securely stored on the database :-)
- All reports use a User parameter so I know who runs the report and can control wether I serve data and what data I serve. The execution account remain the local "app" login which is stored in the database.

Next you have the problem of the email settings security. You have to enable the emails domains you want or you have to enable the emails domain alias property or you have to go though cumbersome AD complex querying scheme. I personally settled for the easy path of editing the XML config file to allow people typing complete email addresses.

I must admit that as I am not a MS insider and was just under the gun to get the thing out of the door, I may have settled for something less than best practice optimal security practice. i would not advertise my shortcuts as to be the way to go
It does not really matter in my case since I am behind lot of firewall and security and does not have the luxury of time to get it done 100% compliant. It works for me My point is;

If Microsoft could help us in an user friendly fashion make good use of security best practice without having to go through all the painful discovery process, it would certainly avoid a lot of us spending time just breaking the security until the basic functionality finally works.

So I call MS gurus to the rescue. And feel free to tell others not to do any of what I did to get it to work. I will surely be very humble and correct my mistakes ASAP.

Philippe|||

You are really kind to answer all my questions. I am going to meet our NT guy, to take his opinion. Will keep you updated.

Thanks once again

No comments:

Post a Comment