SQL Server 2000: Integration with AD and E2K

1. SQL Server 2000:Integration with AD and E2K Dave Fackler Director, Intellinet MCDBA, MCSE, MCT SQL2K-AD-E2K Integration

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 SQL2K-AD-E2K Integration

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 SQL2K-AD-E2K Integration

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 SQL2K-AD-E2K Integration

5. ADSI Query Dialects • LDAP • Binds to an AD location to query against • Specifies filtering criteria • Lists AD properties to return <LDAP://OU=Directors,OU=Atlanta,OU=Intellinet,DC=vizability, DC=intellinet,DC=com>;(objectClass=User);Name,Title;subtree • 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://OU=Directors,OU=Atlanta,OU=Intellinet,…’ where objectClass = ‘User’ SQL2K-AD-E2K Integration

6. ADSI Schema • IADsUser (objectClass=User) • Not all IADsUser properties are available via LDAP • Use AD property names, not ADSI property names • IADsGroup (objectClass=Group) • Very few properties are actually available • Cannot query for array-based properties • Group members for example • Dig into the ADSI SDK for information SQL2K-AD-E2K Integration

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, '<LDAP://OU=Directors,OU=Atlanta,OU=Intellinet,…>; (objectClass=User);Name,Title;subtree') select convert(varchar(30), [Name]) as FullName, convert(varchar(30), Title) as Title, from openquery(ADSI, 'select Name, Title from ''LDAP://OU=Directors,OU=Atlanta,OU=Intellinet,…'' where objectClass = ''User''') SQL2K-AD-E2K Integration

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) SQL2K-AD-E2K Integration

9. Content Classes in E2K • Every resource belongs to a content class • urn:content-classes:message • urn:content-classes:person • urn:content-classes:appointment • Each content class has a set of properties • urn:schemas:mailheader:subject • urn:schemas:contact:sn • urn:schemas:calendar:dtstart • Custom content classes can be defined SQL2K-AD-E2K Integration

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: • urn:schemas:httpmail • urn:schemas:contacts • urn:schemas:calendar • ExOLEDB adds “file:\\.\backofficestorage” and “http://” namespaces SQL2K-AD-E2K Integration

11. SQL Dialect for E2K • Uses syntax similar to SQL2K with Full Text Indexing options included select <list> from scope(<scope1>, <scope2>, …) where <condition> order by <list> group by <list> rank by <list> • “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 SQL2K-AD-E2K Integration

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 “DAV:displayname” from scope(‘shallow traversal of “http://myserver/public/test”’) where “DAV:isfolder” = TRUE order by “DAV:displayname” SQL2K-AD-E2K Integration

13. Querying E2K from SQL2K • Create a linked server using ExOLEDB exec sp_addlinkedserver ‘E2K_PF', 'Exchange OLE DB provider', ‘ExOLEDB.DataSource.1', 'file:\\.\backofficestorage\ vizability.intellinet.com\public folders‘ • Use openquery() to execute queries select convert(varchar(30), "urn:schemas:contacts:sn") as LastName, convert(varchar(30), "urn:schemas:contacts:givenName") as FirstName from openquery(E2K_PF, ‘select "urn:schemas:contacts:sn", "urn:schemas:contacts:givenName" from scope(''shallow traversal of ".\Intellinet\Atlanta\Atlanta Contacts"'')') SQL2K-AD-E2K Integration

14. Resources • ADSI SDK: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/netdir/adsi/active_directory_service_interfaces_adsi.asp?frame=true • Exchange 2000 SDK and Samples: http://download.microsoft.com/download/exchplatinumbeta/ SDK/September_2001/NT5/EN-US/ExchangeSDKDocs.exe SQL2K-AD-E2K Integration

15. Conclusion • Active Directory • ADSI and LDAP • Query Dialects • Querying from SQL2K • Exchange 2000 • Namespaces • SQL Dialect • Querying from SQL2K Go build integrated solutions! SQL2K-AD-E2K Integration