Error using Invoke-SQLcmd in a script/job

PowerShell Universal
1

Using PSU 3.3.7
Service running as system
I want to run the script below. this script works fine in PS ISE ( $credential = Get-Credential)
SQLConnection credential is a normal domain\user account
Using it as a job gets around using alternate credentials (ps sesssion running as one user, but wanting to run the invokeSQLcmd as another user)

The error I get when I run the job is:
The running command stopped because the preference variable “ErrorActionPreference” or common parameter is set to Stop: [localhost] An error occurred while starting the background process. Error reported: Access is denied.

Job is running as default credentials and environment of windows powershell 5.1

$Database = “CM_xxx”
$ServerInstance = “server1”
$Credential = (Get-Secret SQLConnection)
$SQLQuery = "
select
CS.Name0 as [SystemName],
CS.Manufacturer0 as [Manufacturer],
CS.Model0 as [Model],
OS.Caption0 as [Caption]
from
v_GS_COMPUTER_SYSTEM CS
Inner Join
v_GS_Operating_System OS
On CS.ResourceID = OS.ResourceID
Where OS.Caption0 NOT LIKE ‘%Server%’
"

#Submit the job with creds
$job = Start-Job { Invoke-Sqlcmd -AbortOnError -Database $using:Database -Query $using:SQLQuery -ServerInstance $using:ServerInstance} -Credential $Credential | Get-Job | Wait-Job

#Receive the job
$jobInfo = Receive-Job -Job $job
$jobInfo

2

SYSTEM cannot use -Cred.

This is by design :). It’s to prevent malicious actors from gaining SYSTEM access (effectively owning the computer object…) and then using SYSTEM as the platform for then running commands impersonating a user with other privileges.

Setup a service account and run PSU as that and it will work.

Note: If you DO need to run things as system (for testing purposes), using the command below can be very useful.

$Path\psexec.exe -s Powershell.exe

3

Thanks, I did try that, i will try it again, i was running the service with a normal domain account and got another error

4

Another thing to think about is this; secret vaults are registered in the user context that registers to the vault. If you created the vault and secret under your account, then it will not be accessible to the service account.

5

Thanks, yeah, there are a few things that catch you out.
I think it will be just easier to uninstall and install as domain user and put my stuff back in :slight_smile: Thanks for your help!!