locked
syspolicy_purge_history job getting failed (SQL Server 2012) RRS feed

  • Question

  • Job Name  syspolicy_purge_history
    Step Name  Erase Phantom System Health Records.
    Duration  00:00:05
    Sql Severity 0
    Sql Message ID 0
    Operator Emailed 
    Operator Net sent 
    Operator Paged 
    Retries Attempted 0

    Error Message :-

    A job step received an error at line 1 in a PowerShell script. The corresponding line is 'import-module SQLPS  -DisableNameChecking'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The following error occurred while loading the extended type data file:
    Microsoft.PowerShell, D:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS\sqlprovider.types.ps1xml : File skipped because of the following validation exception: AuthorizationManager check failed..

    Tuesday, August 12, 2014 1:51 PM

Answers

  • Hi nap_bhatt,

    Based on your description, we need to verify that if you get the error while starting the syspolicy_purge_history job manually in SQL Server 2012. If so, which account do you use to log on to the SQL Server instance and run the job?

    According to the error message “AuthorizationManager check failed” , it indicates that your account has no permission to execute the PowerShell script in SQL Server Agent job. To troubleshoot this issue, please make sure that your account has permissions to the SQLPS folder and it has rights to execute the PowerShell script.

    In addition, please use the “Get-ExecutionPolicy” PowerShell command to check the current status of execution policy, if the execution policy is set to “Restricted”, try to set it to “RemoteSigned” or “Unrestricted”. Please note that you need to set this execution policy in both the Windows PowerShell  and the SQL PowerShell consoles.

    For more information about syspolicy_purge_history job in SQL Server 2012,  please review this similar blog: SQL job “syspolicy_purge_history” fails on SQL 2012 .

     
    Thanks,
    Lydia Zhang



    Wednesday, August 13, 2014 7:59 AM

All replies

  • Hi nap_bhatt,

    Based on your description, we need to verify that if you get the error while starting the syspolicy_purge_history job manually in SQL Server 2012. If so, which account do you use to log on to the SQL Server instance and run the job?

    According to the error message “AuthorizationManager check failed” , it indicates that your account has no permission to execute the PowerShell script in SQL Server Agent job. To troubleshoot this issue, please make sure that your account has permissions to the SQLPS folder and it has rights to execute the PowerShell script.

    In addition, please use the “Get-ExecutionPolicy” PowerShell command to check the current status of execution policy, if the execution policy is set to “Restricted”, try to set it to “RemoteSigned” or “Unrestricted”. Please note that you need to set this execution policy in both the Windows PowerShell  and the SQL PowerShell consoles.

    For more information about syspolicy_purge_history job in SQL Server 2012,  please review this similar blog: SQL job “syspolicy_purge_history” fails on SQL 2012 .

     
    Thanks,
    Lydia Zhang



    Wednesday, August 13, 2014 7:59 AM
  • Hi Lydia

                 Thank You very much for your reply. i would check you have mentioned and let see what outcomeis coming. I will update you

        

    Wednesday, August 13, 2014 1:41 PM
  • Hi nap_bhatt,

    I’m writing to follow up with you on this post. Was the problem resolved after performing our action plan steps? If you are satisfied with our solution, I’d like to mark this issue as "Answered". Please also feel free to unmark the issue, with any new findings or concerns you may have.

    Thanks,
    Lydia Zhang

    Thursday, August 21, 2014 10:58 AM
  • Hey Lydia

                  Your steps are really help me i did it that way but still job is gettign failed at step #3. powershell is not recognised powersehll commands.

    Thank You

    Nirav Bhatt

    Thursday, August 28, 2014 6:30 PM
  • Can you try the below link

    http://www.orcsweb.com/blog/jamie-furr/sql-job-syspolicy_purge_history-fails-on-sql-2012/

    --Prashanth

    Thursday, August 28, 2014 7:06 PM
  • Hi Lydia,

    I had the same problem with a SQLServer 2014 and created a workaround. You can find it here. Would be nice if you could have an eye on it.


    Regards Jörg

    • Proposed as answer by Jörg Debus Saturday, September 26, 2015 9:38 PM
    Saturday, September 26, 2015 6:02 PM
  • Hi nap_bhatt

    Check if the following registry keys exist:

    For SQL 2012
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\Shellds\Microsoft.SqlServer.Management.PowerShell.sqlps110
    ExecutionPolicy REG_SZ RemoteSigned
    Path REG_SZ C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\SQLPS.exe

    For SQL 2014

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\Shellds\Microsoft.SqlServer.Management.PowerShell.sqlps120
    ExecutionPolicy REG_SZ RemoteSigned
    Path REG_SZ C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\SQLPS.exe

    Regards
    Corsus Piratus


    • Edited by Sumere Wednesday, January 27, 2016 1:35 PM
    • Proposed as answer by Code Chief Friday, May 19, 2017 8:03 AM
    • Unproposed as answer by Code Chief Friday, May 19, 2017 8:04 AM
    • Proposed as answer by Mike Moore City of Renton Friday, September 8, 2017 1:00 AM
    Friday, January 15, 2016 1:19 PM