Frank Dolan wrote:
I haven't done this myself. I've mostly been running PoSh interactively for administrative scripting items. All my Agent jobs are T-SQL based or they use some batch scripting.
I was looking at this link: https://docs.microsoft.com/en-us/sql/powershell/run-windows-powershell-steps-in-sql-server-agent?view=sql-server-ver15
although you could do it through a PS script directly on the step I prefer, and advise, using a command prompt instead and start an instance of powershell - more versatility and less prone to "errors" due to the PS instance not allowing all that the command line one does - And I do not remember now which issue i got when I used it before
as a basis for writing. I suspect that I will need to get the Agent service account to use a profile, or ensure that my PoSh script has some Import-Module items at the beginning to ensure the environment is set.
To wit, I use xp_CmdShell for a whole lot. Common to what appears to be general public opinion, it's not a security risk unless one is dumb enough to grant an unwise low-prived use the privs to call it directly. The real risk of xp_CmdShell is letting an attacker get in with sysadmin or controlserver privs and then they don't need xp_CmdShell to cause a lot of damage or make off with a payload and do it in an invisible fashion. In fact, under such circumstances, they could even turn on xp_CmdShell, use it, turn it off, and reset the logs to cover their tracs. Having it turned off isn't even a speedbump for an attacker that makes it in with such privs.
And it's truly an amazing tool that you can use to actually decrease the privs required even for some "super users".
In other words, instead of calling stored procedures from Powershell, I use stored procedures to call Powershell... and WMIC... and (what people still refer to as) DOS... and anything else that I might need. As strange as it may seem, I even use store procedures to call xp_CmdShell to call SQLCmd to call other procs to do some amazing things with the output returned from the OUTPUT of xp_CmdShell especially where BULK INSERT comes into play.