Connecting to SQL Server via Windows PowerShell with SQL Server authentication

Most of the articles I've written about SQL Server with Windows PowerShell have been using Windows Authentication. And while it is highly recommended to use Windows authentication to connect to SQL Server, the reality is that the IT infrastructures we have don't run on Microsoft Windows.

Here's an article I wrote on how to use Windows PowerShell to connect to SQL Server via mixed mode authentication

Query Hyper-V Virtual machines using Windows PowerShell

Being a lazy administrator as I am, I try to minimize the amount of mouse-clicks I need to make to retrieve information about something on a Windows platform. As I have been using Microsoft Hyper-V on a bunch of my test machines, I always check if a VM is up and running before I power down my host machine (imagine the amount of electricity consumed just by keeping your machine up and running even without using it). This is specifically the case when dealing with my Windows XP VMs. I noticed that the profiles get corrupted if I shutdown the host machine without properly shutting down the VM. So, I always made sure that the VMs are not running before powering down the host machine.

I wrote a PowerShell command to query the current state of the VMs running on Hyper-V


Get-WMIObject -class "MSVM_ComputerSystem"-namespace "root\virtualization"-computername "."

This will actually display a bunch of information about the VMs running on Hyper-V but what we're really concerned about is the name of the VM and it's currently running state. These two properties are associated with the ElementName and EnabledState attributes of the MSVM_ComputerSystem class. All we need to do with the command above is to pipe the results to a Select-Object cmdlet, specifying only these two properties, as follows

Get-WMIObject -class "MSVM_ComputerSystem"-namespace "root\virtualization"-computername "." Select-Object ElementName, EnabledState

While the EnabledState property will give you a bunch of numbers, I'm only concerned with those values equal to 2, which means that the VM is running. But, then, you might not remember what the value 2 means. So might as well write an entire script that checks for the value of the EnabledState property. I've used the GWMI alias to call the Get-WMIObject cmdlet

$VMs = gwmi -class "MSVM_ComputerSystem"-namespace "root\virtualization"-computername "."
foreach ($VM IN $VMs)
{
switch ($VM.EnabledState)
{
2{$state="Running" }
3{$state="Stopped" }
32768{$state="Paused" }
32769{$state="Suspended" }
32770 {$state="Starting" }
32771{$state="Taking Snapshot" }
32773{$state="Saving" }
32774{$state="Stopping" }
}
write-host $VM.ElementName `,` $state
}

On a side note, make sure you are running as Administrator when working with this script as you will only see the VMs that your currently logged in profile has permission to access. Running as Administrator will show you all of the VMs configured on your Hyper-V server

"Cannot Generate SSPI Context" errors

I get to deal with this type of error on a regular basis and, most of the time, end up recommending updating the SQL Server server principal name (SPN) using the setspn utility or simply rebooting the server. I was reading thru the SQL Server CSS blog today and found out another reason for this error message - changed SQL Server service account passwords. While I do not recommend changing the service account passwords during production hours, there may be cases where the account's password need to be changed as well as the corresponding credentials on the services while waiting for approval for downtime to restart the service. Lesson learned from this blog post is that (1) never change service account passwords during production hours and (2) always restart the SQL Server service immediately after changing the service account password for Kerberos to function properly. No wonder a reboot usually fixes this issue as it also restartes the SQL Server service. I will have to reproduce this to validate

Installing SQL Server 2008 Failover Cluster on a Windows Server 2008 R2?

I did a demo fest on installing SQL Server 2008 Failover Cluster on a Windows Server 2008 system a few weeks back for a user group event and the attendees requested that I post more information about how to do a slipstream of service pack in a SQL Server 2008 installation. With Windows Server 2008 R2 already released, Microsoft released KB article 955725 highlighting the need for SQL Server 2008 Service Pack 1 when installing on either Windows 7 or Windows Server 2008 R2. I wrote an article on MSSQLTips.com about it to supplement the series on installing SQL Server 2008 Failover Cluster.

This blog post came a bit late as I needed to wait for the article to be posted on the site so I can use it as a reference.