Have Geek Brain

Change TCP Server SQL SERVER EXPRESS 2008 from Microsoft.SqlServer.Management.Smo

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

+2


a source to share


2 answers


To do this, you need to use the WMI provider that comes with SMO. Add links 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 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 a single IP address, not multiple addresses. If you have multiple, you may need to change the index in prot.IPAddresses [].

+1


a source


usually you reassign the port through your UI

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



However, I think it is just stored in the registry, so the easiest way is to change it once in the UI to a specific number (like 12345) and then look in HKLM \ SOFTWARE \ Microsoft \ Microsoft SQL Server for that number. So I am showing myself that the key is HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Microsoft SQL Server \ MSSQL10.MSSQLSERVER \ MSSQLServer \ SuperSocketNetLib \ Tcp \ IP1 with a REG_SZ value (seems weird) named TcpPort.

If you mean (or should also know) how to connect to the default instance when it is on a non-standard port, just change the source from host to host, port (for example, change FOO to FOO, 12345)

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

+1


a source






More articles:


All Articles