|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
obtaining the ip address of connectionany 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 I 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. Show quote "Matthew Kempf" <mpke***@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 Show quote "Matthew Kempf" <mpke***@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:
Show quote > Note that you can't always derive an IP address from a host name, but Good idea. You'll get better performance if you limit the number of echo > 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 > requests to 1 with the -n parameter: declare @c nvarchar(255) set @c = 'ping ' + host_name() + ' -n 1' exec master..xp_cmdshell @c > Good idea. You'll get better performance if you limit the number of echo Good catch. Though I have to wonder if this is a one-time thing or if we > requests to 1 with the -n parameter: 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............ Show quote "David Gugick" <david.gugick-nospam@quest.com> wrote in message news:erKHYNjjFHA.2152@TK2MSFTNGP14.phx.gbl... > 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 > the problem is I want the closed environment to be able to access, but Why on earth does the firewall accept all incoming connections? Putting > 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 this kind of protection in your T-SQL code is like having the bank's alarm triggered to only one safety deposit box. it doesn't
i have satellites and i don't want to try to launch the program..... i just know they will try. Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:evZZeWjjFHA.1444@TK2MSFTNGP10.phx.gbl... >> 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 > > Why on earth does the firewall accept all incoming connections? Putting > this kind of protection in your T-SQL code is like having the bank's alarm > triggered to only one safety deposit box. > 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... Show quote "Matthew Kempf" <mpke***@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 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 >> >> Why on earth does the firewall accept all incoming connections? Putting >> this kind of protection in your T-SQL code is like having the bank's >> alarm triggered to only one safety deposit box. >> > > 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. Show quote "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" <mpke***@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 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 >>> >>> Why on earth does the firewall accept all incoming connections? Putting >>> this kind of protection in your T-SQL code is like having the bank's >>> alarm triggered to only one safety deposit box. >>> >> >> > > > see what ip address the application is connecting from. if the Can you explain "the application"? Is this an EXE, a service, or ...?> application sees that it attempting a connection from the firewall ip > address then the app will shutdown. compiled vb6 .exe client server app
Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:uGC5gujjFHA.2852@TK2MSFTNGP14.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 ...? > 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. Show quote "Matthew Kempf" <mpke***@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... >> 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 > >  |
|||||||||||||||||||||||
Other interesting topics