Lowell (12/18/2013)
it might be easier to just query the active directory from SQL via a linked server.then the pieces can be pulled out automatically;
does this help you at all? it just worked on my domain:
--sp_dropserver ADSI
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI',
@srvproduct=N'Active Directory Services', @provider=N'ADsDSOObject',
@datasrc=N'MyDomain'
select * from openquery
(ADSI,'SELECT
cn,
sn,
SamAccountName,
mail,
telephoneNumber,
mobile,
co,
distinguishedName,
displayName,
physicalDeliveryOfficeName,
department
FROM ''LDAP://MyDomain''
WHERE objectCategory = ''Person'' AND objectClass = ''user'''
)
Crud. Apparently, that doesn't work in 2005?
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT
cn,
sn,
SamAccountName,
mail,
telephoneNumber,
mobile,
co,
distinguishedName,
displayName,
physicalDeliveryOfficeName,
department
FROM 'LDAP://MyDomain'
WHERE objectCategory = 'Person' AND objectClass = 'user'" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".
An, yeah... I tried changing "MyDomain" to the name of my domain and it produced the same error. Way too bad for me :crying: because this would be wicked useful for the internal security audit we're getting ready to do.
--Jeff Moden
Change is inevitable... Change for the better is not.