Solved

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

Posted on 2007-11-21
9
2,642 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
[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
  • 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

627 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