SQL Agent Powershell step running as proxy 'loses' stored credential

Question

When I run a SQL Agent Powershell step 'as' a proxy credential (a domain service account), the credential which has been associated with the service account on the server is not accessible. The credential is for use to access an Azure fileshare.

The proxy is enabled for:

• Operating System (CmdExec)
• SSIS Package Execution
• PowerShell

To reproduce (names of resources have been sanitised):

1. Associate a stored credential for the fileshare with the proxy credential service account:
   
2. Do the same for the SQL Agent service account:
   
3. Set up a SQL Agent job with two steps which do the same thing, one running as the proxy credential and one as SQL Agent:
   
   
4. Run the job.

Here's the output:
Step 1: Executed as user: DOMAIN_svc_MyCredential. domain_svc_mycredential Currently stored credentials: * NONE *. Process Exit Code 0. The step succeeded.
Step 2. Executed as user: DOMAIN_svc_SQLAgent. domain_svc_sqlagent Currently stored credentials: Target: Domain:target=azurefilesharename.file.core.windows.net Type: Domain Password User: fileshareuser. Process Exit Code 0. The step succeeded.

I can't find anything online to explain why the stored credential is not available to the step running as the proxy credential account.

Answer 1

I guess explanation is in Credential API: https://learn.microsoft.com/en-us/windows/win32/api/wincred/nf-wincred-credreadw

When using Proxy, SQL Agent start process with new user credentials, and this open a new Network Logon Session for that user.

When using SQL Server Agent Account, the process opens under service logon session type and this have a credential set.