lglaw
asked on
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
#### 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
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\Ad
CmdExec cmd.exe /c "RunAdUserGet.bat" (the file contains the above call)
Tsql EXECUTE master..xp_cmdshell '\\ServerName\PsScripts\Ru
TSql EXECUTE procRunAdUserGet (the above in a stored procedure)
Powershell \\Servername\PsScripts\AdU
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!
E:\PsScripts\AdUserGet.ps1
#### 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
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\Ad
CmdExec cmd.exe /c "RunAdUserGet.bat" (the file contains the above call)
Tsql EXECUTE master..xp_cmdshell '\\ServerName\PsScripts\Ru
TSql EXECUTE procRunAdUserGet (the above in a stored procedure)
Powershell \\Servername\PsScripts\AdU
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!
Carlo-Giuliani
Try CmdExec powershell.exe -File "\\Servername\PsScripts\Ad
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It's an easily managed solution.