MS SQL Server Reporting Services - RE: Subscription does not run

Author

RE: Subscription does not run - "logon failed" error (bad username

Terry

2005-11-30, 11:24 am

Did anyone ever resolve this matter?

"dillig" wrote:
[color=darkred]
> Did you ever resolve this issue?
>
> "james_rtl" wrote:
>

Ross

2005-11-30, 11:24 am

On Wed, 30 Nov 2005 08:38:05 -0800, "Terry"
<Terry@discussions.microsoft.com> wrote:

>Did anyone ever resolve this matter?

No but I have the same problem. Subscriptions were working on 2003.

I can see the job in the SQL Server Agent and it always fails on step
1 (the only step) where it runs the AddEvent stored procedure.

NT AUTHORITY\NETWORK SERVICE is in RSExecRole and that has execute
permissions on the sproc and insert, update, select etc directly on
the Events table.

Any suggestions are urgently needed.

Thanks
Ross

Ross

2005-11-30, 8:24 pm

I now have this working. It relates to the 'Web service identity'
application pool.

FWIW, I don't claim to really know what I'm doing but here's the path
I followed.

When you create a subscription, it creates a job in SQL Server Agent.
The jobs are not friendly named but you can find the one you want by
giving it a unique scheduled time or something.

You can observe the problem immediately by doing a right click and
'Start job'. It fails the same as when its scheduled.

Look at the properties and note that the owner of the job is NT
AUTHORITY\NETWORK SERVICE. Hit the button to change the owner, eg to
sa or ServerName\Administr
ator. Now try running it. It succeeds. I
don't know what permission NT AUTHORITY\NETWORK SERVICE is lacking but
EXECUTE AS just doesn't seem to work with it.

I don't know if its talking about the same thing but I found this:
http://msdn2.microsoft.com/en-us/library/ms181362.aspx which says
EXECUTE AS does not work with NT AUTHORITY\NETWORK SERVICE. Also,
that user does not appear in the list to choose from when you change
the owner of a job even though its defined as a SQL login.

The owner of the job is determined by the application pool identity of
the web service.

Start the Reporting Services Configuration Manager.
Go to Web Service Identity
Hit the first 'New' application pool button.
Create a new application pool giving it administrator credentials.
Do the same for the second 'New' button.
Save etc.
Now it works. The jobs created are owned by administrator and run as
they should.

Yes, I know using administrator there is probably very wrong. I
started creating a different user etc but got a bit lost in terms of
what permissions it needs. I need to research that. Its on a pretty
safe internal system.

A slight tidyup I did was to go to IIS admin and delete the new app
pool and then edit the ReportServer app pool to run as administrator
rather than Network Service. You then need to go back to the RS config
and change back to ReportServer app pool.

Well ... that's enough of that for today. I don't know why me and
others are experiencing this but most are not. Perhaps it depends on
the credentials we chose at install.

Cheers
Ross

On Wed, 30 Nov 2005 12:17:38 -0500, Ross <synergy56@hotmail.com>
wrote:

>On Wed, 30 Nov 2005 08:38:05 -0800, "Terry"
><Terry@discussions.microsoft.com> wrote:
>
>
>No but I have the same problem. Subscriptions were working on 2003.
>
>I can see the job in the SQL Server Agent and it always fails on step
>1 (the only step) where it runs the AddEvent stored procedure.
>
>NT AUTHORITY\NETWORK SERVICE is in RSExecRole and that has execute
>permissions on the sproc and insert, update, select etc directly on
>the Events table.
>
>Any suggestions are urgently needed.
>
>Thanks
>Ross

Sponsored Links