Uncategorized

No Answers Yet. Just Questions


So, I’m running into a technical snag and not having much luck solving it, yet. Hence, why I’m posting this here: in the hopes you might see this and respond with an answer. So far, the best (hopeful) solution offered is to run the process as a service (thanks Tony!), which I have not yet tried.

The Challenge

I need to schedule a PowerShell script (shown below) to run daily, using either a service account, or the local ‘SYSTEM’ account (I’ve tried both). This script needs to invoke functions from the PowerShell module ‘dbatools’, and produce output to a log file. Keep in mind that this is a ‘test’ script, and the actual script is far more eye-destructive ugliness than I can share here.

The platforms I’ve tested on (two so far) are the same:

  • Windows Server 2016 (domain-joined member server, entirely different forests/domains)
  • SQL Server 2017
  • PowerShell 5.1 (standard, built-in)
  • dbatools version 0.9.818

The Testing

Here is the script (below). When I run it in an interactive PowerShell console, it works fine. Even when I launch the console using “psexec.exe -i -s powershell -NoProfile” it works absolutely fine.

[CmdletBinding()]
param (
[string] $server = $env:COMPUTERNAME,
[string] $database = "MyDatabase",
[string] $tablename = "table123"
)
Start-Transcript -OutputDirectory "x:\scripts"
try {
Write-Output "importing module: dbatools"
Import-Module dbatools
Write-Output "version: $((Get-Module dbatools).Version -join '.')"
Write-Output "verifying table"
if (Get-DbaDbTable -SqlInstance $server -Database $database -Table $tablename -ErrorAction Stop) {
Write-Output "found it!"
}
else {
Write-Output "not found"
}
}
catch {
Write-Output "error: $($Error[0].Exception.Message)"
}
finally {
Stop-Transcript
}

The Scheduled Task

The Scheduled Task is set to run with the following configuration:

NamePS Test
TriggerDaily at 10:00 am
Actionpowershell -ExecutionPolicy ByPass -NoProfile -File x:\scripts\dbtest.ps1
RunAsNT Authority\System (aka “SYSTEM”)
OtherHighest privileges: enabled (checked)

The Problem

So far, no matter how I configure the task, it skips dbatools entirely and ends. Run it in an interactive console and it works perfectly. Here’s an example transcript from an interactive console invocation:

 Windows PowerShell transcript start
Start time: 20190430105649
Username: SKATTERBRAINZ\SYSTEM
RunAs User: SKATTERBRAINZ\SYSTEM
Machine: CM01 (Microsoft Windows NT 10.0.14393.0)
Host Application: powershell.exe -NoProfile
Process ID: 7300
PSVersion: 5.1.14393.2248
PSEdition: Desktop
PSCompatibleVersions: 1.0, 2.0, 3.0, 4.0, 5.0, 5.1.14393.2248
BuildVersion: 10.0.14393.2248
CLRVersion: 4.0.30319.42000
WSManStackVersion: 3.0
PSRemotingProtocolVersion: 2.3
SerializationVersion: 1.1.0.1
*************************
Transcript started, output file is x:\scripts\PowerShell_transcript.CM01.ycN6+GrS.20190430105649.txt
importing module: dbatools
version: 0.9.818
verifying table
found it!
*************************
Windows PowerShell transcript end
End time: 20190430105649
*************************

And an example from running via a Scheduled Task as local ‘SYSTEM’ account…

 Windows PowerShell transcript start
Start time: 20190430103137
Username: SKATTERBRAINZ\SYSTEM
RunAs User: SKATTERBRAINZ\SYSTEM
Machine: CM01 (Microsoft Windows NT 10.0.14393.0)
Host Application: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.EXE -ExecutionPolicy ByPass -NoProfile -File x:\SCRIPTS\dbtest.ps1
Process ID: 8312
PSVersion: 5.1.14393.2248
PSEdition: Desktop
PSCompatibleVersions: 1.0, 2.0, 3.0, 4.0, 5.0, 5.1.14393.2248
BuildVersion: 10.0.14393.2248
CLRVersion: 4.0.30319.42000
WSManStackVersion: 3.0
PSRemotingProtocolVersion: 2.3
SerializationVersion: 1.1.0.1
*************************
importing module: dbatools
*************************
Windows PowerShell transcript end
End time: 20190430103142
*************************

It just stops at the import-module step and skips over to the “finally{}” block. No error is returned via “catch{}” which seems odd to me.

UPDATE: 04/30/2019

I added the following line just above the Import-Module for dbatools…

Write-Output "importing module: dbatools" 
#Import-Module dbatools
#Write-Output "version: $((Get-Module dbatools).Version -join '.')"
Write-Output "verifying database"
if (Get-SqlDatabase -ServerInstance "." -Name $database -ErrorAction Stop) {
Write-Output "database found!"
}
else {
Write-Output "database not found"
}

Here’s the updated transcript output…

importing module: dbatools
verifying database
database found!
verifying table
WARNING: SQLPS or SqlServer was previously imported during this session. If you encounter weird issues with dbatools, please restart PowerShell, then import dbatools without loading SQLPS or SqlServer first.
WARNING: To disable this message, type: Set-DbatoolsConfig -Name Import.SqlpsCheck -Value $false -PassThru | Register-DbatoolsConfig
found it!

Very weird results (to me, anyway).

UPDATE: 04/30/19 NO. 2 (The Electric Boogaloo)

Apparently, PowerShell 5.1 transcript features are what I was relying on to determine output/results (success/fail), which isn’t reliable. So, modifying my code (per suggestion of Shawn Melton, Slack “Sqlcommunity” channel) to use Out-File instead, seems to work!

# bad kitty!
Start-Transcript -OutputFolder "x:\scripts"
Write-Output "this works sometimes"

# good kitty!
$logfile = (Join-Path $pwd "logfile.log")
"$(get-date -format "yyyy-MM-dd hh:mm:ss") try this!" | Out-File $lotfile -Append
Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s