Solved

Is there a way to create a Reporting Services report on AD information?

Posted on 2007-11-21
9
2,361 Views
Last Modified: 2010-05-18
Is there a way to create a SQL Reporting Services report on Active Directory Objects.
What I am looking for is the User / Group Account, when it was created and by whom if at all possible.

Thanks
0
Comment
Question by:danorme
  • 4
  • 4
9 Comments
 
LVL 11

Expert Comment

by:bsharath
ID: 20332399
I am not sure with an Sql reporting service.But with a script you can get the created dates of users.
But again who created it will only be recorded if you have enabled auditing for ADS.
0
 
LVL 18

Expert Comment

by:PFrog
ID: 20334044
You can use the ADSI (Active Directory Service Interfaces)

To access this from SSRS you need to select OLE DB data source, then OLE DB Provider for Microsoft Directory Services.

More info here  
       http://msdn2.microsoft.com/en-us/library/ms190803.aspx
       http://technet.microsoft.com/en-gb/library/Bb742578.aspx
0
 

Author Comment

by:danorme
ID: 20350565
Ok, I am not able to do this.  Can someone help me with a little more detail.
Here is what I have done.
I created the query:
CREATE VIEW viewADContacts
AS
SELECT [Name], SN [Last Name], ST State
FROM OPENQUERY(Server Name, 'SELECT Name, SN, ST
   FROM ''LDAP://server/OU=Users,DC=domain,DC=com''
   WHERE objectCategory = ''Person'' AND
      objectClass = ''contact''')
GO
SELECT * FROM viewADContacts

and here is the error I get
Msg 7321, Level 16, State 2, Procedure viewADContacts, Line 3
An error occurred while preparing the query "SELECT Name, SN, ST
   FROM 'LDAP://server/OU=Users,DC=domain,DC=com'
   WHERE objectCategory = 'Person' AND
      objectClass = 'contact'" for execution against OLE DB provider "ADsDSOObject" for linked server "servername".
Msg 208, Level 16, State 1, Line 1
Invalid object name 'viewADContacts'.
0
 
LVL 18

Expert Comment

by:PFrog
ID: 20350854
Did you get any errors when you created the linked server? When you view the properties of the linked server, can you see the correct linked server login details?

Are you confident that the LDAP address is correct? ('LDAP://server/OU=Users,DC=domain,DC=com')

There's a post here with the same problem - have a read through and see if it applies to you
   http://www.lazydba.com/sql/1__26949.html
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:danorme
ID: 20351068
Ok, I am still not working.  Lets work this from the top down.
Linked server information:
Linked Server <server name>
Server Type: Other data source
Provider: OLE DB Provider for Microsoft Directory Services
Product Name: Active Directory Services 2.5
Data source: <server name>
Provider string: <server name>
Location: Null
Catalog: Null

Is the above correct?
0
 
LVL 18

Expert Comment

by:PFrog
ID: 20351369
Can you try using this?

EXEC sp_addlinkedserver @server = N'ADSI',
     @srvproduct=N'Active Directory Services 2.5',
     @provider=N'ADsDSOObject',
     @datasrc=N'Servername.domain.com'   --Just change this line to the details of your server
GO

Then right click on the linked server (in SSMS) and go to properties. Set the security to be made without using a security context.

Then run the query

SELECT * FROM OPENQUERY(ADSI, 'SELECT *
   FROM ''LDAP://server/OU=Users,DC=domain,DC=com''
   WHERE objectCategory = ''Person'' AND
      objectClass = ''contact''')
--Ensure ALL quotes are singles not doubles. i.e. '' should be two singles not one double.

Also, make sure the SQL Server service is being run using a domain account with access to AD (i.e. NOT LocalSystem)

0
 

Author Comment

by:danorme
ID: 20351446
I checked the SQL Server Service and it is using a domain account that can access AD.
The first query ran perfectly and I adjusted the context security.
The error from the second query is below:
Msg 7321, Level 16, State 2, Line 7
An error occurred while preparing the query "SELECT *
   FROM 'LDAP://ServerName/OU=Users,DC=domain,DC=com'
   WHERE objectCategory = 'Person' AND
      objectClass = 'contact'" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".
0
 
LVL 18

Accepted Solution

by:
PFrog earned 500 total points
ID: 20356699
Try the following:

1) In the security settings for the linked server, try setting "be made using this security context", and specify a domain user account.

2) See if this works
  SELECT * FROM OPENQUERY(ADSI, 'SELECT *
   FROM ''LDAP://server,DC=domain,DC=com''
   WHERE objectCategory = ''Person'' AND
      objectClass = ''contact''')

3) What happens if you run
   SELECT * FROM OPENQUERY(ADSI, 'SELECT * FROM ''LDAP://server''')
or
   SELECT * FROM OPENQUERY(ADSI, 'SELECT * FROM ''LDAP://server.domain.com''')
0
 

Author Closing Comment

by:danorme
ID: 31410492
PFrog, you da man!!!!!!  Thanks for all of your help!!!!!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

914 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now