Query to get the all the users from ADS datasource

Please help me to get the all the user from ADS, i searched a lot and found we can get that using linked server,

i ran the following query to add the linked server,

sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
'ADSDSOObject', 'adsdatasource'

The query ran succesfully and the 'ADSI' has been added as linked server

I got a query from net to get the users from ADS

SELECT [Name],SN[Last Name]
FROM OPENQUERY( ADSI,
'SELECT Name,SN FROM ''LDAP://servername.domainname.com/CN=Users,
DC=domainname,DC=com''
WHERE objectCategory = ''Person'' AND objectClass = ''user'' order by
name')

I am not able to understand the query above and what i need to give to get my ADS users

please help me.

i tried from my side after refering these links ... go through this...

http://codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx

http://support.microsoft.com/kb/299410

http://blogs.msdn.com/euanga/archive/2007/03/22/faq-how-do-i-query-active-directory-from-sql-server.aspx

Madhu

|||

Madhu,

I am having the same problem as posted above, but despite trying your suggestions, it still doesnt work.

The code used:

Code Block

sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADsDSOObject', 'adsdatasource'sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADsDSOObject', 'adsdatasource'

SELECT * FROM OPENQUERY( ADSI,
'SELECT name, adsPath
FROM ''LDAP://DC=myCompany,DC=lan''
WHERE objectCategory = ''Person'' AND objectClass= ''user''')

PS: the domain is myCompany.lan

We use windows authentication mode. I dont belong to the administrators' group (which is systems administrator's stuff here).

May it be something related to the linked server's security? I've tried "be made using the login's current security context" and "be made without a security context" (we never know ). Both without sucess.

Or would it be related to AD reading permissions?

|||

Well, a clue:

Digging around, I've found this script at http://www.microsoft.com/technet/scriptcenter/resources/qanda/aug04/hey0824.mspx:

Code Block

On Error Resume Next

Const ADS_SCOPE_SUBTREE = 2

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

objCommand.CommandText = _
"SELECT Name FROM 'LDAP://dc=myCompany,dc=lan' " & _
"WHERE objectCategory='user'"
Set objRecordSet = objCommand.Execute

objRecordSet.MoveFirst
Do Until objRecordSet.EOF
Wscript.Echo objRecordSet.Fields("Name").Value
objRecordSet.MoveNext
Loop

It works like charm. Whatever it may be, is not related to permissions. An interesting thing: in Brendan Tompkins' blog http://adsdsoobject.codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx, he quotes this link: http://www.dbforums.com/archive/index.php/t-958399.html, which mentions certain windows registry keys I havent found in both my server and desktops: there is no entry called "provider", under the mentioned path. Is there anything missing here?

Thanks in advance!

|||

More news:

Indeed, the query runs smoothly... using SSMS on the server machine. But not in any workstation.

As I said, I can run that very vb script quoted above from my workstation, but not the query in SSMS. Why is it so?

No clues?!

|||

Do you get any error when you are not expecting correct results?

If it doesn't work with server name you might try with IP Address instead.

|||

Thanks for your reply, Satya.

I didnt understand your first question...

Actually, the query below doesnt work when I try to run from my workstation (that same "An error occurred while preparing the query..." error). I've tried using IP as you suggested:

select * from openquery
(ADSI,'SELECT name
FROM ''LDAP://172.23.0.21''
WHERE objectCategory = ''Person'' AND objectClass = ''user''')

Something weird: it doesnt work while running from SSMS right on the server, either! I get this error:

Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "ADsDSOObject" for linked server "ADSI".