Post reply

Confusion as to what windows service Agent and Engine are running under

  • April 24, 2014 at 10:29 am

    #286059

    Hi

    I have an SP that runs sys.xp_dirtree to read network files e.g. \\servername\share\inputfiles\

    I cannot get an SQL Agent job to run this SP. Permissions issue

    The Agent Service is set to run under a Domain account (PC_SQLAgent) which has access to the folder directory on a network. The SQL Service runs as the built in nt authorityetwork service.

    Now I'm mightly confused. When I log into the machine and then SQL Server Enterprise manager (E.M) as this Domain account PC_SQLAgent and run the Stored proc it works. (I guess nt authority.. has rights?)

    However when I try executing the job under Agent node in E.M. it fails, no error just no results consistent with no permissions.

    1. I thought whatever you run in E.M. Windows sees this as whatever windows account is used to run the service in this case nt authorityetwork service. No matter if I use Execute As or logged in as sa or for any user with windows authentication?

    2. Are Right clicking a job in E.M. and "start job at step" or waiting for the job to be invoked by the schedule are the same i.e. you run as the SQL Agent Service Windows User in this case PC_SQLAgent. It's not that manually starting job runs as the SQL Service user? as I've seen in some blogs.

    When running in E.M. and connected as PC_SQLAgent it works, when issuing an sqlcmd command using trusted connection again logged in to windows as PC_SQLAgent it works.

    However with SQL Agent running as PC_SQLAgent and 2 jobs one calling the SP directly and one calling sqlcmd to call the SP they both failed. I also tried using "Run as user" more out of "curiosity".

    There are work arounds, it would be best to instal SIIS (the new DTS) but I want to clear up the issue of what services run as. So I have straight in my mind.

    Thanks

  • April 24, 2014 at 10:40 am

    #1708503

    "Enterprise manager"... are you sure it's a SQL Server 2008?

    Anyway...T-SQL job steps run under the job owner security context.

    However, if the job owner is a sysadmin, the step will run under the SQL Server agent service account.

    -- Gianluca Sartori

  • April 25, 2014 at 4:39 am

    #1708715

    Sorry, it is Management Studio on 2008 R2.

    So if the owner of the job is sa and SQL Agent service runs as SQLAgentUser then whenever the job interacts with Windows, Windows will see it as SQLAgentUser

    I Must be carefull to create jobs as sysadmin user as above is the behaviour I want.

    Grazie.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply