Solved

ADSI Linked Server not connecting using SQL Server 2008 R2

Posted on 2011-03-11
3
2,374 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:SasDev
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35127331
Hi,
Is that TOP (10) PERCENT more that 1000 rows? Is so it won't work regarding pagesize in AD.
0
 
LVL 1

Accepted Solution

by:
SasDev earned 0 total points
ID: 35241271
The issue ended  up being that our dns could not resolve the Host name. We were initially able to get this working using the ip address. We then flushed the dns on that box specifically and were able to run the above query using the host name.
0
 
LVL 1

Author Closing Comment

by:SasDev
ID: 35308377
This was the solution that worked and was not suggested via EE.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Reformat SQL - so SSRS can read the columns 25 45
SQL QUERY 3 41
If in a where clause in t-sql 7 49
Database-Scoped Permissions 2 40
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question