Using the Customization Handler Feature in RDS 2.1

Kamaljit Bath

January 1999
Updated May 5, 2010

Note: This article refers to a data access technology that is no longer supported. Please review the current data access technologies and base your future development on them rather than the technology referred to in this article.

Summary: The customization handler feature, which was first introduced in RDS 2.0, enables custom logic to be executed on the server when RDS is used to work directly with remote OLE DB providers. This article describes the scenarios that are enabled by this feature and documents the steps needed to make use of it.

The following topics are discussed in this document:

• Introduction to the customization handler feature
• Implementation details for the customization handler feature
• Using the default handler shipped with RDS
• Writing a new custom handler

Introduction to the Customization Handler Feature

RDS 1.5, which ships with Microsoft® Internet Information Server 4.0, enables ADO consumers to work with remote OLE DB providers running in separate processes or on separate machines connected over HTTP or DCOM protocols. Client-side applications have the option of working directly with an OLE DB provider on the remote process/machine or with a COM component on the remote process/machine that uses ADO recordsets as input/output parameters or return values.

When working in the first mode (directly with a remote OLE DB provider, as described above), the client-side application supplies all the information needed by the specified provider, such as the connection string and the command string. In a sense, this extends the traditional client/server model to work over distributed networks. The client specifies the data store to work with and the commands to be executed, and the server simply executes these commands and returns the results, as ADO recordsets, to the client.

In the second case (working with a COM component that uses ADO recordsets), the client-side application invokes public methods only on the middle-tier component and uses the ADO recordset just as a parameter value. This is true three-tier computing, where the presentation and user interface parts stay in the client-side application, business logic in the middle-tier COM component, and the persisted state of data and referential integrity checking remains in the data store.

Starting with RDS 2.0, developers are provided with another data access paradigm, where they can write their own server-side business-rules logic and still have their client-side applications work directly with the remote OLE DB provider without going through a business object. This enables the following scenarios:

• Web server administrators can control access to OLE DB data stores from RDS clients. Because RDS enables consumers, connected over HTTP, to work directly with OLE DB providers, there is a security risk exposed. RDS works over proxies and firewalls. This means that the OLE DB data store (typically an ODBC connection to a database such as SQL Server or Microsoft Access) is exposed to all Internet clients that have relevant connection information, such as the database name and the user ID/password. The customization feature in RDS 2.0 allows server administrators to control Internet connections to OLE DB providers hosted on the server.
• Custom business logic, such as validation checks, can be added to applications using RDS to connect to remote OLE DB providers.
• Server administrators can easily write their own customized handler logic. To help administrators start using the customization feature, a simple handler that works with most SQL-based OLE DB providers is shipped. The behavior of this handler is driven by an .ini file installed on the server.
• To help administrators write their own customized handlers, the DA SDK also includes source code that is very similar to the shipped simple handler.

Implementation Details for the Customization Handler Feature

When RDS is used by client-side applications to work directly with remote OLE DB providers, an RDSServer.DataFactory object on the Web server handles client requests. The DataFactory object implements Query and Execute methods that can open a new recordset against a user-specified OLE DB data source object by running a user-specified command. It also implements the methods SubmitChanges and Synchronize, which can be used to apply the changes made to a recordset to a user-specified OLE DB data source object. (The update feature works only against SQL-based OLE DB providers—for example, the OLE DB Provider for ODBC and the OLE DB Provider for SQL Server.)

Writing a custom handler object that implements a specific interface called IDataFactoryHandler can enhance the DataFactory functionality. Developers can build their own custom objects to implement the IDataFactoryHandler interface. These objects are then implicitly called by DataFactory and used to extend the default functionality. This is illustrated by the following code snippet, where a client-side application is opening an ADO Recordset based on a remote OLE DB provider.

Dim AdoRs as ADODB.Recordset
AdoRs.ActiveConnection = "Handler=MyHandler.obj;Provider=MS Remote;Remote Server=https://Hyperspace;DSN=AdvWorks;"
AdoRs.Source = "Select * from Products"
AdoRs.Open

In the above code segment, the developer has specified that the default handler object to be used on the server side is the MyHandler.obj object. When this code is executed, the DataFactory object is called by default on the server. However, before the Query method on the DataFactory object opens the recordset, it creates the MyHandler.obj object and then calls QueryInterface for its IDataFactoryHandler interface. On this interface, it calls the GetRecordset method, passing in the connect and command strings. The user-written code inside the handler will open an ADO Recordset and pass it back to DataFactory. The user can use ADO events inside the handler to customize the behavior of the Connection and Recordset objects.

The following table shows which IDataFactoryHandler methods are called by each of the DataFactory methods.

By default, MDAC 2.1 clients will use the new DataFactory methods Execute and Synchronize. If the MDAC version on the server is older than version 2.1, it will return an error stating that the method does not exist. If a 2.1 client gets this error, it either will retry with Query or SubmitChanges or, if these older methods cannot satisfy the request, will return an error. For example, if the client wants to execute a parameterized query, the client must use Execute, because Query does not support this.

Architecture Diagram

When the client code uses RDS, without specifying the handler, to work with remote OLE DB providers, the DataFactory executes the client request. The DataFactory uses ADO to work with the underlying OLE DB provider.

When the client code uses RDS to work with remote OLE DB providers and also specifies the handler to be used, the client request is still executed by the DataFactory. The DataFactory in turn uses the specified handler to service the client requests.

Custom Handler

An RDS server custom handler is a COM Automation object that implements the IDataFactoryHandler interface. It must have its ProgID registered in the server's registry as NewHandler Progid under the following key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DataFactory\HandlerInfo \safehandlerlist

To use the specified handler, the client code used to open an ADO Recordset must include the tag "handler=<handler progid>;" in the connection string. If RDS.Datacontrol is used by the client to open the Recordset, the Handler property must be set to the handler's ProgID. If the client is coding directly to OLE DB, the connection string must contain the "handler=<handler progid>;" tag.

Server administrators can require the use of a handler with an RDS server simply by inserting the entry "HandlerRequired=1" and/or the DefaultHandler entry in the following key in the server's registry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DataFactory\HandlerInfo

If the entry "HandlerRequired=1" is inserted and there is no DefaultHandler, then all RDS requests that do not include the "handler=<handler progid>;" tag will be rejected. And <handler progid> must point to a valid custom handler object that is included in the safehandlerlist registry entry described above.

MSDFMAP.Handler

RDS is typically used to work with OLE DB providers for SQL-based data stores. The DataFactory object can directly open and update rowsets from such data stores. To secure such data stores from direct exposure to Internet clients, MDAC 2.0 installs a default custom handler object—MSDFMAP.Handler. The behavior of this handler is driven by the file Msdfmap.ini, also installed with MDAC. The Msdfmap.ini file is installed in the Windows directory (C:\Winnt, for example). This .ini file can be configured by the server administrator to customize the behavior of the MSDFMAP.Handler object.

Configuring MSDFMAP.Handler

Changing the parameters in the supplied .ini file can modify the behavior of this handler. The .ini file(s) must reside in the Windows directory (C:\Winnt, for example).

Sample Msdfmap.ini file:

;[connect name] will modify the connection if rdsControl.connect="name"
;[connect default] will modify the connection if name is not found
;[sql name] will modify the Sql if RDSCONTROL.sql="name(args)"
;[sql default] will modify the Sql if name is not found
;Override strings: Connect, UserId, Password, Sql.
;Only the Sql strings support parameters using "?"
;The override strings must not equal "" or they are ignored
;A Sql entry must exist in each sql section or the section is ignored
;An Access entry must exist in each connect section or the section is ignored
;Access=NoAccess
;Access=ReadOnly
;Access=ReadWrite
;[userlist name] allows specific users to have special access
;The Access is computed as follows:
;  (1) First take the access of the connect section
;  (2) If a user entry is found, it will override

[connect default]
;If we want to disable unknown connect values, we set Access to NoAccess
Access=NoAccess
[sql default]
;If we want to disable unknown sql values, we set Sql to an invalid query.
Sql=" "

[connect CustomerDatabase]
Access=ReadWrite
Connect="DSN=AdvWorks"

[sql CustomerById]
Sql="SELECT * FROM Customers WHERE CustomerID = ?"
[connect AuthorDatabase]
Access=ReadOnly
Connect="DSN=MyLibraryInfo;UID=MyUserID;PWD=MyPassword"

[userlist AuthorDatabase]
Administrator=ReadWrite

[sql AuthorById]
Sql="SELECT * FROM Authors WHERE au_id = ?"

The administrator can change this .ini file to install customized DataFactory behavior on the server. For example, when a client sends in the following request, it is handled as described below:

Rs.open "CustomerById(2)", "Handler=MSDFMAP.Handler;Provider=MS Remote;Remote Server=https://mysite;Data Source=CustomerDatabase"

With the sample .ini file shown above, this is equivalent to the following command:

Rs.open "SELECT * FROM Customers WHERE CustomerId = 2", "DSN=AdvWorks"

The administrator can add new sections to the .ini file or change existing settings to define new mappings. Another option is to write a completely new handler object. The process for writing a new handler is described later in this article. The administrator can also have multiple .ini files. If a different .ini file is to be used (instead of the supplied Msdfmap.ini, which is used by default), the connection string at the client must specify the .ini file name. Example:

RDSCONTROL.Handler = "MSDFMAP.Handler,myOtherNameSpace.ini"

Or:

Rs.open "CustomerById(2)", "Provider=MS Remote;Remote Server=https://hyperspace;Handler=MSDFMAP.Handler,myOtherNameSpace.ini;Data Source=CustomerDatabase"

Note

   

All .ini files to be used with MSDFMAP.Handler must exist in the %SYSTEMROOT% directory.

Modifying the .ini File

The following points explain the workings of the .ini file:

• The default .ini file is installed in the %SYSTEMROOT% directory (c:\winnt, for example) and is called Msdfmap.ini.

• Four kinds of sections can be contained in the file. They are all identified by their keywords—Connect, used to specify connection string mappings; SQL, used to specify command string mappings; UserList, used to override default access permissions for a list of users; and Logs, used to specify a file name where all errors can be logged.

  The keywords Connect, SQL, and Userlist are explained in the following sections. The Logs keyword is used to specify a file name where all run-time errors associated with the handler can be logged. The syntax for specifying the Logs keyword is as follows:

  [Logs]
  err='c:\temp\HandlerLog.err'
  

  If the file specified under the Logs keyword already exists, new errors are appended to it. Otherwise, a new file is created.

• The values ConnectionString and CommandString in the client request are converted to those specified in the matching Connect and SQL sections, respectively.

• Userlist works in a slightly different way than the Connect and SQL keywords. The Userlist keyword works as a subsection inside the connect or sql section. (The matching is done based on the unique identifier for sql or connect.) For example:

  [connect customerdatabase]
  connect=dsn=pubs;uid=sa;pwd=;
  access=readonly
  
  [userlist customerdatabase]
  administrator=readwrite
  

  Thus, a customerdatabase identifier is used with the Connect keyword. When the client comes in with the data store value in connectionstring set to customerdatabase, the connection is mapped to "dsn=pubs;uid=sa;pwd=;". By default, access is set to readonly for this connection unless the user is the administrator, who gets a readwrite permission.

  The userlist subsection is matched with the connect section by matching the identifier customerdatabase. The same is true for the Userlist and SQL keywords.

• To use the Userlist option in the .ini file, the server must be able to ascertain the user credentials of RDS clients. This is not a problem when the client uses DCOM protocol to connect to the server. But if HTTP is used, all users connect to the server under the credentials of a default user specified under the "Anonymous" authentication scheme of Internet Information Server (IIS). The server administrator may choose to run a different authentication scheme to capture more information about Internet clients and to provide them specific services based on their identity.

  To change the authentication scheme, do the following: On the server, start the IIS service manager and select the virtual directory under the default Web site. Right-click the selected directory. In the Properties dialog box, click the Directory Security tab, and then click Edit on Anonymous Access and Authentication control. Next, disable Anonymous Access and select Basic Authentication or NT Challenge/Response. Users can now log in to the IIS Web server by using their client credentials. If Userlist subsections are present in the .ini file, the actions specified therein are applied to users matching the Userlist keyword.

• If the same identifier appears more than once with the same keyword, only the first occurrence is used. The subsequent occurrences are ignored. Therefore, if the following lines are present:

  [sql customerbyid]
  select * from customers where cust_id = ?
  …
  …
  [sql customerbyid]
  select * from customer
  

  only the first occurrence of customerbyid will be used. All sql strings containing customerbyid will be converted to the "select * from customers where cust_id = ?" format.

• The default handler can be invoked only if the client's connection string contains "Handler=msdfmap.handler" or if the DefaultHandler registry entry is msdfmap.handler. In the absence of this substring, DataFactory continues work in its default (nonhandler) mode. To work without a handler, server administrators can add the entry "HandlerRequired=0" to the following key in the server registry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DataFactory \HandlerInfo

Absence of this key—or if the value of the HandlerRequired key is 0—means that the use of the "Handler=" tag is not mandatory. RDS is thus enabled to work in the same manner as it did in RDS 1.5.

Using the Default Handler Shipped with RDS

A DataFactory handler is a COM object that implements the IDataFactoryHandler interface. The following files are included in the Data Access SDK so that users can create their own DataFactory handlers:

• Msdfhdl.idl (interface definition file).

• Msdfhdl.tlb (type library generated from Msdfhdl.idl for the Microsoft Visual Basic® sample.

  Note

     

  Msdfhdl.tlb is version specific. For example, the version shipped in the Data Access SDK 2.0 will not work properly with Visual Basic when MDAC 2.1 is installed.)

Source code (for the Visual Basic and Microsoft Visual C++® versions) similar to that of the default handler, MSDFMAP.Handler, is also included in the SDK. This can be used as a reference for implementing new handlers.

Msdfhdl.idl

[
   uuid(D80DE8B3-0001-11d1-91E6-00C04FBBBFB3),
   version(1.0)
]
library MSDFHDL
{
   importlib("stdole32.tlb");
   importlib("stdole2.tlb");

   // ********** MSADO15.DLL must be registered on your machine ********
   // TLib : Microsoft ActiveX Data Objects 2.1 Library
   // {00000200-0000-0010-8000-00AA006D2EA4}
   importlib("C:\Program Files\Common Files\System\ado\msado15.dll");

   [
      odl,
      uuid(D80DE8B5-0001-11d1-91E6-00C04FBBBFB3),
      version(1.0)
   ]
   interface IDataFactoryHandler : IUnknown
   {
      // The Recordset returned from GetRecordset:
      // must be created with CursorLocation = AdUseClientBatch,
      // and adRunAsync must NOT be used! (Use adUnknown or 8
      // in rs.Open.)
      HRESULT _stdcall GetRecordset(
         [in] BSTR conn,
         [in] BSTR args,
         [in] BSTR query,
         [out, retval] _Recordset **ppRS);

      // DataFactory will use the ActiveConnection property
      // on the recordset after calling Reconnect.
      HRESULT _stdcall Reconnect(
         [in] BSTR conn,
         [in] BSTR args,
         [in] _Recordset *pRS);

      // New method for MDAC 2.1 only
      HRESULT _stdcall OpenConnection(
         [in] BSTR conn,
         [in] BSTR args,
         [in] long lFlags,  // 1=WriteAccess, 2=ModifyQuery
         [in, out] BSTR * pQuery,
         [out, retval] _Connection **ppConnection);
    };
};

Handler Properties

None.

Handler Methods

GetRecordset

This function will create a new ADO Recordset using the arguments provided. The Recordset must be opened with adLockBatchOptimistic. The Connection and Recordset objects must have the Cursor Location property set to adUseClientBatch.

• The conn argument is the connection string.
• The args argument is the string after the first comma in the Handler value. Examples:
  • "Handler=progid,arg1,arg2;" will pass "args=arg1,arg2".
  • "Handler=progid" will pass "args=NULL".
• The query argument is the command text.
• The ppRS argument is where the function returns the new ADO Recordset.

Reconnect

This function will create a new ADO Connection object and attach the given ADO Recordset object to it. The Connection must have the Cursor Location property set to adUseClientBatch.

• The conn argument is the connection string.
• The args argument is the string after the first comma in the Handler value. Examples:
  • "Handler=progid,arg1,arg2;" will pass "args=arg1,arg2".
  • "Handler=progid" will pass "args=NULL".
  • The pRS argument is an ADO Recordset.

OpenConnection

This function will create a new ADO Connection using the arguments provided. The Connection object must have the Cursor Location property set to adUseClientBatch.

• The conn argument is the connection string.
• The args argument is the string after the first comma in the Handler value. Examples:
  • "Handler=progid,arg1,arg2;" will pass "args=arg1,arg2".
  • "Handler=progid" will pass "args=NULL".
• The lFlags argument tells OpenConnection what mode to operate in. The DataFactory Execute method will call with a value of 2, which tells OpenConnection that pQuery can be changed. The DataFactory Synchronize method will call with a value of 1.
• The pQuery argument is the command text. The DataFactory Synchronize method passes NULL for this argument.
• The ppConnection argument is where the function returns the new ADO Connection.

Writing a New Custom Handler

The easy way to write a new custom handler is to start with the Visual Basic or Visual C++ sample handler in the SDK, and then modify it to your liking. The following example uses Visual Basic:

1. Make a new ActiveX® DLL project.
2. Select the Unattended Execution option in the Project:Properties dialog box. You should use the Free Threaded model for best performance on a server, but it's is not available in Visual Basic, so you'll have to use Apartment model for now.
3. Include the "Microsoft ActiveX Data Objects Library" in the project references.
4. Include the Msdfhdl.tlb (type library for the Interface IDataFactoryHandler) in the project references.
5. Add "Implements IDataFactoryHandler" in the Declarations section of your class code.
6. Add code to all of the methods and property set/get procedures on the interface.

Using ADO Events in a Visual Basic Handler

Inside the handler, the Connection object can be created using the WithEvents keyword. The developer can write custom events to be associated with this object.

Assume the following class names:

• Class1 is the name of the class that implements IDataFactoryHandler.
• Class2 is the name of the class that implements the ADO event handlers.

To implement ADO events in your DataFactory Handler:

1. Add the following to the Declarations section of Class1:

   Private c2 as new Class2
   

2. Add the following to the Declarations section of Class2:

   Public conn As New ADODB.Connection
   Private WithEvents connEvh As ADODB.Connection
   

3. Add the following to the Class_Initialize function of Class2:

   Set connEvh = conn
   

4. Implement whatever event functions you wish in Class2.

5. Use c2.conn in Class1.

Note

   

The sample Visual Basic handler, Msdfmap, has ADO event handlers, but they are commented out. See the "Events" class.

Error Strings and Descriptions

When working with a custom handler object, the DataFactory object might generate one of the errors described in the following table. The error messages were changed in MDAC 2.1.