Another quick check I like to do when in 'discovery mode' on a client system is to see what network protocols are enabled. Those of us around in 2003 will remember well the headaches caused by the SQL Slammer virus, and know to shut down Named Pipes unless it's absolutely necessary, just as a precaution.

As part of my investigation on new systems, here's a quick script that returns the protocols on a server, and whether or not they're enabled.

First, we need to load the SMO WMI Managed Computer assembly (in case we're running from native PowerShell.

# Load SMO Wmi.ManagedComputer assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null

Then, it's just a matter of connecting to the server (not the instance), and looking through the ManagedComputer object's ClientProtocols collection.

# Connect to the instance using SMO
$m = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') 'MyServer'
# Return the protocols and whether or not they're enabled.
$m.ClientProtocols | select DisplayName, IsEnabled

If unnecessary protocols are enabled, I can now disable them, and if ones I need aren't enabled, I can fix that, too.

Allen

P.S. Nicholas Cain (blog | twitter) pointed out that he was getting incorrect results from this script, and only got correct results when digging deeper into TCP/IP for an instance. I looked at it and realized I had to go a bit deeper with this script.

The SMO ManagedComputer object has a collection called ServerInstances, and each instance has a collection called ServerProtocols, and for each instance of SQL Server, the true state of the protocol for that instance is reflected there. Here's the last part of the script that'll tell you that:

# Now return for each instance
$inst = $m.ServerInstances
foreach ($i in $inst) {
[string]$nm = $i.Name
$proto = $i.ServerProtocols
foreach ($p in $proto) {
[string]$dispnm = $p.DisplayName
[string]$enabld = $p.IsEnabled
write-output "Instance Name: $nm"
write-output "Protocol Name: $dispnm"
write-output "Is Enabled: $enabld"
write-output ""
}
}

Thanks, Nic, for finding that shortcoming.

Allen