Active Directory Available as Linked Server?

[Steve Ediger]

We're having problems establishing a Linked Server that points at Active
Directory in SQL Sever 2000. Executing sp_addlinkedserver with the sample
variables results in a linked server. However, from the linked server
(ADSI, in this case) we can't see any tables/views and get a 7301 "Could
not obtain a required interface from OLE DB provider "ADSDSOObject"

We've been through the books/MSDN site on this and can't find our problem.

Any hints?

[Max L. Vaughn]

Steve,

Let me do a bit of research on this. I'll make another post later today or tomorrow morning.

Sincerely,
Max Vaughn [MS]
Microsoft Developer Support

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use.

[Dave Phillips]

I am having exactly the same problem. I want to be able to add and edit
entries in Active Directory from SQL server 2000. The OLE DB Provider for
exchange gets tantalisingly close but I don't seem to be ablr to add or edit
Contacts or Public Folder contacts.
"Steve Ediger" <Steve...@woodstock.ac.in> wrote in message
news:ObSwzsN#BHA.1880@tkmsftngp02...

[Steve Ediger]

Dave,
I believe that the documentation is pretty clear about this being a
read-only connection. You'll need to use another tool like perhaps Windows
Scripting Host or VC++ or VC# to add, edit and delete (someone who has more
experience can probably clarify).
Steve

"Dave Phillips" <david.p...@ghs.sa.gov.au> wrote in message
news:u3UiXQ6#BHA.2296@tkmsftngp05...

[Steve Ediger]

Max,

We got it with

CREATE VIEW dbo.viewWSUsers
AS
SELECT *
FROM OPENQUERY(ADSI,
'<LDAP://DC=domain1,DC=com;(&(objectCategory=Person)(ObjectClass=user));name
, adspath;subtree' Rowset_1
as per Simon's instructions in the SQL.Connect group. Thanks.

Now we're into the next phase of trying to figure out how to get the entire
dataset (as it appears in Active Directory Users and Computers). Any way of
doing this short of enumerating the entire AD (over 2000 objects, not
including attributes/properties for us). Can you point me a direction? We
have SQLServer, Visual Studio 6.0 and .NET to work with.

Thanks,

Steve Ediger

"Max L. Vaughn" <ma...@online.microsoft.com> wrote in message
news:dJnLJ$2#BHA.1588@cpmsftngxa08...

[Stevekl]

Hi Steve,

There are some limitations that we will have to work around when querying
the Active Directory from SQL Server.

1. The Active Directory has an LDAP Policy, MaxPageSize, which is set to
1000. What this means is that the Active Directory will only return 1000
objects in response to a single query.
This is usually worked around by setting the Page Size property in the ADSI
OLE-DB provider. Unfortunately, there is no way to set this from SQL
Server. Therefore, we must use filters to return datasets of less than
1000 records/AD objects.

2. With the SRP1 for Windows 2000, ADSI now returns an error when the
MaxPageSize LDAP policy is broken. In SQL Server Query Analyzer, this
error will show up as the following:

Server: Msg 7330, Level 16, State 2, Line 1
Could not fetch a row from OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
IRowset::GetNextRows returned 0x80072023].

The error 80072023 is defined as :
"The size limit for this request was exceeded"

Here is an article that describes some of the limitations with using SQL
Server Linked Servers with the ADSI OLE-DB provider.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q299410

Concerning where you are currently working. Depending on the programming
language you prefer, using ADO with the ADSI OLE-DB Provider
(ADsDSoObject), may be a good option. Using ADO directly to the ADSI
OLE-DB provider will allow you to use the Page Size properties, as well as
other OLE-DB provider specific properties described in the following MSDN
topic:

http://msdn.microsoft.com/library/en-us/netdir/adsi/searching_with_activex_d
ata_objects_ado.asp

If you prefer to use VS.NET, System.DirectoryServices.DirectorySearcher
would also be available to query the Active Directory, and return the data
needed. Please see the MSDN topic on DirectorySearcher for further
information on System.DirectoryServices.DirectorySearcher.

http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemDirectoryServi
cesDirectorySearcherClassTopic.asp

This is a very flexible way to code this, as the DirectoryServices class is
consistant accross all of .NET.

Below is a simple sample of creating the LinkedServer through code, that
also uses the sp_addlinkedsrvlogin. This is important as we may need to
pass credentials to access the information in the Active Directory. The
sample given is if your SQL Server is set up to use SQL Server
authentication. If your server allows only integrated security, the third
argument could also look like: Domain\UserID.

I have included two different query statements. One uses the SQL Select
query format, and the other uses the LDAP query format.
**************************************************************************
Use master
go
exec sp_addlinkedserver
'ADSITest2',
'My Active DirectoryTest',
'ADSDSOObject',
'adsdatasource'
go
exec sp_addlinkedsrvlogin
'ADSITest2',
False,
'sa',
'sk-mapsad\administrator',
'Secret!'
go

Select * from OpenQuery(ADSITest2,'select cn,adspath from
''LDAP://sk-w2ksrv.sk-mapsad.extest.microsoft.com/cn=users,dc=sk-mapsad,dc=e
xtest,dc=microsoft,dc=com'' where objectClass = ''User''')

select * from
OpenQuery(ADSITest2,'<LDAP://sk-w2ksrv.sk-mapsad.extest.microsoft.com/cn=Use
rs,dc=sk-mapsad,dc=extest,dc=microsoft,dc=com>;(objectClass=User);adspath,di
stinguishedname;subtree')
**************************************************************************

Sincerely,

Steve Klem
Microsoft Developer Support

[Stevekl]

Hi Steve,

I realized that I did not address anything concerning the Tables and Views
tab working on the ADSI Linked Server. This is partially due to various
interfaces not being implemented in the ADSI OLE-DB provider. Due to
limitations on the provider due to the way that LDAP queries need to be
handled, this OLE-DB provider does not act like a lot of others act. Here
is something on how it works:

1. When we do a connection in the OLE-DB provider, rather than actually
doing anything to establish a connection with the server, it simply sets
some internal properties, and waits for the query.

2. When it receives the query request, it translates them into LDAP API
calls, and issues those LDAP calls over the wire.

3. With the resulting data from the LDAP server, it passes this information
back to the caller in the format of a recordset.

There are really no 'tables' to be viewed in the Active Directory, so that
is why you did need to create a view to view the data in the SQL Server
Enterprise Manager.

As I indicated in my previous response, to work most effectively with data
from the Active Directory, I would use one of the following interfaces:

Visual Studio 6:
=============
VB6
ADODB directly to the ADSI OLE-DB provider (adsdsoobject)

VC++
OLE-DB
iDirectorySearch interface

Visual Studio .NET
================
System.DirectoryServices.DirectorySearcher class

All of these will allow you to set the Page Size of the query you make.
That way, the Page Size can be set to a value less than what the LDAP
MaxPageSize Policy is set to. If you need the data to get into SQL Server
eventually, it still may be better to use one of these options, rather than
trying to guess how many records a query will return in SQL Server.

Sincerely,

Steve Klem [MS]

[dmartin]

I find that I cannot retrieve any columns except for adsPath. Any other
columns, such as cn, produce a data conversion error using SQL tools. Is
there some secret I have missed?

"Stevekl" <ste...@online.microsoft.com> wrote in message
news:pLw5svF$BHA.1416@cpmsftngxa08...