Handle on Host Machine.

Question

Post reply

Handle on Host Machine.

Nitin Rana-182398

Say Hey Kid

Points: 687
More actions
November 3, 2004 at 10:55 am

#162511

We have a Web application that uses a common ID to connect to the DB and then we have secutiy implemented based on the user's logged on Id. When we check for sp_who, it always shows the same server name as the HOST in sp_who which ofcourse is right. Is there anyway I can get into more depth as to what thread is being used by which client machine? Is it possible to do it??

Application is intranet so getting a handle on machine name or IP address should help.

Thanks in advance,

Viewing 3 posts - 1 through 2 (of 2 total)

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

lholman SSCrazy Points: 2810 More actions · Answer 1

nitin,

just to clarify, when you say the following:

"We have a Web application that uses a common ID to connect to the DB and then we have secutiy implemented based on the user's logged on Id"

do you mean that your webserver is connecting to SQL Server through one login, with a corresponding user in the database, then security is managed internally within the application? unless you are allowing users to individually fire requests to SQL Server through Internet Explorer using trusted connections (windows authentication) then as far as SQL Server is concerned requests are being made through the common login connection between the webserver and the SQL Server.

as you mentioned it is an intranet application you should be able to grab the users windows login and/or hostname through script, (i.e. VBScript, Javascript etc..) you could then save these to a table within the database when a user logs in, along with a time stamp and any other relevant info/activity (kind of like hand rolling your own audit table). another option is to try and sift through the webserver logs, believe you me this can be very daunting (especially in IIS).

hope i've understood what you're trying to achieve?

lloyd

vadba SSChampion Points: 11133 More actions · Answer 2

If you aren't using the program_name column in master..sysprocesses (i.e. explicitly setting it as part of your connection string), then you could use that to store your desired value.

Of course, that would be predicated on your ability to obtain the actual machine name/IP/user id before connecting to SQL Server.

You could then do the following:

PART 1 - Simple .asp code to connection to SQL Server, passing the user id as the program name

<%

Dim strLoginID

Dim strPassword

Dim strConnect

strLoginID = ""

strPassword = ""

strConnect = "Driver={SQL Server};Server=YourServer;UID=" + strLoginID + ";PWD=" + strPassword

Dim strClient

strClient = ...assign the actual machine anme/IP/user id here, somehow

strConnect = strConnect + "; APP=" + strClient

%>

PART 2 - Get the value from sysprocesses. Run this query (or sp_who2) instead of sp_who.

SELECT spid,

CONVERT(varchar(20), loginame) AS loginame,

CONVERT(varchar(20), hostname) AS hostname,

CONVERT(varchar(20), program_name) AS program_name_or_userid, -- your APP parm shows up here

login_time,

dbid

FROM sysprocesses

WHERE spid >= 50