How do I determine the IP number and TCP Port of a SQL instance

I'm trying to determine the specific IP address and the TCP port on which a SQL server is communicating.

As far as I can tell, I can't use SMO for this, since I need a method that is compatible with both SQL 2005 AND SQL 2000. Is there a work around or can I use DMO?

Since my target is to be able to use this code against multipel servers, it would be nice to find the method with the least overhead possible. I would also like to avoid being forced to read registries remotely.

--
Jakob BindsletLook at the ServerIPAddress and IPAddressProperty values within the Microsoft.SqlServer.Management.Smo.Wmi namespace.|||
http://sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/EntryID/19/Default.aspx

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de|||According to BOL (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/smo9/html/6527388b-26b5-4ebb-8a52-0cf4415f9134.htm) the Microsoft.SqlServer.management.Smi.Wmi namespace isn't supported on SQL Server 2000 and below.|||

I don't see why the SQL Server version has any bearing on what information WMI will provide. I'm trying to work out the details but have run into a bit of confusion regarding the IP Address, but I'm getting the Port information back. I've blogged about it at http://sqljunkies.com/WebLog/marathonsqlguy/archive/2006/11/30/25785.aspx if you're interested.

|||

Hi Jagoop,

Did you find a way to retrieve this information for both SQL 2000 as well as SQL 2005 instances?

Thanks,

|||AS WMI is not supported you will have to use the registry directly:

using Microsoft.Win32;

namespace SMOProject

{

class Program

{

static void Main(string[] args)

{

string SQLServerKey = @."SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib";

RegistryKey rk = RegistryKey.OpenRemoteBaseKey(RegistryHive.LocalMachine, "JensOne");

RegistryKey protocols = rk.OpenSubKey(SQLServerKey);

Console.WriteLine(SQLServerKey);

foreach(string s in protocols.GetSubKeyNames())

{

RegistryKey rkprotocol = protocols.OpenSubKey(s);

Console.WriteLine("\tProtocol: {0}",rkprotocol.Name );

foreach (string ValueName in rkprotocol.GetValueNames())

{

Console.WriteLine("\t\t[Type]:Value: [{0}]:{1}",ValueName, rkprotocol.GetValue(ValueName));

}

}

HTH, Jens K. Suessmeyer.

http.//www.sqlserver2005.de

|||

I'd prefer to use DMO, instead of using the registry for this. It will also take care of the instance names for you.

See http://msdn2.microsoft.com/en-us/library/ms134942.aspx (Registry2).

Note you need to have the updated DMO version installed that ships with SQL Server 2005 and is also available as a redist as well (see the FAQ of this group) -- that is, if you plan to use it with SQL Server 2000 and 2005.

I need to lookup, but if I recall correctly it will work for SQL Server 2005 as well, as we aimed at making the port of SQL-DMO clients from 2000 to 2005 totally transparent.

|||

Thanks Jens,

If you have multiple ip addresses listening on different ports, the above registry location doesn't show that information. I tried searching for these values in the registry but couldn't find it anywhere. I have only seen this informaiton ouput to the SQL Server logs during the startup process. Is it possible to retrieve this information?

PK

|||

This is in:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp

(For the first instance)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de