[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2092
  • Last Modified:

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!
0
lglaw
Asked:
lglaw
  • 2
1 Solution
 
Carlo-GiulianiCommented:
Try CmdExec powershell.exe -File "\\Servername\PsScripts\AdUserGet.ps1" -ExecutionPolicy unrestricted
0
 
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
 
lglawAuthor Commented:
It's an easily managed solution.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now