Retriving host IP Address via T-SQL

Question

Retriving host IP Address via T-SQL

Viewing 11 posts - 31 through 40 (of 40 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

There's no way for the SQL Server to know what USERNAME is set to at the calling clients workstation, unless that host sends that info along to SQL Server.

When you use integrated logins, this is what is passed along, hence it's there to be read.

But if you use standard logins, then that's not necessarily there.

The one thing that seems to be always passed to the server in terms of ID coming from the host machine, seems to be the MAC adress of the adapter.

master..sysporcesses.net_address

Then again USERNAME is just a variable, and it's free to change to whatever the client wants.

Try this from a promt: SET USERNAME=DonaldDuck

then see what your variable contains 😉

/Kenneth

Theo Jacobs Say Hey Kid Points: 694 More actions · Answer 2

Thanx Kenneth.

I was wondering if there might be a cmd command that can be run to check the username.

Maybe it could be derived by getting the HOST_NAME() to check the workstation name, going to that resource and checking the logon??

or is it wishful thinking... 😀

thx

Sergiy SSC Guru Points: 110206 More actions · Answer 3

Yes, it's possible.

But you need to grant administrative privileges to the account running SQL Server on all client machines. Existing and not compiled yet.

And you must make holes in all firewalls to give that Server user full access every client's computer.

_____________
Code for TallyGenerator

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

Well, 'set username' in a prompt returns the contents of USERNAME on the ws it's executed.

Problem is how to find that ws? And what about security? And then we have all those virtual thingies... Like Citrix. How do we know when we've reached the real 'enduser/client/workstation'..?

I think it's wishful thinking...

I think that if that clientlogin identity is really important, then the best thing would be to require that it would be passed along, especially if the SQL Server login used is a standard login.

Then pops the question how to pass it? And to what, where to catch it?

/Kenneth

Theo Jacobs Say Hey Kid Points: 694 More actions · Answer 5

That's what I thought. For now the client will have to be satisfied with workstation name as it was them that wanted std sql login instead of (our suggestion) using integrated security.

thanx again for all the input.

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 6

Theo Jacobs (10/10/2007)
That's what I thought. For now the client will have to be satisfied with workstation name as it was them that wanted std sql login instead of (our suggestion) using integrated security.

You should also probably point out to them that it is up to the client to send the correct hostname. The client isn't required to pass correct information and it's pretty trivial to forge it.

K. Brian Kelley
@kbriankelley

Theo Jacobs Say Hey Kid Points: 694 More actions · Answer 7

Theo Jacobs

Say Hey Kid

Points: 694

October 10, 2007 at 8:46 am

#740843

thx, will do.

phillipg 54222 Grasshopper Points: 13 More actions · Answer 8

Hey guys,

Any ideas how to do this without using xp_cmdshell? I have a couple of servers where this is turned off for security reasons...

🙁

Phillip

Lowell SSC Guru Points: 323504 More actions · Answer 9

with 2005 and above, this got a lot easier; there are DMV's that have the connecting IP address now;

here's an example:

--the auditing snippet below works fine in a

--login trigger,

--database trigger

--or any stored procedure.

SELECT

getdate() AS EventDate,

DB_NAME() AS DBName,

HOST_NAME() AS HostName,

APP_NAME() AS ApplicationName,

OBJECT_NAME(@@PROCID) AS ProcedureName,

USER_ID() AS Userid,

USER_NAME() AS UserName,

SUSER_ID() AS sUserid,

SUSER_SNAME() AS sUserName,

IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],

IS_MEMBER('db_owner') AS [Is_DB_owner],

IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],

IS_MEMBER('db_datareader') AS [Is_DB_Datareader],

ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],

client_net_address AS ipaddress,

auth_scheme AS AuthenticationType

FROM sys.dm_exec_connections where session_id = @@spid

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DanMaxic Grasshopper Points: 17 More actions · Answer 10

Hello Guys,

Please dont try use master.sys.xp_cmdshell, becouse of security reason it might be blocked.

u can use the DMV dm_exec_connections to get the info:

SELECT TOP 1 local_net_address FROM sys.dm_exec_connections WHERE local_net_address IS NOT NULL

Johan Bijnens SSC Guru Points: 135259 More actions · Answer 11

DanMaxic (12/13/2012)
Hello Guys,
Please dont try use master.sys.xp_cmdshell, becouse of security reason it might be blocked.
u can use the DMV dm_exec_connections to get the info:
SELECT TOP 1 local_net_address FROM sys.dm_exec_connections WHERE local_net_address IS NOT NULL

Thank you for the feedback !

Always nice to know how one could or should do it in the newer versions of SQLServer.

( this thread is SQL7 - SQL2000 related 😉 )

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me