Search code examples
sql-serveractive-directorylinked-serveropenquery

LDAP OpenQuery returns only one column ADsPath

I have added my Domain Controller as a linked server to SQL Server in order to pull some data from active directory and populate my tables with it. After that, I have executed an OpenQuery to get the users list in my domain

SELECT * FROM OpenQuery(ADSI, 'SELECT * FROM ''LDAP://OU=SmallOU, DC=MyDOMAIN,DC=xx,DC=xx'' WHERE objectClass=''User'' ')

I have performed the query against a small OU since performing the query against the whole directory returned errors because of number of rows limitation

Surprisingly, the query returns only one column "ADsPath", whereas I expected to get sAMAccount, Title, Last Name, .... ,etc.

enter image description here

I want to get all the users data, how can I do that?

Solution

  • The provider will not try to find out which attributes to retrieve, if you use the asterisk with the SQL syntax. Instead of SELECT * you must specify the attributes you want like SELECT sAMAccountName, sn, givenName.