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 ID: 156224
Download Presentation The PPT/PDF document "Querying Active Directory From" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Querying Active Directory From SSRS
To Customize The User Experience And Secure ReportsSlide2
Introduction
Wes Springob
(No relation to Mr.
Squarepants)BI Consultant at New York Life Originally from Pittsburgh, now New Port RicheyWife Ashley, Portrait Photographerhttp://SQLWes.comhttp://twitter.com/SQLWeshttp://LinkedIn.com/in/SQLWesSlide3
Disclosure:
Extensive use of SSRS
Proficient T-SQL Developer
LDAP novice0 AD administration experienceRecently exploring reporting of data sources that aren’t SQL ServerHave 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 Slide4
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:Slide5
We can do moreSlide6
System of record
Many database systems store a user’s Active
D
irectory 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. Slide7
Tools
A SQL Server with SSRS installed
Management StudioVisual Studio, BIDS or Report Builder to create RDL’sActive Directory ExplorerAids in writing LDAP queriesFree tool, downloadable from TechnetAny domain account typically has sufficient, Read Only, rights to query ADSlide8
Approaches
Linked Servers / T-SQL
CLR /
System.DirectoryServices NamespaceCustom Code in the RDL / System.DirectoryServices NamespaceQuery AD Directly as a Data source From within the RDL / T-SQLSlide9
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.” -- WikipediaUsed to query or filter Active Directory from T-SQL or .Net code.Neither AD or LDAP are RDBMSAD is Optimized for fast readsSlide10
Active Directory Explorer
Definitions:
dn
= Distinguished Name
dc
= domain
component
ou
= organizational
unit
cn
= common name
sn
= surname (last name)
givenName
= (First Name)Slide11
Linked ServersSlide12
OpenQuerySlide13
Creating the reportSlide14
Creating the AD data sourceSlide15
Creating the datasetSlide16
Creating the Query ExpressionSlide17
PreviewSlide18
Solving the Use CaseSlide19
Queries?Slide20
Additional LDAP QueriesSlide21
Resources:
Active Directory Explorer Download:
http://
technet.microsoft.com/en-us/sysinternals/bb963907.aspxThis Slide Deck:http://SQLWes.com