An issue when enabling protocols with Powershell
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?"