THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

Find your Enabled SQL Server Network Protocols Using PowerShell

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

Published Saturday, May 21, 2011 4:38 PM by AllenMWhite

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Puneet said:

Hey Allen, I tried to use your script and took me a while to figure out. also, I am still newbie to powershell.

I was getting an error :

The following exception was thrown when trying to enumerate the collection: "SQL Server WMI provider is not available on servername>.".

You will need to load SMO assembly as well

[System.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

or

(reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

It works fine after that for me :).

October 24, 2011 1:34 PM

Leave a Comment

(required) 
(required) 
Submit

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement