CODEWITHSUNDEEP
c#sql-server-2008tcp
Chuck
Chuck

Reputation: 31

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

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()

Upvotes: 3

Views: 2626

Answers (2)

Chris Schiffhauer
Chris Schiffhauer

Reputation: 17290

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);

Upvotes: 0

Brock Hensley
Brock Hensley

Reputation: 3645

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

Upvotes: 1

Related Questions