Linking with Windows 2000 Servers Active Directory

Is there a way to create a live link between SQL Server 7.0 and Active Directory?
NickMalloyAsked:
Who is Participating?
 
Brendt HessConnect With a Mentor Senior DBACommented:
Try:

SELECT * FROM OpenQuery( ADSI, 'SELECT name, adsPath FROM ''LDAP://DC=am1st,DC=com'' WHERE objectCategory = ''Person'' AND objectClass= ''user''')
0
 
Brendt HessSenior DBACommented:
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')
0
 
NickMalloyAuthor Commented:
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.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Brendt HessSenior DBACommented:
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.
0
 
NickMalloyAuthor Commented:
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'
0
 
Brendt HessSenior DBACommented:
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?
0
 
NickMalloyAuthor Commented:
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
0
 
NickMalloyAuthor Commented:
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
0
 
Brendt HessSenior DBACommented:
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.
0
 
NickMalloyAuthor Commented:
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
0
 
Brendt HessSenior DBACommented:
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.
0
 
NickMalloyAuthor Commented:
I made it like this

SELECT * FROM OpenQuery( ADSI, 'SELECT name, adsPath FROM 'LDAP://DC=am1st,DC=com WHERE objectCategory = Person AND objectClass= user')
0
 
NickMalloyAuthor Commented:
I get an incorrect syntax near LDAP. Then it says Unclosed quotation mark
0
 
NickMalloyAuthor Commented:
OKay I saw where that was, but now I get that same ODBC error I had above
0
 
Brendt HessSenior DBACommented:
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).
0
 
NickMalloyAuthor Commented:
Window 2000 Server, SQL Server 7.0 Service Pack 3. ADO Version 2.53.62
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.