1 / 21

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

kevork
Download Presentation

Querying Active Directory From SSRS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Querying Active Directory From SSRS To Customize The User Experience And Secure Reports

  2. 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

  3. 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

  4. 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:

  5. We can do more

  6. 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.

  7. Tools • A SQL Server with SSRS installed • Management Studio • Visual Studio, BIDS or Report Builder to create RDL’s • Active Directory Explorer • Aids in writing LDAP queries • Free tool, downloadable from Technet • Any domain account typically has sufficient, Read Only, rights to query AD

  8. Approaches • Linked Servers / T-SQL • CLR / System.DirectoryServices Namespace • Custom Code in the RDL / System.DirectoryServicesNamespace • Query AD Directly as a Data source From within the RDL / T-SQL

  9. 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

  10. Active Directory Explorer Definitions: • dn = Distinguished Name • dc = domain component • ou = organizational unit • cn = common name • sn = surname (last name) • givenName = (First Name)

  11. Linked Servers

  12. OpenQuery

  13. Creating the report

  14. Creating the AD data source

  15. Creating the dataset

  16. Creating the Query Expression

  17. Preview

  18. Solving the Use Case

  19. Queries?

  20. Additional LDAP Queries

  21. Resources: Active Directory Explorer Download: http://technet.microsoft.com/en-us/sysinternals/bb963907.aspx This Slide Deck: http://SQLWes.com

More Related