obtaining the ip address of connection

any one know how the get the ip address of a source connection to my sql
server using a query/stored procedure/ex stored procedure etc.
essentially to ip address of the "host" process
thanksI don't if IP is possible, but system_user returns the Windows
Authentication domain\user, host_name() returns the workstation name, and
host_id() returns workstation id.
"Matthew Kempf" <mpkempf@.gmail.com> wrote in message
news:OeeW$6ijFHA.4000@.TK2MSFTNGP12.phx.gbl...
> any one know how the get the ip address of a source connection to my sql
> server using a query/stored procedure/ex stored procedure etc.
> essentially to ip address of the "host" process
> thanks
>|||Note that you can't always derive an IP address from a host name, but in an
enclosed environment, this might work for you:
declare @.cmd varchar(255)
set @.cmd = 'ping '+HOST_NAME()
create table #foo (l VARCHAR(8000))
set nocount on
insert #foo exec master..xp_cmdshell @.cmd
select substring
(
l,
charindex('[', l)+1,
charindex(']', l)-charindex('[',l)-1
)
from #foo
WHERE l like 'Pinging%'
drop table #foo
"Matthew Kempf" <mpkempf@.gmail.com> wrote in message
news:OeeW$6ijFHA.4000@.TK2MSFTNGP12.phx.gbl...
> any one know how the get the ip address of a source connection to my sql
> server using a query/stored procedure/ex stored procedure etc.
> essentially to ip address of the "host" process
> thanks
>|||Aaron Bertrand [SQL Server MVP] wrote:
> Note that you can't always derive an IP address from a host name, but
> in an enclosed environment, this might work for you:
>
> declare @.cmd varchar(255)
> set @.cmd = 'ping '+HOST_NAME()
> create table #foo (l VARCHAR(8000))
> set nocount on
> insert #foo exec master..xp_cmdshell @.cmd
> select substring
> (
> l,
> charindex('[', l)+1,
> charindex(']', l)-charindex('[',l)-1
> )
> from #foo
> WHERE l like 'Pinging%'
> drop table #foo
>
Good idea. You'll get better performance if you limit the number of echo
requests to 1 with the -n parameter:
declare @.c nvarchar(255)
set @.c = 'ping ' + host_name() + ' -n 1'
exec master..xp_cmdshell @.c
David Gugick
Quest Software
www.imceda.com
www.quest.com|||> Good idea. You'll get better performance if you limit the number of echo
> requests to 1 with the -n parameter:
Good catch. Though I have to wonder if this is a one-time thing or if we
plan on putting this into production. =)|||thanks,
the problem is I want the closed environment to be able to access, but
certain connections from the firewall not to be able to. I have replication
coming in over the firewall (connection I want), but do not want someone
just trying to launch the app (unwanted connection) from across the
internet, if I knew the ip of the connection this could be accomplished, but
it looks like so far it can't be done.........
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:erKHYNjjFHA.2152@.TK2MSFTNGP14.phx.gbl...
> Aaron Bertrand [SQL Server MVP] wrote:
> Good idea. You'll get better performance if you limit the number of echo
> requests to 1 with the -n parameter:
> declare @.c nvarchar(255)
> set @.c = 'ping ' + host_name() + ' -n 1'
> exec master..xp_cmdshell @.c
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Assuming Windows Authentication, to verify a that a connection is internal,
all you need is to check the host name of the connection. Also, system_user
will return the domain\user like: Cerebrus\jturner
Is resolving to a specific IP really necessary?
Also change the default listening port, remove support for unneeded
protocols, etc.
"Matthew Kempf" <mpkempf@.gmail.com> wrote in message
news:OpUktQjjFHA.1480@.TK2MSFTNGP10.phx.gbl...
> thanks,
> the problem is I want the closed environment to be able to access, but
> certain connections from the firewall not to be able to. I have
> replication coming in over the firewall (connection I want), but do not
> want someone just trying to launch the app (unwanted connection) from
> across the internet, if I knew the ip of the connection this could be
> accomplished, but it looks like so far it can't be done.........
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:erKHYNjjFHA.2152@.TK2MSFTNGP14.phx.gbl...
>|||So what is your protection doing inside the database? Don't you think
Windows (or another application) will be better at analyzing incoming
connections, trying to PREVENT your app from being called, than from the
database merely responding to the fact that your firewall has been breached
and the app has been successfully called?
Again, you seem to be protecting one morsel of cheese from the mice, but
letting them have their way with the rest of the fridge...
"Matthew Kempf" <mpkempf@.gmail.com> wrote in message
news:eBpkbZjjFHA.1048@.tk2msftngp13.phx.gbl...
> it doesn't
> i have satellites and i don't want to try to launch the program.... i
> just know they will try.
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:evZZeWjjFHA.1444@.TK2MSFTNGP10.phx.gbl...
>|||the firewall is fine; the security is fine
i have an application that makes an encrypted connection to an sql server
located at a satellite. the satellite uses transactional replication over
ssl to push a copy of the database to the host so reporting can be run; so
the only connection that can be made to the sql server is that application
or if you know the sa password or application userid and password. the
problem is if someone launches that application from the satellite and makes
a connection to the firewall (not violating any security). what i'm
attempting to do is write the remote application to have sql server check to
see what ip address the application is connecting from. if the application
sees that it attempting a connection from the firewall ip address then the
app will shutdown. it needs to do this because it will break replication if
this occurs, and a snapshot merge of 24 8gb databases over the internet
sucks royally.
"Aaron Bertrand [SQL Server MVP]" <ten.xocdnartreb.noraa> wrote in message
news:%23$gVjdjjFHA.3336@.TK2MSFTNGP10.phx.gbl...
> So what is your protection doing inside the database? Don't you think
> Windows (or another application) will be better at analyzing incoming
> connections, trying to PREVENT your app from being called, than from the
> database merely responding to the fact that your firewall has been
> breached and the app has been successfully called?
> Again, you seem to be protecting one morsel of cheese from the mice, but
> letting them have their way with the rest of the fridge...
>
>
> "Matthew Kempf" <mpkempf@.gmail.com> wrote in message
> news:eBpkbZjjFHA.1048@.tk2msftngp13.phx.gbl...
>|||> see what ip address the application is connecting from. if the
> application sees that it attempting a connection from the firewall ip
> address then the app will shutdown.
Can you explain "the application"? Is this an EXE, a service, or ...?