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!
Best Answer
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;
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;
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! :)