Credential Manager, PnP Powershell and SQL Agent Proxy permissions

Pam 46 Reputation points
2021-09-20T21:12:18.657+00:00

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!

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,767 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,435 questions
{count} vote

3 answers

Sort by: Most helpful
  1. MotoX80 32,531 Reputation points
    2021-09-21T00:55:48.24+00:00

    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   
    

  2. MichaelHan-MSFT 18,021 Reputation points
    2021-09-21T07:03:05.553+00:00

    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.


  3. Limitless Technology 39,471 Reputation points
    2021-09-21T10:54:00.973+00:00

    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,