Change SQL SERVER EXPRESS 2008 TCP port with Microsoft.SqlServer.Management.Smo

Question

I need to change the default port(1433) of SQL EXPRESS 2008 instance in c#.

Answer 1

normally you reassign the port via their UI

http://msdn.microsoft.com/en-us/library/ms177440.aspx

however, i think it just persists it in the registry, so the easiest thing is likely to change it once in the UI to some particular number (12345, for instance) and then look under HKLM\SOFTWARE\Microsoft\Microsoft SQL Server for that number. Doing so myself shows the key being HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IP1 with a REG_SZ (seems odd) value named TcpPort.

If you mean (or also need to know) how to connect to the default instance when it's on the non-default port, just change the source from host to host,port (for instance, change FOO to FOO,12345)

http://msdn.microsoft.com/en-us/library/ms191260.aspx

Answer 2

You have to use the WMI provider that comes with SMO to do it. Add references to

Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SqlWmiManagement
Microsoft.SqlServer.WmiEnum

and a using for

using Microsoft.SqlServer.Management.Smo.Wmi;

Then the code is basically like this:

ManagedComputer c = new ManagedComputer();

//Get the SQL service and stop it if it's running
Service svc = c.Services["MSSQL$SQLEXPRESS"];
if (svc.ServiceState == ServiceState.Running)
{
    svc.Stop();
}

//Connect to the SQLEXPRESS instance and change the port
ServerInstance s = c.ServerInstances["MSSQL$SQLEXPRESS"];
ServerProtocol prot = s.ServerProtocols["Tcp"];
prot.IPAddresses[0].IPAddressProperties["TcpPort"].Value = "1433";

//Commit the changes
prot.Alter();

//Restart the service
svc.Start();

This assumes you have one IP address and not multiple addresses. If you have multiple you may need to modify the index into prot.IPAddresses[].