Powershell script from Sql Job Agent
Posted on 2012-08-10
Struggling to get the script to run from the Sql Agent. It is saved to a shared directory on the Server that runs Sql Server 2008 r2 on Windows server 2008 R2. Ii is signed and the server policy is set to AllSigned.
#### Import Active Directory Module
#### Get Ad Users
Get-ADUser -Filter * -Properties SamAccountName, AccountExpirationDate, accountExpires, CanonicalName, City, Company, Country, Deleted,`
Department, Description, DisplayName, DistinguishedName, Division, EmployeeID, EmployeeNumber, Enabled, GivenName, HomePhone, Initials,`
isCriticalSystemObject, isDeleted, Manager, MobilePhone, Name, Office, OfficePhone, Organization, OtherName, PrimaryGroup, primaryGroupID,`
sAMAccountType, SID, State, StreetAddress, Surname, Title, UserPrincipalName | Export-CSV \\NetworksServerName\spool\adexport\ADUserDetail.csv
On the local server it will RUNAS the sql server agent service account from the command prompt.
From powershell on the local server it will execute.
On my machine it executes.
From the agent I have tried these Job Steps:
CmdExec powershell.exe "\\Servername\PsScripts\AdUserGet.ps1"
CmdExec cmd.exe /c "RunAdUserGet.bat" (the file contains the above call)
Tsql EXECUTE master..xp_cmdshell '\\ServerName\PsScripts\RunAdUserGet.bat'
TSql EXECUTE procRunAdUserGet (the above in a stored procedure)
Other iterations have included using the server physical path of E:\ for the batch file or script file. the physical path for cmd.exe or powershell.exe
When using the xp_cmdshell iteration it hangs and the spid shows a wait type of preemptive_os_pipeops. The other iterations the job hangs and I don't see any error messages on the SQL Server instance or in the Windows Logs. No failed logins.
Help is appreciated!