Today i was struggling getting the active directory users list from our domain, but finally it worked. Here's the query.
1st step---
Thanks to Alina from MSDN blog (http://blogs.msdn.com/b/alina/archive/2009/04/16/ldap-query-to-get-a-user-memberships-in-sql.aspx) for this awesome SQL script:
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI',
@srvproduct=N'Active Directory Services', @provider=N'ADsDSOObject',
@datasrc=N'bisdragons.com'
GO
@srvproduct=N'Active Directory Services', @provider=N'ADsDSOObject',
@datasrc=N'bisdragons.com'
GO
Note: Change @datasrc=N'your_domain.com'
2nd step---
Run this query:
SELECT samAccountName, givenName [First Name], sn [Last Name],mail FROM OPENQUERY(ADSI,'SELECT samAccountName,givenName, sn, mail FROM ''LDAP://OU=Teaching Faculty,OU=Staff,DC=BISDRAGONS,DC=COM'' WHERE objectClass=''Person'' AND objectClass = ''User'' ORDER BY samAccountName') WHERE mail IS NOT NULL
Note: change "LDAP://OU=your_organizational_unit, DC=your_domain,DC=COM(*optional com,org,ca,etc...)"
You can also modify the SQL query above to get another attributes from your Active Directory. This link provides a complete Active Directory attributes in pdf.
Good luck!
5 comments:
Please not that querying AD is limited to 3000 records (1000 standard) so putting your 'where' outside the 'openquery' could lead to misleading results. Better query would be:
SELECT samAccountName, givenName [First Name], sn [Last Name],mail FROM OPENQUERY(ADSI,'SELECT samAccountName,givenName, sn, mail FROM ''LDAP://OU=Teaching Faculty,OU=Staff,DC=BISDRAGONS,DC=COM'' WHERE objectClass=''Person'' AND objectClass = ''User'' AND mail>0 ORDER BY samAccountName')
Thanks for the suggestions Michiel!
If I wanted to further narrow this down to only users in a specific group membership how would I accomplish that. For example I'm looking for something like this:
SELECT samAccountName, givenName [First Name], sn [Last Name],mail
FROM OPENROWSET('ADSDSOObject','adsdatasource','SELECT samAccountName,givenName, sn, mail
FROM ''LDAP://COPPOLA.LOCAL/dc=COPPOLA,dc=LOCAL''
WHERE objectClass=''Person'' AND objectClass = ''User''
AND mail>0
AND Group=''Sales*''
ORDER BY samAccountName')
Thank in advance for your help,
sks
hi, is it possible to get multi value properties form AD like description, memberOf. if I run simply by adding memberOf this gives error
select * FROM OPENQUERY(ADSI,'SELECT initials, samAccountName, displayName, distinguishedName, mail, memberOf FROM ''LDAP://DC=corp, DC=contoso, DC=com'' WHERE objectClass=''Person''')
"ERROR"
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "ADSDSOObject" for linked server "ADSI". Could not convert the data value due to reasons other than sign mismatch or overflow.
Is it possible to get data form multi values. I am using SQL Server 2008 R2
no, it is not. it looks like openquery command does not support this, because you can do it in code, C# or VB.NET for example. so, if you really need this functionality, you can create CLR proc, which will do it.
Post a Comment