Transcript Querying Active Directory From SSRS

Querying Active
Directory From SSRS
To Customize The User Experience And Secure Reports
Introduction
Wes Springob
(No relation to Mr. Squarepants)
BI Consultant at New York Life
 Originally from Pittsburgh, now New Port Richey
Wife Ashley, Portrait Photographer
http://SQLWes.com
http://twitter.com/SQLWes
http://LinkedIn.com/in/SQLWes
Disclosure:




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.
Tools
 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
Approaches
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
Explorer
Definitions:
dn = Distinguished Name
dc = domain component
ou = organizational unit
cn = common name
sn = surname (last name)
givenName = (First Name)
Linked Servers
OpenQuery
Creating the report
Creating the AD data source
Creating the dataset
Creating the Query Expression
Preview
Solving the Use Case
Queries?
Additional LDAP Queries
Resources:
Active Directory Explorer Download:
http://technet.microsoft.com/en-us/sysinternals/bb963907.aspx
This Slide Deck:
http://SQLWes.com