Page 2 of 2

Re: start-job alternate credentials directory name is invalid

Posted: Wed Nov 06, 2019 1:20 pm
by mxtrinidad
I use the script sample provided and did some adjustments to execute against my SQLServer. The script didn't work in ISE editor. I had issues with the credential section and discover a few things.

Here's what I found:

1. Try/Catch block is not needed to around a script-block, as nothing is executing yet.

2. The errors in ISE with the credentials code block were due to the Try/Catch block been in the wrong place (before the script-block).

3. The try/catch was moved to before the Start-Job code block.

4. Then, create Credential code, also moved prior to the Start-Job. (This clears the error)

5. Also, the code that creates the "Archive" folder is wrong as it was including the filename.

BTW, you may want to create another filename report string for when the job error-out.

After clearing these issues, I can confirm that this code will execute in either ISE and PrimalScript.

Code: Select all

#Scriptblock
$sqlScript = {
try{
#SQL variables
$sqlDataSource = "earth\MSQLCTP33A"
$sqlDatabase = "SampleDB"

$connectionString = "Server=$sqlDataSource;Database=$sqlDatabase;Integrated Security=True;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$connection.Open()

#Sql Query
$query = @"
Select ID, FirstName, LastName, Age from People
"@

$command = $connection.CreateCommand()
$command.CommandText = $query

[System.Data.SqlClient.SqlDataAdapter]$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
[System.Data.DataSet]$SQLDS = New-Object -TypeName System.Data.DataSet "SQLDS"

$null = $SqlAdapter.fill($SQLDS)
$connection.Close()
$SQLDS.Tables[0]
}
catch
{
$connection.Close()

if ($_.Exception.InnerException)
{
Write-Output $Error
Write-Output $_.Exception.InnerException.Message
}
else
{
Write-Output $Error
}
}
}

#Script starts here:
try
{
#Credentials Variables
$username = "earth\user1"
## this line didn't work for me -> $secPass = 'MyPa&&w0rd!' | ConvertTo-SecureString -Key (1..32)
$secPass = ConvertTo-SecureString 'MyPa&&w0rd!' -asplaintext -force;
$cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $username, $secPass

$sqlJob = Start-Job -ScriptBlock $sqlScript -Credential $cred
$recJob = Receive-Job -Job $sqlJob -Wait -ErrorAction Inquire

#export data
$CSV = $recJob | ConvertTo-Csv -NoTypeInformation
$CSVName = '{0}.csv' -f (Get-Date -UFormat "%Y-%m-%d_Software_Export_%I.%M.%S")
$CSVPath = "$env:USERPROFILE\Documents\Audit\$CSVName"

if (!(Test-Path "$env:USERPROFILE\Documents\Audit")) {
## - Below line is wrong:
#-> $null = New-Item -Path "$env:USERPROFILE\Documents\Audit\$CSVName" -ItemType Directory -Force
##

## - Fixed code:
$null = New-Item -Path "$env:USERPROFILE\Documents\Audit" -ItemType Directory -Force

}
$CSV| ConvertFrom-Csv | Export-Csv -Path $CSVPath -NoTypeInformation
}
catch
{
if ($_.Exception.InnerException)
{
Write-Output $Error
Write-Output $_.Exception.InnerException.Message
}
else
{
Write-Output $Error
}
}

This should work and the code is reusable with a little more fine-tuning.

Re: start-job alternate credentials directory name is invalid

Posted: Thu Nov 07, 2019 5:18 am
by PXL_Posh
Thank you all for your help Alexander Riedel and mxtrinidad!

The password using "| ConvertTo-SecureString -Key (1..32)" wouldn't work for you unless your encrypted password was generated using -key (1..32)

Apologies, csv is used elsewhere but in sanitizing it for the post I made some mistakes.
The try blocks are also only around executable sections.
Half of asking for assistance is accurately retrofitting the code it would seem.

Instead of outputting the data to a csv it is actually pushed through a function which adds the rows into a new datatable. As anyone who uses start-job with queries knows datasets and datatables are deserialized in start-job.
The new datatable is then hooked up to a WPF datagrid.
$syncHash.Datagrid_1.ItemsSource = $SQLDT.DefaultView

In case anyone is interested.

for reasons i have not yet determined, I still need that environmental current directory set to the poweshell path or it will continue to throw "directory name is invalid".
it's just odd that it happens on any of our workstations regardless of user without it.