Get SQL Server IP Address

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by sunshine » Get SQL Server IP Address

Get SQL Server IP Address

Rate Topic

Display Mode

Topic Options

Author

Message

sunshine-587009

Posted Monday, August 01, 2011 11:22 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 2:20 PM
Points: 360, Visits: 1,085

Comments posted to this topic are about the item Get SQL Server IP Address

¤ §unshine ¤

Post #1152292

Bertil Forsberg

Posted Tuesday, August 02, 2011 12:20 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, September 28, 2012 9:00 AM
Points: 11, Visits: 156

Hi all!

I suggest a little add on to this 'IP-script' as follows:

OR upper (ipLine) like '%IPv4 ADDRESS%'

Cheers,
Bertil F

Post #1152301

Peter E. Kierstead

Posted Tuesday, August 02, 2011 10:51 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 25, 2013 8:37 AM
Points: 163, Visits: 275

If you're running SQL Server 2005 or above you can use this (some shops disable xp_cmdshell):

Select
   client_net_address,local_net_address
from sys.dm_exec_connections
where session_id=@@SPId;

Limitation: You only get IP addresses if you're connective via TCP/IP. If you're local and using Shared Memory then those attributes don't exist. If you turn off Shared Memory (or any protocols except for TCP/IP) via Server Configuration Manager you will always get IP address for any connection using this technique.

PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

Post #1152739

sunshine-587009

Posted Wednesday, August 03, 2011 10:36 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 2:20 PM
Points: 360, Visits: 1,085

Awesome!Thanks!

¤ §unshine ¤

Post #1153994

vlad-548036

Posted Friday, September 30, 2011 1:09 PM

SSCommitted

Group: General Forum Members
Last Login: Monday, May 13, 2013 1:51 PM
Points: 1,825, Visits: 257

Select
client_net_address,local_net_address
from sys.dm_exec_connections
where session_id=@@SPId;

- This works
Yours does not on our cluster evironment (NULL)

create table #temp (ipLine varchar(200))
insert #temp
exec master..xp_cmdshell 'ipconfig'

Post #1183908

logicinside22

Posted Thursday, February 16, 2012 1:46 PM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 10:59 AM
Points: 280, Visits: 1,029

Hi
How i can get all remote servers IP add and host name ? you have any updated script?

Aim to inspire rather than to teach.
SQL Server DBA

Post #1253416

« Prev Topic | Next Topic »

Permissions