Scheduled PowerShell job failing

Question

Scheduled PowerShell job failing

PHXHoward

SSCrazy Eights

Points: 8384
More actions
December 27, 2010 at 8:06 am

#388508

Hi everyone. I am struggling to get a PowerShell scheduled job to run in SQL Server 2008.
When I run the .ps1 file myself, it works fine but when I create a scheduled job through SSMS, it fails.
--
Message
Executed as user: <domain>\<AD user>. The job script encountered the following errors. These errors did not stop the script: A job step received an error at line 3 in a PowerShell script. The corresponding line is '$instanceNameList = dir $serverGroupPath -recurse | where-object { $_.Mode.Equals("-") } | select-object Name -Unique'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot find path 'SQLSERVER:\SQLRegistration\Central Management Server Group\CMSSERVER\' because it does not exist. '. Process Exit Code 0. The step succeeded.
--
It does exist and works correctly if I run it as myself but through a scheduled job as the SQL Agent user, it fails. SQL Agent user is a sysadmin so it should have access to whatever it needs.
Has anyone ever run acress a problem like this?
Thanks for reading.
Howard

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

I've seen errors like this and they've always been a security issue. I'd double check that the job is running under the account you think it is and that that account has access to the directory.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

PHXHoward SSCrazy Eights Points: 8384 More actions · Answer 2

Thanks Grant. I'll double check.

I don't know how to check that the account has access to the directory as it is PowerShell turning the database into a directory structure. If the account is a sysadmin then shouldn't it have the access it needs?

PHXHoward SSCrazy Eights Points: 8384 More actions · Answer 3

The problem was that the registration of a Central Management Server is user specific. I had logged into SSMS and registered the CMS under my account and therefor it was showing up in the directory tree for me. The service account that I was using had never logged into SSMS and never registered the CMS so the path did not exist in the PowerShell script.

Also I had to give that service account the "ServerGroupReaderRole" in MSDB for it to be able to see the list of servers. Assuming that role is not necessary if the service account is sysadmin. If the service account is going to run queries across multiple servers, I want it to have the least access possible.

Howard

sonaliak Ten Centuries Points: 1346 More actions · Answer 4

I have similar setup I am trying to get it to work.

I have CMS registered with windows authenticated id that is running sql agent, it has sysadmin permissions.

I have powershell scripts that does database health checks i want to schedule them as sql agent jobs-

The powershell script looks like below

foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Central Management Server Group'\rps-dbcentral\ | where {$_.Mode -ne "d"} |get-unique)

{$dt=Invoke-sqlcmd -ServerInstance "$($RegisteredSQLs.ServerName)" -Database "msdb" -Query "

if I run it with sqlps from any machine where CMS is regsitered it works but just not as schedule sql agent job.

Message

Unable to start execution of step 1 (reason: line(1): Syntax error). The step failed.

Any help will be appreciated - Thanks Sonali