question

Pam-7890 avatar image
0 Votes"
Pam-7890 asked Pam-7890 commented

SQL Agent Job Proxy account vs SQL Agent Service logon

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-generalwindows-server-powershellsharepoint-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@ErlandSommarskog thank you for confirming this with the test,
what permissions your proxy has vs Sql Agent Server agent service account,
is Proxy local sysadmin? is your proxy domain user or member of any specific groups?
Iis your Sql Agent Server agent service account local admin, domain user or member of any specific groups??

0 Votes 0 ·

SirPaul is just a plain user with no particular permissions. (But obviously I had granted permission so that he could read the BAT file.)

The service account for agent is NT Service\SQLSERVERAGENT, which has the permissions that Setup grants it to be able to run the service. That does not include any admin roles to my knowing.

0 Votes 0 ·
LimitlessTechnology-2700 avatar image
0 Votes"
LimitlessTechnology-2700 answered Pam-7890 commented

Hello @Pam-7890,

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

https://docs.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,

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Pam-7890 , is there any update? Have you checked LimitlessTechnology-2700's suggestion?

0 Votes 0 ·

Sorry, I could not respond sooner, I have found out why the actual powershell commands, they get executed under
$Env:USERNAME (local NT Service\SQLAgent service account) which is not whoami
1.It happened during testing when somebody rdp-d or open cmd using Proxy account.
2.If local sys admin is given to Proxy and SQL agent, and somebody rdp-d or open cmd using Proxy account THEN the script runs successfully and Powershell uses Proxy account profile
3.If noone rdp-d or open cmd using Proxy account THEN the script fails with "unable to connect to remote server" and Powershell uses Default account

Wondering why when SQL Agent with Proxy, Powershell uses Default account not actual Proxy? Account?
Checked the Group policy and both SQL Agent with Proxy are allowed to impersonate..what else to test?




0 Votes 0 ·

Any chance that you can show some screenshots of how the job is set up, so that we a better understanding of it?

0 Votes 0 ·
Show more comments