Link to home
Start Free TrialLog in
Avatar of SasDev
SasDevFlag for United States of America

asked on

ADSI Linked Server not connecting using SQL Server 2008 R2

We have two namespaces:
sasnet.external (for our customers)
 and sas.sasinc.com (for our internal users)

Can't wrap my head around why I'm not able to query AD within one namespace but I have no issues with the other.
In using a linked ADSI server, ADSI is the generic name to use correct? Any suggestions would be very helpful.

We have set up an ADSI linked server on one of our SQL Servers to conect to the sas.sasinc.com Active Directory. This works fine with the following Query on sqlserver1:
SELECT     TOP (100) PERCENT employeeID, department, telephoneNumber, mail, sn, givenName
FROM         OPENQUERY(adsi,
                      '  
select  employeeID, givenName,  
    sn,  
    mail,  
    telephoneNumber,  
   department  

from    ''LDAP://dc=sas, dc=sasinc, dc=com''  
where   objectCategory = ''Person''  
        and  
        objectClass = ''user''  
')
                       AS derivedtbl_1
ORDER BY sn

The above query also works fine on sqlserver2, however when I attempt to change the distinquished name to connecto to the external domain we receive errors such as:
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "ADSI".

Below is the following query we are using for the sasnet.external on sqlserver2:
SELECT TOP (10) PERCENT  Name, displayName,givenname,distinguishedName, SAMAccountName  FROM OPENQUERY( ADSI, 'SELECT Name, displayName,givenname,distinguishedName, SAMAccountName
                FROM ''LDAP:// dc=sasnet,dc=external''
               
                                ')
GO

I added the linked server using:
/****** Object:  LinkedServer [ADSI]    Script Date: 03/11/2011 11:39:50 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'adsdatasource', @provider=N'ADsDSOObject'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'Ldaproaccess',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

EXEC sp_addlinkedsrvlogin @rmtsrvname = N'SASNETDC1', @locallogin = NULL , @useself = N'False',
      @rmtuser = N'SASNET\ldapaccess', @rmtpassword = N'*******'
GO
Avatar of Daniel_PL
Daniel_PL
Flag of Poland image

Hi,
Is that TOP (10) PERCENT more that 1000 rows? Is so it won't work regarding pagesize in AD.
ASKER CERTIFIED SOLUTION
Avatar of SasDev
SasDev
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of SasDev

ASKER

This was the solution that worked and was not suggested via EE.