Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Active Directory Query Expand / Collapse
Author
Message
Posted Friday, February 17, 2006 1:40 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, December 8, 2012 7:21 PM
Points: 234, Visits: 269

Is it possible to query Active Directory for user account information?   I thought I heard something about an Extended Stored Proc that did something similar to this.

Thanks!

Post #259587
Posted Friday, February 17, 2006 2:41 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:34 AM
Points: 6,259, Visits: 2,031

http://support.microsoft.com/default.aspx?scid=kb;en-us;299410
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adsi/adsi/distributed_query.asp
http://msdn.microsoft.com/library/en-us/adsi/adsi/distributed_query.asp?frame=true

 

Cheers,

 




* Noel
Post #259595
Posted Friday, February 17, 2006 4:10 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, December 8, 2012 7:21 PM
Points: 234, Visits: 269

Thank you for the links.  I did find those, but I seem to still be missing something.  Do I just simply run the sp below, or do I still need to do some additional configurations?

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

Thanks again!

Post #259632
Posted Friday, February 17, 2006 4:54 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, December 8, 2012 7:21 PM
Points: 234, Visits: 269

Here is some more information:

PDC: Windows 2003 Server

SQL Server 2000 on Windows 2000 Server 

I executed the sp to add the linked server using the exact same verbage listed on that page.

I then executed the next query to specify the user and password since we use Mixed Mode.  I used the domain administrator account. 

I then executed the query to return the users.  That returns this error:

Server: Msg 7321, Level 16, State 2, Procedure viewADContacts, Line 3
An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare:repare returned 0x80040e14].
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'viewADContacts'.

 

What are the steps I'm missing?  Thank you!!

 

 

Post #259641
Posted Friday, February 17, 2006 6:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 8:22 PM
Points: 133, Visits: 323

You can try this SQL query:

 

select *  FROM OPENROWSET('ADSDSOObject',
 'adsdatasource;', 'SELECT  cn, mail, co, distinguishedName, displayName
 FROM ''xxxxxxxxxxxxxxxxxx'' where objectClass = ''User'' ')

 

Note that xxxxxxxxxxxxxxxx can be 
 GC://something.com or LDAP://something.com where something.com is an Active directory server.

Post #259653
Posted Monday, February 20, 2006 9:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, December 8, 2012 7:21 PM
Points: 234, Visits: 269
That worked perfectly, thanks!
Post #259921
Posted Monday, February 20, 2006 1:31 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:54 AM
Points: 197, Visits: 625
Note that there is a resultset size threshold (normally 5000) on LDAP queries, set at the domain level. If you have more users, the OPENROWSET part of the query will always return exactly 5000 rows - no error message.
Post #259987
Posted Friday, March 31, 2006 12:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 21, 2010 4:42 AM
Points: 1, Visits: 3

I used your sp:

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

And ran the query against it on MS SQL 2000 it works fine, but when I repeat the same steps on MS SQL 2005 the server executes the sp just fine. The query is what fails with the following error:

Msg 7321, Level 16, State 2, Line 1

An error occurred while preparing the query "SELECT cn, mail, co, distinguishedName, displayName FROM 'LDAP://presidioad/DC=presidio,DC=corp' where mail = 'm@m.com' " for execution against OLE DB provider "ADSDSOObject" for linked server "(null)".

I'm runing the sp like this:

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

Any suggestions?

 

Thanks,

Mamoon

Post #270278
Posted Friday, April 21, 2006 1:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 26, 2014 7:49 AM
Points: 16, Visits: 76

Just checking.  Are your 2005 services running as a local user? 

If this is the case change your linked server to connect as a domain user with relevent AD Query access rights (or change services to run as a domain user with relevent AD Query access rights). 

Post #274383
Posted Thursday, September 27, 2007 2:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:49 AM
Points: 3, Visits: 96
Folks,

I have the same problem here, but the solutions provided solved the problem... while running the query on the server.

select * FROM OPENROWSET('ADSDSOObject',
'adsdatasource;', 'SELECT cn, mail, co, distinguishedName, displayName
FROM ''LDAP://myDomain.lan'' where objectClass = ''User'' ')


If i try to run the same query using SSMS on any desktop accessing this server, I get this error:

Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT cn, mail, co, distinguishedName, displayName
FROM 'LDAP://myDomain.lan' where objectClass = 'User' " for execution against OLE DB provider "ADSDSOObject" for linked server "(null)".



If I try the other solution

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


I get the same weird error people complain about:

Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT name
FROM 'LDAP://myDomain.lan'
WHERE objectCategory = 'Person' AND objectClass = 'user'" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".


Any clues? Thanx!

Post #403764
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse