How can I use SQL 7's HDQ facilities to get at NT domain or Exchange information?

A. This information is supposed to get ADSI working from SQL Server, however your mileage may vary (please provide any feedback on updating/correcting the information here).

In order to get an ADSI linked server to SQL Server 7.0, you will need to install the following pre-requisites. ADSI 2.0 doesn't work with MDAC 2.1 (installed w/ SQL Server 7.0). For more information on the issues of ADSI 2.0 and MDAC 2.1, see:

http://support.microsoft.com/support/kb/articles/q216/7/09.asp

Requirements:
========



1. You must install SQL Server 7.0. For more information about SQL Server, visit http://www.microsoft.com/sql 

2. You must install ADSI 2.5 Runtime on the machine SQL Server is installed :-
http://www.microsoft.com/adsi -or- 
ftp://ftp.microsoft.com/bussys/sitesrv/sitesrv-public/fixes/usa/siteserver3/sp2/x86/adsi/ads.exe


3. You must either use Active Directory (Windows 2000), an NT 4.0 SAM database or Exchange Server as a basis for directory information.


Step by Step Instructions

============


In to add a linked server, do the following:

1. Run the Query Analyzer 

2. Logon the the SQL Server machine.

3. Execute the following line. 

/***********************************************************/
sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject','adsdatasource'
go
/***********************************************************/

This tells SQL Server to associate word 'ADSI' with ADSI OLE DB provider - 'ADSDSOObject'

Following is an example scenario of common operations: 

============================================



/***********************************************************/
SELECT * FROM OpenQuery( 
ADSI,'<LDAP://DC=Microsoft,DC=com>;(&(objectCategory=Person)(objectClass=use
r));name, adspath;subtree')
/***********************************************************/

Note: you should change the DC=.., DC=.. accordingly. This query asks for 
all users in the 'Microsoft.com' domain.


You may also use the ADSI SQL Dialect, for example:

/***********************************************************/
SELECT * FROM OpenQuery( ADSI, 'SELECT name, adsPath FROM 
''LDAP://DC=Microsoft,DC=com'' WHERE objectCategory = ''Person'' AND 
objectClass= ''user''') 
/***********************************************************/

Creating, Executing a View

================



You may create a view for data obtained from Active Directory. Note that 
only the view definition is stored in SQL Server, not the actual result 
set. Hence, you may get a different result when you execute a view later.

To create a view, type and execute:

/***********************************************************/
CREATE VIEW viewADUsers AS
SELECT * FROM OpenQuery( 
ADSI,'<LDAP://DC=Microsoft,DC=com>;(&(objectCategory=Person)(objectClass=use
r));name, adspath;subtree')
/***********************************************************/

To execute a view, type

/***********************************************************/
SELECT * from viewADUsers 
/***********************************************************/ 

Heteregenous Join between SQL Server and Active Directory


Create a SQL table, a employee performance review table

/***********************************************************/
CREATE TABLE EMP_REVIEW
(
userName varChar(40),
reviewDate datetime,
rating decimal 
)
/***********************************************************/

Insert few records

/***********************************************************/
INSERT EMP_REVIEW VALUES('Administrator', '2/15/1998', 4.5 )
INSERT EMP_REVIEW VALUES('Administrator', '7/15/1998', 4.0 )
/***********************************************************/

Note: You can insert other user names.

Now join the two

/***********************************************************/
SELECT ADsPath, userName, ReviewDate, Rating 
FROM EMP_REVIEW, viewADUsers
WHERE userName = Name
/***********************************************************/

Now, you can even create another view for this join,

/***********************************************************/
CREATE VIEW reviewReport
SELECT ADsPath, userName, ReviewDate, Rating 
FROM EMP_REVIEW, viewADUsers
WHERE userName = Name 
Advanced Operations
/***********************************************************/

You may log-on as different user when connecting to the Active Directory. 
To specify the alternate credential. 

Example:

/***********************************************************/
sp_addlinkedsrvlogin ADSI, false, 'MICROSOFT\Administrator', 
'CN=Administrator,CN=Users,DC=Microsoft,DC=com', 'passwordHere'
/***********************************************************/

This line tells Distributed Query that if someone logs on in SQL Server as 
'Microsoft\Administrator', the Distributed Query will pass the 
'CN=Administrator,CN=Users, DC=Microsoft, DC=com' and 'passwordHere' to 
ADSI as the credentials.

To stop connecting as an alternate credential, type:

/***********************************************************/
sp_droplinkedsrvlogin ADSI,'MICROSOFT\Administrator' 
/***********************************************************/


The following links may also be helpfull:

====================================

http://msdn.microsoft.com/developer/news/feature/120197/Exchange/adsi.htm

http://msdn.microsoft.com/library/sdkdoc/adsi/ds2cli_9x9u.htm#_ds_formal_gra
mmar_for_the_sql_queries

http://msdn.microsoft.com/library/sdkdoc/adsi/ds2intro_0ckz.htm
Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish