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!
Get-ExecutionPolicy -List
helped me to see the different scopes. The cmdGet-ExecutionPolicy
does not show all the scopes.Import-Module SQLPS
is now working with policies changed as follows:{Undefined- Process,MachinePolicy,UserPolicy,}; {RemoteSigned- CurrentUser, LocalMachine}
.