//
you're reading...
Database, IT-Center, SQL Server

Getting Client IP Address from SQL Server

First you must open blocked xp_cmdshell command

sp_configure 'show advanced options',1
GO
RECONFIGURE
GO
sp_configure xp_cmdshell,1
GO
RECONFIGURE

You can try this action to check your reconfigure is active and already use

EXEC master.dbo.xp_cmdshell 'ipconfig'

Second, please copy this procedure to getting IP address after SQL Server resulting client HOST NAME :

USE [dbname]
GO
/****** Object:  StoredProcedure [dbo].[getIPAdress]    Script Date: 12/05/2011 18:36:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[sp_getIPAdress]

AS

declare  @Hostname VARCHAR(255)
set @Hostname=(select HOST_NAME())
SET NOCOUNT ON
CREATE TABLE #Results
(
Results VARCHAR(4000)
)

DECLARE @Commandstring VARCHAR(300)

SET @Commandstring = 'ping ' + @Hostname

--Insert the results in a temporary table

INSERT INTO #Results
EXEC master..xp_cmdshell @Commandstring

--Get the response addresses
Select DISTINCT SUBSTRING(Results,12,CHARINDEX(':',Results)-12) AS HostIpAdress
from #Results
Where Results LIKE 'Reply From%'

Using this script to getting client IP Address:

 exec [sp_getIPAdress]

NEW UPDATE INFO
______________________________________

SELECT hostname, net_library, net_address
FROM sys.sysprocesses WHERE spid = @@SPID

other script :

SELECT  CONNECTIONPROPERTY('net_transport') AS net_transport,
CONNECTIONPROPERTY('protocol_type') AS protocol_type,
CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
CONNECTIONPROPERTY('local_net_address') AS local_net_address,
CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
CONNECTIONPROPERTY('client_net_address') AS client_net_address

Or this

SELECT  *
FROM    sys.dm_exec_connections
WHERE   session_id = @@SPID

About berbagisolusi

Berbagi merupakan sebuah bentuk simbol keikhlasan untuk membantu dan menolong, sedangkan solusi adalah cara menyelesaikan masalah. Setiap manusia pasti mengalami masalah, tetapi kita tidak perlu mengalami masalah yang sama jika orang lain pernah mengalami dan kita tahu hal tersebut.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 9 other followers

December 2011
M T W T F S S
« Nov   Jan »
 1234
567891011
12131415161718
19202122232425
262728293031  

Archives

Web Statistic

Blog Stats

  • 149,580 hits
%d bloggers like this: