setting the TCP Port using WMI or using regsitry in C#

The TCP port is already enabled during the installation using the parameter TCPENABLED="1” in the SQLDemoConfig.ini file in silent Installation.Once the installation is complete we will change the TCP Port number using Windows Management Instrumentation (WMI). The WMI does not work on the Default SQL Instance it only works on the Instance we have installed. If we try it on the default instance we will get a “Alter() failed” error. One more reason why you may get “Alter() failed” is the Visual Studio is not run in administrator mode. Right click and “Run as administrator”.

The following references should be added to your Project/Solution.

 

1.    Microsoft.SqlServer.ConnectionInfo.dll

2.    Microsoft.SqlServer.Management.Sdk.Sfc.dll

3.    Microsoft.SqlServer.Smo.dll

4.    Microsoft.SqlServer.SmoExtended.dll

5.    Microsoft.SqlServer.SqlWmiManagement.dll

Use the Namespaces as below
 

using System.ServiceProcess; using Microsoft.SqlServer.Server; using Microsoft.SqlServer.Management.Smo.Wmi; using System.Diagnostics;

 

The code to update the TCP Port number using WMI is as follows:-

  

ManagedComputer mg = new ManagedComputer();   //Get the service by service name ServiceController service = new ServiceController("MSSQL$DEMOSQLEXP");   //Stop the service if its running if (service.Status == ServiceControllerStatus.Running) {      service.Stop(); }   //Get the SQLEXPRESS instance ServerInstance serverInstance = mg.ServerInstances["DEMOSQLEXP"]; ServerProtocolCollection prot = serverInstance.ServerProtocols; ServerProtocol serverProtocol = serverInstance.ServerProtocols["Tcp"]; //Change the TCP port number to 1433 or desired port serverProtocol.IPAddresses["IPALL"].IPAddressProperties["TcpPort"].Value = "1433";   //Change the TCP Dynamic port number to blank             serverProtocol.IPAddresses["IPALL"].IPAddressProperties["TcpDynamicPorts"].Value = String.Empty;     //Commit the changes serverProtocol.Alter();     //Start the service which is already stopped if (service.Status == ServiceControllerStatus.Stopped) {      service.Start(); }

 

It is important that we stop the service before making the changes and start the service once the change is done as the changes won’t take effect unless we restart the service. That is the reason we are using the ServiceController class.

There is one more way to enable the TCP Port and update TCP Port number is to write the Registry entry but that approach is not recommended.

The code to update the TCP port number is as follows:-

string keyNameTcp = @"SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQLServer\SuperSocketNetLib\Tcp";                    string keyNameTcpIP = @"SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQLServer\SuperSocketNetLib\Tcp\IPAll";     RegistryKey baseKey = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, RegistryView.Registry32);                    RegistryKey subkeyTcp = Registry.LocalMachine.OpenSubKey(keyNameTcp, true);   subkeyTcp.SetValue("Enabled", "00000002", RegistryValueKind.DWord); subkeyTcp.Close();   RegistryKey subkeyTcpIP = Registry.LocalMachine.OpenSubKey(keyNameTcpIP, true); subkeyTcpIP.SetValue("TcpPort", "1433"); subkeyTcpIP.Close();