SQL Agent Job Proxy account vs SQL Agent Service logon

Pam 46 Reputation points
2021-09-21T20:46:35.157+00:00

Hi We use SQL Agent with "run as" Job Proxy account to run cmdexec bat and powershell script.

The powershell script connects to Sharepoint with:
Connect-PnPOnline - URL -$siteURL -Credentials StoredSharepointCredentials
or

Get-PnPStoredCredential -Name StoredSharepointCredentials

Proxy account is domain account

SQL Agent Service account is local NT Service\SQLAgent service account.

"run as" Job Proxy account is supposed to execute cmdexec bat and powershell script under the security context of proxy account. This Proxy account has access to Sharepoint.

But when I Run SQL Agent job:
bat log shows:
whoami -> I get Proxy account
$USERDOMAIN$\$USERNAME$ -> I get NT Service\SQLAgent service account

Similarly powershell log shows:
whoami -> I get Proxy account
$Env:USERDOMAIN\$Env:USERNAME -> I get NT Service\SQLAgent service account

So , my conclusion, when it comes to the actual powershell commands, they get executed under
$Env:USERNAME (local NT Service\SQLAgent service account) which is not whoami (Proxy account with domain access)
and it appears that execution account does not have access to Stored Credentials (cuz it different user) or "unable to connect to remote server".

Why in this scenario "run as" Job Proxy account DOES NOT execute cmdexec bat and powershell script under the security context of proxy account FULLY? What is missing?
How to configure proxy account so it will not get "overwritten" by NT Service\SQLAgent service account?

Note - when I rdp to the server and login under Proxy account and run these scripts manually in cmd or powershell, they run fine. $Env:USERNAME and whoami - both refer to Proxy account.

Thank you.

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,486 questions
SharePoint Development
SharePoint Development
SharePoint: A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.Development: The process of researching, productizing, and refining new or existing technologies.
2,616 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,322 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 100.1K Reputation points MVP
    2021-09-21T21:41:32.493+00:00

    I tested this. I created a file test.bat with the contents:

    echo %USERDOMAIN%\%USERNAME%
    

    and then I created a test job with this text for the CmdExec step:

    echo %USERDOMAIN%\%USERNAME%
    C:\temp\test.bat
    

    I executed the job, and in the job history I saw:

    Executed as user: SOMMERWALD\SirPaul. SOMMERWALD\SirPaul. Process Exit Code 0. The step succeeded.

    That is the name of my proxy user. I'm uncertain why I only got one line. I had expected to get two, and that the first line would be service account for Agent. To wit, I suspect that the environment variable are expanded before the new process for the proxy login is created. So that is why you put it in a .BAT file (or .ps1 for PowerShell.)


  2. Limitless Technology 39,301 Reputation points
    2021-09-23T10:41:58.757+00:00

    Hello @Pam ,

    There is a similar issue discussed in the below thread, you can use the below link to view it,

    https://learn.microsoft.com/en-us/answers/questions/265815/proxy-account-and-jobs-in-sql-server.html

    ----------------------------------------------------------------------------------------------------------------------

    Hope this answers all your queries, if not please do repost back.
    If an Answer is helpful, please click "Accept Answer" and upvote it : )

    Regards,