Retrieve SQL Protocol Status

Question

Using SQL query or WMI, I want to retrieve whether SQL protocol such as Named Pipes, TCP/IP is enabled or not.

I need to do this for SQL Server 2000, 2005, 2008.

Thank you.

Answer 1

The WMI Provider for Configuration Management Classes include the ServerNetworkProtocol WMI class, which list the protocols installed on an instance of Microsoft SQL Server, this class is located in the root\Microsoft\SqlServer\ComputerManagement10 namespace (for sql server 2008, in other versions you must modify the namespace).

Check this C# sample

using System;
using System.Collections.Generic;
using System.Management;
using System.Text;

namespace GetWMI_Info
{
    class Program
    {

        static void Main(string[] args)
        {
            try
            {
                string ComputerName = "localhost";
                ManagementScope Scope;                

                if (!ComputerName.Equals("localhost", StringComparison.OrdinalIgnoreCase)) 
                {
                    ConnectionOptions Conn = new ConnectionOptions();
                    Conn.Username  = "";
                    Conn.Password  = "";
                    Conn.Authority = "ntlmdomain:DOMAIN";
                    Scope = new ManagementScope(String.Format("\\\\{0}\\root\\Microsoft\\SqlServer\\ComputerManagement10", ComputerName), Conn);
                }
                else
                    Scope = new ManagementScope(String.Format("\\\\{0}\\root\\Microsoft\\SqlServer\\ComputerManagement10", ComputerName), null);

                Scope.Connect();
                ObjectQuery Query = new ObjectQuery("SELECT * FROM ServerNetworkProtocol");
                ManagementObjectSearcher Searcher = new ManagementObjectSearcher(Scope, Query);

                foreach (ManagementObject WmiObject in Searcher.Get())
                {
                    Console.WriteLine("{0,-35} {1,-40}","ProtocolDisplayName",WmiObject["ProtocolDisplayName"]);// String
                    Console.WriteLine("{0,-35} {1,-40}","Enabled",WmiObject["Enabled"]);// Boolean
                    Console.WriteLine("{0,-35} {1,-40}","InstanceName",WmiObject["InstanceName"]);// String
                    Console.WriteLine("{0,-35} {1,-40}","MultiIpConfigurationSupport",WmiObject["MultiIpConfigurationSupport"]);// Boolean
                    Console.WriteLine("{0,-35} {1,-40}","ProtocolName",WmiObject["ProtocolName"]);// String
                    Console.WriteLine();


                }
            }
            catch (Exception e)
            {
                Console.WriteLine(String.Format("Exception {0} Trace {1}",e.Message,e.StackTrace));
            }
            Console.WriteLine("Press Enter to exit");
            Console.Read();
        }
    }
}

This will return something like this

ProtocolDisplayName                 Shared Memory
Enabled                             True
InstanceName                        MSSQLSERVER
MultiIpConfigurationSupport         False
ProtocolName                        Sm

ProtocolDisplayName                 Named Pipes
Enabled                             False
InstanceName                        MSSQLSERVER
MultiIpConfigurationSupport         False
ProtocolName                        Np

ProtocolDisplayName                 TCP/IP
Enabled                             True
InstanceName                        MSSQLSERVER
MultiIpConfigurationSupport         True
ProtocolName                        Tcp

ProtocolDisplayName                 VIA
Enabled                             False
InstanceName                        MSSQLSERVER
MultiIpConfigurationSupport         False
ProtocolName                        Via