Credential Manager, PnP Powershell and SQL Agent Proxy permissions

Question

Hi,
I have SQL Agent Job that runs cmdexec via Proxy user and executes pnp ps1 script that picks credentials from Credential Manager and connects to Sharepoint online.

This is the commands that connects to Sharepoint:
Connect-PnPOnline -Url $SiteURL -Credentials SharepointCredentials

When I run this pnp ps1 script MANUALLY in Powershell logged in as Proxy user , it runs successfully. It finds and reads SharepointCredentials, connects to Sharepoint online and dowloads needed files.

When this script is executed via SQL Agent Job that runs cmdexec via Proxy user it fails with error : Credentials not found. No Credential store entry named "SharepointCredentials" exists.

SQL Agent Job service account is local: NT service\SQLAgent
Proxy account is domain: domainname\acctname and not sysadmin

How Windows controls Credential Manager, what additional permissions are needed for Proxy user running on SQL Agent Job service account (NT service)?
Are any specific permissions needed for SQL Agent service account?

Thank you!

Answer 1

This web site .....

https://learn.microsoft.com/en-us/powershell/module/sharepoint-pnp/connect-pnponline?view=sharepoint-ps

...says ....

-Credentials
Credentials of the user to connect with. Either specify a PSCredential object or a string. In case of a string value a lookup will be done to the Generic Credentials section of the Windows Credentials in the Windows Credential Manager for the correct credentials.

Cmdkey.exe will list available credentials. Add these commands to your PS script and review the txt file to verify that your script is running as the account that you expect and view the available credentials.

cmdkey.exe /list | out-file C:\temp\ProxyCreds.txt   
whoami.exe | out-file C:\temp\ProxyCreds.txt -append    

Cmdkey shows some of my credentials as "Saved for this logon only" while others show "Local machine persistence". You may have a "this logon only" problem.

I don't know how you created those credentials, but I see that the New-StoredCredential Powershell cmdlet has a -Persist switch. Maybe try that.

New-StoredCredential -Target Test -UserName test -Password test -Comment "test" -Persist LocalMachine   

Answer 2

Hi @Pam ,

As a workaround, you could store the crendential in a varibale in the script. Then use this to connect to sharepoint, like this:

 $username = "xxx"  
 $password = "xxx"  
 $cred = New-Object -TypeName System.Management.Automation.PSCredential -argumentlist $userName, $(convertto-securestring $Password -asplaintext -force)  
 Connect-PnPOnline -Url "https://tenant.sharepoint.com/sites/yoursite" -Credentials $cred  

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hello @PAM7890,

The local Credential Manager store access can be in different containers:

I would recommend to add the user in the ACL list for

C:\Users\<userprofile>\AppData\Roaming\Microsoft\credentials
and
C:\Users\<userprofile>\AppData\Local\Microsoft\Vault
(*note that <userprofile> must be the user for windows to which this credential was stored)

In regards to the Connect-PnPOnline cmdlet usage, I know for a fact that the user running it needs to be Application Admin role in the Admin center.

If you still have issues with the cmdlet I would recommend opening a discussion with their developers from github, through the link from the "About SharePoint PnP PowerShell CmdLets" section. They do have an active maintenance of the code as well have an active communication with their community.

Hope you find this helpful,
Best regards,