Solved

Get ip address of Delphi client calling a stored procedure

Posted on 2009-05-07
8
663 Views
Last Modified: 2012-05-06
I have a Delphi program which uses Sql Server 2008.  When it calls a stored procedure, I would like to get the ip address of the Delphi client (computer hosting the Delphi program) that called the stored procedure.  Instead of the client himself sending the ip-address as a parameter.  This is for security reasons.
Is this possible?

This doesn't help, it only gives the ip-address of the machine hosting the sql server.
select * from sys.dm_exec_connections
0
Comment
Question by:FridaA
  • 5
  • 3
8 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 24328343
0
 

Author Comment

by:FridaA
ID: 24334322
Thanks for the answer.

I can use
declare @host as varchar(20)
SET @host = (select host_name())
select @host

to get the host name, as suggested in your link, but I don't understand how to use the xp_cmdshell 'netstat -an'  command to get the ip-address.  Can I do this in SQL ?  pls provide sql code if you can
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24350519
Here's an example using netstat -n
For more info on how to use netstat, just run this on the Windows Command Line (CMD): netstat /?
 


 

declare @temptable table (

row varchar(2000)

)
 

declare @getipaddress table (

proto varchar(3),

localaddress varchar(20),

Foreignaddress varchar(20),

state varchar(20)

)
 

insert @temptable 

exec sp_executeSql N'xp_cmdshell  ''netstat -n'''  
 

insert @getipaddress 

select	left(ltrim(row),3) as proto,

		substring(ltrim(row), 8, 19) as localaddress,

		substring(ltrim(row), 31, 19) as Foreignaddress,

		right(row, charindex(' ', reverse(row))-1) as State

from @temptable

where left(ltrim(row),3) = 'TCP'
 

select * from @getipaddress

Open in new window

0
 

Author Comment

by:FridaA
ID: 24503314
Sorry for the later answer, I'm still waiting for an answer from the dba.  The problem is that I get an error executing:  
exec sp_executeSql N'xp_cmdshell  ''netstat -n'''

The error is:
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

And I'm waiting for the dba to answer if he can do anything, or if this is just not possible (for security reasons).

Not sure what to do in the meantime, I can't really accept a solution when I don't know if it works.  Do I just answer later when I find out?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 41

Accepted Solution

by:
ralmada earned 125 total points
ID: 24503687
To use my approach, you will definitively need to activate xp_cmdshell in the Surface Area Configuration or by executing this in SSMS:
 
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
0
 

Author Comment

by:FridaA
ID: 24503729
Thank you for this, I will send this to the DBA.  Now he has no excuse, everything done for him :)
0
 

Author Comment

by:FridaA
ID: 24683416
Thanks for the (possible) solution.
I'll accept your solution ralmada since I'm sure it works, but the DBA at my company said that it is not an option to activate xp_cmdshell  :/
So I guess I'll have to find another solution, or just not do this at all.
Thanks for your help, and sorry for the late response.
0
 

Author Closing Comment

by:FridaA
ID: 31579094
See my last comment about the solution.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now