title: Powershell from SQL with xp_cmdshell?
body: <p>I am trying to run a powershell command to return some data to a SQL table but I am having no luck getting it working.</p>
  
  <p>Specifically I want to pull a list of all the Users details in the User Profile service from SharePoint and then query LDAP through a linked server connection and get back all the user details so I can compare the details, because I think the sync process is broken. </p>
  
  <p>I am trying to avoid needing to save the powershell script to a file.</p>
  
  <p>I have a powershell script to get the details.</p>
  
  <pre><code>$site = Get-SPSite "http://site-dev.com.au";
  $context = Get-SPServiceContext $site;
  $profileManager = New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($context);
  $Profiles = $ProfileManager.GetEnumerator();
  
  $Userlist = New-Object System.Collections.ArrayList;
  $Userlist.Clear();
  
  foreach($up in $Profiles)
  {
      $prof = New-Object PSObject;
      $prof | Add-Member -type NoteProperty -Name 'SID' -Value $up.get_Item("SID");
      $prof | Add-Member -type NoteProperty -Name 'ADGuid' -Value $up.get_Item("ADGuid");
      $prof | Add-Member -type NoteProperty -Name 'AccountName' -Value $up.get_Item("AccountName");
      $prof | Add-Member -type NoteProperty -Name 'WorkEmail' -Value $up.get_Item("WorkEmail");
      [void]$Userlist.Add($prof);
  }
  
  $Userlist
  </code></pre>
  
  <p>This works and I so have then combined this with some more code to run it remotely.</p>
  
  <pre><code>$pwd = '***************************************************************' | ConvertTo-SecureString; $crd = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList "Staff\Admin-DEV", $pwd; Invoke-Command -Computername server01.staff.ad.com.au -Authentication CredSSP -Credential $crd -ScriptBlock {Add-PSSnapin Microsoft.SharePoint.PowerShell; $site = Get-SPSite "http://site-dev.com.au"; $context = Get-SPServiceContext $site; $profileManager = New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($context); $Profiles = $ProfileManager.GetEnumerator(); $Userlist = New-Object System.Collections.ArrayList; $Userlist.Clear(); foreach($up in $Profiles) { $prof = New-Object PSObject; $prof | Add-Member -type NoteProperty -Name 'SID' -Value $up.get_Item("SID"); $prof | Add-Member -type NoteProperty -Name 'ADGuid' -Value $up.get_Item("ADGuid"); $prof | Add-Member -type NoteProperty -Name 'AccountName' -Value $up.get_Item("AccountName"); $prof | Add-Member -type NoteProperty -Name 'WorkEmail' -Value $up.get_Item("WorkEmail"); [void]$Userlist.Add($prof); } $Userlist }
  </code></pre>
  
  <p>Where <code>$pass</code> is an already encripted string</p>
  
  <pre><code>$pass = Read-Host -AsSecureString |ConvertFrom-SecureString
  </code></pre>
  
  <p>But when I take this and try and run in from SQL it is failing.</p>
  
  <pre><code>DECLARE @command nvarchar(max)
  SET @command = N'EXEC xp_cmdshell N''powershell -Command {'
  SET @command = @command + N'$pwd = ''''**************************************************'''' | ConvertTo-SecureString; $crd = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList "Staff\Admin-DEV", $pwd; Invoke-Command -Computername server01.staff.ad.com.au -Authentication CredSSP -Credential $crd -ScriptBlock {Add-PSSnapin Microsoft.SharePoint.PowerShell; $site = Get-SPSite "http://site-dev.cqu.edu.au"; $context = Get-SPServiceContext $site; $profileManager = New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($context); $Profiles = $ProfileManager.GetEnumerator(); $Userlist = New-Object System.Collections.ArrayList; $Userlist.Clear(); foreach($up in $Profiles) { $prof = New-Object PSObject; $prof | Add-Member -type NoteProperty -Name ''''SID'''' -Value $up.get_Item("SID"); $prof | Add-Member -type NoteProperty -Name ''''ADGuid'''' -Value $up.get_Item("ADGuid"); $prof | Add-Member -type NoteProperty -Name ''''AccountName'''' -Value $up.get_Item("AccountName"); $prof | Add-Member -type NoteProperty -Name ''''WorkEmail'''' -Value $up.get_Item("WorkEmail"); [void]$Userlist.Add($prof); } $Userlist }'
  SET @command = @command + '}'''
  EXEC sp_executeSQL @command
  </code></pre>
  
  <p>I am getting an error</p>
  
  <blockquote>
    <p>"'ConvertTo-SecureString' is not recognized as an internal or
    external command,"</p>
  </blockquote>
  
  <p>I am not sure if this error is anything to do with why it is failing or if it is because I have not escaped some of the characters correctly when passing between SQL CMD and PowerShell. </p>