locked
SQL Powershell (SQL Server 2014) does not start because of insufficient execution policy "Restricted" on "Process" scope (E.g. "syspolicy_purge_history" fails) RRS feed

  • Question

  • After migration to Windows 10 and PS 4.0 SQL PowerShell cannot be launched by SQL Server. SQL PS tries to load module SQLPS but the execution policy is set to "Restricted" at process level. Because manual setting of execution policy on this level is not made persistent I don't see a workaround.

    I tried to set variable PSExecutionPolicyPreference = RemoteSigned in the system environment. Standalone PowerShell friendly reflects that in its reports as a setting for "Process" scope. But in SQL PowerShell this is not used to override the "Restricted" value.

    Any idea how to fix that?

    Update September 1st

    Because this seems to be bug and not a specific problem of my installation, I have posted this case as a bug in Connect. Feel free to support this by upvoting the issue. Here is the link.

    Update September 26th

    Pls. consult the workaround I have "replied" below. The SQL-PowerShell is usable with all SQL additions for me after applying it. And "syspolicy_purge_history" runs happily to its end.

    Update March 2020

    Still not fixed and the workarounds below are still going strong.


    Regards Jörg









    • Edited by Jörg Debus Wednesday, March 4, 2020 9:32 AM
    Sunday, August 30, 2015 10:30 PM

Answers

  • Thank you to everyone in this thread. Although it's a couple years old, I was able to solve my issue with the syspolicy_purge_history task not running the 3rd step by following the registry edit advice for giving Powershell execute permission for SQLServer.

    For SQL Server 2017 use the following key instead:

    Microsoft.SqlServer.Management.PowerShell.sqlps140

    • Marked as answer by Jörg Debus Wednesday, March 4, 2020 9:26 AM
    Tuesday, March 3, 2020 10:08 PM
  • Workaround

    In between I can publish a workaround for this problem for SQLServer 2014 on a 64-bit Windows 10.The problem is that the registry key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps120" either does not exist or does contain the value "ExecutionPolicy"="Restricted". In both cases no PS-scripts can be executed and SSMS-Jobs scripted with PS like "syspolicy_purge_history" will fail.

    Changing this Registry entry as shown below solves the problem by resetting the ExecutionPolicy to the same level as the PowerShell command shell:

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps120]
    "ExecutionPolicy"="RemoteSigned"

    Be careful when changing the registry. Check the node before applying the change.


    Regards Jörg




    • Marked as answer by Jörg Debus Saturday, September 26, 2015 5:55 PM
    • Edited by Jörg Debus Wednesday, March 4, 2020 9:29 AM
    Saturday, September 26, 2015 5:39 PM
  • My key didn't exist for SQL2012.  I keyed it in just like you have it above (using 110 instead of 120) and the job, finally, works.  Thank you for posting the workaround.  Update 1/18/2019:  It also works for SQL2014 with a missing key.

    • Edited by BrownleeSG Friday, January 18, 2019 5:18 PM
    • Marked as answer by Jörg Debus Wednesday, March 4, 2020 9:25 AM
    Wednesday, September 26, 2018 9:33 PM

All replies

  • Hi VIMD,

    I posted a specific question related to starting of Powershell from SQL Server Management Studio. My problem is, that a PS-wrapper named SQLPS.EXE calls PowerShell with ExecutionPolicy set to "Restricted" on the Process level.

    All things you posted are well known here and it is preliminary knowledge to Execute SET-ExecutionPoliy to RemoteSigned  when beginning loading modules and executing PS1-scripts. BTW ExecutionPolicy does not reflect the trustworthiness of Powershell. It addresses the scripts from other people. It's like macros in Office well done to protect from virus in these external components.

    So any idea from where SQLPS.EXE gets the setting for this ExecutionPolicy setting and uses it so badly, that the SQL Modules do not load? I have set the defaults properly using environment variable "PSExecutionPolicyPreference = RemoteSigned".  


    Regards Jörg


    • Edited by Jörg Debus Saturday, September 26, 2015 3:28 PM
    Monday, August 31, 2015 9:42 PM
  • Workaround

    In between I can publish a workaround for this problem for SQLServer 2014 on a 64-bit Windows 10.The problem is that the registry key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps120" either does not exist or does contain the value "ExecutionPolicy"="Restricted". In both cases no PS-scripts can be executed and SSMS-Jobs scripted with PS like "syspolicy_purge_history" will fail.

    Changing this Registry entry as shown below solves the problem by resetting the ExecutionPolicy to the same level as the PowerShell command shell:

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps120]
    "ExecutionPolicy"="RemoteSigned"

    Be careful when changing the registry. Check the node before applying the change.


    Regards Jörg




    • Marked as answer by Jörg Debus Saturday, September 26, 2015 5:55 PM
    • Edited by Jörg Debus Wednesday, March 4, 2020 9:29 AM
    Saturday, September 26, 2015 5:39 PM
  • My key didn't exist for SQL2012.  I keyed it in just like you have it above (using 110 instead of 120) and the job, finally, works.  Thank you for posting the workaround.  Update 1/18/2019:  It also works for SQL2014 with a missing key.

    • Edited by BrownleeSG Friday, January 18, 2019 5:18 PM
    • Marked as answer by Jörg Debus Wednesday, March 4, 2020 9:25 AM
    Wednesday, September 26, 2018 9:33 PM
  • Thank you to everyone in this thread. Although it's a couple years old, I was able to solve my issue with the syspolicy_purge_history task not running the 3rd step by following the registry edit advice for giving Powershell execute permission for SQLServer.

    For SQL Server 2017 use the following key instead:

    Microsoft.SqlServer.Management.PowerShell.sqlps140

    • Marked as answer by Jörg Debus Wednesday, March 4, 2020 9:26 AM
    Tuesday, March 3, 2020 10:08 PM
  • Hi Nemo,

    what about upvoting the workaround and the updates?


    Regards Jörg


    • Edited by Jörg Debus Wednesday, March 4, 2020 9:25 AM
    Wednesday, March 4, 2020 9:25 AM
  • In addition to this, I was able to find a server that has the "Microsoft.SqlServer.Management.PowerShell.sqlps140"

    Exported it out, and imported to the server that has the missing entry.  Also made sure that the file indicated in the path exists.  

    This is the exported missingreg.reg 

    Windows Registry Editor Version 5.00
    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps140]
    "Path"="D:\\Program Files (x86)\\Microsoft SQL Server\\140\\Tools\\Binn\\SQLPS.exe"
    "ExecutionPolicy"="RemoteSigned"

    Wednesday, April 8, 2020 5:46 PM