Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2908
  • Last Modified:

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
0
danorme
Asked:
danorme
  • 4
  • 4
1 Solution
 
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
 
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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

Featured Post

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!

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now