Accessing SWebmObjectSet Objects

I'm working in an environment where domain structure and firewall rules only allow access to a SQL Server - including OS - via a SQL Server client connection. I'm attempting to collect various pieces of information for an inventory database that are not normally accessible through standard t-sql calls.

A specific example is collecting network adapter and IP information via WMI and sp_OAxxx procedures. I've been able to interface WMI and retrieve the SWebmObjectSet collection with the information I want, but I can't seem to get to the individual objects in the collection for two reasons. First, t-sql doesn't have any sort of "for each" construct that allows me to iterate through the objects. Second, the Item() method of SWebmObjectSet requires an object path that I haven't been able to enumerate.

I'm avoiding enabling xp_cmdshell in SQL 2005 so calls such as "ipconfig /all" are not at option at this point.

How can I access the individual objects in the collection via t-sql? Is there another technology I might use? Remember - I can only access via a standard SQL client.

Here is some code I've come up with so far.

-- INITIALIZE SCRIPT

DECLARE@.wmiLocatorINT,

@.wmiServicesINT,

@.wmiObjectSetINT,

@.wmiObjectCountINT,

@.wmiObjectINT,

@.wmiNetAdapterNameNVARCHAR(200),

@.wmiNetAdapterIPNVARCHAR(200),

@.loopIdxINT,

@.oleSourceNVARCHAR(500),

@.oldDescNVARCHAR(500),

@.rcBIGINT,

@.msgNVARCHAR(400)

-- INITIALIZE WMI COM OBJECTS

EXEC @.rc = master.dbo.sp_OACreate 'WbemScripting.SWbemLocator', @.wmiLocator OUTPUT

IF @.rc <> 0 BEGIN

PRINT 'Create WMI object failed'

RETURN

END ELSE BEGIN

EXEC @.rc = master.dbo.sp_OAMethod @.wmiLocator, 'ConnectServer', @.wmiServices OUTPUT, '.'

IF @.rc <> 0 BEGIN

EXEC master.dbo.sp_OADestroy @.wmiLocator

RETURN

END

END

-- COLLECT DESIRED DATA

EXEC@.rc = master.dbo.sp_OAMethod @.wmiServices, 'InstancesOf', @.wmiObjectSet OUTPUT, 'Win32_NetworkAdapterConfiguration'

EXEC@.wmiObjectCount= master.dbo.sp_OAGetProperty @.wmiObjectSet, 'Count', @.wmiObjectCount OUTPUT

SELECT@.loopIdx= 0

WHILE @.loopIdx < @.wmiObjectCount - 1 BEGIN

EXEC@.rc= master.dbo.sp_OAMethod @.wmiObjectSet, 'Item', @.wmiObject OUTPUT, @.loopIdx

IF @.rc <> 0 BEGIN

EXEC@.rc= master.dbo.sp_OAGetErrorInfo @.wmiObjectSet, @.oleSource OUTPUT, @.oldDesc OUTPUT

END ELSE BEGIN

EXEC@.rc= master.dbo.sp_OAGetProperty @.wmiObject, 'Caption', @.wmiNetAdapterName OUTPUT

EXEC@.rc= master.dbo.sp_OAGetProperty @.wmiObject, 'IPAddress', @.wmiNetAdapterIP OUTPUT

END

SELECT@.loopIdx= @.loopIdx + 1

END

-- CLEANUP

EXEC master.dbo.sp_OADestroy @.wmiServices

EXEC master.dbo.sp_OADestroy @.wmiLocator

Is it fair to assume that you have completely explored and discarded the various system metadata, security, stastical and configuration functions, as well as the ODBC functions?

Would it be possible to have a Windows Scheduler task that would freqently run, using SQLCmd.exe to populate a table in the server with the desired information?

|||

If you mean the SQL Server metadata, security, statistical and configuration functions, then yes I have. But it is entirely possible I missed something which is why I posted the question.

It is possible to use the Windows Scheduler as you noted. I'm not a fan of installing DBA utilities on every server I manage if I can get away with a centralized solution. Additionally, there are many devices in scope that have firewall rules preventing console access and/or file transfer mechanisms. In short, the only mechanism I have is a standard SQL client. That is not to say that I couldn't petition the security team for relaxed access, but security is king here and it would be a battle I would likely lose.

One solution I've considered is use of the xp_cmdshell. Enabling it in SQL 2005 is possible, but I would prefer to leave it alone if I can.

Bruce.

|||

This really sounds like a task for an administrative WMI script -controlled and executed by the system administrators, retrieving the data and storing it in a central server for you to access. I would think that such an approach would molify the net administrator's security concerns. Easily done with MOM/SMS or whatever monitoring/management software is being used.