Avatar of E_Gordon
E_Gordon

asked on 

Access is denied when stopping SSRS via SQL job

So, I have a .dtsx package that contains two steps; VB script to stop, then restart, the local Report Server service.  I can execute this package and everything works as-expected.  

I have created a SQL Server Agent job to call this package.

When the job runs, it fails with:
"...System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidOperationException: Cannot open ReportServer service on computer '.'. ---> System.ComponentModel.Win32Exception: Access is denied     --- End of inner exception stack trace --..."

Based on the "Acces is denied" in there, I am assuming that this is a permissions issue.
My question is:  why does this package run "stand-alone', but fail when called as a SQL job?  (Assuming permissions, but...) More specifically, where/what do I need to change to allow SQL to run the package without error?  I have treid changing the SQL job owner to my own account (which runs the package just fine) and also to the "Log On" account of the service (which is a service account; not "local system"); same result...access is denied.

Thanks,
Eric
Microsoft SQL ServerMicrosoft SQL Server 2005SSRS

Avatar of undefined
Last Comment
Jason Yousef
ASKER CERTIFIED SOLUTION
Avatar of Jason Yousef
Jason Yousef
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of E_Gordon
E_Gordon

ASKER

I didn't realize that I had to restart the service after making the change.  I changed the job owner to be the same account as the service Log On, and after a resart of the SQL Agent service it now works...
Thanks!
Avatar of Jason Yousef
Jason Yousef
Flag of United States of America image

Great ! I always do that and expect it to work too!
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo