Calling Reporting Services’ CreateReport() from Powershell

The specification for CreateReport() provided by Microsoft is inaccurate, as far as I can tell.  In c# it won’t matter, but it does in Powershell, due to the oddities in how Powershell handles empty arrays.

The specification says that CreateReport() will return an empty array if there are no errors or warnings.  I believe that in SQL Server 2008 it actually returns a null.  Maybe later I’ll find a flaw in my thinking…but maybe not!

Look at this code:

Read the rest of this entry »

Powershell: nulls, empty arrays, single-element arrays

One of the biggest gotchas for people new to Powershell is the handling of null, empty arrays, and single-element arrays.  If you come from another language such as c# you’ll be shocked.  And even for an experienced Powershell writer, the behavior can lead to ugly bugs.

Other people have covered this topic before.  Here’s a good discussion by Keith Hill.  But I wanted a short reference with examples of the strange (to many people) behavior, so I’m writing this today somewhat for my own benefit.  But maybe someone else will find it useful.

Read the rest of this entry »

Testing A New Syntax Highlighter

This is a test of a new syntax highlighting plugin that works for WordPress-hosted blogs.  I think I’m going to like it!  I’ll post here the same code as in my previous post.  In that one I couldn’t get it formatted acceptably by my previous favorite, Code Snippet so I had to try something else that I didn’t like but at least was readable.  This one seems to work much better, probably because it was specifically designed to work with WordPress.

Read the rest of this entry »’s Tip Of The Day–Avoid Use Of Set-StrictMode In Production? No Way!

I appreciate the Powershell tips from

But the Monday, 3/21/11 tip recommends avoiding the use of Set-StrictMode “latest” in production, and I think that’s bad advice.  Here’s a quote from the tip:

However, Set-StrictMode should only be used on development machines. You should never use it on production machines or in production scripts as PowerShell will not complain about non-existing properties. Have a look:

Dir $env:windir | Where-Object { $_.Length -gt 1MB }

Read the rest of this entry »

Credentials vs. WindowsIdentity in Powershell

I’ve been implementing some automated deployment processes using PS-Remoting, which means that I’ve also been learning about some security issues. This post is about how I solved a problem, and what I learned along the way.



My Windows login is in domain X, but the machine I run the deployment process from, and the machines being deployed to, are in domain Y. There’s a one-way trust set up, so that I have no troubles logging into the machines in the Y domain, belonging to the local administrator group on each machine, pushing files, etc., using my domain X login–but from a domain Y machine I cannot see or do anything with machines in the X domain. All as it’s meant to be.



The trouble was that remoting didn’t work in domain Y unless I used a domain Y login. We have multiple test environments, with the lower ones (including the lowest: my box) all in domain X, where everything worked fine. We finally got up to the first environment which completely mirrors the security configuration of production, and BOOM! I couldn’t get a remote connection to anything.



OK, first off: HOORAY for having a test environment that mirrors production security! I think that this is always worth doing, and the latest issue is an excellent example. It would not have been fun to catch this on production deploy night.

But now: how did I solve it?

I have an "infrastructure" team at my organization that takes care of the network infrastructure. Working with them, we had a hard time figuring out what to do to resolve the remoting problem. We tried three things:

  1. Figure out why the trust that is setup doesn’t support what we’re trying to do with remoting. We struck out after hours of research. Maybe we’ll get an answer some day.
  2. Short term, we created a special login in the Y domain. I logged in using that Y login, then was able to run the deployment process successfully, remoting to different machines as needed. So this worked, but was not something we wanted to support long term. From a network security standpoint, I am domainX\username, and that’s the only way I should be identified.
  3. A combination of two things gave us a satisfactory long-term solution:
    1. From the domain Y server where we wanted to initiate remoting, we added the target servers to the list of TrustedHosts: Set-Item wsman:\localhost\client\trustedhosts *.domainY -Force. (Note: “*.domainY” means “all machines in the domainY domain”. For this to work, you then need to specify the full domain name of machinename.domainY when creating a remote session.)
    2. We prompt for the deployer’s credentials using Get-Credential, then explicitly pass the credentials, using the -credentials parameter, whenever setting up a remoting session.

Option three worked for us, although we did notice that the authentication mechanism of the remote session used NTLM. We were unable to make it use Kerberos. I’ll bet that if we could figure that out, we wouldn’t have to use TrustedHosts at all.


What I l Learned

Incidentally, I used [Security.Principal.WindowsIdentity]::GetCurrent() to find out about the type of authentication being used. Try this out yourself:


So then the problem became how to get the credentials in the first place. At first I couldn’t figure out why you can’t just automatically (no prompting) get a credentials object for the current user. I’m new to a lot of this and was pretty ignorant about it. But now I understand some of it.

A System.Security.Principal.WindowsIdentity object, which is what you get from a call to [Security.Principal.WindowsIdentity]::GetCurrent(), gives you information about your authenticated identity on the network. As such, we would hope that it’s highly secure. It doesn’t even contain your password.

However, a System.Management.Automation.PSCredential object, which is what you get from a call to Get-Credential, is barely more than a simple data structure. You can enter any kind of user ID and password–no authentication is done when Get-Credential is called. The PSCredential object has a method called GetNetworkCredential(). You might think from its name that it gives you a WindowsIdentity object, but no, it simply breaks your user name into separate Domain and UserName strings and hands you the credential password in clear text. Try it out with a dummy login and password–it works fine. No authentication is done, and although you’ll see a secure string for the password in the PSCredential object, you can see it in clear text by calling GetNetworkCredentials().

Clearly, the intended use of WindowsIdentity and PSCredential objects are very different. One contains secure information about a live, authenticated user on the network. The other is an object that is secure enough to pass over a network connection (since the password is a secure string), and which will be USED LATER to authenticate someone at the time a remote session is created.

Once I got that straight I could easily see why there’s no support for converting from one to the other.

I really do have to prompt for credentials in my deployment process, then pass those credentials when opening a remote session, in order to make remoting work in a mixed-domain environment. I wish I didn’t.  But at least I understand the difference between the two objects!


And remoting is working fine!

What Version Of Powershell Is On This Machine? Ask PSVersionTable

I didn’t know about this built-in variable until recently.  $PSVersionTable is new with Powershell 2.0.  (Thus you can tell if you have v2 installed by seeing if this variable exists.)




It’s a hashtable containing versions of various parts of your system.   For example, here’s what I see on my machine:

PS c:\temp> $PSVersionTable


Each value is actually an object of type System.Version. 



So, for example, next time Microsoft updates Powershell and you want to see if you have the update, you could type $PSVersionTable.PSVersion to find out.

I won’t use this information every day, but I’ve wanted it a few times, and now I finally know how to get at it.

I haven’t looked into the other values…I’m disappointed that the CLRVersion is showing as 2.0.  This was run on a computer with the Framework 4.0 installed and I was hoping to see it.

Newbie problems with SMO and SQLPSX: unclosed connections

I have little experience with the SQL Server Management Objects (SMO) library and am looking at SqlServer Powershell Extensions (sqlpsx) for the first time.

It’s a lot to learn…

My first real problem was a steady growth of open SQL Server connections. In my script I would use a Server object, which opened a database connection.  When my script ended, the connection remained open, so next time I ran it a new connection would open.  You can’t just let something like this continue—you’d eventually run out of connections!

After banging my head, experimenting, searching on Google, etc., for a few hours (I’m sure you’ve been there), I finally came across a link that showed me how to close the connection:

Call the Disconnect() method on the ConnectionContext property of the Server object.

   1: $smoServer = Get-SqlServer $instanceName


   3: # ... do whatever you need here...


   5: # close the connection or you end up with more connections every time you run this!

   6: $smoServer.ConnectionContext.Disconnect()

Many thanks to the folks out there in the ether!

Rare PowerGUI 2.2 debugger problem

After upgrading to PowerGUI 2.2, which I use for developing Powershell scripts, I encountered a problem with the new debugger. I got it fixed, and am delighted with this version of PowerGUI.

But then a couple months later I fired up a VM that I hadn’t used for a while. I tried to debug a script and–BAM!–there was the error again. Then I had to go searching for the solution again.

So here’s my reminder, which may also help those rare people who have this problem.

Breakpoints don’t work at all if debugging a script contained in a directory that has certain special characters in its name. This is related to the same Powershell issue I blogged about at:

The solution is to simply put the script into a different directory, one with no unusual characters in the directory name.

Read more here:

New-Service cmdlet vs. InstallUtil.exe

I like using Powershell cmdlets (where possible) rather than command-line utilities. It keeps the syntax consistent and often makes things easier in general.

But this is an exception. In most cases, if you want to create a Windows service based on a .Net assembly, I recommend that you use InstallUtil.exe, not New-Service.

InstallUtil uses reflection to find installers for the service, so the developer can put that information right into the .Net solution. This includes specification of the service name, display name, and description. When you use New-Service you have to specify these properties, and then ensure that you take any other special steps to install the service.

Both ways offer convenience, but using InstallUtil you can get consistency across all your development, test, and production environments without having to know anything but the assembly name.

For more information, see the InstallUtil reference on msdn and the New-Service reference on TechNet.

Powershell WebAdministration Provider Inconsistencies

The WebAdministration module for Powershell comes standard in Windows 7 and Window 2008 R2.  It is available as a snapin for older versions of Windows at  Both appear to work the same way so I’ll just refer to it as “the module.”

I’m just beginning to delve into it.  I think I’ve uncovered some functionality that I love.  But the module seems immature to me.  I may say more after I work with it more—or maybe I’ll have to correct myself.  But today’s topic is an inconsistency in the provider.


When you list the contents of a directory in which there are no web sites you get the normal output, including "Mode".  This is the same kind of output you’d get from using “Dir” on your C: drive.



If you convert one of the directories into an application, the output changes. Mode is gone, replaced by Type.


But there is no "Type" property.

Try to use it.  Notice that nothing is returned by the following statement.


Let’s see what properties actually exist.


Ah hah!  So, to list applications you need to look for "NodeType", not "Type."