question

Pam-7890 avatar image
1 Vote"
Pam-7890 asked MikkelBachmannNrgaard-5899 commented

Credential Manager, PnP Powershell and SQL Agent Proxy permissions

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!

windows-server-powershellsharepoint-dev
· 1
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,

I have the same trouble when trying to execute the command pnp-connectOnline from SQL server agent service account. Did you find a solution to the problem, which is not to store the credentials in the script?

0 Votes 0 ·
MotoX80 avatar image
0 Votes"
MotoX80 answered Pam-7890 commented

This web site .....

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


· 6
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.

Thank you for helpful cmdkey command, if executed under SQL Agent with "run as' Proxy, it returns blank list. Apperently it still uses SQL Agent service account as logon, not Proxy (whoami), do not know why.
If cmdkey command runs under proxy used (rdp-ed) it is obviously has logon and whoami as Proxe and returns full list.

0 Votes 0 ·

If you have cmdkey and whoami on a .ps1 file they should both execute as the same user.

When you RDP in as the proxy user, does cmdkey show multiple stored credentials?

What about the "Saved for this logon only" and "Local machine persistence" question?

0 Votes 0 ·

Hi @MotoX80,
"If you have cmdkey and whoami on a .ps1 file they should both execute as the same user." - even with SQL Agent with "run as' Proxy security context? What could cause the mismatch..?

"When you RDP in as the proxy user, does cmdkey show multiple stored credentials?" - yes, when RDP-ed it shows list of all credentials Proxy account has in Credential Manager. When executed via SQL Agent with run as proxy, this command returns "NONE"

"Cmdkey shows some of my credentials as "Saved for this logon only" while others show "Local machine persistence". - Funny thing, the credentials Proxy uses for Sharepoint, they do not have "Saved for this logon only" or "Local machine persistence" option specified, no such or any option at all.
Is it a good thing or bad thing?
T

0 Votes 0 ·

What could cause the mismatch..?

I'm not sure what it's doing. I've seen where job scheduling software does impersonation of users and a full user environment doesn't get loaded, so that could be an issue.

I

s it a good thing or bad thing?


I see that the other value is Enterprise, but I would have to research what that means. I did define a scheduled task (I don't have SQL) and it was able to see this entry.

134095-capture.jpg



ITry creating an entry in the script and see if cmdkey will list it in the RDP session. Use a PS transcript to log what it does.

 Start-Transcript -Path  C:\temp\ProxyCreds.txt 
 ""
 "HKEY_CURRENT_USER says that I am...."
 (Get-ItemProperty 'HKCU:\Volatile Environment').USERNAME
 New-StoredCredential -Target Test -UserName test -Password test -Comment "test" -Persist LocalMachine 
 cmdkey.exe /list:test
 ""
 Stop-Transcript





0 Votes 0 ·
capture.jpg (39.9 KiB)

If the credentials get created but you still don't see it, run this command from an admin Powershell prompt. It will show the last couple of credentials that were created and in which users folder. That night answer the SQL Agent vs Proxy account question.

 get-childitem C:\Users\*\AppData\Local\Microsoft\Credentials\* -force | Sort-Object -Property LastWriteTime -Descending| Select-Object -First 5 | format-table -Property LastWriteTime, Directory, Name 
0 Votes 0 ·

Thank you @MotoX80 , I got the output/some enties for Proxie, for some reason LastWriteTime does not correspond to Modified date for any of credentials I see in Credential Manager UI. So I cannot for sure pinpoint the entries from the command output and UI. Will be doing more tests. Waht to create Stored credentials via command in SQL Job and see what happens :)

0 Votes 0 ·
MichaelHan-MSFT avatar image
0 Votes"
MichaelHan-MSFT answered Pam-7890 edited

Hi @Pam-7890,

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.


· 1
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.

Thank you @MichaelHan-MSFT , yes, this would solve Cred Store issue.
We tried using AESkey stored in txt file, but no longer allowed to store encripted password in txt or in a script.
The challenge, is that even with username and password , when script is executed under SQL Agent, it will not get Proxy security context fully.
SQL Agent account is local with "run as" Proxy with Domain access = it still uses SQL Agent account logon and even with username/password throws "unable to connect to remote server"

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

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,

· 1
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.

Thank you @LimitlessTechnology-2700 ! What permissions are needed to see another <userprofile> 's credentials?

0 Votes 0 ·