sql server net_address

Question

I have 2 applications in IIS which are calling same database. When I am executing the stored procedure which has below query, it returns 2 net_addresses every time.

These two address.

Application 1 has net_address 001914828D7EEE

Application 2 has net_address 0038B4A5DEC333

My question is why net_addresses are not same although those application are on same machine :)

P.S: Database is on other server.

SELECT  hostname,
        net_library,
        net_address,
        client_net_address
FROM    sys.sysprocesses AS S
INNER JOIN    sys.dm_exec_connections AS decc ON S.spid = decc.session_id
WHERE   spid = @@SPID

Output:

hostname | net_library | net_address | client_net_address
PIERRE   | TCP/IP      | 0090F5E5DEFF| 10.38.168.5

Answer 1

1. If the net_Address starts with 00 the program which opened the connection is running in a simple environment or is a regular connection. If you have letter is because the connection was opened from SQl Analyzer or a server which shared sessions. Like using Terminal server or Remote desktop connection.

000EA61DBEFF MICHELENEW

B7D36284FF31 MICHELENEW

The "good" MAC address is 00-0E-...

2. How to map back the net_Address (MAC) to IP. In any domain controller from the DOS command type:

ARP -a

look for your MAC address in the list. I know this is a pain but the point is that U can get the info from DHCP. Maybe a script could help. Remembert ARP is a cache list but it helps.

3. From SQL Server type select net_address,loginame,hostname nt_username, * from master..sysprocesses order by net_address It can help you to figure it our who is using the connection.

Source of answer