115 lines
4.3 KiB
Plaintext
115 lines
4.3 KiB
Plaintext
//+-------------------------------------------------------------------------
|
|
//
|
|
// THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF
|
|
// ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
|
|
// THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
|
|
// PARTICULAR PURPOSE.
|
|
//
|
|
// Copyright (c) Microsoft Corporation. All rights reserved.
|
|
//
|
|
// DistQuery SQL Sample: ADSI Query from SQL
|
|
//
|
|
//--------------------------------------------------------------------------
|
|
|
|
Description
|
|
===========
|
|
The DistQuery SQL sample illustrates how to call ADSI using SQL Server.
|
|
It creates an employee performace review table, adds records, and then
|
|
joins the two tables before printing out a report.
|
|
|
|
This sample uses the LDAP: provider and is suitable for Windows 2000 and
|
|
later networks running Active Directory.
|
|
|
|
Sample Files
|
|
============
|
|
* Dq.Sql
|
|
|
|
Running the Sample
|
|
==================
|
|
This sample requires SQL Server 7.0 with Service Pack 2 or later. You must
|
|
install the ADSI Runtime on the SQL Server computer.
|
|
|
|
To run this sample
|
|
1. Open the file Dq.Sql in a text editor such as Notepad.
|
|
2. Run the Query Analyzer by selecting in the Start menu:
|
|
Programs, Microsoft SQL Server 7.0.
|
|
3. Logon to the SQL Server computer.
|
|
4. One at a time, Cut and Paste the SQL commands, substituting appropriate
|
|
domains and users for those given in the file. The following examples
|
|
assume the domain is Fabrikam.Com and the user is the Administrator.
|
|
|
|
How the Sample Works
|
|
====================
|
|
The Dq.Sql file contains the following commands.
|
|
|
|
1. These commands tell SQL Server to associate the word "ADSI" with the
|
|
ADSI OLE DB Provider, "ADSDSOObject".
|
|
|
|
sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
|
|
'ADSDSOObject', 'adsdatasource'
|
|
go
|
|
|
|
2. This command queries Active Directory for all users in the Fabrikam.Com
|
|
domain from SQL Server.
|
|
|
|
SELECT * FROM OpenQuery( ADSI,'<LDAP://DC=fabrikam,DC=com>;
|
|
(objectClass=user);adspath;subtree')
|
|
-or-
|
|
'-- Using the ADSI SQL Dialect
|
|
SELECT * FROM OpenQuery( ADSI, 'SELECT name, adsPath
|
|
FROM ''LDAP://DC=Fabrikam,DC=com''
|
|
WHERE objectCategory = ''Person''
|
|
AND objectClass= ''user''')
|
|
|
|
3. The following commands first create and then execute a view.
|
|
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
|
|
again later.
|
|
|
|
'-- Create the view
|
|
CREATE VIEW viewADUsers AS
|
|
SELECT *
|
|
FROM OpenQuery( ADSI,'<LDAP://DC=Fabrikam,DC=com>;
|
|
(&(objectCategory=Person)(objectClass=user));
|
|
name, adspath;subtree')
|
|
'-- Execute the view
|
|
SELECT * from viewADUsers
|
|
|
|
4. The following commands create a heterogeneous Join between the SQL
|
|
Server and Active Directory.
|
|
|
|
'-- Create an employee performance review table
|
|
CREATE TABLE EMP_REVIEW
|
|
(
|
|
userName varChar(40),
|
|
reviewDate datetime,
|
|
rating decimal
|
|
)
|
|
'-- Insert two records
|
|
INSERT EMP_REVIEW VALUES('Administrator', '2/15/1998', 4.5 )
|
|
INSERT EMP_REVIEW VALUES('Administrator', '7/15/1998', 4.0 )
|
|
'-- Now join the view and the review table
|
|
SELECT ADsPath, userName, ReviewDate, Rating
|
|
FROM EMP_REVIEW, viewADUsers
|
|
WHERE userName = Name
|
|
'-- Create a report for the join
|
|
CREATE VIEW reviewReport
|
|
SELECT ADsPath, userName, ReviewDate, Rating
|
|
FROM EMP_REVIEW, viewADUsers
|
|
WHERE userName = Name
|
|
|
|
5. These commands allow you to logon as a different user when connecting
|
|
to Active Directory by specifying alternative credentials.
|
|
|
|
'-- Maps 'FABRIKAM\Administrator' to the OLE DB user name
|
|
'-- (in this case the Active Directory user
|
|
'-- 'CN=Administrator,CN=Users,DC=fabrikam,DC=com')
|
|
sp_addlinkedsrvlogin ADSI, false, 'FABRIKAM\Administrator',
|
|
'CN=Administrator,CN=Users,DC=fabrikam,DC=com', 'passwordHere'
|
|
go
|
|
|
|
6. This command stops using the alternative credentials.
|
|
|
|
sp_droplinkedsrvlogin ADSI,'FABRIKAM\Administrator'
|
|
|