PowerShell script does not run from TaskScheduler

Question

I have a script that pulls some information from AD, inserts rows into a temp table, then calls a SQL script that transforms and upserts rows into a crosswalk table. The script runs fine in ISE, but fails when running in TaskScheduler, whether manually run or scheduled.

On the 'ACTION' page, the program is 'powershell.exe', and the arguments are '-executionpolicy bypass C:\scripts\SysManagement\Populate_AD_Xwalk.ps1.' The Last Run Result is (0x1).

Any idea what is wrong?

Thanks

#   Invoke-sqlcmd Connection string parameters
$params = @{'server'='xxx';'UserName'='xxx';'Password'='xxx'; 'Database'='xxx'}

###################### 
#   Function to manipulate the data
Function writeDiskInfo
{
param($UPN,$EMAIL,$SAM,$ACTIVE)
$InsertResults = @"
INSERT INTO [xxx].[dbo].[WORK_UPN_Email](UPN, EMAIL, SAM, ACTIVE)
VALUES ('$UPN','$EMAIL','$SAM', '$ACTIVE')
"@      
#   call the invoke-sqlcmdlet to execute the query
         Invoke-sqlcmd @params -Query $InsertResults
}

#####################
#   Query AD objects and store in an array
$dp = Get-ADUser -property 'emailaddress' -Filter *

#   Loop through array and insert into WORK table
foreach ($item in $dp)
{
#   Call the function to transform the data and prepare the data for insertion
writeDiskInfo $item.UserPrincipalName $item.EmailAddress $item.SamAccountName $item.Enabled
}
#   Call SQL procedure to delete rows with blank upns and upsert crosswalk table
Invoke-Sqlcmd @params -Query "EXEC ZZproc_Upsert_AD_Email"

Answer 1

The Last Run Result is (0x1) , could mean it’s a privilege issue.

1. Check what user is the scheduler running as . Can the scheduler run even if the user is not logged in?
2. I believe you are using AD user for sql operations. Is the scheduler running as that user. OR does the user running scheduler have sufficient DB privileges ?
3. Let’s say scheduler is being run as the AD user, then check if the user has sufficient privileges to the folder where the powershell script resides
4. Under system32 folder you have a Folder “Tasks” . Does this user have read and execute privileges to the Tasks folder
5. Most importantly the user running scheduler should have the privilege “Log on as batch job”