Linking with Windows 2000 Servers Active Directory

Link the server:

sp_addlinkedserver 'ADSI', 'Active Directory
Services 2.0', 'ADSDSOObject', 'adsdatasource'

Add a login:
sp_addlinkedsrvlogin ADSI, false, null, null, null

Query like this:
SELECT cn, adspath
FROM OPENROWSET(
     'ADSDSOObject',
     ''; 'cn=Administrator,ou=Wellington,o=ATP-DEV';'dev4ATP', '<LDAP://wlgdev01/o=atp-dev/ou=wellington/cn=recipients>;(&(objectClass=
organizationalPerson));objectClass,ADsPath,cn;subtree')

Can you explain the above a little better. I don't quite understand what this is doing or what I need to change to work with my system.

I can certainly try:

(1) Add the ADSI provider as a linked server in SQL Server.  To do this, use the sp_addlinkedserver stored procedure.  Syntax:

sp_addlinkedserver [@server =] 'server' [, [@srvproduct =] 'product_name']
    [, [@provider =] 'provider_name'] [, [@datasrc =] 'data_source']
    [, [@location =] 'location'] [, [@provstr =] 'provider_string']
    [, [@catalog =] 'catalog']

server = 'ADSI'
SrvProduct = 'Active Directory Service Interfaces'  (Sorry, I got an incorrect value here).
Provider = 'ADSDSOObject'
Datasrc = 'adsdatasource'

These values are mandatory names, not optional values.  Thus, the final call is:

sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'

(2) Add a Linked Server login using sp_addlinkedsrvlogin.  Syntax:

sp_addlinkedsrvlogin [@rmtsrvname =] 'rmtsrvname'
    [,[@useself =] 'useself']
    [,[@locallogin =] 'locallogin']
    [,[@rmtuser =] 'rmtuser']
    [,[@rmtpassword =] 'rmtpassword']

RmtSrvName = 'ADSI' (that's just its name).
UseSelf = False (mandatory)
All other parameters null.  Thus, the final call is:

sp_addlinkedsrvlogin ADSI, false, null, null, null

(3)  Query your data

With a linked ADSI server, you must use the OpenRowset syntax to get data:

OPENROWSET('provider_name'
    {
        'datasource';'user_id';'password'
        | 'provider_string'
    },
    {
        [catalog.][schema.]object
        | 'query'
    })

datasource = 'ADSDSOObject'  (you can look up this object in MSDN for more information.  It is the ADSI ADO driver)

user_id, password -- this is dependent on who/what has the necessary rights on your server.

query: also dependent on what you need.

Here's a simpler (and hopefully clearer) sample query (the syntax for ADSI queries is complex - see Microsoft's MSDN site for more info and examples) and returning user names:

SELECT * FROM OPENROWSET('ADSDSOObject', ''; 'DOMAIN\username'; 'password',
'SELECT name FROM ''LDAP://DC=xxxxxx,DC=yyy'' WHERE
objectClass=''User'' AND objectCategory=''Person'' ') AS Users

Discussion of the full syntax of the query is beyond the scope of this discussion, I'm afraid.  See:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/netdir/adsi/active_directory.asp

for more (**much** more) information.

When I try the above method I get an error on the linked server and I can't do step 3 Here is my error, Can you help??

Could Not Obtain a required interface from OLE DB Provider 'ADSDSOObject'

Have you seen this article on MS site:

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

Did you alter the DC fields (or change to the appropriate subcommands for the LDAP connection) as needed?

I believe I did, I set the DC for our domain. When I run this query

SELECT * FROM OPENROWSET('ADSDSOObject', ''; 'DOMAIN\username'; 'password',
'SELECT name FROM ''LDAP://DC=xxxxxx,DC=yyy'' WHERE
objectClass=''User'' AND objectCategory=''Person'' ') AS Users

it doesn't like it when I change it to this

SELECT * FROM OPENROWSET('ADSDSOObject', ''; 'MY_DOMAIN\NMALLOY'; 'password',
'SELECT name FROM ''LDAP://DC=mydomain,DC=com'' WHERE
objectClass=''Users''') AS Users

This link

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/netdir/ad/joining_heterogeneous_data.asp

gives me a page cannot be found error. I wonder if they deleted it

I don't believe that Users is a valid ObjectClass for the ldap provider.  That's why User/Person were used in the original -- just User returns the Computer objects as well.

Try this one:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/netdir/adsi/joining_heterogeneous_data.asp

Okay I just did the article you listed and when I put in my query

SELECT * FROM OpenQuery( ADSI, 'SELECT name, adsPath FROM 'LDAP://DC=am1st,DC=com' WHERE objectCategory = 'Person' AND objectClass= 'user'')

I get this error

ODBC Error: [Microsoft] {ODBC SQL Server Driver][SQL Server] One or more properties could not be set on the query for OLE DB Provider 'ADSDSOOJECT'. The Provider could not support a required property.  

Any ideas

I am trying to create a view by the way

is this a cut and paste query?  If so, you need to change the embedded single quotes in the string:

SELECT * FROM OpenQuery( ADSI, 'SELECT name, adsPath FROM ''LDAP://DC=am1st,DC=com'' WHERE objectCategory = 'Person' AND objectClass= ''user''')

double up the embedded single quotes to make them correct.

I made it like this

SELECT * FROM OpenQuery( ADSI, 'SELECT name, adsPath FROM 'LDAP://DC=am1st,DC=com WHERE objectCategory = Person AND objectClass= user')

I get an incorrect syntax near LDAP. Then it says Unclosed quotation mark

OKay I saw where that was, but now I get that same ODBC error I had above

What's your server OS?  What ADO version is on it?

This can be mismatched ODBC proividers, apparently (at least for some OLE DB providers).

Window 2000 Server, SQL Server 7.0 Service Pack 3. ADO Version 2.53.62