Solved

SQL Stored Procedure --> Getting the server to send its MAC address to a client (trigger)

Posted on 2004-09-10
22
898 Views
Last Modified: 2008-02-01
Here's the problem......

I have a MS Access Application that connects to a SQL database on a remote server. What has to happen is that when the connection is made to the the server, the server needs to send back its MAC address to the application so I can compare it to the MAC address I have stored in a local MS access table.

Is there some kind of trigger / SP that will do this for me?

Thanks
0
Comment
Question by:b_o_b
  • 8
  • 8
  • 3
22 Comments
 
LVL 21

Expert Comment

by:mastoo
ID: 12026758
You'd have to probably run a proc to retrieve sysprocesses.net_address where spid = @@SPID from the master database.  I believe that is the client MAC address.
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12026773
There is no T-SQL function for this, and the difficulty is compounded by the fact than many SQL Servers use mulitple network cards.

Here's a basis for one solution: Create a program that loops through a computer's LANA's, uses each LANA in an NCBASTAT command to get the MAC address for each network card, and then connect to the database and store the values in a configuration table of your own.

Here's some sample code to get started: http://support.microsoft.com/default.aspx?scid=kb;en-us;118623
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12026833
I happily stand corrected (and better educated!). sysprocessess does indeed store the MAC addresses. Just be careful about one server having more than one...

Thanks, mastoo!
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 15

Expert Comment

by:jdlambert1
ID: 12026945
As simple as:

SELECT Distinct net_address
FROM master.dbo.sysprocesses
WHERE net_address <> ''

I didn't know you could do that!  I love EE!
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12026946
ah.   one slight flaw,     "the server needs to send back its MAC address to the application "   of course net_address in sysprocesses is the client macaddress.
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12026976
I tested the query using FROM master.dbo.sysprocesses against a couple of my systems, and it only returned the server's MAC addresses, so using the master table seems to be the key.
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12027000
Okay, I'm still ignorant. sysprocesses only exists in master, so how come I'm not getting client addresses?
0
 
LVL 18

Accepted Solution

by:
ShogunWade earned 150 total points
ID: 12027001
BOL:

"net_address nchar(12) Assigned unique identifier for the network interface card on each user's workstation. When the user logs in, this identifier is inserted in the net_address column. "
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12027051
jd,  i dont suppose you were lookin at the process info for any sql agent tasks ?
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12027505
Try this:





CREATE PROCEDURE usp_ServerMacs AS

create table #t (a varchar(500))
insert #t
exec xp_cmdshell 'ipconfig /all'
select LTRIM(RTRIM(SUBSTRING(a,CHARINDEX(':',a)+1,200))) MacAddress from #t where a like '%physical address%'
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12027745
I'm not only ignorant, I'm stupid. I ran it on development systems, not production boxes. The only connections were from the tools and services running on the dev systems.  Duh.

@@spid doesn't seem to help, as it "Returns the server process identifier (ID) of the current user process."

The command shell you posted worked for me, Shogun. I'm gonna bookmark this Q in case I ever need that. :)
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12027806
no worries.    one slight thing is that the format returned has -'s in which is a a touch differnet than the net_address in sysprocesses.   but of course its trivial to remove those.   but I think it should return all macs.  cant really test that here though cos I've only got a single card :(
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12027874
I tested it with multiple cards and it works perfectly!

You want us to take up a collection to get you a second card, Shogun?
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12027902
Na, Ive got no free slots in this 8088 machine ;)
0
 
LVL 21

Expert Comment

by:mastoo
ID: 12029324
Ah, as you all observed I read the original question too quickly and thought the question was looking for the client MAC.  I like the shell approach above.  I think you ccould also use the sysprocesses method but just go after one of the sqle entries - since sqle is running on your sql server box (assuming you have sqle running).
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12042603
only snag there is that the net_address is supressed for system processes, otherwise you could have easily done this by taking the net_address of SPID 1
0
 
LVL 21

Expert Comment

by:mastoo
ID: 12044813
I forgot sometimes people have trouble reading my mind.  By sqle I meant whatever account you have Sql Agent running under, assuming it is not just using the local system account.  It runs on the same box and shows up with the server's mac.  But the shell method is probably better as it doesn't rely on undocumented behavior such as counting on Sql Server storing the MAC in this column.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12296075
I think my solution gives what was asked
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12301920
All points should go to Shogun, IMHO.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query Syntax 17 36
Find SQL query used by application 3 20
Unable to Uninstall Visual Studio 2015 7 27
SQL Syntax: How to force case sensitive query? 2 30
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question