0

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.

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.

I am trying to avoid needing to save the powershell script to a file.

I have a powershell script to get the details.

$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

This works and I so have then combined this with some more code to run it remotely.

$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 }

Where $pass is an already encripted string

$pass = Read-Host -AsSecureString |ConvertFrom-SecureString

But when I take this and try and run in from SQL it is failing.

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

I am getting an error

"'ConvertTo-SecureString' is not recognized as an internal or external command,"

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.

gofr1
  • 15,741
  • 11
  • 42
  • 52
user802599
  • 787
  • 2
  • 12
  • 35
  • Yoy run your script manually and try to use sqlcmd on the same machine? – gofr1 Mar 02 '16 at 15:05
  • have run the original powershell(without remoting on the SharePoint server) Then ran the PowerShell script with the remoting added directly from cmd on the SQL server then tried running the PowerShell script with the remoting on the SQL server in SSMS, using xp_cmdshell. – user802599 Mar 03 '16 at 00:12
  • Well, I have had same problem with Active Directory module, on my local machine all works fine, but when I run script from xp_cmdshell on SQL server machine I get same message. All I needed to do was import-module ActiveDirectory, but then I run into another issue - the PowerShell versions on my machine and SQL server machine was not the same. When I synchronize the versions and modules my script run pretty nice. – gofr1 Mar 03 '16 at 03:11

0 Answers0