SQL%20Server%202000:%20Integration%20with%20AD%20and%20E2K

1
SQL Server 2000Integration with AD and E2K

• Dave Fackler
• Director, Intellinet
• MCDBA, MCSE, MCT

2
Agenda

• Active Directory
• Overview
• ADSI and LDAP
• Query Dialects and the ADSI Schema
• Querying from SQL2K
• Exchange 2000
• Overview
• Namespaces and Content Classes
• SQL Dialect
• Querying from SQL2K

3
Overview of Active Directory

• Directory service provided with Win2K
• Hierarchical organization of objects
• Domains
• Domain trees and forests
• Organizational units (OU)
• User and computer accounts
• Groups (security and distribution)
• Resources (contacts, printers, shares)
• Use AD tools to manage
• AD Domains and Trusts
• AD Users and Computers
• Highly evolved replication

4
Active Directory Interfaces

• LDAP (Lightweight Directory Access Protocol)
• Connection protocol between client and server
• Provides query and search capabilities
• Programmatic interface via C, C
• ADSI (Active Directory Services Interface)
• COM interface to AD
• OLE DB provider for AD (ADsDSOObject)
• Uses LDAP as connection protocol
• Provides mechanism for integration with SQL2K

5
ADSI Query Dialects

• LDAP
• Binds to an AD location to query against
• Specifies filtering criteria
• Lists AD properties to return
• ltLDAP//OUDirectors,OUAtlanta,OUIntellinet,DC
  vizability, DCintellinet,DCcomgt(objectClassUse
  r)Name,Titlesubtree
• SQL
• SELECT list provides AD properties to return
• FROM clause provides AD location to query against
• WHERE clause allows filtering
• select Name, Title
• from LDAP//OUDirectors,OUAtlanta,OUIntelli
  net,
• where objectClass User

6
ADSI Schema

• IADsUser (objectClassUser)
• Not all IADsUser properties are available via
  LDAP
• Use AD property names, not ADSI property names
• IADsGroup (objectClassGroup)
• Very few properties are actually available
• Cannot query for array-based properties
• Group members for example
• Dig into the ADSI SDK for information

7
Querying AD from SQL2K

• Create a linked server using ADsDSOObject
• exec sp_addlinkedserver 'ADSI', 'Active
  Directory Services 2.5',
• 'ADsDSOObject', 'adsdatasource
• Execute LDAP queries via openquery()
• select convert(varchar(30), Name) as FullName,
• convert(varchar(30), Title) as Title
• from openquery(ADSI,
• 'ltLDAP//OUDirectors,OUAtlanta,OUIntelli
  net,gt
• (objectClassUser)Name,Titlesubtree')
• select convert(varchar(30), Name) as FullName,
• convert(varchar(30), Title) as Title,
• from openquery(ADSI,
• 'select Name, Title
• from ''LDAP//OUDirectors,OUAtlanta,OU
  Intellinet,''
• where objectClass ''User''')

8
Overview of Exchange 2000

• Next generation messaging system
• Win2K integration
• Integrates with AD for directory services
• Message transport via Win2K services (IIS, DNS)
• Web Storage System
• Provides HTTP, WebDAV, and XML access
• Supports multiple databases for mail and folders
• Real-time collaboration
• Programmatic interfaces
• CDO, CDOEXM, and ADO
• OLE DB Provider for Exchange (ExOLEDB)

9
Content Classes in E2K

• Every resource belongs to a content class
• urncontent-classesmessage
• urncontent-classesperson
• urncontent-classesappointment
• Each content class has a set of properties
• urnschemasmailheadersubject
• urnschemascontactsn
• urnschemascalendardtstart
• Custom content classes can be defined

10
Namespaces in E2K

• Namespaces provides unique names for every
  property in the Web Storage System
• Allows properties with the same name to be
  distinguished (for example, the From property)
• A namespace includes various properties and may
  be used by different types of resources
• DAV
• urnschemashttpmail
• urnschemascontacts
• urnschemascalendar
• ExOLEDB adds file\\.\backofficestorage and
  http// namespaces

11
SQL Dialect for E2K

• Uses syntax similar to SQL2K with Full Text
  Indexing options included
• select ltlistgt
• from scope(ltscope1gt, ltscope2gt, )
• where ltconditiongt
• order by ltlistgt
• group by ltlistgt
• rank by ltlistgt
• Select clause specifies the properties to
  return, using namespace notation
• From specifies the scope and path to search
• Scope specifies the depth of the search
• Path specifies the URL to search

12
SQL Dialect for E2K

• Where clause specifies the filtering condition
• Can use any of the following predicates,
  functions, and clauses
• cast, contains, formsof, freetext, like, rank by
• Conditions connected via and and or
• Group by and Order by used as in SQL2K
• Rank by allows for ranked results
• select DAVdisplayname
• from scope(shallow traversal of
  http//myserver/public/test)
• where DAVisfolder TRUE
• order by DAVdisplayname

13
Querying E2K from SQL2K

• Create a linked server using ExOLEDB
• exec sp_addlinkedserver E2K_PF', 'Exchange OLE
  DB provider',
• ExOLEDB.DataSource.1', 'file\\.\backofficestor
  age\
• vizability.intellinet.com\public folders
• Use openquery() to execute queries
• select convert(varchar(30), "urnschemascontacts
  sn")
• as LastName,
• convert(varchar(30), "urnschemascontacts
  givenName")
• as FirstName
• from openquery(E2K_PF,
• select "urnschemascontactssn",
• "urnschemascontactsgivenName"
• from scope(''shallow traversal of
• ".\Intellinet\Atlanta\Atlanta
  Contacts"'')')

14
Resources

• ADSI SDK
• http//msdn.microsoft.com/library/default.asp?url
  /library/en-us/netdir/adsi/active_directory_servi
  ce_interfaces_adsi.asp?frametrue
• Exchange 2000 SDK and Samples
• http//download.microsoft.com/download/exchplatin
  umbeta/ SDK/September_2001/NT5/EN-US/ExchangeSDKDo
  cs.exe

15
Conclusion

• Active Directory
• ADSI and LDAP
• Query Dialects
• Querying from SQL2K
• Exchange 2000
• Namespaces
• SQL Dialect
• Querying from SQL2K
• Go build integrated solutions!