Getting SQL Server NIC Mac Addresses in SP

How can I get the MAC Addresses of all the NICS in a SQL Server box
either through code (VB .Net) or a Stored Procedure?
I know the winmgmts is something that can allow this but I am not
familiar with SP programming, and think that is the best way to go
since I don't want to worry about authentication to the SQL box if I
attempt it with code, a SP can just be called cleanly and retrieve the
info.
Anyone ever try this or have a sample? Thanks a ton!Assuming winmgmts is a set of com objects have a look at:-
sp_OA*
<gfricke@.gmail.com> wrote in message
news:1130160778.628613.27460@.g14g2000cwa.googlegroups.com...
> How can I get the MAC Addresses of all the NICS in a SQL Server box
> either through code (VB .Net) or a Stored Procedure?
> I know the winmgmts is something that can allow this but I am not
> familiar with SP programming, and think that is the best way to go
> since I don't want to worry about authentication to the SQL box if I
> attempt it with code, a SP can just be called cleanly and retrieve the
> info.
> Anyone ever try this or have a sample? Thanks a ton!
>|||Code generated with WMI Code Generator (VB.NET)
Imports System
Imports System.Management
Imports System.Windows.Forms
Namespace WMISample
Public Class MyWMIQuery
Public Overloads Shared Function Main() As Integer
Try
Dim searcher As New ManagementObjectSearcher( _
"root\CIMV2", _
"SELECT * FROM Win32_NetworkAdapter")
For Each queryObj As ManagementObject in searcher.Get()
Console.WriteLine("--")
Console.WriteLine("Win32_NetworkAdapter instance")
Console.WriteLine("--")
Console.WriteLine("MACAddress: {0}",
queryObj("MACAddress"))
Next
Catch err As ManagementException
MessageBox.Show("An error occurred while querying for WMI
data: " & err.Message)
End Try
End Function
End Class
End Namespace
Regards
Jacek
"gfricke@.gmail.com" wrote:

> How can I get the MAC Addresses of all the NICS in a SQL Server box
> either through code (VB .Net) or a Stored Procedure?
> I know the winmgmts is something that can allow this but I am not
> familiar with SP programming, and think that is the best way to go
> since I don't want to worry about authentication to the SQL box if I
> attempt it with code, a SP can just be called cleanly and retrieve the
> info.
> Anyone ever try this or have a sample? Thanks a ton!
>|||Thanks for the help with the VB side, now I am trying to replicate this
in a SP anyone have experience doing anything like this in a SP?|||Step 1. create vbs script (for example d:\a.vbs) with following code:
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2")
Set colItems = objWMIService.ExecQuery( _
"SELECT * FROM Win32_NetworkAdapterConfiguration",,48)
For Each objItem in colItems
if Len(Trim(objItem.MACAddress))>0 Then
Wscript.Echo "MACAddress: " & objItem.MACAddress
end if
Next
Step 2. create stored procedure (T-SQL) :
create procedure getMAC
AS
create table #O(output varchar(2000))
INSERT INTO #O
exec
master..xp_cmdshell 'cscript.exe /nologo d:\a.vbs'
select * from #O
Regards
Jacek
"gfricke@.gmail.com" wrote:

> Thanks for the help with the VB side, now I am trying to replicate this
> in a SP anyone have experience doing anything like this in a SP?
>|||Ok so far everything has been working great, however now I want to do
the same task using just the GetObject("winmgmts:\\" & strComputer &
"\root\CIMV2") concept, not the System.Management objects which are
part of the .Net framework. This is because I want to implement it in
FoxPro eventually (v7 or 8) which don't have the ability to use .Net
dlls.
1. So can anyone accomplish the task of getting the MAC Addresses from
a REMOTE box (assuming the machine has authentication to the remote
box) using this approach?
2. Also, what level of access does a user account need to get the MAC
Address info on the remote box, I don't want to give them full Admin
access if it can be avoided, any ideas?
Again thanks for everything, the vbs, and System.Management methods
worked great, now my boss wants these other approaches :)