How can I change the SQL Server Configurations(TCP/IP, port number,IP Address) using SMO through C#

By 3rd December 2018C#

You can configure your sql server and these settings through c#.

first of all you have to add 2 references in your program, that will be available at nuGet package manager.

install them and add in program first.

Go to visual studio->tools->nuget package manager->package manager console

and than install below two references.

Install-Package Microsoft.SQLServer.SMO -Version 150.18068.0-xplat
Install-Package MicrosoftSQLServerSMO2014Wmi -Version 1.0.1

after that include namespaces.


using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.Wmi;
namespace ConsoleAppTestDemo
{
    class Program
    {
        static void Main(string[] args)
        {

            //Declare and create an instance of the ManagedComputer    
            ManagedComputer mc = new ManagedComputer("MAHAVEER");

            mc.ConnectionSettings.ProviderArchitecture = ProviderArchitecture.Use64bit; //if instance not found

            Service sqlservice = mc.Services["MSSQL$SQL"];

            if (sqlservice.ServiceState == ServiceState.Running)
            {
                sqlservice.Stop();
            }
            ServerProtocol tcpProtocols = mc.ServerInstances["SQLEXPRESS"].ServerProtocols["tcp"];
            ServerProtocol sharedmemoryProtocols = mc.ServerInstances["SQLEXPRESS"].ServerProtocols["sm"];

            //Enable TCP/IP
            tcpProtocols.IsEnabled = true;

            //Enable Shared Memory Protocol
            sharedmemoryProtocols.IsEnabled = true;

            //Set IP4 port
            tcpProtocols.IPAddresses["IP4"].IPAddressProperties["TcpPort"].Value = "1433";

            //Set IP4 Address
            tcpProtocols.IPAddresses["IP4"].IPAddressProperties["IpAddress"].Value = "127.0.0.1";


            //Set IPAll port
            tcpProtocols.IPAddresses["IPAll"].IPAddressProperties["TcpPort"].Value = "1433";

            //Done Changes
            tcpProtocols.Alter();
            sharedmemoryProtocols.Alter();

            sqlservice.Start();
}
}
}

7 Comments

  • Cialis says:

    This design is incredible! You obviously know how to keep a
    reader entertained. Between your wit and your videos, I was almost moved to start my own blog
    (well, almost…HaHa!) Fantastic job. I really loved what you had to say,
    and more than that, how you presented it. Too cool!

  • Anonymous says:

    Service sqlservice = mc.Services[“MSSQL$SQLEXPRESS”];

    i got this error

    for sql server 2012

    An unhandled exception of type ‘System.Runtime.InteropServices.COMException’ occurred in System.Management.dll
    Additional information: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

    • Veer Jangid says:

      Hi,

      – Check if you are able to Ping the Mirror SQL Server from Lync Front End server

      – Basics such as Windows Firewall turned Off or created exemption to allow the SQL Server traffic (TCP 1433 and UDP 1434 Inbound)

      – Read/Write permission for DB File share?

  • Anonymous says:

    An unhandled exception of type ‘System.Runtime.InteropServices.COMException’ occurred in System.Management.dll

    Additional information: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

  • This article provides clear idea in support of the new
    people of blogging, that actually how to do running a blog.

Leave a Reply