Powershell script from Sql Job Agent

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.

E:\PsScripts\AdUserGet.ps1 is:
#### Import Active Directory Module
Import-module ActiveDirectory
#### 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)
Powershell \\Servername\PsScripts\AdUserGet.ps1


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!
lglawAsked:
Who is Participating?
 
lglawAuthor Commented:
The root problem has been my lack of understanding about certificates.  I thought by using my personal credentials when signing the script file (.ps1) would be appropriate certificate for the 'Allsigned' execution policy to take effect.  Referenced these two blogs.
http://dbamohsin.wordpress.com/tag/set-authenticodesignature/
http://www.hanselman.com/blog/SigningPowerShellScripts.aspx

Signing the script with the service account credentials allows the script to execute from the SQL agent.  Adding the execution policy change to the end of command didn't work becuase the service account does not have administrator permissions on the server and so can't make changes to the execution policy.

What seems to be the most secure, there is no outside the network exposure for this server, and easiest to maintain is to use Set-ExecutionPolicy -scope LocalMachine -executionPolicy Unrestricted

The Unrestricted policy still prompts for permissions scripts downloaded from the internet and local users on the server are limited to adminsitrators and now this Job Agent service account.
0
 
Carlo-GiulianiCommented:
Try CmdExec powershell.exe -File "\\Servername\PsScripts\AdUserGet.ps1" -ExecutionPolicy unrestricted
0
 
lglawAuthor Commented:
It's an easily managed solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.