How to run a different account using SQL Job to execute powershell script

Question

How to run a different account using SQL Job to execute powershell script

WhiteLotus

SSChampion

Points: 11963
More actions
January 28, 2022 at 5:43 am

#3978171

Hi All ,
I need to run powershell script which is located in other server, lets say server A . In server B , i created the SQL job ..
What is the query that I need to put in Command area so I can run it as ( ABC\xxx ) and execute the script using that account ?
type : powershell
script location : \\serverA\D$\folderA\script.ps1
default : running as sql server agent account
i tried : runas /user:ABC\xxx "\\serverA\D$\folderA\script.ps1”
the outcome of sql job is asking the password which is not what i expect …
i expect to see Hello world as it is written in my simple script
please kindly help
thank you

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 89748 More actions · Answer 1

You need to create a credential in SQL Server for the domain account ABC\xxx. Once that is created - create a proxy account for the appropriate subsystem (Powershell or Operating System).

For the agent job step, you can then use that Proxy account to run the script.

Note: for a PS script - if it is stored on a network share it must be signed or you need to set the execution policy on the command line.

powershell.exe -command "try { & \\serverA\D$\folderA\script.ps1} catch {throw $_}"

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

WhiteLotus SSChampion Points: 11963 More actions · Answer 2

Hi Jeff

thanks so much for the response

How do I tell SQL to use that proxy account to run the script ?

there is no option “run as “if i choose type : powershell in SQL job

unless the type is Transact SQL

Jeff Moden SSC Guru Points: 1000369 More actions · Answer 3

WhiteLotus wrote:

I need to run powershell script which is located in other server, lets say server A .

I'm curious... what does the PowerShell script actually do?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions