AD Data via SQL Server and BI Studio

Way 1: Accessing Active Directory Data in SQL Server Reporting Services
This is the procedure followed for Getting Active Directory Data In Reporting Services
1.Create New Report Server project

2.Add New Report named as samplereport.rdl

3.In sampleReport.rdl select Data Tab and Create New Dataset

4.In Data Source Wizard Select Type as oledb

5.In connection string select provider as “oledb provider for Microsoft DirectoryServices”give Server name as where Active Directory is Residing and specify location, Username and Password
6.Under Credential Tab of Data Source wizard enter password and click ok

7. Then create query according to our requirements for getting Data from Active Directory. Here I am retrieving all usernames from Active Directory “select sAMAccountName from 'LDAP://xxx.xxx.xxx.xxx' where objectclass='user'”

8.Then Binding Dataset Data to the table in samplereport.rdl

9.Then after Deploying Report,the Extracted Data from active directory is displayed in Report

Way 2: Connect using Linked Server

1. Connect Database in SQL Management Studio
2. Right Click the 'Server Objects' Folder and Select New-->Linked Server
3. Under General Tab give one Linked Server Name, Then Select Other data source
4. Provider name: Active Directory Services
5. Data source: LDAP:\\xxx.xxx.xxx.xxx
6. Provider string: ADSDSOObject
7. Location: ubi.co.in
8. Under Security Tab click add button
9. Local Login: Local Database Login Name
10. Remote User: administrator@smile.co.in
11. Remote Password: Give Password
12. Select 'Be made using the login's current security context' then click ok 13. Now the Linked Server is created 14. Open the Query Analyzer and paste the following query Select * from openquery(LN,'select name from ''LDAP://xxx.xxx.xxx.xxx" where objectClass=''user''')