Run a PowerShell script on a different server - from inside a SQL Server job

Question

I have a procedure to backup SSAS Databases.

That is working as a charm.

Now my server is filling up with SSAS backups and I would like to delete the backup files that are older than 2 days.

to achieve this I am using the following POWERSHELL script:

#-------------------------------------------------------------------------------
# Script to delete old SSAS backup files
#
# Marcelo Miorelli
# 
# 19-novembre-2014 Wed
#-------------------------------------------------------------------------------


#-- connect to the remote server -- SQLBILON1
#
ENTER-PSSESSION sqlbilon1


#-- set the Path where the backup files (.abf) are located
#
$path = 'H:\SQLBackups'

#-- set the number of days backups should be deleted -- in this case 2
#
$NumberOfDays = 2

#-- calculate the date of the backup files - if they are older than $days they will be deleted
#
$days = (Get-Date).AddDays(-$NumberOfDays)

#--get the list of the backup files to be deleted and delete them
#
Get-ChildItem $Path -Recurse '*.abf' | ? {$_.CreationTime -lt $days} | Remove-Item

The problem with this script is that I use MYSQLSERVER1 server to backup the databases that are on SQLBILON1 server. The backup files are on the folder H:\SQLBackups of SQLBILON1.

The job is failing with the following error message:

 The job script encountered the following errors. These errors did not stop the script:
A job step received an error at line 13 in a PowerShell script. 
The corresponding line is 'ENTER-PSSESSION sqlbilon1'. 
Correct the script and reschedule the job. The error information returned by PowerShell is: 
'Connecting to remote server failed with the following error message : 
Access is denied. For more information, see the about_Remote_Troubleshooting Help topic.'

Question, how can I run a PowerShell script on a remote server? This script runs from a sql server job.

Can I create a proxy and use it to connect to the remote server?

Answer 1

If you are within a domain environment you can setup a proxy account that has permissions on SQLBILON1. However, using remote PowerShell is a bit of overkill and adds an unneeded layer to troubleshoot. If you do not have remote PowerShell enabled on both servers and firewall access between the servers configured properly it will have issues.

I would simply use UNC paths to the server. Whether you use the admin share like \\SQLBILON1\H$\SQLBackups or create share directly to \\SQLBILON1\SQLBackups. Unless you want to give the SQL Agent service on MYSQLSERVER1 permissions to that backup directory you would need to create a proxy account that has the appropriate permissions.

Your script will be much more simply if you just go this route:

#-------------------------------------------------------------------------------
# Script to delete old SSAS backup files
#
# Marcelo Miorelli
# 
# 19-novembre-2014 Wed
#-------------------------------------------------------------------------------


#-- set the Path where the backup files (.abf) are located
#
$path = '\\SQLBILON1\H$\SQLBackups' # OR \\SQLBILON1\SQLBackups

#-- set the number of days backups should be deleted -- in this case 2
#
$NumberOfDays = 2

#-- calculate the date of the backup files - if they are older than $days they will be deleted
#
$days = (Get-Date).AddDays(-$NumberOfDays)

#--get the list of the backup files to be deleted and delete them
#
Get-ChildItem $Path -Recurse '*.abf' | ? {$_.CreationTime -lt $days} | Remove-Item

Answer 2

Based on the answer by @Shawn Melton above

I have created a proxy in SQL Server using this code:

-- script for creating a proxy in order to run a set of POWERSHELL commands
-- this is to delete backups older than 2 days
-- marcelo miorelli
-- 19-nov-2014

--==============================================================================================
-- server is MySQLServer1
-- and this script deletes old backup files that are located on SQLBILON1 

  PRINT @@SERVERNAME
--==============================================================================================




--Script #1 - Creating a credential to be used by proxy
USE MASTER
GO 
--Drop the credential if it is already existing 
IF EXISTS (SELECT 1 FROM sys.credentials WHERE name = N'PowerShell_Credentials') 
BEGIN 
DROP CREDENTIAL [PowerShell_Credentials] 
END 
GO 
CREATE CREDENTIAL [PowerShell_Credentials] 
WITH IDENTITY = N'MYCOMPANY\SQLAgent_DW', 
SECRET = N'zd(8A1(7m5=xSC%mTsDw<4V)6@vQfp+f' 
GO


--Script #2 - Creating a proxy account 
USE msdb
GO 
--Drop the proxy if it is already existing 
IF EXISTS (SELECT 1 FROM msdb.dbo.sysproxies WHERE name = N'PowerShell_Proxy') 
BEGIN 
EXEC dbo.sp_delete_proxy 
@proxy_name = N'PowerShell_Proxy' 
END 
GO 
--Create a proxy and use the same credential as created above 
EXEC msdb.dbo.sp_add_proxy 
@proxy_name = N'PowerShell_Proxy', 
@credential_name=N'PowerShell_Credentials', 
@enabled=1 
GO 
--To enable or disable you can use this command 
EXEC msdb.dbo.sp_update_proxy 
@proxy_name = N'PowerShell_Proxy', 
@enabled = 1 --@enabled = 0 
GO

--Script #3 - Granting proxy account to SQL Server Agent Sub-systems 
USE msdb
GO 
--You can view all the sub systems of SQL Server Agent with this command
--You can notice for SSIS Subsystem id is 11 
EXEC sp_enum_sqlagent_subsystems 
GO

--Grant created proxy to SQL Agent subsystem 
--You can grant created proxy to as many as available subsystems 
EXEC msdb.dbo.sp_grant_proxy_to_subsystem 
@proxy_name=N'PowerShell_Proxy', 
@subsystem_id=11 --subsystem 11 is for SSIS as you can see in the above image 
GO 
EXEC msdb.dbo.sp_grant_proxy_to_subsystem 
@proxy_name=N'PowerShell_Proxy', 
@subsystem_id=12 --subsystem 12 is for PowerShellStart
GO 
EXEC msdb.dbo.sp_grant_proxy_to_subsystem 
@proxy_name=N'PowerShell_Proxy', 
@subsystem_id=10 --subsystem 10 is for ANALYSISCOMMAND
go

--View all the proxies granted to all the subsystems 
EXEC dbo.sp_enum_proxy_for_subsystem


--Script #4 - Granting proxy access to security principals 
USE msdb
GO 
--Grant proxy account access to security principals that could be
--either login name or fixed server role or msdb role
--Please note, Members of sysadmin server role are allowed to use any proxy 
EXEC msdb.dbo.sp_grant_login_to_proxy 
@proxy_name=N'PowerShell_Proxy'
,@login_name=N'MYCOMPANY\SQLAgent_DW' 
--,@fixed_server_role=N'' 
--,@msdb_role=N'' 
GO 
--View logins provided access to proxies 
EXEC dbo.sp_enum_login_for_proxy 
GO

and after that I changed my Powershell script to the following: (which is a little bit different than @ShawnMelton's but it is working fine), any comments appreciated.

set-location -Path Microsoft.PowerShell.Core\FileSystem::"\\SQLBILON1\H$\sqlBackups\"

#-- set the number of days backups should be deleted -- in this case 2
#
$NumberOfDays = 2

#-- calculate the date of the backup files - if they are older than $days they will be deleted
#
$days = (Get-Date).AddDays(-$NumberOfDays)

#--get the list of the backup files to be deleted and delete them
#
Get-ChildItem -Recurse '*.abf' | ? {$_.CreationTime -lt $days} | Remove-Item