to it.

Querying Active
Directory From SSRS
To Customize The User Experience And Secure Reports
Wes Springob
(No relation to Mr. Squarepants)
Senior Business Intelligence Developer at American Express
 Co-Leader of the PASS Business Intelligence Virtual Chapter (
 Originally from Pittsburgh, now New Port Richey
Wife Ashley, Portrait Photographer
Extensive use of SSRS
Proficient T-SQL Developer
LDAP novice
0 AD administration experience
Recently exploring reporting of data sources that aren’t SQL Server
Have been wanting to work with AD Data in SSRS for years, but only recently have had the opportunity.
The names have been changed to protect the innocent = No live demo of Active Directory
Use Case
Why would you need to secure a report with AD? Doesn’t the Report Server do that
for you?
Short answer, yes it does:
We can do more
System of record
Many database systems store a user’s Active Directory ID. But they also store a lot
more, unfortunately, to “support” the application. Where in most cases, we should be
going back to query AD to ensure we are using the most current values.
 A SQL Server with SSRS installed
Management Studio
Visual Studio, BIDS or Report Builder to create RDL’s
Active Directory Explorer
1. Aids in writing LDAP queries
2. Free tool, downloadable from Technet
3. Any domain account typically has sufficient, Read Only, rights to query AD
Linked Servers / T-SQL
CLR / System.DirectoryServices Namespace
Custom Code in the RDL / System.DirectoryServices Namespace
Query AD Directly as a Data source From within the RDL / T-SQL
What is LDAP?
Lightweight Directory Access Protocol
“is an open, vendor-neutral, industry standard application protocol for accessing and maintaining
distributed directory information services over an Internet Protocol (IP) network.” -- Wikipedia
Used to query or filter Active Directory from T-SQL or .Net code.
Neither AD or LDAP are RDBMS
AD is Optimized for fast reads
Active Directory
dn = Distinguished Name
dc = domain component
ou = organizational unit
cn = common name
sn = surname (last name)
givenName = (First Name)
Linked Servers
Creating the report
Creating the AD data source
Creating the dataset
Creating the Query Expression
Solving the Use Case
Additional LDAP Queries
Active Directory Explorer Download:
This Slide Deck:

similar documents