Calling a powershell script from TSQL

Question

Post reply

Calling a powershell script from TSQL

Brad Picone

Old Hand

Points: 373
More actions
February 3, 2010 at 2:51 pm

#210515

Does anyone know if this is possible?

Viewing 11 posts - 1 through 10 (of 10 total)

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

xp_CmdShell (I believe) but why would you want to do that?

--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)

Brad Picone Old Hand Points: 373 More actions · Answer 2

Our DBA's are beginning to restrict xp_commandshell permissions and we have several SSIS packages which are essentially triggered on demand through logic in our stored procs. One of the workarounds we were looking at was running the package executions through powershell scripts, although setting this up as a scheduled job, or unscheduled (triggered from client apps) are not really suitable options. I understand CLR might be another option, but Im still trying to understand what exactly are my options at this point.

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

Restricting xp_CmdShell is probably a good idea but proxies can be setup to allow calling of stored procedures that use it without exposing xp_CmdShell to most users. I don't know off the top of my head how to do that but that's what the Systems DBA's did at my last 3 jobs. Proxies are also documented in Books Online.

--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)

Brad Picone Old Hand Points: 373 More actions · Answer 4

Thanks Jeff, Setting up of a proxy account has been mentioned although I still don't think its gone down too well with the DBA's. But I don't think we have too many options at this point - thanks for your advice.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Brad Picone (2/4/2010)
Thanks Jeff, Setting up of a proxy account has been mentioned although I still don't think its gone down too well with the DBA's. But I don't think we have too many options at this point - thanks for your advice.

Good DBAs really bust a hump trying to make for systems that are as secure as possible. The belief among many DBAs is that xp_CmdShell is a real "Bozo-no-no" especially if the DBAs don't want to take the time to do code reviews. Even though I don't have a real big appreciation for their fears when it's enabled for "proxy use only", I do have to respect any DBA that's thoroughly commited to protecting the data. Be courteous with them with them if they say "No" because they're just trying to do their job. Also, ask them for a workaround if they say "No"... they should be able to provide one and it may require a change in programatic tactics on your part.

--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)

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 6

Yeah, if they are restricing xp_cmdshell, then powershell scripts won't work either. You have to start the powershell executable with the the correct parameters and the path to the powershell script to execute it automatically. And that is assuming that your environment will allow the scripts run, since powershell restrics running scripts by default to only digitally signed scripts (I believe that is the default setting). I do not know of any other way to run powershell scripts, especially in SQL.

Joie Andrew
"Since 1982"

cARBONc Mr or Mrs. 500 Points: 513 More actions · Answer 7

It might be easier to create an Agent Job that calls the powershell script.

Then from the Stored Procedure, call the job to run.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

You could go down the road of using a scheduled job to act as your "PowerShell runner", just know that sp_start_job is going to start the job and return control immediately to the caller, i.e. it does not wait for the job to complete before returning control to the caller. That behavior trips a lot of people up when trying to use Agent to work around the lack of access to xp_CmdShell as many times code later in the procedure is expecting for the work done by the job to be complete, when in actuality it's now running in parallel.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

cARBONc Mr or Mrs. 500 Points: 513 More actions · Answer 9

I actually loop, about every few seconds, requesting the agent job status. Once the status says it's complete, you are good to continue.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 10

cARBONc (4/20/2012)
I actually loop, about every few seconds, requesting the agent job status. Once the status says it's complete, you are good to continue.

I wrote a proc several years back to wrap sp_start_job to simulate synchronous calls to run jobs from T-SQL. I have since moved away from doing this kind of work in T-SQL. It is a preference and opinion, but for me, if you're looking to call jobs and PowerShell scripts from within T-SQL then you need to inspect your design and turn it inside out if possible. ...if I can find that proc code I'll post it.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato