Monday, August 27, 2012

Querying Active Directory from SQL Server


This is quite easy except the restriction on the number of records fetched.
After sql server 2008, the limit is 901, so 

a simple query to AD from sql is

select * from
openquery(ADSI,'select  samaccountname, cn, employeeID,mail,distinguishedname  from ''LDAP://DC=prod,DC=companydomain,DC=com''
where objectClass = ''person'' and objectClass = ''user'' and objectClass = ''organizationalPerson''
and objectClass <> ''contact'' and msExchHideFromAddressLists <> TRUE and accountExpires <> 0
')
but this fails with the below error, though it displays the 901 records.

Cannot fetch a row from OLE DB provider "ADsDSOObject" for linked server "ADSI".

The same query using openrowset is:

SELECT * FROM OPENROWSET('ADSDSOObject', '','SELECT cn, ADsPath FROM ''LDAP://DC=prod,DC=mycompany,DC=com'' ')

I read somewhere that the restriction in sql 2000 is 1000 records
This limit is defined by the "maxpagesize" in Active Directory.

If you want to view this value.
logon to a windows 2003 server, run-> ntdsutil.exe
and you can see this value using the command "show values".

you can use the same query from the command prompt using "dsquery" and get the records
the query is something like this:
dsquery * dc=prod,dc=companydomain,dc=com -scope -base -attr sAMAccountname employeeid department cn employeetype

using dsquery you can get all the data with a "-limit" parameter and dump it into a text file.
this is one way of overcoming the 1000 record limit in sql server.

To add the linked server in sql:
eXEC sp_addlinkedserver @server = 'ADSI', @srvproduct = 'Active Directory Services 2.5', 
@provider = 'ADSDSOObject', @datasrc = 'adsdatasource'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'dd\sssss',@rmtpassword='ddddd'

sample queries:
SELECT top 900 * FROM OpenQuery
(ADSI, 'SELECT Name,SN,ST,cn,adspath FROM ''LDAP://OU=Users,OU=CSC-Managed,DC=au,DC=amp,DC=local''
where objectCategory=''Person'' and objectClass=''User''   ')

No comments:

Post a Comment