Powershell execution policy within SQL Server

Question

Can anyone help?

Firstly I apologise if this is the wrong forum and for the amount of text you have to read. I'm not allowed to post images yet and a picture is worth a thousand words.....

When starting powershell from SQLSMS 2008R2 and SQL2012 (right click instance.... select Start Powershell), it reports that scripts are disabled for the process. I understand that is the default setting and I need to change this, However, it looks like the import of the SQLPS module is failing as well

import-module : File D:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help about_signing" for more details. At line:1 char:14 + import-module <<<< SQLPS -DisableNameChecking + CategoryInfo : NotSpecified: (:) [Import-Module], PSSecurityException + FullyQualifiedErrorId :RuntimeException,Microsoft.PowerShell.Commands.ImportModuleCommand Convert-UrnToPath : The term 'Convert-UrnToPath' is not recognized as the name of a cmdlet,function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path iscorrect and try again. At line: At 1 char:70 &{[System.Console]::Title = 'SQL Server Powershell';Convert-UrnToPath<<<< 'Server[@Name=''xxxx\xxxx'']'|cd} + CategoryInfo :ObjectNotFound: (Convert-UrnToPath:String) [], CommandNotFoundException + FullyQualifiedErrorId :CommandNotFoundException

I cannot change the execution policy.

set-executionpolicy : Windows PowerShell updated your execution policy successf ully, but the setting is overridden by a policy defined at a more specific scop e. Due to the override, your shell will retain its current effective execution policy of "Restricted". Type "Get-ExecutionPolicy -List" to view your executio n policy settings. For more information, please see "Get-Help Set-ExecutionPoli cy." At line:1 char:20 + set-executionpolicy <<<< unrestricted + CategoryInfo : PermissionDenied: (:) [Set-ExecutionPolicy], Sec urityException + FullyQualifiedErrorId : ExecutionPolicyOverride,Microsoft.PowerShell.Com
mands.SetExecutionPolicyCommand

However, I have setup SQL Agent powershell jobs and sql2008R2 runs successfully, However, SQL2012 fails with a message:

Executed as user: xxxxxxx set-executionpolicy untre. 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\120\Tools\PowerShell\Modules\SQLPS\sqlprovider.types.ps1xml : File skipped because of the following validation exception: AuthorizationManager check failed.. '. Process Exit Code -1. The step failed.

If I run the powershell under the SQL account that SQL uses (D:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\SQLPS.exe), then I get the same error message about not being able to load the SQLPS module (makes sense if SQL cannot load it), however, if I try to change the execution policy under the SQL account I get the error message:

set-executionpolicy : Windows PowerShell updated your execution policy successfully, but the setting is overridden by a policy defined at a more specific scope. Due to the override, your shell will retain its current effective execution policy of Restricted". Type "Get-ExecutionPolicy -List" to view your execution policy settings. For more information, please see "Get-Help Set-ExecutionPolicy." At line:1 char:20 + set-executionpolicy <<<< unrestricted + CategoryInfo : PermissionDenied: (:) [Set-ExecutionPolicy], Sec urityException + FullyQualifiedErrorId : ExecutionPolicyOverride,Microsoft.PowerShell.Com mands.SetExecutionPolicyCommand

So it looks like the setting is overridden by a policy defined at a more specific scope. Where is this policy set? And what is this policy? I asked the Server team if they set this at an AD policy level but they said they did not.

If I use powershell proper (from Accessories... windows powershell), then I have no issues. I've searched the web for these issues and everyone just say use set-execution-policy. No one seem to get the failing to load SQLPS module or unable to change the execution policy.

Any advice would be greatly received!

Answer 1

First, run the powershell command "Get-ExecutionPolicy -List" (as mentioned in the error message). This will give you a list of the different execution policy scopes and which setting is defined in which scope.

This list of scopes has an order of precedence (MachinePolicy, UserPolicy, Process, CurrentUser, LocalMachine). Each of these scopes can have a different setting;

• Restricted
• AllSigned
• RemoteSigned
• Unrestricted
• Bypass
• Undefined

Powershell will evaluate these scopes in the order listed and apply the first setting that is set to something other than 'Undefined'.

The error you're getting when running 'Set-ExecutionPolicy' indicates that you have a setting of 'Restricted' defined at a more specific scope than the default of LocalMachine. The Get-ExecutionPolicy -List cmdlet will tell you where.

If the Restricted setting is applied in either the LocalUser or Process scopes then you can change this. Use the Set-ExecutionPolicy cmdlet, but specify the scope using the -scope parameter. For example;

Set-ExecutionPolicy -Scope Process RemoteSigned

If the Restricted setting is defined at either the MachinePolicy or UserPolicy scopes then this indicates your domain administrators have configured a group policy that is overriding your local settings. This is where you would need to go and talk to them and see if you can have a less restrictive setting applied via GPO.

To the best of my knowledge you cannot easily override the GPO settings locally; however if I'm wrong on this then someone please do correct me! :)

Answer 2

I also suspect this is an issue with ExecutionPolicy, albeit a bit difficult to diagnose properly.

Is there a reason why you are not using this command?

PS C:\> Import-Module SQLPS

A few notes and troubleshooting steps:

• You can use Set-ExecutionPolicy -Scope MachinePolicy or Set-ExecutionPolicy -Scope UserPolicy, provided that your account is has permissions to make these changes.

• There is a 32bit version of PowerShell and a 64bit version. There is a different ExecutionPolicy for each of them. You should check the 32bit version Execution policy (Windows PowerShell (x86) in the Start menu). $env:PROCESSOR_ARCHITECTURE will return x86 on a 32bit and AMD64 on a 64bit console.

• Make sure that when running from SQL or the console, it is running as the user than you expect. $env:USERNAME will tell you.

• Run the PowerShell console as the sql user, and run Get-ExecutionPolicy -list