Linking with Windows 2000 Servers Active Directory

Is there a way to create a live link between SQL Server 7.0 and Active Directory?
NickMalloySystems LeadAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
NickMalloySystems LeadAuthor 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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

NickMalloySystems LeadAuthor 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
NickMalloySystems LeadAuthor 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
NickMalloySystems LeadAuthor 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
NickMalloySystems LeadAuthor 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
NickMalloySystems LeadAuthor 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
Brendt HessSenior DBACommented:
Try:

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NickMalloySystems LeadAuthor Commented:
I get an incorrect syntax near LDAP. Then it says Unclosed quotation mark
0
NickMalloySystems LeadAuthor 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
NickMalloySystems LeadAuthor Commented:
Window 2000 Server, SQL Server 7.0 Service Pack 3. ADO Version 2.53.62
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.