2

Was wondering if it was possible to get the mac address of the server using a stored procedure? I did some searching for a while but found nothing. This is using SQL2008.

Update
Unfortunately the answer that uses newsequentialid() can return the wrong MacAddress if there is VMWare on the server.

Donny V.
  • 22,248
  • 13
  • 65
  • 79

6 Answers6

5

A somewhat round about method!

declare @t table
(
i uniqueidentifier default newsequentialid(),
m as cast(i as char(36))
)

insert into @t default values;

select
    substring(m,25,2) + '-' + 
    substring(m,27,2) + '-' + 
    substring(m,29,2) + '-' +
    substring(m,31,2) + '-' +
    substring(m,33,2) + '-' +
    substring(m,35,2) AS MacAddress
FROM @t
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • It works like a charm but I still don't understand how its getting it. I get it, newsequentialid uses mac addresses. Thank you by the way. – Donny V. Aug 03 '10 at 18:53
  • 1
    Unfortunately newsequentialid() can return the wrong MacAddress if there is VMWare on the server. – Donny V. Aug 04 '10 at 20:11
  • @Donny - Ah to be honest I felt a bit uncomfortable relying on that anyway. I guess the algorithm could change without warning at some point in the future. – Martin Smith Aug 04 '10 at 20:19
  • @MartinSmith, I followd you query but yesterday it was giving me the value "85-61-27-27-FF-A7" and now when i checkd it, it generates "B8-CA-3A-D0-2C-6F". What is the problem? Is there anything that can be persistent throughout? – CodeGenius Dec 12 '16 at 20:10
2

I would guess that you'd need to execute a shell command from SQL to get the MAC address. If I recall correctly, you have to turn on the execute shell command option before you can use it. Then, you could run "getmac" to retrieve a list of MAC address for the interfaces on the server. You'd have to work your way through the text returned, but that shouldn't be too hard.

Doug Dawson
  • 1,254
  • 2
  • 21
  • 37
1

sys.sysprocesses, net_address column. For my connection from SSMS it's 795C70BAD9B0

There is no equivalent in sys.dm_exec_connections (net_address is IP address not MAC address and is 111.222.111.222 from my SSMS). In fact there is no sys.sysprocesses equivalent either

So you can just query sys.sysprocesses in your own stored proc...

gbn
  • 422,506
  • 82
  • 585
  • 676
  • 1
    But that's the address of a client connecting to the server, which is not necessarily connecting from the same machine. As far as I can see, internal system processes don't have that column filled. – James Curran Aug 03 '10 at 16:30
0

declare @macadd nvarchar(50) select @macadd=net_address from master.dbo.sysprocesses where program_name like 'SQLAgent%' and hostname=SERVERPROPERTY('MachineName') and net_address<>'000000000000' --getting the MAC address set @macadd=SUBSTRING(@macadd,1,2)+'-'+SUBSTRING(@macadd,3,2)+'-'+SUBSTRING(@macadd,5,2)+'-'+SUBSTRING(@macadd,7,2)+'-'+SUBSTRING(@macadd,9,2)+'-'+SUBSTRING(@macadd,11,2) --To include '-' between each two characters of MAC address select @macadd

Zo Has
  • 12,599
  • 22
  • 87
  • 149
prasunTheGhosh
  • 1
0
DECLARE @IP_Address varchar(20);
DECLARE @mac_Address varchar(20);

SELECT @IP_Address=client_net_address,@mac_Address=net_address 
    FROM sys.dm_exec_connections c
    join sys.sysprocesses p on c.session_id=p.spid
    WHERE c.Session_id = @@SPID
davchino
  • 1
  • it's exactly as: select net_address from sysprocesses where spid = @@SPID . This won't give the MAC address but some other net address (I checked) – Shai Alon Feb 25 '16 at 15:10
0

Well, since in Sql Server 2008, a Stored proc can contained managed code, it should be possible, however, you'd technically be running a c#/vb app which was launched by SqlServer, rather than truly getting the info from a stored proc, although that more of a theoric rather than practical difference.

James Curran
  • 101,701
  • 37
  • 181
  • 258
  • 2
    Managed procedures *are* stored procedures, the term technically covers both T-SQL and CLR. And there is no 'app launched by SQL Server' either, SQL Server *is* a CLR host itself, it does not launch apps to run managed code. – Remus Rusanu Aug 03 '10 at 16:55
  • Granted I phrased that badly. how 'bout "SQL doesn't enter into this solution, so it's not really a stored proc in the conventional sense, but it's run like one" – James Curran Aug 03 '10 at 17:05
  • Actually this doesn't work because you need to creat a SQLServerProject to deploy and debug CLR stored procedures. SQLServerProject doesn't support System.Managment. – Donny V. Aug 05 '10 at 12:23
  • You don't need System.Management. You want System.Net.NetworkInformation, which is available. – James Curran Aug 05 '10 at 16:44
  • Check out this question http://stackoverflow.com/questions/151231/how-do-i-get-the-local-network-ip-address-of-a-computer-programmatically-c (Skipped the accepted answer and read the next one or mine further down) – James Curran Aug 05 '10 at 16:48
  • I'm looking at the available references and I'm not seeing System.NET. – Donny V. Aug 12 '10 at 19:54
  • @Donny: System.Net.NetworkInformation is a namespace, not an assembly. It is contained in the System.dll assembly. – James Curran Aug 12 '10 at 20:04