Active Directory

[Coppermill]

Is it possible to get information about Active Directory in to SQL Server so I can do different tasks depending on what information the user has in Active Directory?

 

[jmeckley]

1. set up a linked server to active directory
2. set up a view to display the information you want.

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'

CREATE VIEW viewADContacts 
AS
SELECT [Name], SN [Last Name], ST State
FROM OPENQUERY( ADSI, 
   'SELECT Name, SN, ST
   FROM ''LDAP://ADSISrv/ OU=Sales,DC=sales,DC=northwind,DC=com''
   WHERE objectCategory = ''Person'' AND
      objectClass = ''contact''')
GO
SELECT * FROM viewADContacts

One thing to know. you cannot query 1 to many relationships in AD. Example: you cannot get the groups the person is associated with. you can get their address, email, phone number.

thread183-587842 may also help along with BOL Index: [tt]linked servers, access Windows Directory[/tt]

Jason Meckley
Database Analyst
WITF