Linked server to AD

• Ford Fairlane

  SSCertifiable

  Points: 7664

  More actions

  August 15, 2012 at 10:04 pm

  #280267

  Hi,

  I am trying to query our AD with LDAP through a linked server in SQL Server 2008 R2. This is what I have done so far;

  EXEC master.dbo.sp_addlinkedserver @server = N'ADSI',

  @srvproduct=N'Active Directory Services', @provider=N'ADsDSOObject',

  @datasrc=N'RHSPWDCOC02'

  GO

  For security I am using my account which is a domain admin to access the server and log into SQL Server.

  I run :

  Select [Name] ,[DisplayName]

  From OpenQuery

  ( ADSI,'SELECT name, displayname FROM ''ADSI://RHSPWDCOC02''

  WHERE objectCategory = ''Person'' AND objectClass = ''user''')

  OUTPUT:

  Msg 7321, Level 16, State 2, Line 1

  An error occurred while preparing the query "SELECT name, displayname FROM 'ADSI://RHSPWDCOC02'

  WHERE objectCategory = 'Person' AND objectClass = 'user'" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".

  Any ideas as to what I am missing ???

  Hope this helps...

  Ford Fairlane
  Rock and Roll Detective

  

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  August 16, 2012 at 1:10 pm

  #1526801

  Try it with LDAP instead of ADSI:

  Select [Name] ,[DisplayName]

  From OpenQuery

  ( ADSI,'SELECT name, displayname FROM ''LDAP://RHSPWDCOC02''

  WHERE objectCategory = ''Person'' AND objectClass = ''user''')

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• Ford Fairlane

  SSCertifiable

  Points: 7664

  More actions

  August 16, 2012 at 5:42 pm

  #1526870

  Hi,

  Thanks for the advice. I found the problem to be a security issue AND the fact that the MaxPageSize is limited to 901 and I was pulling down more than that. I found the solution here [/url]which worked a treat.

  Hope this helps...

  Ford Fairlane
  Rock and Roll Detective

  

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  August 16, 2012 at 9:13 pm

  #1526890

  I did not have any luck using ADSI:// myself. Only when switching to use LDAP:// did it work although it sounds like you had a few things going on simultaneously. I see the article used LDAP:// as well. Happy you got it all sorted.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato