Execute powershell function within tsql

Question

_{Wednesday, August 15, 2012 6:12 PM}

Is it possible to use your own powershell function and run it from MSSQL(TSQL).

I have powershell function within 'createuser.ps1', let's say:

function New-ADUser{ ... }
New-ADUser -Name 'User1' Container 'OU=Staff,DC=contoso,DC=com'

When I run this file from TSQL :

declare @sql varchar(200)

set @sql = 'powershell.exe -file "C:\createuser.ps1" -nologo'

EXEC xp_cmdshell @sql

it creates user1 for me in Staff OU.

My question is can I use this function within my tsql code, like for example:

set @sql = 'powershell.exe -command function new-aduser( . . . )  -New-AdUser -name 'user1' and etc...    

Or use the function as ps1 file, and then put extra command aside, like for example:

set @sql = 'powershell.exe -file c:\creteuser.ps1 -command -New-AdUser -name 'user1' and etc...

The reason is that I can't really make it work dynamically. What I want is to run this command when ever I need to create user in AD.

or if you have any other suggestions. 

I do this with vbscript but the scripts for creating AD users is not functioning correct. So if you have any links for updated microsoft vbs scripts that would be helpfulll thank you

All replies (4)

_{Wednesday, August 15, 2012 7:11 PM ✅Answered}

When you run xp_cmdshell, picture it as opening an operating system window and typing the string into the command line and hitting ENTER.

So, you need to format the string in TSQL to produce the string needed at the operating system command prompt.  To experiment, build your string and select it in TSQL.  Then copy and paste the string and run it at the command prompt.  If it runs, the format was valid. :)

DECLARE @username NVARCHAR(50)
DECLARE @sql NVARCHAR(2000)
set @username = 'user1'
set @sql = 'powershell.exe -command function new-aduser( . . . )  -New-AdUser -name '''+@username+''' and etc...' 
SELECT @sql

This will return the following command:

powershell.exe -command function new-aduser( . . . )  -New-AdUser -name 'user1' and etc...

If that runs, then congratulations.  (Of course this is currently invalid, but you get the picture.)

RLF

Required Warning:  Using xp_cmdshell for any function that is not rigidly controlled and limited to administrators offers many opportunities for malicious code to be run.

---

_{Wednesday, August 15, 2012 6:46 PM}

You say that you cannot make it work dynamically.  What is the problem?   Perhaps something like:

set @username = 'user1'set @sql = 'powershell.exe -command function new-aduser( . . . )  -New-AdUser -name '''+@username+''' and etc...' 

Does that work better?  If not, please provide the messages that you get.

RLF

---

_{Wednesday, August 15, 2012 6:58 PM}

Thank's for reply,

set @username = 'user1'
set @sql = 'powershell.exe -command function new-aduser( . . . )  -New-AdUser -name '''+@username+''' and etc...' 

  this one doesn't give me any errors. The problem is I don't know what will be the code structure for tsql to handle the functions. Up until now I only see the executing of just the file ps1 in google, like:

set @sql = 'powershell.exe -file "C:\createuser.ps1" -nologo'

but I can't find even simple example that run powershell scripts within the xp_cmdshell. Is it possible?

thank You

---

_{Thursday, August 16, 2012 8:32 PM | 1 vote}

Thanks, that was it.