An issue when enabling protocols with Powershell

Published 09 October 09 07:01 PM | MartinBell

I was looking at the topic “How to: Enable or Disable a Server Network Protocol (SQL Server PowerShell)” books online to enable the TCP protocol for a SQL Server instance using Powershell. So I started up SQLPS from object explorer and posted the following script into the window.

$wmi = new-object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') .
$uri = "ManagedComputer[@Name='" + (get-item env:\computername).Value + "']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol"
$tcp = $wmi.getsmoobject($uri + "[@Name='Tcp']")
$tcp.IsEnabled = $true
$tcp.alter()
$tcp

Unfortunately this gave me an error:

Exception calling "Alter" with "0" argument(s): "Alter failed. "
At line:1 char:11
+ $tcp.alter( <<<< )

I was dumbfounded!!! How could books online be promoting incorrect code? So I trawled the internet and came up with nothing :( Then a flash of inspiration came to me (hopefully not my last!) and that was to run Powershell as an administrator. Et voila it worked.

This raised the problem of
how do you start SQLPS from the Object Explorer as an administrator. The solution is to start Management studio as an administrator and the script will work.

So the only question remaining is "Why does this error occur when it's a permissions issue?"

Filed under:

Comments

# Dew Drop – October 10, 2009 | Alvin Ashcraft's Morning Dew said on October 10, 2009 03:19 PM:

Pingback from  Dew Drop &#8211; October 10, 2009 | Alvin Ashcraft&#039;s Morning Dew

This Blog

SQL Blogs

Syndication