change tcp/ip port for sql server express using C#

Question

I am deploying SQL Server Express with my application and I need to have C# code that changes the TCP/IP properties without changing them in SQL Server Configuration Manager.

The following code stops SQL Server then it changes the tcp port to 1433 and then starts SQL Server again. The code works fine on a Windows XP machine; however, it errors out on a Windows 7 machine. The error occurs on the prot.Alter(); line.

try
{
    Service Mysvc = mc.Services["MSSQL$SQL" + machineName];

    if (Mysvc.ServiceState == ServiceState.Running)
    {
       MessageBox.Show("Stopping Service","PMP");
       Mysvc.Stop();
    }

    ServerInstance s = mc.ServerInstances["SQL" + machineName];
    ServerProtocol prot = s.ServerProtocols["Tcp"];
    prot.IPAddresses[0].IPAddressProperties["TcpPort"].Value = "1433";
    prot.Alter();

    MessageBox.Show("Starting Service","PMP");
    Mysvc.Start();
}
catch (Exception e)
{
    MessageBox.Show("Error in setting TCP Port " + e.ToString(),"PMP");
}

The following error message is what I receive when I run it on a Windows 7 machine:

SqlServer.Management.Smo.FailedOperationException: Alter failed. ---> Microsoft.SqlServer.Management.Smo.FailedOperationException: SetStringValue failed for ServerIPAddress 'IPAll'. ---> System.NullReferenceException: Object reference not set to an instance of an object.
at System.Management.ManagementObject.MapOutParameters(Object[] args, ManagementBaseObject >outParams, IWbemClassObjectFreeThreaded outParamsClass)
at System.Management.ManagementObject.InvokeMethod(String methodName, Object[] args)
at Microsoft.SqlServer.Management.Smo.Wmi.WmiSmoObject.InvokeMgmtMethod(ManagementObject mo, >ManagementOperationObserver observer, String methodName, Object[] parameters)

--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Smo.Wmi.WmiSmoObject.InvokeMgmtMethod(ManagementObject mo, >ManagementOperationObserver observer, String methodName, Object[] parameters)
at Microsoft.SqlServer.Management.Smo.Wmi.WmiSmoObject.AlterProtocolProperties (ProtocolPropertyCollection protocolProperties)
at Microsoft.SqlServer.Management.Smo.Wmi.ServerIPAddress.AlterImplWorker()
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Smo.Wmi.ServerIPAddress.AlterImplWorker()
at Microsoft.SqlServer.Management.Smo.Wmi.ServerProtocol.AlterImplWorker()
at Microsoft.SqlServer.Management.Smo.Wmi.ProtocolBase.Alter()
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Smo.Wmi.ProtocolBase.Alter()

Well, when you call something like this: Service Mysvc = mc.Services["MSSQL$SQL" + machineName];, you should always check whether the value returned (Mysvc) is really != null and not just call other methods on it! Most likely, your SQL Server service has a different name on Windows 7, and thus this call doesn't return anything, but you're then calling Mysvc.ServiceState on it... — marc_s
– marc_s, Commented Apr 10, 2013 at 16:02

Brock Hensley · Accepted Answer · 2014-05-06 19:48:30Z

1

UAC is the issue here. Run as Administrator will resolve the issue.

answered May 6, 2014 at 19:48

Brock Hensley

3,6652 gold badges31 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chris Schiffhauer · Accepted Answer · 2016-02-27 19:13:39Z

The Service class does not easily allow you to wait for the service to start or stop before moving on to the next actions. As your code stands, the service is sent a Stop command but continues executing before the service is stopped.

You can resolve this by refactoring to the ServiceController class:

using Microsoft.SqlServer.Management.Smo.Wmi;

const string instanceName = "SQLEXPRESS";

var managedComputer = new ManagedComputer();

var serviceController = new ServiceController(string.Concat("MSSQL$", instanceName));

var serverInstance = managedComputer.ServerInstances[instanceName];

var serverProtocol = serverInstance?.ServerProtocols["Tcp"];

var ipAddresses = serverProtocol?.IPAddresses;

if (ipAddresses != null)
{
    for (var i = 0; i < ipAddresses?.Count; i++)
    {
        var ipAddress = ipAddresses[i];

        if (!string.Equals(ipAddress.Name, "IPAll"))
        {
            continue;
        }

        if (serviceController.Status == ServiceControllerStatus.Running)
        {
            serviceController.Stop();

            serviceController.WaitForStatus(ServiceControllerStatus.Stopped);
        }

        ipAddress.IPAddressProperties["TcpDynamicPorts"].Value = "0";
        ipAddress.IPAddressProperties["TcpPort"].Value = "1433";

        serverProtocol.Alter();

        break;
    }
}

if (serviceController.Status == ServiceControllerStatus.Running)
{
    return;
}

serviceController.Start();

serviceController.WaitForStatus(ServiceControllerStatus.Running);

Collectives™ on Stack Overflow

change tcp/ip port for sql server express using C#

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related