locked
Powershell from a SQL Agent Job (SQL Server 2012) RRS feed

  • คำถาม

  • So I have the exact same setup on 119 other instances, but on the 2012 instance (the only 2012 I have) the job fails saying the path is invalid. However, I can take the path and open it from start run just fine, and I can manually run the powershell script on the server and it runs fine. I only get the error from SQL Agent. Its just a simple PowerShell call to a backup script. The SQL Agent and SQL Server Service accounts have full permissions to the directory. I logged in with the account itself and tested a start-->run with it. No problem. Only when executed from the SQL Agent Job does it fail with invalid path.


    John M. Couch





    • แก้ไขโดย John Couch 8 พฤษภาคม 2556 0:51
    8 พฤษภาคม 2556 0:36

คำตอบ

ตอบทั้งหมด

  • I only get the error from SQL Agent. Hello John,

    And what for an error message do you get; no one could guess this?

    By the screenshot, you execute the Job with the "SQL Server-Agent" Service account; so may a permission Problem? You could create a SQL Server-Agent Proxy account with your credential to execute the Job; see Creating SQL Server Agent Proxies


    Olaf Helper

    Blog Xing

    8 พฤษภาคม 2556 3:17
  • I tried setting up a proxy but have the same error.

    StartIndex cannot be less than zero.

    Parameter name: startIndex

    Invalid Path: '\\Server\share\SQLServer\instance\master\'.

    I paste the above path in a run box and it opens fine.

     

     


    John M. Couch

    8 พฤษภาคม 2556 3:26
  • Hi John,

    Please check SQL Server and SQL Server Agent logon account via SQL Server Configuration Manager and make sure it has permission to access the folder '\\Server\share\SQLServer\instance\master\'. Additionally, you can post your PowerShell codes here for analysis.

    Configure SQL Server Agent
    http://msdn.microsoft.com/en-us/library/ms178142.aspx

    Setting Up Windows Service Accounts
    http://msdn.microsoft.com/en-us/library/ms143504(v=sql.100).aspx

    If you have any feedback on our support, please click here.


    Allen Li
    TechNet Community Support

    9 พฤษภาคม 2556 7:55
  • I can logon to the server using the service account and run the powershell code via powershell console and everything works fine. As soon as I try to run it through SQL Server Agent via job is when it fails. I really think there is a bug in 2012. I have the same job setup on numerous 2005, 2008 and 2008 R2 SQL environments and it works fine.

    John M. Couch

    • เสนอเป็นคำตอบโดย Kalman Toth 27 พฤษภาคม 2556 0:05
    • ยกเลิกการนำเสนอเป็นคำตอบโดย John Couch 28 พฤษภาคม 2556 0:02
    9 พฤษภาคม 2556 12:01
  • These permissions in the local security policy section are all applied by default from group policy. And I know the jobs are configured the same as every other server (119 instances) because they are configured via scripted configuration. Just the 3 2012 SQL instances we have, have this problem.

    Log on as a service (SeServiceLogonRight)

    • Replace a process-level token (SeAssignPrimaryTokenPrivilege)

    • Bypass traverse checking (SeChangeNotifyPrivilege)

    • Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)


    John M. Couch

    9 พฤษภาคม 2556 12:06
  • Didn't see an answer, but I experienced the same issue with trying to access a file share:

    Example:

    Server 2012 running SQL 2012 SP1 Ent

    SQL Job type: PowerShell

    Run as: SQL Server Agent Service Account

    Command:

    $TestDir = ls \\Server1\Test

    Results:

    The job script encountered the following errors. These errors did not stop the script:  A job step received an error at line XX in a PowerShell script. The corresponding line is '$TestDir = ls ls \\Server1\Test'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot find path '\\Server1\Test' because it does not exist.  '.  Process Exit Code 0.  The step succeeded.

    To resolve the issue I explicitly defined the PSPProvider by using the following command:

    $TestDir = Microsoft.PowerShell.Core\FileSystem::ls \\Server1\Test

    Just one of the issues you can run into when using UNC paths when working with PowerShell providers.  For a full list of the PSPProviders available on your system use the Get-PSPProvider cmdlet.  FYI - Still working on how to get it to default so it just works out of the box again.  Hope this helps some!


    Lewis_Carroll "His answer trickled through my head like water through a sieve."

    3 กันยายน 2556 16:24
  • I found the problem, and it has been reported as a bug, with a workaround. Here is the connect item:

    https://connect.microsoft.com/SQLServer/feedback/details/768743/sql-server-2012-import-module-sqlps-breaks-the-test-path-powershell-cmdlet


    John M. Couch

    • ทำเครื่องหมายเป็นคำตอบโดย John Couch 21 ตุลาคม 2556 21:59
    21 ตุลาคม 2556 21:59
  • Same issue for me. Thanks for the link; it has solved it.


    9 มกราคม 2558 12:58
  • the location of the path is changed ... Here is the new path

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/719ede23-9f1a-43f5-8c4e-8188fea0b19f/sql-server-2012-importmodule-sqlps-breaks-the-quottestpathquot-powershell-cmdlet?forum=sqlsmoanddmo&prof=required



    • แก้ไขโดย Savineers 5 ตุลาคม 2561 0:45
    5 ตุลาคม 2561 0:44
  • Thank you so much for providing the updated link. It helped me solve my issue.

    16 ตุลาคม 2562 15:20