SQL Agent Job Proxy account vs SQL Agent Service logon

Question

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.

Answer 1

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.)

Answer 2

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,