3

I have a Powershell script to backup MSSQL on a Windows Server 2008 R2 box. It backs up a specific DB to a local disk, then moves the backup to a Network mounted CIFS volume. It works 100% fine when run locally no matter how I invoke it. I installed Freesshd (latest) to be able to start the script using a scheduler (not windows task scheduler ). I am constantly getting cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help abo ut_signing" I connect via SSH, invoke powershell, then run .\scriptname. Running get-executionpolicy returns remoteSigned. I am logged in as the local Administrator account via password authentication.

#################
# Backup MSI    #
# A POwershell  #
# Script.       #
#################
#load SQL snap-in
Add-PSSnapin *SQL*

#pull the current date
$date = Get-Date -Format yyyyddMM

#set location of backup files and create it if not present
$DIRECTORY = "D:\Temp\"

if (-Not (Test-Path $DIRECTORY) ) { md $directory }

#Grab the database names into an array
$dbname = dir 'SQLSERVER:\SQL\MYHOST\MYDBMSSQL\Databases' | Select Name

#Backup each database found which matches the regex "stats".
$dbname | foreach { $_.Name.ToString() }|where { $_ -match "stats" } | foreach {$bakfile = "$DIRECTORY" + $_ + "_" + $date + ".bak";
"Backing up Database: $_"; Invoke-Sqlcmd -QueryTimeout 10000 -SuppressProviderContextWarning -Query "BACKUP DATABASE $_ TO DISK=N'$bakfile' WITH INIT";}

# Move Backup from local disk to CIFS mount
Copy-Item $DIRECTORY\*.bak \\e-nfs-01.mycompany.net\backup-bi-em\Backups
Remove-Item $DIRECTORY\*.bak
cd \\e-nfs-01.mycompany.net\backup-bi-em\Backups

#Get array of existing Backups
$backups=@( ls \\e-nfs-01.mycompany.net\backup-bi-em\backups\*.bak|sort-object -property CreationTime -descending|foreach { $_.Name } )

#Delete Anything Beyond 3 Newest Backups
if ($backups.Length -gt 3 ) {
    foreach ($_ in $backups[3..$backups.Length]) { Remove-Item $_ }
}
sed_and_done
  • 193

3 Answers3

5

You seem to have two different issues preventing you from doing what you want here - ExecutionPolicy and SysWOW64 File System Redirection.

Bypass Execution Policy

To bypass the execution policy, do so when launching PowerShell, like so:

PowerShell.exe -ExecutionPolicy Bypass -File .\scriptname.ps1

Bypass File System Redirection

Since freeSSHd seems to be a 32-bit application, Windows tries to ensure no compatibility issue on your 64-bit machine, by way of a few nifty tricks (or "garbage" behavior, depending on your preconception). One way is via the File System Redirector

To disable File System Redirection, you can call the Wow64DisableWow64FsRedirection Win32 API function, and all subsequent calls from that thread will no longer be affected by redirection:

$MethodSignature = @"
[DllImport("kernel32.dll", SetLastError=true)]
public static extern bool Wow64DisableWow64FsRedirection(ref IntPtr ptr);
"@

$Kernel32 = Add-Type -MemberDefinition $MethodSignature -Namespace "Kernel32" -Passthru

$ptr = [IntPtr]::Zero
$Result = $Kernel32::Wow64DisableWow64FsRedirection([ref]$ptr)

# Now you can call 64-bit Powershell from system32
C:\Windows\System32\WindowsPowershell\v1.0\powershell.exe -ExecutionPolicy Bypass -File .\Script.ps1

Save the above as a script (wrapper.ps1), and invoke it from ssh with:

powershell -ExecutionPolicy Bypass -NoProfile -File .\wrapper.ps1

To avoid any 64-bit modules or snapins from the profile being loaded in the 32-bit session

Mathias R. Jessen
  • 26,351
0

Use WinRM to invoke the script with the proper security context remotely SSH doesn't know how to delegate impersonation (nor should it) also use the scheduler built in to windows which can also invoke with the proper credentials.

Jim B
  • 24,276
0

Windows is horrific. Freesshd seems to be the way to go for an SSH Daemon for Windows, and to get Powershell to work at all over SSH was the worst experience I have had with computers in a long time. In the end, with a suggestion from my coworker, this is how I solved it:

Initially, I set this up as a Windows Task Scheduler job, and it was fine, but my boss wanted us to call it from our main scheduler, and use SSH to do so. After a Million powershell failures, I ended up calling the Windows task scheduler CLI via SSH so that the environment was that of a local process. ssh -l Administrator windows.server.name 'schtasks /RUN /TN "Backup MSI Stats DB"' Done.

sed_and_done
  • 193