August 26, 2020 at 3:51 pm
Hi team,
I created a new windows task scheduler job using GMSA.
Command was success:
$Action = New-ScheduledTaskAction "E:\TEST\DeleteFiles.bat"
$Trigger = New-ScheduledTaskTrigger -At 06:00 -Daily
$Principal = New-ScheduledTaskPrincipal -UserID DOMAIN\SVCSQL$ -LogonType Password
Register-ScheduledTask TestTask –Action $Action –Trigger $Trigger –Principal $Principal
The task is just clear any files in a folder, example D:\TEST\ - any files from this location.
When I executed the task for test, it didn't clear any files.
When I use my own ID and create the task, the job runs successfully and also clears the file in the location.
Not sure, if there would be any special permissions required? Any suggestions to this please?
Thanks
August 26, 2020 at 5:16 pm
To me this sounds like either:
A - permissions
B - drive letter vs FQDN
I would check that SVCSQL$ has permissions to the files and folders and also check if "D:\TEST" is a network drive or not as well as if E:\TEST is a network drive. If D or E are network drives, it is likely that they are not mapped for the user the job is running as and as such, you will need to change it to the FQDN.
Another thing you could (and should) do is add logging to your DeleteFiles.bat file while you are debugging it like this. Once the bugs are worked out, you can reduce the logging, but while it is buggy, I tend to do very extensive logging to file so I can see exactly what was run, and any other interesting information I can think of that I want to review.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 26, 2020 at 6:16 pm
Thanks for the suggestion Brian, I also suspect that the GMSA needs to be added to Log on as batch Job and needs to be in the Administrator group. I will check this with my windows server team to add it. Keep posted.
August 26, 2020 at 6:35 pm
it does not need to be on admin group for this purposes. - but it will need permissions on the folder in question
logon as a batch and/or logon as a service is most likely required.
the execution log on task scheduler should have some indication of the error - if not try and add some displays to it - including output onto its "temp" folder of any type of file to see if it is indeed executing the bat file
EDIT: One thing that MUST be done is adding the GMSA to the computer object - this needs to be done through powershell if not done already.
August 26, 2020 at 6:54 pm
My opinion - the execution log on the task scheduler is not the approach I would take. That will tell you the success/error code for the last command executed. If, for example, you have a little bit of error handling in your bat file, the job may report success while the bat file has a failure.
But I do agree it does not need to be in the admin group and probably shouldn't be unless it needs to do some administrative related tasks (such as working in the WINDOWS folder).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 26, 2020 at 8:20 pm
Just curious - but why are you using a domain service account to run a system level task? You can set that task to run as SYSTEM since it does not need any access outside of that system.
Another note: the task appears to be setup to delete files. It might be a better approach if you are dealing with SQL Server backup files (I am assuming here because you are posting here) to use the Powershell subsystem in SQL Server Agent and Powershell commands. For that you wouldn't need your service account either - but would need to make sure permissions are set.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 27, 2020 at 12:38 pm
Thank you all for the suggestions/ inputs , really appreciate it.
Previously, I was using my own account to create the task scheduler job and it was running successfully. Now, management decided to go with GMSA, so all the tasks that were previously ran using individual ids have to be changed to GMSA to use. I can use Power shell script for clearing old backup files.
Like example :
Start-Process E:\Test\DeleteBAKFiles.bat
$out = E:\Test\DeleteBAKFiles.bat
Add this to a SQL server agent job , but yes proper permissions need to be granted to the sql agent service account on the folders. - This part is fine.
But, I will also need to run a batch job that would call the program Extractor64,- this is required to extract backup files that are sent to us from a vendor.
Having this scheduled using SQL server agent job gives me an issue.
The error is : The error information returned by PowerShell is: ''Extractor64' is not recognized as an internal or external command, '
This is how this Extract.bat file looks like :
:Variables
SET DatabaseBackupPath=E:\Test
echo.
FOR /F "delims=|" %%I IN ('DIR "%DatabaseBackupPath%\*.bak" /B /O:D') DO SET NewestFile=%%I
Extractor64 -F "E:\Test\%NewestFile%" -E D:\TestBackups\test.bak
-- how can this be called in SQL server agent job? Any suggestions - please?.
So far I have found that, this can only be called using a task scheduler job , that's why I was using the Windows task scheduler job.
August 27, 2020 at 12:47 pm
specify the full path to that executable - that is basic stuff for any operating system - either it is on the PATH or full path needs to be specified
August 27, 2020 at 12:52 pm
This same bat file ran successfully through Windows task scheduler job. That is the extractor bat file.
You mean to say ==> : E:\Extractor64 -F "E:\Test\%NewestFile%" -E D:\TestBackups\test.bak ?
August 27, 2020 at 6:25 pm
You are still using a DOS batch file - you should switch to a Powershell script (.ps1).
For example - to loop over a directory contents: Get-ChildItem -Path E:\Test
To loop over a directory contents, get the full filename: Get-ChildItem -Path E:\Test | ForEach-Object {& E:\Extractor64.exe -F "E:\Test\$($_.FullName)" -E "D:\TestBackups\test.bak"}
To use the basename of the file (and the alias foreach-object): Get-ChildItem -Path E:\Test | % {.\Extractor64.exe -F "E:\Test\$($_.FullName)" -E "D:\TestBackups\$($_.BaseName).bak"}
Notice the .\ in the above...if you start in the same folder where the executable lives you can execute it using .\ instead of the full path with the &. You could also use Start-Process to launch the executable and pass in the parameters using that method - which might be a better option.
Build a PS script - then all you need to do is call that PS script from the agent job...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 28, 2020 at 11:42 am
Thank you Jeff ! and all others!
The issue is fixed by granting access to the GMSA to the folders where the files were extracted and where the backup files were required to be deleted. Once this was done, the task scheduler job ran successfully
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply