Solved

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

Posted on 2007-11-21
9
2,501 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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
 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql Permission 6 71
Record open by another user 6 72
How to simplify my SQL statement? 14 55
How can I exclude some wording in a like statement? 39 78
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

685 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