Post reply

Proxy Account as member of the Local Administrator Group

  • May 31, 2011 at 10:05 pm

    #148112

    Hi guys,

    Is it required that the Proxy account of the SQL Server Agent be a member of the Local Administrators group?

    Because when its not, the job step fails to execute saying

    "DTSRun OnError: DTSStep_DTSCreateProcessTask_1, Error = -2147024891 (80070005) Error string: Access is denied."

    By the way, the job executes a DTS package.

    Thanks.

  • June 6, 2011 at 3:05 am

    #1334255

    It is bad practice for a SQL Agent proxy account to be a local administrator. The aim should always be to minimise the rights granted to a proxy account.

    You are getting an access denied mesage. This should prompt you do do some troubleshooting to find what resource cannot be accessed, and grant the required access to the proxy account.

    Tools such as Process Explorer can help greatly in this troubleshooting, and if you are not familiar with them it is a useful learning experience both for this problem and for the future.

    Another approach that can need less skill is to log on using the proxy account and manually try the commands that are in the SQL Agent job. Typically you get better diagnostics when logged on interactively than you get from SQL Agent, and this can help identify the problem.

    If you go the lazy way of making the proxy account a local admin, then you are also making a hole in your security model. If your business does not care about such holes then go ahead. But if your business does care and this causes a security problem in the future, then do not be surprised if you receive some 'counselling' about your actions.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • June 13, 2011 at 2:49 am

    #1337621

    Hi SSCrazy,

    Thanks for your inputs especially your suggestion to login to the computer using the Proxy account.

    It allowed me debug the specific components of the DTS.

    The Access Denied error comes from the cmd.exe. It's because the DTS executes a batch file which internally calls the cmd.exe.

    A "Read & Execute" permission solved the issue.

    Thanks again very much.

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

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