none
query AD from sql using linked server-not returning all records

    Question

  • Hi,

    There is already a linked server created and now trying(to query) to obtain attributes of all users present in a domain and we have more than 8000 such users.I am not able to pull all of them and query returns following error:

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

    Based on search results,I feel lot of people have faced the issue and it is that the AD wont return more than 900 entries.I plan to dump all the user and their attributes in sql table.I didnt find any solution online yet,pls help.

    This is the query:

    SELECT distinct SAMAccountName, displayName,userPrincipalName
    FROM OpenQuery (ADSI,  
                    'SELECT SAMAccountName, pwdLastSet, lockoutTime, employeeID, displayName, givenname, sn, 
                            scriptpath, distinguishedName,userPrincipalName
                     FROM ''LDAP://example.com/DC=example,DC=com'' 
                     WHERE objectClass =  ''User'' 
                     ') AS tblADSI_CBS 

    Friday, January 11, 2019 6:28 PM

All replies

  •  it is that the AD wont return more than 900 entries.

    Exactly AD queries returns max. 1,000 rows and that's by design, see Retrieving Large Results Sets

    Yes, writing the part result into a table is a good solution, but where do you have an issue with it?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, January 11, 2019 7:22 PM
  • Sorry,the AD returns some 7000 rows but not more than that.I want all the users within domain,how do i go about it
    Friday, January 11, 2019 7:34 PM
  • Can you put your query in a loop. Then add a TOP 5000 to the query WHERE LDAP.Column1 > DestTableMaxColumn1 /* Max value - put in 0 first time */ and an ORDER BY Column1. Hopefully Column1 is something unique. Then insert the results into DestTable. After the insert SET DestTableMaxColumn1 to the Max(Column1) from the DestTable. Stay in loop until  @@RowCount < 5000.  
    Friday, January 11, 2019 8:30 PM
  • Brenda,

    Thanks for the reply,however,i am not clear about the LDAP.column1>  conditions.Could you please post a sample ,its easier to understand

    Friday, January 11, 2019 8:48 PM
  • I have not tested this. BUt the general idea is the following. Get the records in batches of 5000.

    declare @MaxEmployeeID varchar(256) = ''
    declare @RowCount int = 5000
    
    If object_ID(N'tempdb..#Table') is not NULL
    	drop table #Table
    create table #Table (samAccountName varchar(256), displayName varchar(256), userPrincipalName varcar(256), employeeID varchar(256))
    
    
    While @RowCount > 4999
    
        BEGIN
    
        insert into #Table (SAMAccountName, displayName,userPrincipalName, employeeID)
    	   SELECT SAMAccountName, displayName,userPrincipalName, employeeID
    	   FROM OpenQuery (ADSI,  
    				    'SELECT TOP 5000 SAMAccountName, pwdLastSet, lockoutTime, employeeID, displayName, givenname, sn, 
    						  scriptpath, distinguishedName,userPrincipalName
    					FROM ''LDAP://example.com/DC=example,DC=com'' 
    					WHERE objectClass =  ''User'' 
    					and employeeID > @MaxEmployeeID
    					ORDER BY employeeID
    					') AS tblADSI_CBS 
    				 
        set @RowCount = @@ROWCOUNT
        
        set @MaxEmployeeID = (select MAX(employeeID) from #Table)
        
        END   
        
    select distinct SAMAccountName, displayName,userPrincipalName from #Table

    Friday, January 11, 2019 8:57 PM
  • Thanks,but I see a problem here ,my AD has some admin accounts that dont have employeeID and hence i cannot use that column.My plan is to just get me all users in AD as a query result
    Friday, January 11, 2019 9:44 PM
  • I just picked a random column. I don't know your data or AD data. Ideally you would like to use a column, or group of columns, that make a unique id. 
    • Edited by TheBrenda Friday, January 11, 2019 9:49 PM
    Friday, January 11, 2019 9:48 PM
  • How do i use this condition with null employee_ids.Without this ,i cannot do the top N and query will return "preparing of query incorrect".I dont have any unique IDs in my AD except DN,email /userprincipalname and Samaccountname
    employeeID > @MaxEmployeeID
    Friday, January 11, 2019 10:53 PM
  • Brenda has already said that she used EmployeeID as an example. Use one of the unique columns instead.

    But there are one or two problems with the query she posted, and that is why you get an error. You cannot refer to a T-SQL variable inside of the AD query - that's a query that executes in the AD, and not in SQL Server. You could build a dynamic SQL string to get the actual value into the query, but it is better to use EXEC AT:

       insert into #Table (SAMAccountName, displayName,userPrincipalName, employeeID)
    	 EXEC('SELECT TOP 5000 SAMAccountName, pwdLastSet, lockoutTime, employeeID, displayName, givenname, sn, 
    			        scriptpath, distinguishedName,userPrincipalName
    		FROM ''LDAP://example.com/DC=example,DC=com'' 
    		WHERE objectClass =  ''User'' 
    		  and employeeID > ?
    		ORDER BY employeeID', @MaxEmployeeID) AT ADSI
    

    Note that the linked server must be configure to permit rpc out:

    EXEC sp_serveroption ADSI, 'rpc out', true

    And, again, Employee serves as an example here.

    However, this may still fail of the simple reason that the TOP syntax may not be supported on the SQL for the AD. TOP is a proprietary extension to SQL Server, so I would not take for granted what it works with ADSI. (I have no experience with ADSI, so I cannot say neiter aye or nay in this case.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, January 12, 2019 10:16 AM
  • Thanks Erland,but the above does not work as the SQL complains of "The requested operation could not be performed because OLE DB provider "ADSDSOObject" for linked server "ADSI" does not support the required transaction interface."
    Monday, January 14, 2019 1:40 AM
  • Thanks Erland,but the above does not work as the SQL complains of "The requested operation could not be performed because OLE DB provider "ADSDSOObject" for linked server "ADSI" does not support the required transaction interface."

    Hi msdnpublic1234,

    Thanks for your reply.

    Per your reply and recent description, it seems that the issue is not related to problems of SQL Server product. Also, I don't know so much about the knowledge of Active Directory Service Interfaces.

    Besides, from your description, I know that you would like to pull the attributes of all users into the SQL Server table, also I think there is not only one way to achieve that.

    Therefore, you need to submit your doubts to Active Directory Service Interfaces forum for professional support.

    Please refer:

    https://social.technet.microsoft.com/Forums/en-US/home?forum=winserverDS

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 14, 2019 5:26 AM
    Moderator