Powershell script from Sql Job Agent

Posted on 2012-08-10
Last Modified: 2012-08-18
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!
Question by:lglaw
    LVL 12

    Expert Comment

    Try CmdExec powershell.exe -File "\\Servername\PsScripts\AdUserGet.ps1" -ExecutionPolicy unrestricted

    Accepted Solution

    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.

    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.

    Author Closing Comment

    It's an easily managed solution.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Microsoft Windows Server Update Service (WSUS) is free for everyone, but it lacks of some desirable features like send an e-mail to the administrator with the status of all computers on the WSUS server. This article is based on my PowerShell script …
    New Windows 7 Installations take days for Windows-Updates to show up and install. This can easily be fixed. I have finally decided to write an article because this seems to get asked several times a day lately. This Article and the Links apply to…
    This tutorial will show how to push an installation of Backup Exec to an additional server in both 2012 and 2014 versions of the software. Click on the Backup Exec button in the upper left corner. From here, select Installation and Licensing, then I…
    This tutorial will walk an individual through the steps necessary to configure their installation of BackupExec 2012 to use network shared disk space. Verify that the path to the shared storage is valid and that data can be written to that location:…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now