http://www.sqlservercentral.com/blogs/discussionofsqlserver/2013/02/03/a-month-of-powershell-day-3-security/

Printed 2014/12/20 01:01AM

A Month of PowerShell – Day 3 (Security)

By Wayne Sheffield, 2013/02/03

Welcome to Day 3 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at http://blog.waynesheffield.com/wayne/a-month-of-powershell/. Please refer to this page to see all of the posts in this series, and to quickly go to them.

Scripting

So far, we’ve only performed one simple task. It may be only one line, but if you’re like me, then you don’t want to have to type this in every time you want to run this… you’ll want to save this as a script file. As a plus, having it as a script prevents errors and enhances repeatability and reusability. So, in the PowerShell ISE (Integrated Scripting Environment), enter the following in an empty tab:

Source code   
Get-Process | Where-Object ProcessName –EQ 'sqlservr'

Now save the file (I used the name GetSQLProcesses.ps1 in a “My PowerShell Scripts” folder in my Documents library). (If you are using PowerShell (not the ISE), then the above path will give you an error because of the space in the name – the entire path needs to be in double-quotes. However, this causes it to be a string, so to run the script the statement needs to be prefaced with the ampersand (&).) Click the green arrow in the menu bar, or press F5 to run this script. This nicely runs and generates … the following error:

File D:\Users\wgshef\Documents\My PowerShell Scripts\GetSQLProcesses.ps1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170.

    + CategoryInfo          : SecurityError: (:) [], ParentContainsErrorRecordException

    + FullyQualifiedErrorId : UnauthorizedAccess

Execution Policy

Yikes! What went wrong here? PowerShell comes configured in, well, “Safe Mode”, and its default security setting doesn’t allow for running scripts. Okay, let’s throw scripts out the window and just type in everything whenever we need it – the repetition will just improve our typing speed and understanding of how PowerShell works, so it’s for the better. Agreed?

You don’t? Well, no wonder… that’s really not a solution. What we need to do is relax the security a wee bit. In PowerShell, we can run the cmdlet Get-ExecutionPolicy, and see that the security setting is set to Restricted.

So, what is this execution policy stuff? Well, just ask PowerShell with Get-Help Get-ExecutionPolicy -FULL. The Notes section explains how the execution policy works with PowerShell security. So, we just need to set the execution policy to a different setting. Let’s see if you can figure out what cmdlet that we would use to set the execution policy… did you come up with Set-ExecutionPolicy? That would be it. Now, how do you figure out what setting to set it to? Again, let’s ask PowerShell with Get-Help Get-ExecutionPolicy –FULL. The Parameters section shows these possible parameters:

Execution Policy Description
Restricted Does not load configuration files or run scripts. “Restricted” is the default execution policy.
AllSigned Requires that all scripts and configuration files be signed by a trusted publisher, including scripts that you write on the local computer.
RemoteSigned Requires that all scripts and configuration files downloaded from the Internet be signed by a trusted publisher.
Unrestricted Loads all configuration files and runs all scripts. If you run an unsigned script that was downloaded from the Internet, you are prompted for permission before it runs.
Bypass Nothing is blocked and there are no warnings or prompts.
Undefined Removes the currently assigned execution policy from the current scope. This parameter will not remove an execution policy that is set in a Group Policy scope.

I prefer to set the execution policy on my computers to RemoteSigned, so just run Set-ExecutionPolicy RemoteSigned. And most likely, you get another error. In order to change the Execution Policy, you need to be running PowerShell as an administrator. So, just launch PowerShell again by right-clicking the program, and selecting “Run as Administrator”. In this window, you will be able to set the execution policy. And now, you can finally run the saved script.

PowerShell Security

Since that script has been saved as a file, let’s try something else. Open up Windows Explorer, and navigate to the directory where you saved the file. When the file is double-clicked, you might expect the script to be run within PowerShell. However, what happens is that the file is opened in NotePad. Again, this is a security safety feature – the default action for PowerShell scripts is to Open, not Run. To run the script, right-click the file and select “Run with PowerShell”. Notice that I said that opening is the default action – like all file extensions, the default action can be modified. I don’t recommend changing this particular setting, but if you insist then you certainly can do so.

Well, today’s scripting session turned into security. Come back tomorrow as we resume the scripting section.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.