Post reply

Fetching the IP address of SQL Server instance.

  • April 1, 2009 at 5:42 am

    #129013

    I want to find the IP address of the machine on which an instance of SQL Server is running.

    select SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

    returns the NetBIOS name but not the IP.

    I need the IP because I am in an environment where lookup by IP address of the server will work but lookup by NetBIOS name won't.

    Is there any solution?

    Regards

    Nabeel Mukhtar

  • April 1, 2009 at 7:49 am

    #969832

    Do you need to get the IP via SQL?

    I found this on the net:

    EXEC Master.dbo.xp_cmdshell 'ipconfig'

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62872

  • April 1, 2009 at 7:56 am

    #969843

    the old chicken and the egg; which do you need first?

    you need to connect before you can run a query to try and find the IP,

    ....but you need the IP before you can connect to run a query....

    the answer is to find the IP outside of SQL i think; maybe you need to explain your requirement?

    searching my registry on my server, i found the my current IP address for my machine in this reg key:

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\{0658EA3B-FFCE-4EDB-9DB1-21D5B98256B7}\Parameters\Tcpip

    inside the DWORD key DhcpIPAddress

    so you could use TSQL to read the registry; but i don't know what service is "{0658EA3B-FFCE-4EDB-9DB1-21D5B98256B7}"

    much better off using the network to ping/nslookup or using WINS to resolve the NetBios or DNS name.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 1, 2009 at 8:46 am

    #969922

    Hi

    You can check out the below link, tho it is from front end application.

    http://www.codeproject.com/KB/database/locate_sql_servers.aspx

    Btw can you tell us what is requirement!

    Thanks -- Vijaya Kadiyala

    http://www.dotnetvj.vom

    Thanks -- Vijaya Kadiyala
    www.dotnetvj.com
    SQL Server Articles For Beginers


  • April 1, 2009 at 9:59 am

    #970030

    Ok. I need to elaborate a little.

    I have a clustered environment in which an SQL server is setup on two nodes. I want to connect to the active node through telnet etc. I can get the name of the active node from SERVERPROPERTY('ComputerNamePhysicalNetBIOS') but this name is not accessible/resolvable from the remote machine although its IP is. So basically I need to find the IP of the node on which SQL server is running. The technique should also work on non-clustered environment. I connect to the SQL server through the virtual IP which is known. But I need the node IP. And I can't use xp_cmdshell.

    I hope I have made it clear now. Any idea how it can be done?

    Thanks for your responses.

    Nabeel

  • April 1, 2009 at 10:20 am

    #970038

    If your cluster is setup correctly, you should be able to RDP or Telnet to the VirtualIP address which will direct you to the node that is currently hosting that instance of SQL Server.

    Are you saying this does not work?

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • April 1, 2009 at 10:22 am

    #970044

    BTW - why are you even using telnet? I would not even enable telnet on any of my SQL Servers in the first place.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • April 1, 2009 at 10:40 am

    #970063

    I totally agree, you should connect via the VIRTUAL NAME or VIRTUAL IP

    which goes to the ACTIVE node

    and telnet, really? what is that for?

    not Remote Desktop?

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005

  • April 1, 2009 at 11:48 am

    #970123

    Same question..why do you want to use Telnet.

    Thanks -- Vijaya Kadiyala
    www.dotnetvj.com
    SQL Server Articles For Beginers


  • April 1, 2009 at 11:27 pm

    #970378

    Further elaboration.

    I use telnet to gather some machine/OS statistics because I cant use xp_cmdshell. And I need to gather statistics programmatically (not manually) from both the nodes (active and passive) so I cannot merely telnet the virtual address because it will only give me the statistics of active node.

    Assuming that I have the IPs for both the nodes and I have statistics for both of them. Now from SQL Server I need to find out on which node its currently active so that I can use the statistics of that node to do some analysis (again programmatically). It seems that all it returns is the Net BIOS name, which I cannot map to the IP because its not resolvable.

    So my question is: Is there any way to get the IP of the physical node on which SQL Server is currently running?

    Thanks again for all your responses.

    Regards

    Nabeel

  • April 2, 2009 at 7:41 am

    #970676

    use ping servername

    Then the ip address is displayed.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply