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 »

Powershell.com’s Tip Of The Day–Avoid Use Of Set-StrictMode In Production? No Way!

I appreciate the Powershell tips from Powershell.com.

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.

WHITE SPACE

Background

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.

WHITE SPACE

Problem

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.

WHITE SPACE

Resolution

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.

WHITE SPACE

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:

clip_image001

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!

WHITE SPACE

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.)

 

image

 

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

image

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

image

 

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

   2:  

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

   4:  

   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!