1

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"
Tom Repetti
  • 39
  • 2
  • 8
  • Initial guess goes permission, use add-content in between steps to track how far along it goes. maybe even add a line to log the output of "whoami" first. check window events, sql error event, and enable sql tracing session for something that could be more useful. – vhoang Nov 19 '19 at 18:05
  • Thanks. For a start I put a line in that creates a text file with the whoami output. When I run from ISE, the file is written. When run from the scheduler no file is created. – Tom Repetti Nov 19 '19 at 19:10
  • hmm. It's tricky to troubleshoot without a more specific message. try variations of task scheduler settings. first, make sure the period you pasted above doesn't actually exists at the end of the arguments field. then test various combo including adding the start in path "c:\scripts\SysManagement\", making sure the operating system is correct, etc. at least try to trigger an error or run with a simpler ps1 maybe in the temp folder open to Everyone read/write. – vhoang Nov 19 '19 at 19:31

1 Answers1

0

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”
Satish Kumar Nadarajan
  • 186
  • 1
  • 8