SQL Server Express – Scheduling Jobs via Windows Task Scheduler

Background

Needing a lightweight SQL Server to use as a failover, in case of Production outage,  settled on Sql Express, v2017 no doubt.

 

Problem

SQL Server Express is free, but missing SQL Server Agent, and so no built-in scheduler.

There are some tasks that I need to schedule.

Those tasks are backing up the databases, Index Defrag, and incremental data refresh.

Like Dela potholes soon as I make my way to doorsteps.

 

Files

refreshData.cmd

Outline

  1. Logging
    • Make directory log, if not present
    • If log\refreshdata.log file exists remove it
  2. Issue sqlcmd
    • Pass along the following arguments
      • -b
        • If error occurs in SQL, set errorlevel environment variable
      • -d
        • pass along database name; in our case hrdb
      • -e
        • Pass along payload
        • Call Stored Procedure ( exec dbo.usp_RefreshData )
      • -o
        • Output file is log\refreshData.log
  3. If %errorlevel% equal (-equ ) to zero (0), exit batch file
  4. call emailNotificationFailure.cmd errorlevel

Code


if not exist log mkdir log

if exist log\refreshData.log del log\refreshData.log

ECHO Errorlevel [SQLCMD - PRE] :- %ERRORLEVEL%


sqlcmd -b -S (local) -d hrdb -Q"exec [dbo].[usp_RefreshData]" -o log\refreshData.log

ECHO  [SQLCMD - POST] :- %ERRORLEVEL%

if "%errorlevel%" equ "0" exit /b %errorlevel%

REM Email Notification Failure
call emailNotificationFailure.cmd errorlevel

 

emailNotificationFailure.cmd

Outline

  1. Set _domain name
    • Here we set our email provider domain name
  2. Invoke powershell
    • Pass along -file emailNotificationFailure.ps1

Code

set _domain=labdomain.org
powershell.exe -file emailNotificationFailure.ps1 %_domain%

 

emailNotificationFailure.ps1

Code

Set-StrictMode -Version 1

#Get domain name as first argument
$domain=$args[0]

$self="daniel.adeniji"
$computer=$env:computername

$emailFrom = "$computer@$domain"
$emailTo = "$self@$domain"

$subject = "failed to synch data"

$body = "failed to synch data"

$SMTPServer = "smtp.$domain"

$SMTPServerPort = "25"

$fileAttachment="log\refreshData.log"

"emailFrom :- $emailFrom"
"emailTo :- $emailTo"
"SMTPServer :- $SMTPServer"
"SMTPServerPort :- $SMTPServerPort"

$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, $SMTPServerPort)

$objEmailMessage = New-Object System.Net.Mail.MailMessage

$objEmailMessage.From = $emailFrom;
$objEmailMessage.To.Add($emailTo);
$objEmailMessage.Subject = $subject;
$objEmailMessage.Body = $body;

$objAttachment  =$null
if (Test-Path $fileAttachment -PathType Leaf)
{

	"File Attachment ( $fileAttachment ) Added "

	$objAttachment = new-object Net.Mail.Attachment($fileAttachment)

	$objEmailMessage.Attachments.Add($objAttachment)
	
}	
else
{

	"File Attachment ( $fileAttachment ) :- Skipped"

}

$SMTPClient.Send($objEmailMessage);

if ($objAttachment )
{

	$objAttachment.Dispose()
	
}	

 

Task Scheduler – Management

Task Scheduler – Management – Add task

General

Triggers

 

Actions

Actions – Edit Action

Icacls – Usage – Scheduled Tasks

Background

In an earlier post, Raimund Andrée – NTFSSecurity, Link, we spoke of having considered using Microsoft’s icacls to review NTFS permissions.

icacls

Let us try using Microsoft’s icacls.

 

Sample Script

Scenario

In our use case we will be using icacls to review NTFS Permissions on the C:\Windows\System32\Tasks\ folder.

Windows saves metadata on scheduled tasks in this folder.

 

 

Code Overview

  1. Issue “setlocal” to initiate localize environment settings
  2. Set environment variables
    • set target folder to C:\Windows\System32\Tasks\
    • set appPgm to icacls
    • Set local variables to indicate entries that we will like to discard from showing in the output
  3. Issue forfiles
    • /p
      • set Target folder
        • “%_folder%.”
          • Notice the period that prefixes the enclosing quotes
          • It is needed to handle Windows batch treating the ending backslash as an escape
    • /c
      • Set command to invoke
        • cmd /c
        • if @ISDIR==FALSE
          • Skip folders, work only actual files
        • if @ext==\”\”
          • Tasks are saved without extensions and so limit files we work on to those files that do not have extensions
        • Issue “%_appPgm% @PATH /q”
          • _appPgm is previously set to icacls
            • Parameters
              • @PATH
                • Forfiles is asked to substitute the current full filename
              • /q
                • Tells icacls to suppress success when displaying messages
  4. Issue “endlocal” to revert localized environment settings

Code Actual


setlocal

set _folder=C:\Windows\System32\Tasks\
set _appPgm=icacls

set _skipInherited=
set _skipSP=

set _skipInherited=find /V "(I)"
set _skipSP=find /V "Successfully processed"

REM Added period (.) after folder name for /p argument

forfiles /P "%_folder%." /c "cmd /c  if @ISDIR==FALSE  if @ext==\"\"  echo @PATH | %_appPgm% @PATH /q"  | %_skipInherited% | %_skipSP%

endlocal

Output

Listening

Talking about traps and ‘ving to escape them.

Xscape – Who Can I Run To
Link

 

References

  1. Command Line Reference
    • Icacls
      • Management and Tools > Command-Line Reference > Command-Line Reference
        Link
    • Setlocal
      • TechNet Archive > Windows XP > Command-line reference A-Z
        Link
  2. QandA
    • StackOverflow
      • Forfiles Batch Script (Escaping @ character)
        Link
      • How to use forfiles to delete files without extension
        Link
  3. Blogs
    • SS64
      • FORFILES.exe (Native command in Vista/Windows7/2008, via Resource Kit for XP)
        Link
    • Windows Command Line
      • Srini

Win OS – Task Scheduler – Access Rights – Read & Synchronize

Background

Let us move another pebble in our understanding of Scheduled Tasks.

Tasks are saved as files in the File System.

Lineage

Here are earlier posts on our journey:

  1. Task Scheduler – The user account is unknown, the password is incorrect, or the user account does not have permission to modify the task”
    Link
  2. Raimund Andrée – NTFSSecurity
    Link
  3. Raimund Andrée – NTFSSecurity – Usage Scenario – Day 1
    Link

Error

Here is the original message graciously captured by our developer.

Image

Textual

Task Scheduler cannot apply your change.

The user account is unknown, the password is incorrect, or the user account does not have permission to modify the task.

Scenarios

Review permissions

Remote DB Server

Code


set _folder=\\p-hrdb01\c$\Windows\System32\Tasks\PS\HRDB\DBA\SQLServer

powershell .\..\getNTFSPermissions.ps1 -folder %_folder% 

Output

Explanation

  1. Inherited Permissions
    • We are not showing inherited permissions
  2. Explicit Permissions
    • NT AUTHORITY\SYSTEM
      • Access Rights
        • Read, Synchronize
          • Read
            • The user that has been assigned to run this script is granted Read access
          • Synchronize
            • Same user has also been granted Synchronize permissions
            • The helps lessens the likelihood of disruptions and conflicts as the job is ran and edited

Remote App Server

Code


set _folder=\\p-hrapp01\c$\Windows\System32\Tasks\

powershell .\..\getNTFSPermissions.ps1 -folder %_folder% 

Output

Explanation

  1. Google Update Tasks
    • Tasks
      • C:\Windows\System32\Tasks\GoogleUpdateTaskMachineCore
      • C:\Windows\System32\Tasks\GoogleUpdateTaskMachineUA
    • Account :- NT AUTHORITY\SYSTEM
    • Permissions :-
      • ReadAndExecute
      • Synchronize
  2. Ours
    • LaunchSignup
      • Task :- c:\Windows\System32\Tasks\LaunchSignup
      • Account :- daemon Account we created in Active Directory
      • Permissions :- Read, Synchronize
    • Public Equity – Allocated MV Devel
      • Task :- c:\Windows\System32\Tasks\Public Equity – Allocated MV Devel
      • Account :- daemon Account we created in Active Directory
      • Permissions :- Read, Synchronize

Summary

Job’s metadata are saved in files arranged in hierarchical folders.

The Scheduled task creator does not have permissions on the corresponding file.

The account assigned to run the job is granted read permission on the file.

The file captures and contain metadata on the task and so read permission is need for  the account to open the envelope and read its contents.

Advisory

Best practice will suggest the following

  1. Create Folders for different teams
  2. Assign NTFS permissions to folders and files to delegate and manage access

 

Raimund Andrée – NTFSSecurity – Usage Scenario – Day 1

Background

Now that we have downloaded and installed Raimund Andrée’s NTFSSecurity in one of the standard PowerShell Module’s folder, we are ready to write a little test code and see how well it works.

Code

Script

getNTFSPermissions.ps1


param (
      [string]$file
    , [string]$folder
    , [string]$fileExt
 )
Set-StrictMode -Version 2.0
 
#Import NTFSSecurity
Import-Module NTFSSecurity

# Declare variables
[string] $CONST_FILEMODE_DIRECTORY = "d-----";
[boolean] $fileCheck = $false;

if ([string]::IsNullOrEmpty($file))
{
    $fileCheck = $false;
}
else
{
    $fileCheck = $true;

}

if ($fileCheck -eq $false)
{

    if ([string]::IsNullOrEmpty($folder))
    {
    
        $folder = Get-Location
        
    }
    
}

function getNTFSFile([string] $_fileLocal)
{

       
    Get-NTFSAccess -Path $_fileLocal

    
} #getNTFSFile()    

function getNTFSFolder([string] $folderLocal, [string] $fileExtLocal)
{

    #Declare Local variables
    [string]$_file = $null;
    [string]$_fileFullName = $null; 
    [string]$_fileExt = $null;
    [string]$_fileMode = $null;   
    [boolean]$_fileExtMatch = $true;

    # Get files
    Get-ChildItem $folderLocal | foreach {

       $_file = $_
       $_fileFullName =  $_.FullName 
       $_fileExt = $_.extension
       $_fileMode = $_.mode    
       
       <# #$_file #$_fileFullName #$_fileMode #>
       
       $_fileExtMatch = $true;

       <# If we are matching on file extensions let us see whether it matches #>
       if ([string]::IsNullOrEmpty($fileExt ))
       {
            $_fileExtMatch = $true;
       }
       else
       {
       
            if ( $_fileExt -eq $fileExtLocal )
            {
                $_fileExtMatch = $true;
            }
            else
            {
                $_fileExtMatch = $false;
                
                #"file extension $_fileExt does not match $fileExt "
            }
       
       }
       
       
       if (`
                 ($_fileMode -ne $CONST_FILEMODE_DIRECTORY )`
            -and ( $_fileExtMatch)`
          )
       {
       
            Get-NTFSAccess -Path $_fileFullName -ExcludeInherited

       }
        
    }
    
} #getNTFSFolder()  

if ($fileCheck -eq $true)
{

    getNTFSFile $file 

}

elseif ($fileCheck -eq $false)
{

    getNTFSFolder $folder $fileExt

}


 

Sample

Get Permissions for Excel files

Code


powershell .\getNTFSPermissions.ps1 -folder C:\temp -fileExt .xlsx

Output

Get Permissions for Scheduled Tasks ( Local to machine)

Code


powershell .\getNTFSPermissions.ps1  -folder C:\Windows\System32\Tasks

Output

MS Windows – Task Scheduler – Listing Tasks

Background

There is a nightly batch process that we need to test out as part of a Database Upgrade we are trying to process.

Unfortunately, the task is not running properly when scheduled through the MS Windows Scheduler.

There are likely a few likely causation.

Documentation

As part of a long trek, we need to document the scheduled tasks.

GUI

Microsoft greatly expanded the list of scheduled tasks in MS Windows.

Overview

Using Microsoft’s built-in Task scheduler, here is what ours look like.

GUI – Individual Tasks

To document individual tasks, please select the task, right click on it, and choose  “Export…” from the drop-down menu.

 

Bill Stewart

Need a way of scripting and generating a text file and that is where Bill Stewart comes in.

Code

I have uploaded his Powershell Script to GitHub and here is the Link.

Usage

Here are some sample usage scenarios.

Usage – List Chkdsk

Script

set _taskName=\Microsoft\Windows\Chkdsk\*

powershell ./Get-ScheduledTaskRevised.ps1 -TaskName %_taskName% -Subfolders

Output

Scheduled Task – Powershell Starts, but does not complete

Background

Scheduled a Task, but it is not completing.

 

Task Scheduler

Let us review the Task…

Task Overview

 

Task – Action

TroubleShooting

Task Manager

Launched Task Manager and looked for the Task.

One pointed to do so is to look at Command Line Column.

Image

 

Findings

  1. Command Line
    • C:\Windows\System32\notepad.exe “E:\Scripts\Service\ServiceMgmt\serviceStart.ps1”

 

Interpretation

It seems that ps1 files are attached to notepad.exe

 

Why Notepad?

 

Jacob Zinicola // How To Geek
How to Configure Windows to Work with PowerShell Scripts More Easily
Link

PowerShell is not associated to the .PS1 file extension by default.
Windows sets the default action for .PS1 files to open them in Notepad, instead of sending them to the PowerShell command interpreter.
This is to directly prevent accidental execution of malicious scripts when they’re simply double-clicked.

 

Workaround?

Associate PS1 with Powershell.exe

It is easy enough to associate ps1 files with Powershell.exe and get PS1 files to run directly from the command line.

 

Scheduled Task

But, even after this change, was still unable to get PS1 to run directly as a scheduled task.

 

True Solution

The only true solution is the one where we  a write a command file and invoke the PS1 script in the cmd file…


set "_app=c:\windows\system32\WindowsPowerShell\v1.0\powershell.exe"
set "_PSExecutionPolicy=bypass"

set "_currentFolder=%cd%"
set "_script=serviceStart.ps1"
set "_scriptFullName=%cd%\%_script%"

%_app% -NoProfile -Executionpolicy %_PSExecutionPolicy% -file %_scriptFullName%

 

References

  1. Warren Frame // Cookie Monster
    • Troubleshooting PowerShell Based Scheduled Tasks
      Link
  2. Stack Overflow
    • Powershell script does not run via Scheduled Tasks
      Link
  3. Weekend Scripter: Use the Windows Task Scheduler to Run a Windows PowerShell Script
    Link
  4. How to Geek
    • Jacob Zinicola
      • How to Configure Windows to Work with PowerShell Scripts More Easily
        Link