Task Schedule: This task requires that the user account specified has Log on as batch job right

Server Administration

When creating a new Task within Task Scheduler and attempting to set the “When running the task, use the following user account” option, you may receive the following error message:

Task Schedule: This task requires that the user account specified has Log on as batch job rights

This can be resolved by ensuring the user account has the correct permissions on the server.

  1. Click the Start button.
  2. Within the Search field, type secpol and, when displayed, click Local Security Policy.
  3. Within the Local Security Policy window, expand the Local Policies node.
  4. Expand the User Rights Assignment node.
  5. In the right-hand panel, right-click Log on as a batch job and select the Properties option.
  6. In the Log on as batch job Properties window, click the Add User or Group… button and add the user or group you want to give access.
  7. Click the OK button to close the window.
  8. Click the OK button to close the Log on as batch job Properties window .

Extra: Task Scheduler Error and Success Constants

Creating a SQL Server backup plan

Server Administration

This is something that is generally out of my area of responsibility but has to be done every now and then: creating and configuring a maintenance backup plan for a SQL database server.

This post from Kyle Laffoon outlines the steps needed to create a plan that executes full and differential backups, as well as looking after transaction logs and cleaning up after itself.

Create a maintenance backup plan in SQL Server 2008 R2 using the wizard

Task Scheduler & PowerShell

Server Administration

To manage repeating tasks, its common for us (the team I work with 9-5) to automate things with a PowerShell script and then schedule it to repeat at specific times using a scheduled task.

When I create the task I use a specific set of parameters , which I always forget – so I’m noting them here for future reference.

PowerShell.exe -NoProfile -ExecutionPolicy ByPass -File C:\PowerShell\Get-SomethingNice.ps1

Creating SharePoint alerts via CSOM

Development, Server Administration

I’m posting this as a useful reminder. It outlines how alerts can now be created via CSOM.

New SharePoint CSOM version released for SharePoint Online – February 2017

The included code sample is in C#, but I’ve translated it to PowerShell – which suits my administrative needs better.

[System.Reflection.Assembly]::LoadWithPartialName( "Microsoft.SharePoint.Client" ) | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName( "Microsoft.SharePoint.Client.Runtime" ) | Out-Null


# Set variables
$path = "C:\Temp\Usernames.txt"

$url = "https://xxxxxx.sharepoint.com/sites/help"

$list = "My List"

$title = "$list (Auto-subscribed)"


# Prompt the administrator to log in
$credential = Get-Credential -Credential $null


# Create a connection to SharePoint Online
$context = New-Object Microsoft.SharePoint.Client.ClientContext( $url )
$context.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials( $credential.UserName, $credential.Password )


# Retrieve the targeted web
$web = $context.Web

$context.Load( $web )
$context.ExecuteQuery()


# Loop through the provided usernames and create a new alert for each one
$usernames = Get-Content -Path $path

foreach ( $username in $usernames )
{
    $user = $context.Web.EnsureUser( $username )

    $alert = New-Object Microsoft.SharePoint.Client.AlertCreationInformation
    $alert.List = $context.Web.Lists.GetByTitle( $list )
    $alert.AlertFrequency = [Microsoft.SharePoint.Client.AlertFrequency]::Daily
    $alert.AlertTime = ( Get-Date ).AddDays( 1 )
    $alert.AlertType = [Microsoft.SharePoint.Client.AlertType]::List
    $alert.AlwaysNotify = $false
    $alert.DeliveryChannels = [Microsoft.SharePoint.Client.AlertDeliveryChannel]::Email
    $alert.Status = [Microsoft.SharePoint.Client.AlertStatus]::On
    $alert.Title = $title
    $alert.User = $user
    $alert.EventType = [Microsoft.SharePoint.Client.AlertEventType]::All
    $alert.Filter = "1"

    $guid = $user.Alerts.Add( $alert )

    $user.Update()

    $context.ExecuteQuery()
}

Truncating a Database Table with a Foreign Key

Server Administration

To remove all data from a table and reset its primary key you can normally run the following SQL command:

TRUNCATE TABLE MyTable;

However, if you try this on a table with one or more foreign keys, you’re going to get an error message. Normally, the way around this is to drop the constraints, truncate the table, and recreate the constraints – which is a lot of fuss.

The following does the same job, but without the fuss.

DELETE FROM MyTable;
DBCC CHECKIDENT (MyTable, RESEED, 0);

 

Secure credentials within PowerShell

Server Administration

For PowerShell scripts that need to access resources (e.g. a site within SharePoint) and be run as scheduled tasks, I will normally use an encrypted password…

Read-Host "Enter Password" -AsSecureString | ConvertFrom-SecureString | Out-File "$PSScriptRoot\Password.txt"

… and then refer to it within my script.

$username = "me@domain.co.uk"
$password = Get-Content "$PSScriptRoot\Password.txt"

$securePassword = $password | ConvertTo-SecureString
$credential = New-Object System.Management.Automation.PSCredential( $username, $securePassword )

This <a href="https://blog.kloud.com.au/2016/04/21/using-saved-credentials-securely-in-powershell-scripts/">blog post by Kloud</a> covers some this technique in greater detail.

Upload limits within IIS7

Server Administration

By default, IIS7 will restrict uploaded greater than 30mb. To overcome this, the following settings can be added to the web.config file within your web application.

<system.webServer>
    <security>
        <requestFiltering>
            <requestLimits maxAllowedContentLength="157286400" />
        </requestFiltering>
    </security>
</system.webServer>

The value for the maxAllowedContentLength attribute should be specified in bytes (MB to Bytes Conversion).

See Error message when you visit a Web site that is hosted on a server that is running Internet Information Services 7.0: “HTTP Error 404.13 – CONTENT_LENGTH_TOO_LARGE”.

It can also be helpful to define the maxRequestLength and executionTimeout attributes.

<system.web>
    <customErrors defaultRedirect="~/Error.aspx" mode="On">
        <error statusCode="404" redirect="~/Error.aspx" />
    </customErrors>
    <pages />
    <globalization culture="en-GB" uiCulture="en-GB" />
    <sessionState timeout="60" cookieless="AutoDetect" />
    <httpRuntime executionTimeout="300" maxRequestLength="150000" useFullyQualifiedRedirectUrl="false" minFreeThreads="8" minLocalRequestFreeThreads="4" appRequestQueueLimit="100" />
</system.web>

Falling foul of SharePoint SSL

Server Administration

Recently, despite ensuring the SSL certificates within Internet Information Services (IIS) were updated ahead of their expiry, I fell foul of the hidden certificate within SharePoint 2013’s Security Token Service. In my defence, this is a certificate not mentioned within IIS or the Central Administration site. Its one of those lovely settings that can only be seen, analysed, and amended via PowerShell commands. Once you know/remember its there, its relatively straight forward to update.

$path = "C:\certificate.pfx"
$password = "thepassword"

$certificate = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2 $path, $password, 20

Set-SPSecurityTokenServiceConfig -ImportSigningCertificate $certificate

certutil -addstore -enterprise -f -v root $certificate

iisreset

net stop SPTimerV4
net start SPTimerV4

See: Replace the STS certificate for the on-premises environment