Allen White

Getting IP Address/Port information from SMO

In response to a question in the MSDN SMO forum yesterday I started playing around with the SMO WMI wrapper objects. The object is to find the IP address and port used for each of my servers. I started with my own workstation for my testing. The first thing I needed to do was to add the imports for the SMO WMI Namespace at the top of my program (Imports Microsoft.SqlServer.Management.SMO.Wmi). I then wrote the following code:

        Dim mcServer As ManagedComputer
        Dim strPhysName As String
        Dim strInstanceName As String
        Dim objServerInstance As ServerInstance
        Dim objProtocol As ServerProtocol
        Dim objProtocolProperty As ProtocolProperty
        Dim objIPAddress As ServerIPAddress
        Dim objIPAddressProperty As IPAddressProperty
        Dim file As System.IO.StreamWriter

        strSrvName = "MyServer\Instance"
        strPhysName = "MyServer"
        strInstanceName = "Instance"

        file = My.Computer.FileSystem.OpenTextFileWriter("C:\IPProperties.txt", True)

        'Get WMI Network values
        mcServer = New ManagedComputer(strPhysName)
        objServerInstance = mcServer.ServerInstances.Item(strInstanceName)
        objProtocol = objServerInstance.ServerProtocols.Item("Tcp")
        file.WriteLine("Protocol: " + objProtocol.Name)
        For Each objIPAddress In objProtocol.IPAddresses
            file.WriteLine("IPAddress: " + objIPAddress.Name + ", " + objIPAddress.IPAddress.ToString)

            For Each objIPAddressProperty In objIPAddress.IPAddressProperties
                file.WriteLine("IPAddressProperty: " + objIPAddressProperty.Name + ", " + objIPAddressProperty.Value.ToString)
            Next
        Next

        For Each objProtocolProperty In objProtocol.ProtocolProperties
            file.WriteLine("ProtocolProperty: " + objProtocolProperty.Name + ", " + objProtocolProperty.Value.ToString)
        Next

        file.Close()

The first thing the program does is open a text file for my returned properties. After that I instantiate a ManagedComputer object, set the ServerInstance object to the SQL Server instance I'm interested in, then grab the ServerProtocol object for the TCP/IP protocol (which is identified by the property name of "Tcp". Once I have that I iterate through the ServerIPAddress objects within the protocol, and iterate through the IPAdressProperty objects in each ServerIPAddress object. Once that's done I iterate through the ServerProtocol object's ProtocolProperty objects, and display the names and values of each of the properties for the overall TCP/IP protocol.

I did all this to discover what values are set so I can make targeted requests of WMI to get the IP address and port, as I mentioned before. The results I got back were interesting. First, here's the result of my ipconfig command:

Windows IP Configuration

Ethernet adapter Local Area Connection:
        Connection-specific DNS Suffix  . : ws.mydomain.com
        IP Address. . . . . . . . . . . . : 10.47.120.123
        Subnet Mask . . . . . . . . . . . : 255.255.0.0
        Default Gateway . . . . . . . . . : 10.47.2.1

Ethernet adapter Local Area Connection 2:

        Media State . . . . . . . . . . . : Media disconnected

When I looked at the result of my application, though, I saw these results:

Protocol: Tcp
IPAddress: IP1, 10.8.3.23
IPAddressProperty: Active, True
IPAddressProperty: Enabled, False
IPAddressProperty: IpAddress, 10.8.3.23
IPAddressProperty: TcpDynamicPorts, 0
IPAddressProperty: TcpPort, 
IPAddress: IP2, 192.168.1.100
IPAddressProperty: Active, True
IPAddressProperty: Enabled, False
IPAddressProperty: IpAddress, 192.168.1.100
IPAddressProperty: TcpDynamicPorts, 0
IPAddressProperty: TcpPort, 
IPAddress: IP3, 127.0.0.1
IPAddressProperty: Active, True
IPAddressProperty: Enabled, False
IPAddressProperty: IpAddress, 127.0.0.1
IPAddressProperty: TcpDynamicPorts, 0
IPAddressProperty: TcpPort, 
IPAddress: IPAll, 0.0.0.0
IPAddressProperty: TcpDynamicPorts, 3181
IPAddressProperty: TcpPort, 
ProtocolProperty: Enabled, True
ProtocolProperty: KeepAlive, 30000
ProtocolProperty: ListenOnAllIPs, True
ProtocolProperty: NoDelay, False

The IP address values on each of the three reported IP addresses don't match the IP address assigned on my network to my system. The Port my instance is using is in the 'IPAll' IPAddress in the TcpDynamicPorts property, so that's good, but I'm curious why the IP Address I get from ipconfig isn't returned in my application results.

I poked around the Configuration Manager and found that the Properties window for the TCP/IP protocol (under Network Configuration) matches the data coming from my application, but I really don't know where these values were established.

If anyone has a good idea where this is coming from, and maybe how I can get the ipconfig IP address back from SMO/WMI I'd appreciate it.

Allen