SQL Server Performance

Permissions for the proxy account

Discussion in 'SQL Server 2008 General DBA Questions' started by dinkar82, Dec 16, 2010.

  1. dinkar82 New Member

    Hi everyone,
    I am new in SQL Server so please bear with me.
    A question regarding proxy a/c permissions in a SQL Server Agent job:
    I am logged in as user1 with sysadmin privillages, created a job with owner user2 who only have public permissions. Used same a/c to create proxy a/c which i used in user2's agent job.
    Agent job is just fetching new rows from one server and transferring them into another.
    I was thinking that it wont be sucessful because user2 didnt have any sysadmin or agentjob roles(just had public privilage). But job got executed sucessfully and it did transfer rows to other server as well.
    My query is that why it happened, how come without the permissions?
    And could you please tell me what minimum permissions i need to give to login to run a multiserver job because i want to use this login just to run in-future agent jobs as well. no extra permissions i want to give to this login.
    Any help will be highly appretiated.
    Thanks in advance.
    dinkar82, Dec 16, 2010
    #2

  2. satya Moderator

    WElcome to the forums.
    Follow these guidelines to improve the security of your SQL Server Agent implementation:
    • Create dedicated user accounts specifically for proxies, and only use these proxy user accounts for running job steps.

      • Only grant the necessary permissions to proxy user accounts. Grant only those permissions actually required to run the job steps that are assigned to a given proxy account.

        • Do not run the SQL Server Agent service under a Microsoft Windows account that is a member of the Windows Administrators group.
      • A proxy provides SQL Server Agent with access to the security credentials for a Microsoft Windows user. Each proxy can be associated with one or more subsystems. A job step that uses the proxy can access the specified subsystems by using the security context of the Windows user. Before SQL Server Agent runs a job step that uses a proxy, SQL Server Agent impersonates the credentials defined in the proxy, and then runs the job step by using that security context.
    satya, Dec 21, 2010
    #3

  3. dinkar82 New Member

    Thnx for replying Satya,
    I followed your steps, which were quite useful. But before giving any permissions(except public, which is default) to proxy account i tried to ran my job.
    Job was bringing new rows from one database to another across different servers.
    Job was completed without any error, which is strange.
    Why my job got executed sucessfully without giving me any error?
    i didnt give any sysadmin or SQLAgentOperatorRole, SQLAgentReaderRole, SQLAgentUserRole or TargetServersRole.
    Is it anything to do with Domain permissions of my proxy account?
    Any help will be highly appretiated.
    dinkar82, Dec 21, 2010
    #4

  4. satya Moderator

    Glad to know you are proceeding well, also confirm what is the privilege set for the user account on the database.
    satya, Dec 22, 2010
    #5

  5. dinkar82 New Member

    No extra permissions..just created them and assigned them--
    sql login---no role assigned
    proxy account-public permission only
    i was getting error when i was assigning proxy account to sql login.
    then i added sql login into proxy account, went smoothly.
    no extra permission at all anywhere, which is making me mad.
    i read too many articles about sqlagentuser/operator/readerRole, when using proxy but all of these permissions didnt come in use.
    Am i doing something wrong somewhere?
    for more info--i checked msdb, master and even database level permissions. everywhere both(SQL Login and Proxy a/c login) of them have only public permission.
    Is there anything to do with mine default schema assigned--whcih is dbo(only have db_owner role assigned)
    dinkar82, Dec 22, 2010
    #6

  6. satya Moderator

    Who is the owner for your user database?
    Also we are bit diverting from the actual problem, so if you can explain what you want to achieve then we can concentrate to avoid any security lapse on yoru machine.
    satya, Dec 23, 2010
    #7

  7. dinkar82 New Member

    Hi Satya Really appreciate your help.
    I will provide you the detailsI am logged in as gc estingc(mixed authentication--local admin on my computer), have sysadmin permissions.logged in SSMS with windows authentication.1) The account the SQL Server Agent service running: Administration tools>Services>SQLSERVERAGENT(MSSQLSERVER): Right Click>properties: log on as Local System Account
    In Server Manager (SERVERNAME)>Configuration>Local Users and Group>Group>SQLServerSQLAgentUser$<Servername>$MSSQLSERVER> Right Click On General page in memebers >>gcagentjobs and NT ServiceSQLSERVERAGENT (i think there is the problem, i have to remove gcagentjobs from here)2) Owner of the job: DinkarTest(SQL Login)3) Proxy account which run this job gcagentjobs4) Job History Log: Date 23/12/2010 4:15:30 PM
    Log Job History (Testing Job)Step ID 1
    Server ServerName
    Job Name Testing Job
    Step Name Testing_Dinkar
    Duration 00:00:01
    Sql Severity 0
    Sql Message ID 0
    Operator Emailed
    Operator Net sent
    Operator Paged
    Retries Attempted 0Message
    Executed as user: GCagentjobs. Started: 4:15:30 PM DTExec: The package execution returned DTSER_SUCCESS (0). Started: 4:15:30 PM Finished: 4:15:31 PM Elapsed: 0.61 seconds. The package executed successfully. The step succeeded.5) Account gcagentjobs is not included in msdb database (hence, no targetserversrole assigned), neither in master databaseWell, DinkarTest(only have public permission, no sqlagentuser/operator/readerRole and targetserversrole)
    Motive: i just want to create a seperate account who can run my jobs with minimum permissions needed to run multiserver jobs(yea with proxy account)Trying to achieve: here is to keep any random user restricted from changing anything in my jobs. they can watch them but dont want to let them change anything.Hopefully i have provided everything, if not please let me know.Thnx heaps
    dinkar82, Dec 23, 2010
    #8

  8. satya Moderator

    Hi Dinakar
    Now it is getting clear on what terms you wanted to accomplish, let me give you feedback step by step.
    SQL Server Agent runs under Local System Account, as per BOL and architecture of service account is local system account name is NT AUTHORITYSystem, a powerful account that has unrestricted access to all local system resources. It is a member of the Windows Administrators group on the local computer, and is therefore a member of the SQL Server sysadmin fixed server role.
    As it is a backward compatibility account, and security best practice is not to run SQLAgent as it has permissions that SQL Server Agent does not require. Avoid running SQL Server Agent as the Local System account. For improved security, use a Windows domain account with the permissions required. Also as your environment needs multiserver job processing, the account must be a member of the msdb database role TargetServersRole on the master server.
    One way you are using a PROXY account to handle the jobs, which means the account has required privileges to modify the job if required. I recommend you to read on SQL Server Books Online (BOL) on the topic "Implementing SQL Server Agent Security".
    Coming to the next part of restricting any user (non sysadmin) to modify the jobs but execute them. BOL comes to our rescue in explaining about :
    SQL Server 2005 introduced the following msdb database fixed database roles, which give administrators finer control over access to SQL Server Agent. The roles listed from least to most privileged access are:
    • SQLAgentUserRole

      • SQLAgentReaderRole

        • SQLAgentOperatorRole
      • These are best database roles for SQLAgent to use when any user or group who are not members of one of these roles are connected to SQL Server in SQL Server Management Studio, the SQL Server Agent node in Object Explorer is not visible. A user must be a member of one of these fixed database roles or a member of the sysadmin fixed server role to use SQL Server Agent.
        Hope this helps.
    satya, Dec 29, 2010
    #9

  9. satya.sqldba New Member

    I had to deal with similar issue before, check out the below and see if it helps
    http://sqlserver-satya.blogspot.com/2009/09/execute-ssis-package-under-proxy.html
    satya.sqldba, Dec 29, 2010
    #10

  10. dinkar82 New Member

    Thnx satya.sqldba
    quite handy steps to follow.
    dinkar82, Dec 29, 2010
    #12

  11. satya.sqldba New Member

    Glad I could help, you are welcome
    satya.sqldba, Dec 30, 2010
    #13

  12. dinkar82 New Member

    Hi Satya,
    yiipppeee..i have done it.
    really appreciate your help. Followed your steps and assign some select and update permissions on databases(which were going to be used) and all went smoothly.
    Moreover, removed gcagentjobs from--Group>Group>SQLServerSQLAgentUser$<Servername>$MSSQLSERVER> Right Click On General page in memebers >>gcagentjobs and NT ServiceSQLSERVERAGENT-- because it was giving it sysadmin rights.
    Really a nice learning experience for me.
    Cheers
    dinkar82, Dec 29, 2010
    #14

Share This Page