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

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
danormeAsked:
Who is Participating?
 
PFrogConnect With a Mentor Commented:
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
 
bsharathCommented:
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
 
PFrogCommented:
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
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.

 
danormeAuthor Commented:
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
 
PFrogCommented:
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
 
danormeAuthor Commented:
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
 
PFrogCommented:
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
 
danormeAuthor Commented:
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
 
danormeAuthor Commented:
PFrog, you da man!!!!!!  Thanks for all of your help!!!!!
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.