In my previous introduction to PowerSMO, I covered the most basic operations of PowerShell, PowerSMO, and WMI. This article will investigate some of the more powerful PowerShell features, in particular scripts and functions, and then use those with PowerSMO to manage the extended properties of SQL Server objects.

PowerShell allows scripts to be signed and by default it will not run a signed script that’s been tampered with. If you make a PowerShell signed script part of your administrative toolkit then you can be sure that no one will “tweak” your script before running it.

We will spend quite a few words on signed scripts because it takes a bit to explain them but they are important in an administrative environment.

Script basics

Reusable scripts in PowerShell are stored in files, which must have a .ps1 extension. A script is invoked simply by typing its path on the PowerShell command line. Create a file called t.ps1 containing the following text, “Test” (including the quotes).

We can run this file by typing its path in the command line. All this simple script does this type of text “Test”. If your are following along then, at this point, make sure your execution policy is RemoteSigned, set-executionpolicy RemoteSigned.

PS C:\SimpleTalk> .\t.ps1
Test

It’s important to note that the full path is required, though we can use the “.” to indicate the current directory. If the path includes white space it will have to be quoted, and since it is quoted it will have to be preceded by an “&” character otherwise it will be treated as a simple string, not a path. For example:

PS C:\SimpleTalk> &”C:\Simple Talk\t.ps1″

The ‘&‘ tells PowerShell to “execute this”.

Running standalone scripts

What if you want to have PowerShell run a script, but not open a command shell window? When PowerShell is started it interprets the text following its program name as a script. So if we are in an ordinary Windows command line shell we can run our simple script as follows:

PS C:\SimpleTalk>PowerShell .\t.ps1
Test

This worked because PowerShell interpreted the command line text as a script and executed it. We could put any script we wanted after “PowerShell” on the command line, but because it was the path to a file it invoked our simple script.

Profiles

One of the common uses of PowerShell scripts is to customize PowerShell to your own preferences. Here is a script named MyPreferences.ps1 that sets the color of the text used for error messages:

$host.PrivateData.ErrorBackgroundColor=”White”
$host.PrivateData.ErrorForegroundColor=”Black”

In PowerShell, run the script as follows:

PS C:\SimpleTalk> .\MyPreferences.ps1

The result will be that error messages will be returned in black characters on a white background. Preferences, when set in this manner will affect only the current instance of PowerShell. If you start a new instance, then it will use the defaults.

To “persist” your preferences, you can put them in the well known file MyDocuments\WindowsPowerShell\Microsoft.PowerShell_profile.ps1 that PowerShell runs when it starts up. This file is called a profile. You can put the script to setup your PowerShell preferences into this file and then whenever you start PowerShell it will be customized to your taste. Actually there are number of profile files you can use to customize PowerShell but this is the one used when you, i.e. the owner of MyDocuments, starts PowerShell.

Script arguments

Typical scripts have some input arguments and there are a number of ways to specify these. Here is a script, named add.ps1, which adds two numbers and returns the result:

$args[0] + $args[1]

You can invoke this as follows:

PS C:\SimpleTalk> .\add.ps1 1 2
3

This script uses the built-in variable $args. It is an array of the arguments passed in on the command line. Note that command line arguments are separated by spaces, not commas, and that the array is zero based.

Symbolic arguments

The add.ps1 script is using positional arguments. It’s fairly typical for scripting languages to use positional arguments, but you might want to use symbolic arguments to make maintenance easier, as shown in script addsymb.ps1:

param ($addend1, $addend2)
$addend1 + $addend2

This script uses the PowerShell keyword param, which lets you name the command line arguments ($addend1 and $addend2, in this case). The script is invoked as for add.ps1 and produces the same results.

Strongly typed arguments

PowerShell arguments may be strongly typed. For example, we might want to modify the addsymb.ps1 script, to require that both arguments be integers. The addstrong.ps1 script shown below does this:

param ([Int32]$addend1, [Int32]$addend2)
$addend1 + $addend2

Each argument variable for the param keyword is preceded by a type name in square brackets. PowerShell interprets text inside of square brackets as the name of a type. Whenever PowerShell sees a variable preceded by a type name like this, it will try to convert the value of the variable to that type. Failure to convert the value of the variable will cause an error. Below are examples of using addstrong.ps1.

PS C:\SimpleTalk> .\addstrong.ps1 1 2
3
PS C:\SimpleTalk> \addstrong.ps1 1 Two
C:\SimpleTalk\addstrong.ps1 : Cannot convert value “Two” to type
 “System.Int32”. Error: “Input string was not in a correct format.”
At line:1 char:2
+ .’ <<<< C:\SimpleTalk\addstrong.ps1′ 1 Two

In the second example, PowerShell could not convert the string “Two” to an integer so it returned an error. Another example of using addstrong.ps1 is shown below:

PS C:\SimpleTalk> .\addstrong.ps1 1 2.6
4

In this example “2.6” is not an integer but PowerShell was successful in converting it to an integer. Note that PowerShell did the conversion by rounding $addend2.

Security, signatures and certificates

Security is a first class citizen of PowerShell and, as such, it is very picky about how it runs scripts and which scripts it will run.

Script context

By default a script is, in effect, “walled off” from the copy of PowerShell that invokes it. This means that it cannot access variables in the copy of PowerShell that invoked it. Consider the following script, named reachout.ps1.

$currentBalance += 100

All reachout.ps1 does is add 100 to the variable $currentBalance. At first glance it seems like you could use this script to modify a variable named $currentBalance you created before invoking the script. Try the following and you will see that it does not do that.

PS C:\SimpleTalk> $currentBalance = 100
PS C:\SimpleTalk> .\reachout.ps1
PS C:\SimpleTalk> $currentbalance
100

As you can see the reachout.ps1 script was not successful in modifying $currentBalance. The script was run in its own PowerShell context so PowerShell created a new $currentBalance variable in that context and that was the variable that was modified.

You have to specifically tell PowerShell that it is OK for a script to share your current context. You do this by preceding the name of the script with the “. ” operator (full stop followed by a space), as shown below.

PS C:\SimpleTalk> $currentBalance = 100
PS C:\SimpleTalk> . .\reachout.ps1
PS C:\SimpleTalk> $currentBalance
200

Signed scripts

PowerShell script files may be signed with certificates. We won’t cover the details of how certificates and public key encryption work but, in short, PowerShell public key encryption works on the principle that something encrypted with a private key can be decrypted with the corresponding public key.

For example, Reachoutsigned.ps1 is a signed script and looks something like this:

$currentBalance += 100

# SIG # Begin signature block
# MIIEEgYJKoZIhvcNAQcCoIIEAzCCA/8CAQExCzAJBgUrDgMCGgUAMGkGCisGAQQB
# gjcCAQSgWzBZMDQGCisGAQQBgjcCAR4wJgIDAQAABBAfzDtgWUsITrck0sYpfvNR
…
# M2NwIf5cjGiCY9Rvsz/g1bDewAdgu4J6NpjYqf5K7qnYF+OgWVw=
# SIG # End signature block

The part between “# SIG # Begin…” and # SIG # END…” is the signature of the script that precedes it. The signature is the hash of the script encrypted with a private key, plus a few other things such as the name of the certificate used to sign the script. PowerShell uses the corresponding public key for this certificate to decrypt the hash value and then compares that to the actual hash of the script. If the hashes are the same then the script has not been tampered with.

Execution policy

Note that signatures are not checked unless the execution policy requires it. So if, as we did at the beginning of this article, have an execution policy of RemoteSigned then the signatures of local scripts will not be checked. In a production environment you probably want to only allow signed scripts to be run. To do this you set the execution policy to AllSigned, as follows:

Set-ExecutionPolicy AllSigned

Now if you try to run our original reachout.ps1 script to update the current balance you will get an error as is shown next:

PS C:\SimpleTalk> $currentBalance=100
PS C:\SimpleTalk> . .\reachout.ps1
File C:\SimpleTalk\reachout.ps1 cannot be loaded.
The file D:\dan docs\SimpletalkArts\PowerSMO@Work\Samples\reachout.ps1
is not digitally signed. The script will not execute on the system.
Please see “get-help about_signing” for more details..
At line:1 char:2
+ .  <<<< .\reachout.ps1

However the reachoutsigned.ps1 works just fine… at least on my computer. You don’t yet have the proper certificate installed on your system; we will get to how to do that shortly.

PS C:\SimpleTalk> $currentBalance=100
PS C:\SimpleTalk> . .\reachoutsigned.ps1
PS C:\SimpleTalk> $currentBalance
200

As you can see the script ran. What if someone changes the reachoutsigned.ps1 script to add 200 to the current balance

$currentBalance += 200
# SIG # Begin signature block
# MIIEEgYJKoZIhvcNAQcCoIIEAzCCA/8CAQExCzAJBgUrDgMCGgUAMGkGCisGAQQB
…

Now they try to run it.

PS C:\SimpleTalk> $currentBalance=100
PS C:\SimpleTalk> . .\reachoutsigned.ps1
File D:\dan docs\SimpletalkArts\PowerSMO@Work\Samples\reachoutsigned.ps1
cannot be loaded. The contents of file C:\SimpleTalk\reachoutsigned.ps1
may have been tampered because the hash of the file does not match the
hash stored in the digital signature. The script will not execute on
the system. Please see “get-help about_signing” for more details..
At line:1 char:2
+ .  <<<< .\reachoutsigned.ps1

As you can see, PowerShell has used the signature that is embedded in the reachoutsigned.ps1 file to detect that the file has been tampered with, and refused to run it.

Certificate stores

PowerShell uses the signature embedded in the script to verify that the script has not been tampered with, but this is not enough to ensure the integrity of the script. It must also check that the signature embedded in the script is one that it trusts.

Windows keeps track of certificates in special locations. PowerShell treats these locations as though they were in a file system on a drive named “cert:“. To see the locations in which certificates are stored just use the dir cmdlet as shown below.

PS C:\SimpleTalk> dir cert:*

Location   : CurrentUser
StoreNames : {UserDS, AuthRoot, CA, Trust…}

Location   : LocalMachine
StoreNames : {AuthRoot, CA, Trust, Disallowed…}

The dir command has found two locations: CurrentUser and LocalMachine. Each of these locations contains a number of stores and we can use the dir command again, as shown below, to see the stores in the LocalMachine location.

PS C:\SimpleTalk>dir cert:LocalMachine\*
Name : AuthRoot
Name : CA
Name : Trust
Name : Disallowed
Name : My
Name : Root
Name : TrustedPeople
Name : SPC
Name : TrustedPublisher

The Root store is important; PowerShell will not trust a certificate unless it can be found in a Root store, or the source of the certificate can be traced to a certificate in a Root store. For example, if we look at the certificates in the Root store we will see that there are many from VeriSign, as shown below.

PS C:\SimpleTalk> dir cert:LocalMachine\Root\* |
 ?{$_.subject -like “*verisign*”}

    Directory: Microsoft.PowerShell.Security\Certificate::
LocalMachine\Root

Thumbprint                                Subject
———-                                ——-
F46AC…C0DE12D  CN=VeriSign Time Stamping Services CA, O=”VeriSign,
817E…6123A690  CN=VeriSign Time Stamping Services Signer, O=”VeriSign,
…
3F85…B4BEF8BA  OU=VeriSign Trust Network, OU=”(c) 1998

Many web sites use certificates that are purchased from VeriSign and they are accepted by browsers because their source certificate can be found in a Root store. There are many other details about certificates that we are not going to cover because they are beyond the scope of this article. The important point to note here is that the certificate PowerShell uses to check a signed script must be appropriately installed into a certificate store.

Deploying certificates

If you want to follow along, set your execution policy to RemoteSigned for now. Later we will set it back to AllSigned.

PS C:\SimpleTalk> set-ExecutionPolicy RemoteSigned

The certificate you need in order to run reachoutsigned.ps1 is in the file root.cer. You can use the deployACert.ps1 script to install the certificate on your computer so that PowerShell can use it to check signed scripts. Later we will see how this script works, but for now this is how you use it.

PS C:\SimpleTalk> .\deployACert.ps1 C:\SimpleTalk\root.cer

Note that the full path to root.cer was used. You will need administrator rights to do this as this script will install the certificate in the Root for the LocalMachine.

Once you have done this set your execution policy back to AllSigned and you will still be able to run reachoutsigned.ps1, but not be able to run reachout.ps1.

Creating a self-signed certificate

In order to sign a script you will need a certificate. There are many ways to obtain or make a certificate; you may have capability for creating certificates in your own enterprise and they can also be purchased. The script makeACert.ps1 will create what is called a self-signed certificate. This is the simplest way to get a certificate and we will use it to show the basics of signed scripts.

The makeACert.ps1 script is shown below. All it is doing is running the MakeCert.exe utility. Watch out for line warps, the script below really has only two lines.

param ($certName)
&”C:\Program Files\Microsoft SDKs\Windows\v6.0\Bin\makecert” -n
   “CN=$certName PowerSMO” -a sha1 -eku 1.3.6.1.5.5.7.3.3 -r  -pe
   -ss Root -sr LocalMachine “$certName.cer”

Note that MakeCert.exe is not part of a standard Windows installation. It is included as part of the Windows SDK and Visual Studio. You will need one of these installed on your machine. The SDK can be downloaded from:

http://www.microsoft.com/downloads/details.aspx?FamilyID=c2b1e300-f358-4523-b479-f53d234cdccf&DisplayLang=en.

Even though its title mentions “Vista,” it will work on any version of Windows. When you run this script, if you get an error that indicates that MakeCert is not a cmdlet then check to be sure the path to MakeCert.exe is correct. It might be in something like C:\Program Files\Microsoft Visual Studio 8\Common7\Tools\Bin.

You use makeACert.ps1 by passing the name for the certificate you want to make on the command line. Be sure to set your execution policy to RemoteSigned to follow along.

PS C:\SimpleTalk> .\MakeACert.ps1 “MyCertificate”
Succeeded

Running this script does two things. Firstly, it adds a certificate named “MyCertificate PowerSMO” to the Root store of the LocalMachine location. Secondly, it creates a file named MyCertificate.cer. We will need that file when we deploy signed scripts onto other machines.

There is a lot of gobbledygook on the command line for the MakeCert.exe utility. Going into all of the details is beyond the scope of this article, but these command line switches are telling MakeCert.exe to make a self-signed certificate that can be used to sign scripts. The -eku switch is marking the certificate for use in signing scripts.

Signing scripts

Once you have a certificate installed in the Root store you can sign scripts. There is a sample, unsigned, script named signme.ps1, which simply contains the content, “Hello“, and will print out that content when executed. We can use a second script, signAScript.ps1, to sign the script signme.ps1. The signAScript.ps1 script is shown below.

param ($scriptPath, $certName)
$cert = %{dir “cert:LocalMachine\Root” |
?{$_.Subject -eq “CN=$certName PowerSMO”}}
Set-AuthenticodeSignature “$scriptPath” $cert

SignAScript.ps1 takes two input arguments. The first is the path to the script file you want to sign. The second is the name you used to make your certificate. The first script block uses the dir cmdlet to find all of the certificates in LocalMachine\Root. The second script block tests to see if the name is that of the certificate we are looking for, and then sets the $cert variable to this certificate. You sign a PowerShell script using the Set-AuthenticodeSignature cmdlet. It requires two things; the path to the file you want to sign and the certificate you want to use to sign it. This is what the last line of signAScript.ps1 does.

Here is how you sign the script signAScript.ps1 to sign the script signme.ps1:

PS C:\SimpleTalk> .\SignAScript.ps1 “$pwd\signme.ps1″ MyCertificate”

    Directory: D:\Dan docs\SimpletalkArts\PowerSMO@Work\Samples

SignerCertificate              Status               Path
—————–              ——               —-
DBB…A16E4CC                    Valid                signme.ps1

Note that in this example we used the built-in $pwd variable to add the path to the file name.

Set your execution policy back to AllSigned and try to run signme.ps1 and you will find that is works. Open up signme.ps1 with a text editor and you will see that it now contains a signature.

Deploying signed scripts

We now have a working, signed script and need to deploy it to another machine. To do this we need to use the.cer file for our certificate to put that certificate into the LocalMachine\Root store on the system to which we wish to deploy our script. The deployACert.ps1 script will do that for us, as we saw earlier. The deployACert.ps1 is shown below, watch out for the line wraps.

param([String]$certPath)
$cert =  [System.Security.Cryptography.X509Certificates.X509Certificate]
            ::CreateFromCertFile($certPath)
$store = new-object “System.Security.Cryptography.X509Certificates.
            X509Store” “Root”, “LocalMachine”
$store.Open(“ReadWrite”)
$store.Add($cert)
$store.Close()

This script uses the support for cryptography that is built into .NET. Its input argument is the path to the .cer file we want to add to the certificate store. The line “[Syste…]::CreateFromCertGFile” creates a certificate from the .cer file.

The next line gets a reference to the LocalMachine\Root part of the certificate store. Next that part of the certificate store is opened, the certificate created earlier is added to the store, and then the store is closed.

Certificate strategy

What we have been doing with the createACert.ps1, signAScript.ps1, and deployAScript.ps1 scripts is probably the easiest way to make and see how scripts are signed in PowerShell. You will probably find a more effective way to do this in your environment, but it will still involve obtaining a certificate, deploying it to machines, and signing scripts. There are many details that we did not discuss for the sake of simplicity, but this should be enough to get you started.

Functions

PowerShell supports creating functions. In fact, when you use something like get-SMO_Server you are using a function that was dynamically created by the InitPowerSMO.ps1 script. PowerShell stores function definitions in what appears to be a disk named “function:“.

NOTE:
The following example, and one that follows it, assume that you have run the initPowerSMO.ps1 script after opening PowerShell. If you have not done so, please run it now. It is available with the code download for this article.

You can list all the PowerSMO functions with the dir cmdlet as shown below:

PS C:\SimpleTalk> dir function:*SMO*

CommandType     Name                         Definition
———–     —-                         ———-
Function        Get-SMOT_ExecutionStatus     [Micro…
Function        Get-SMO_ExecutionStatus      if($args.len

The Definition column is, in fact, of the function itself. We can use the get-content cmdlet to extract the definition of the get-SMO_Server function as follows.

PS C:\SimpleTalk> dir function:get-smo_server | get-content
if($args.length -eq 0)
{
new-object “Microsoft.SqlServer.Management.Smo.Server”
}
else
{
new-object “Microsoft.SqlServer.Management.Smo.Server” ($args)
}

The script for a PowerSMO function is pretty simple, it just creates an instance of the desired SMO class; it passes arguments to the constructor if there are any. For your PowerSMO scripts you will probably not need all of the functions created by the InitPowerSMO.ps1 script. You might find it more effective just to hard-code the functions you need into your profile.

You create a function in PowerShell by using the function keyword. Below is a function, in the Hello.ps1 script file, that says “Hello” to the name passed into it.

function Hello
{
“Hello ” +  $args[0]
}

You can use the Hello.ps1 to add this function to your current session, or you could include it in your profile if you are going to use this function a lot.

PS C:\SimpleTalk> . .\hello.ps1
PS C:\Simpletalk > hello dan
Hello dan

Don’t miss the extra “. ” , which puts the hello function in your context!

Function arguments

Functions manage arguments in the same way scripts do, by using the param keyword. Here is the hello function, in hellosymb.ps1, rewritten using a symbolic argument.

function HelloSymb
{
param ($name)
“Hello ” + $name
}

This function works the same as the previous, as shown below.

PS C:\SimpleTalk > . .\hellosymb.ps1
PS C:\SimpleTalk > hellosymb dan
Hello dan

Using functions to manage SQL Server extended properties

One of the features of SQL Server is that most of the objects in a server instance can have some extended properties; these are really just extra metadata you can make up to help you manage a server. For example, you might want to add a comment directly to a database or table that explains why it was added, or maybe add some information that other tools might use when they need to use the table. The AddComment function, addComment.ps1, shown below uses PowerSMO to add a simple comment to an object in a instance of SQL Server.

function addComment
{
param ($object, $class, $comment)
$extendedProperty = get-SMO_ExtendedProperty $object $class
$extendedProperty.value = $comment
$extendedProperty.Create()
)

The addComment function uses PowerSMO to create an extended property. The first argument, $object, is the server object to which the extended property will be added. The second argument, $class, is the name of the extended property itself. A server object may have more than one extended property, but each is distinguished by its name.

The value of the extended property is set to the value of the $comment variable. Lastly the Create method is called on the $extendedProperty; this physically adds the extended property to the database.

My server has three databases on it, Test1, Test2 and Test3. Test1 and Test2 were involved in an auditing process which is identified as “Audit3”. Test1 passed and Test2 failed. Here is an example of using the addComment function to annotate these databases with the results of the audit.

PS C:\SimpleTalk> . .\addComment.ps1
PS C:\SimpleTalk> $server = get-SMO_Server
PS C:\SimpleTalk> $t1 = $server.Databases[“Test1”]
PS C:\SimplTalk > $t2 = $server.Databases[“Test2”]
PS C:\SimpleTalk> addComment $t1 “Audit3” “Passed”
PS C:\SimpleTalk> addComment $t2 “Audit3” “Failed”

The first thing we do is to run the addComment.ps1 script to add the addComment function. Next we get a reference to the default server. Then we get a reference to each of the databases we want to add extended properties to. Lastly we use the addComment function to note that Test1 passed the audit and Test2 did not. At a later point in time I want to find out what databases were part of the Audit3 process.

PS C:\SimpleTalk > $server.databases |
 ?{$_.ExtendedProperties[‘Audit3’] } |
 %{$_.Name}
Test1
Test2

The example above makes use of a pipeline that uses a test script block to find each database involved in the audit. If a database has an extended property named “Audit3” is was part of the audit, otherwise it was not. I might want to further refine things and add the results of the audit.

PS C:\SimpleTalk > $server.databases |
?{$_.ExtendedProperties[‘Audit3’] } |
%{$_.Name + “: ” + $_.ExtendedProperties[‘Audit3’].Value}
Test1: Passed
Test2: Failed

Extended properties are really very useful, especially when combined with PowerSMO. You can easily report on the status of an audit, for example, or use them to trigger further processing.

Conclusion

We have covered how to make scripts and sign them, and how to use scripts to configure PowerShell to the way we want to work. We have also seen how to encapsulate scripts inside of functions to make it easy to reuse them. And we have seen how to put all this stuff together to make use of SQL Server extended properties.

I think that this is just barely enough to get you going with using PowerSMO to manage SQL Server. There are many other things that can be done with PowerShell and PowerSMO… if you have some particular issues you would like to have covered, please add comments mentioning them. No guarantee, but I will try to work them into subsequent articles.

Some of the topics in this article are covered in the Pluralsight.com Applied SQL Server 2005 course.