?
Solved

Query Active Directory with SQL Server

Posted on 2004-09-16
8
Medium Priority
?
750 Views
Last Modified: 2008-05-30
Is there any way to setup SQL Server to link to Active Directory so I can setup various reports to run automatically querying the SQL server?  I am looking for a solution that will work with my existing setup, so I do not have to purchase expensive software.

Thanks for any help!
0
Comment
Question by:blouckswwu
[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
  • 4
  • 3
8 Comments
 
LVL 41

Expert Comment

by:graye
ID: 12077895
Sure... it's just like you'd imagine.   You create a "linked server" to point to Active Directory.

From the SQL Management Console, <server name>, Security, Linked Server, click to create a new linked server.   From the list of providers, pick "OLEDB Provider for Microsoft Directory Services"... fill out the appropriate info, and you're cooking.

The tables under this linked server are called like this in TransactSQL:

         AD...TableName

(Where AD is the name of the linked server)
0
 

Author Comment

by:blouckswwu
ID: 12078384
So what do put in the other fields?

Product Name: (??)
Data Source: (servername of my Active Directory Master Browser)
Provider String: (??)
Location: (??)

I'm sorry if these seem like stupid questions, but I can't seem to find any information on how to set this up.

Thanks for your help!!
0
 
LVL 41

Accepted Solution

by:
graye earned 1200 total points
ID: 12080128
You can leave a bunch of those fields blank (I usually do... I'm not actually sure what they're for!)

I'm not at an Active Directory setup right now... and I can't find any examples using the Management Console.  If I remember correcly... you don't have to put anything in there except the name of the linked server.

I did find an an article on how to do it all from the command-line  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_12_94fn.asp
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:blouckswwu
ID: 12080259
I must be missing something here.  I followed the msdn article to a 'T' and I still get a damn error.  I am using Windows Auth and I am a Domain Admin.  But I still get this same error everytime.  I was assuming that this was not rocket science, but I assumed wrong.  Its frustrating to have all this information in a Database (Active Directory) and I can't get it out easly and Microsoft doesn't put out hardly any information on how to get it out.  That one link you gave me was the only real document that shows that it can be done and its one little page.  Sorry just frustrated.  If you can get a chance to test it and figure out what I am doing wrong, I will give you  1500 points, as I have 3 entries out on different sections regarding this.

Thanks again
0
 
LVL 41

Expert Comment

by:graye
ID: 12083379
Relax... it's not about points.   I bet we can figure this out.

What error are you getting?
0
 

Author Comment

by:blouckswwu
ID: 12083727
Sorry...had a long day yesterday.  Here is the error:

Error 7301: Could not obtain a required interface from OLE DB provider 'ADsDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADsDSOObject' IUnknown::QueryInterface returned 0x80004002:  IDBSchemaRowset].

I have looked up that error code on google and got nothing worth looking at.  I'm wondering if this has something to do with permissions?  I am wondering what login SQL server is using to connect to Active Directory?  I would assume that you would have to be a domain admin to be able to connect.  I looked at the security tab and tried different settings, but couldn't get anything to work.

Thanks
0
 

Author Comment

by:blouckswwu
ID: 12083804
Ok...on one of my other post I got this solution:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20429310.html

I followed it and I get this error when I run the query (the one with the accepted answer):

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare returned 0x80040e14].

Here is the query:
--Create the Link to Active Directory
EXEC sp_addlinkedserver 'ADSI2', 'Active Directory
Services 2.0', 'ADSDSOObject', 'adsdatasource'
Go

--Create a login entry (not sure what this is for, since it looks like its putting in nulls)
EXEC sp_addlinkedsrvlogin ADSI2, false, null, null, null
Go

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

Thanks
0
 
LVL 8

Assisted Solution

by:jwarnken
jwarnken earned 300 total points
ID: 12084440
I do not know it this will help but I use a vbscript to query AD.
check out http://www.microsoft.com/resources/documentation/windows/2000/server/scriptguide/en-us/sas_ads_overview.mspx for a overview to script a query it may point you in the right direction.

good luck
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Always backup Domain, SYSVOL etc.using processes according to Microsoft Best Practices. This is meant as a disaster recovery process for small environments that did not implement backup processes and did not run a secondary domain controller that ne…
This process allows computer passwords to be managed and secured without using LAPS. This is an improvement on an existing process, enhanced to store password encrypted, instead of clear-text files within SQL
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

649 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