proxy account and jobs in sql server

oly 66 Reputation points
2021-02-09T16:37:07.147+00:00

Hello all;

I am still a beginner, please bear with me. I am trying to understand the proxy accounts in sql server in relation to jobs.

For example, lets say you started off by going to security --> credentials and create two accounts called testA and testB

You then go to "sql server agent" --> proxies --> ssis package execution and create two proxy account called testA and testB

Now, you create a job and run the job uses proxy account testA, the job runs fine with no issues. Then you go back and edit the job step properties to run as testB but the job doesnt run anymore. It is giving an error stating

unable to start execution of step 1(reason: could not get proxy data for proxy id=3). The step failed

How does one fix and debug this issue?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,676 questions
0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. CathyJi-MSFT 21,086 Reputation points Microsoft Vendor
    2021-02-10T02:58:22.653+00:00

    Hi @oly ,

    Did you set the Run as account to be a proxy account(proxy account testB, default is SQL Server Agent Service Account) when you edit the job step? Did the user TestB and TestA have the same permission? The user specified in the credential must have "Access this computer from the network" permission (SeNetworkLogonRight) on the computer on which SQL Server is running. Did the proxy testB has access to the subsystem?

    Please refer to the MS document Create a SQL Server Agent Proxy and the blog How to: Configure an SSIS Package to Execute as a Proxy Account to get more detail information about proxy accounts.

    If the response is helpful, please click "Accept Answer" and upvote it, thank you.

  2. CathyJi-MSFT 21,086 Reputation points Microsoft Vendor
    2021-02-10T09:50:45.13+00:00

    Hi @oly ,

    > "Access this computer from the network" permission (SeNetworkLogonRight) on the computer on which SQL Server is running. i am also trying to understand this statement, can you please explain further

    This quote from MS document, SQL Server Agent proxies use credentials to store information about Windows user accounts. The user specified in the credential must have "Access this computer from the network" permission (SeNetworkLogonRight) on the computer on which SQL Server is running.

    You can check this follow below steps.

    1. Run "gpedit.msc".
    2. Navigate to Local Computer Policy >> Computer Configuration >> Windows Settings >> Security Settings >> Local Policies >> User Rights Assignment>> "Access this computer from the network" right

    > Did the proxy testB has access to the subsystem?

    I mean that did you active the subsystem when you create proxy testB?

    66260-screenshot-2021-02-10-174754.jpg

    If the response is helpful, please click "Accept Answer" and upvote it, thank you.

    0 comments
  3. oly 66 Reputation points
    2021-02-10T20:50:21.577+00:00

    This quote from MS document, SQL Server Agent proxies use credentials to store information about Windows user accounts. The user specified in the credential must have "Access this computer from the network" permission (SeNetworkLogonRight) on the computer on which SQL Server is running.

    You can check this follow below steps.

    1. Run "gpedit.msc".
    2. Navigate to Local Computer Policy >> Computer Configuration >> Windows Settings >> Security Settings >> Local Policies >> User Rights Assignment>> "Access this computer from the network" right

    Thanks, I just confirmed that and access and permission are the same.

    I mean that did you active the subsystem when you create proxy testB?

    Yes both testA and testB have the associated SQL server Integration Service package checkbox checked.

  4. oly 66 Reputation points
    2021-02-11T14:29:44.157+00:00

    Hi Cathyji-msft;

    Yeah that is the mystery and since I am still quite a novice, i have a bit of limited knowledge. I am thinking of deleting the account set-up in the credential section of sql server and also the account set-up in the proxy section of sql server and reconfigure it again. maybe it will magically work