3

I am downloading a file from sharepoint. I have already scheduled the job in SQL job agent Its working fine when i use the following code

 $UserName = "xxxx"

$PswdPath = "D:\securestring.txt"
$SecurePassword = cat $PswdPath| convertto-securestring
$fileName = [System.IO.Path]::GetFileName($FileUrl)
$DownloadPath = "D:\Excel\"
$downloadFilePath = [System.IO.Path]::Combine($DownloadPath,$fileName)

$client = New-Object System.Net.WebClient 
$client.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $SecurePassword)
$client.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f")
$client.DownloadFile($FileUrl, $downloadFilePath)
$client.Dispose()

But the problem here i face is whenever i update my password i need to update the secure string as well

So i wanted to use the default credentials

so i used the following script 

$webclient = New-Object System.Net.WebClient

$webclient.UseDefaultCredentials = $true
$webclient.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f")
$webclient.DownloadFile($FileUrl, $DownloadPath)

but its getting failed with the following error

Exception calling "DownloadFile" with "2" argument(s): "The remote server returned an error: (401) Unauthorized."

went through different blogs all were suggesting the same approach which i have followed Any help in this regard?

Tyler Durden
  • 207
  • 1
  • 5
  • 10
  • from what is see your second solution is the good one but are you sure of the account runing the sql job have all rigth required?.iContact your dba and look what account on sql server run the sql job change it or give it the good rigth to access you webclient. Agent Job is a service account it have like no much rigth out of the sql server – Zwan Dec 10 '15 at 13:45
  • No the account with which we are running the job has admin access to webclient as well. The username which we are providding as of now(first approach), the same account we are using to run the sql job – Tyler Durden Dec 12 '15 at 04:15
  • can you check under "servivce" you have something called "Agent SQL Server" and check the session that run it?. check domain rigth for this account is it an admin account ?? is it allowed to exec script write disk (since you seem download something?) try launch you powershell out of sql server with the agent account for check if same error happen. – Zwan Dec 14 '15 at 08:21

1 Answers1

0

As far as I understand default credentials users the account and password the sql agent process is started and since it will not match the SharePoint online account it will fail. It would be easier if you create a powershell that updates all secure strings once password is changed.

Balu Dharmarajan
  • 1
  • 1