For those who are still unaware, Microsoft has provided the world with a very slick way to connect to databases from a client web page, and that is through Remote Data Services (RDS). It is fast and secure, but getting it to work at all can be tricky, and getting it to work safely requires some special knowledge. For those that can use RDS, this article can help overcome these obstacles and allow you to get on your way to making your code vastly more efficient and user-friendly and changing your coding style to be considerably more productive.

Suppose, for example, you want to retrieve a recordset from a server database. Without RDS, most programmers do this by passing control back to an ASP page, which would do the database access, and then regenerate the client page. This process is certainly an awkward way to do something as simple as retrieve records. In fact, it is downright ugly. With RDS, however, we can connect to the database more directly without invoking any ASPs and without regenerating the client page. Less load on the server, a lot less bandwidth, and better user interaction.

Of course, there are other ways to call a server program without having to regenerate the client page. One way is by using a little trick with IFrames (see the attached example). Another is by using remote scripting. But, neither of these approaches will allow you to easily pass a recordset without converting it into an array first, which can make these methods more than just a bit aggravating.

With RDS, you get the recordset returned to your client page as a recordset object, which means that you have the full cursor capabilities for manipulating it. And, what is especially nice is that you can bind this recordset to your HTML elements if you desire. The result is that you can save gobs of code and cut your development time down drastically. In fact, you can often cut it down to 10-50% of what it might be by using non-RDS approaches. That translates into serious dollars, which is why for some companies, RDS becomes the technology of choice for client-side development whenever it is practical to do so, even when you don’t consider savings in server-load and bandwidth.

A Brief Intro to RDS

RDS is a subset of the ADO technology. When you get ADO, you get RDS, so there isn’t a whole new technology to learn, and the way you work with recordsets is pretty much the same. However, unlike a full ADO connection, the RDS connection creates a "disconnected" recordset. This means that after users grab records from a server database under RDS, their database connections with the server are severed. This allows the server to remain stateless so that it can easily handle the hundreds or thousands of users at once that is necessary in an Internet application.

Users can add/update/delete downloaded records on their workstations, or even change their sort order, without maintaining an "expensive" ADO connection to the server. Then, when ready, they can fire them back again to update the master database. Depending upon how the application is written, the interaction with the database may be so dynamic that users may even think they have a full connection even though they do not.

RDS does have a serious limitation, however, which is that due to its reliance on COM, it is currently only supported in I.E. browsers. This is becoming less of a factor now that so much of web development is really occurring within intranet or extranet environments where companies can control the browsers used by the user community. So, for those who have been fortunate enough to have standardized on I.E., you have a powerful tool at your disposal.

In spite of its power, due to this limitation, RDS still hasn’t caught on with many developers. Another big reason is that many seem to have a problem not only understanding it, but in just getting it to work. The fact is that RDS is quite simple to understand when you get a complete example of it, but it does require a working knowledge of DHTML to use effectively, which, sadly to my surprise, many programmers just don’t have.

Beyond that, the biggest hurdle is just getting the security settings correct on your server to enable it to work at all. In my own case, this resulted in hours and hours of calls to Microsoft tech support, only to find that there really were only a few settings necessary. Too often even the tech support people seemed clueless, resulting in them fumbling around for days on end. According to one MS tech support person, about 99% of all calls regarding RDS problems are concerning security settings, so I will discuss these settings shortly and perhaps save you much of this aggravation.

Another big reason RDS has had problems catching on is that a couple of years ago a security hole was found in servers supporting RDS applications. This hole allowed anyone to break in and steal or wipe out your entire database. Most unpleasant. The security hole was quickly fixed by Microsoft through the invention of "customization handlers", a.k.a. "data handlers", but even when MS announced the fix, it was still presented in such a scary tone that programmers remained spooked about the use of RDS, and this feeling still prevails for many in the developer community today.

Although the fix is for real, it still seems to be hard for some to understand. And too frequently, MS tech support personnel are clueless about it as well. It again requires a bit of an understanding of a certain setting on the server, so I will discuss that as well.

Let’s start by looking at an example of a complete RDS-enabled HTML that allows us to display a couple of columns from the good ol’ Northwind database:

<HTML>
<!—  This is the RDS data control: -->
<OBJECT classid=clsid:BD96C556-65A3-11D0-983A-00C04FC29E33 id=myCustomers
        VIEWASTEXT>
<PARAM name="SQL" value="SELECT CustomerID,CompanyName FROM Customers">
<PARAM name="Connect" value="Data Source=NWind">
<PARAM name="Server" value="http://MyServerName">
</OBJECT>

<BODY>

<!--   Here is a table bound to the RDS data control: -->
<TABLE datasrc="#myCustomers" border="1" >
 <TR>
    <TD><SPAN datafld="CompanyName"></SPAN></TD>
  <TD><SPAN datafld="CustomerID"></SPAN></TD>
 </TR>
</TABLE>

</BODY>
</HTML>

For those not familiar with RDS, this example will download a recordset to your browser and display the results in a table. This is done through the RDS data control object shown loaded into the page that communicates with a special system " DataFactory " object on the server that handles the database access. You don’t need to write any code on the server for this example to work. The RDS data control object is bound to the HTML table by setting an id=myCustomers in the RDS object, and a corresponding datasrc=#myCustomers in the table. Note how the " # " character is used in one place and not the other.

Through your own script, you can add, change, and delete records from the recordset and send those changes back to the server without leaving your page. I have included a sample program called " boundtable.htm " to show some of the ways you can manipulate these elements and perform such functions. Although the program is not really user-friendly in some aspects, it is just intended to display these capabilities. It also shows such things as ways to access table elements through script and generate dynamic drop-down lists, but I will leave a discussion of it for another time. In the meantime, just notice how little code is used to do all of these functions.

Getting It to Work

To get the example programs to function, you will need to set the proper authority to MSAccess.exe and the Northwind.mdb , or in the case of SQL Server, you will need to make sure the database and tables authorities are properly set and that there are no restrictions to using SQL Server itself.

If you are using NT Authentication, you will need to add the username and password to the connection string like this:

 <PARAM name="Connect" 
        value="Data Source=Nwind;user id=MyID;password=MyPassword">

Things are a little trickier when no authentication is used, because IIS will assign as a default the identity " IUSR_<server name> " when the system goes to access your tables. So, you will need to assign this username to your server database objects instead. Make sure there isn’t a password already assigned to IUSR_<server name> by your system administrator. And, to play it safe, even if the password appears to be blank, make sure there are no hidden characters in it, so within the password assignment dialog box for IUSR_<server name> , just highlight the entire password field and hit the delete key. (This hidden character problem alone cost me a Microsoft tech support call.)

Here’s another one that will save you hours and hours of MS tech support time with clueless tech support personnel: You will need to grant read and execute permissions to the %systemdrive%\Program Files\Common Files\System\MSADC folder on the server. Very important.

Make sure that you have the latest version of MDAC loaded on your server and that your browsers have I.E. 4.0 or above loaded on them. The latest version of MDAC (currently 2.6) can be found at: http://www.microsoft.com/data?WROXEMPTOKEN=1585035ZQdCmDnEoJyYkndGbqI

Whichever version of MDAC you have, it can be important that the version on the server is at the same level or more recent than that in the browsers, so you will want to keep your server, at least, up to date with this.

And finally, create a system DSN with the name "Nwind" on your server pointing to the database. Make sure it is created on the server and not your workstation PC, and that it is a System DSN and not a User or File DSN. By the way, you could use a DSN-less connection string instead, but since that requires more explanation, I am not going into it here. But remember that using a DSN results in an ODBC connection, which is generally not desirable due to performance issues. So, when you feel comfortable enough to create a real production program, you might want to consider using OLE DB connections instead.

If you take the above steps, the program above will automatically fill the table when the page is invoked from the browser. If you miss anything at all, the program will not only fail with the display of a blank page, but will likely either not display any error message at all, or something totally meaningless and unrelated. Various versions of Microsoft operating systems and service packs have helped in this regard, but things still can be hard to debug, so be forewarned.

Fixing the Security Hole

Have you noticed it yet? There is a gaping hole in security embedded in the code. Anyone who has access to your system can write a program using the same DSN and play havoc with your database. At the moment, that DSN is visible to anyone who views the source through his/her browser. And this is where customization handlers come in. A full description of these handlers can be found at: http://www.microsoft.com/data/ado/rds/custhand.htm?WROXEMPTOKEN=1585035ZQdCmDnEoJyYkndGbqI

Let me describe the essence of it. For example, let's say that in my client-side HTML file, I had specified RDS data control parameters that looked like this:

<OBJECT classid=clsid:BD96C556-65A3-11D0-983A-00C04FC29E33 id=myCustomers
        VIEWASTEXT>
<PARAM name="Handler" value="MSDFMAP.Handler,dfmap.ini">
<PARAM name="SQL" value="BooBoo">
<PARAM name="Connect" value="data source=RDSTest">
<PARAM name="Server" value="http://MyServerName">
</OBJECT>

Looks meaningless, doesn't it? As you can see, an SQL value of " BooBoo " or a data source of " RDSTest " doesn't tell you anything about the true name of a database or table that the program will be using. What they do, however, is specify that the program is to use a file named " dfmap.ini " which we create on the server to get connection information. This file is unavailable for viewing to anyone except a system administrator. Inside it is the following information:

[connect RDSTest]
Access=ReadWrite
Connect="DSN=NWind"

[SQL BooBoo]
SQL="SELECT * FROM Customers"

Now, when the client sends a request to the server, the server will execute a special system program called MSDFMAP , which in turn will look inside our dfmap.ini file, which it keeps in its secure WINNT system folder, and then look up the DSN (data set name = NWind ) which has already been defined on the system to connect to the Northwind database, and then looks at the SQL, which is associated with " BooBoo ", which will in turn grab all customer records. (Again, we did not have to use a DSN, but could have included a full OLE DB connection string instead). Essentially, the dfmap.ini file allows the developer to encode connection parameters that would be meaningless to anyone else into valid database requests.

In the example above, you will see that I did not include a parameter that might have been used in a WHERE clause in the SQL statement. When you look at the article I mentioned above on customization handlers, you will see how to pass parameters. You can execute stored procedures as well, but the only way to pass them parameters is to write your own server data factory object, which is beyond the scope of this article.

If you plan on always using customization handlers, you should run the Handsafe.reg program contained in the server’s MSADC folder. To run it, just double-click on this filename and the necessary registry entry will be made. Doing this will force the system to always use handlers. If you do not use handlers but Handsafe.reg has been run, then the RDS connection will fail. If you know that you will not use handlers, but you will be using RDS, then you will need to run the Handunsf.reg program. This will open up the system to allow connections to be made both with or without handlers.

But, this is obviously more of a security risk, so Microsoft recommends that you always use handlers and force the system to require them when a connection attempt is made by running the Handsafe.reg program. You will need to reboot when either Handsafe.reg or Handunsf.reg is run.

At this point, your program should run, but your system may not be fully RDS secure. So, check to see if you have the following folder on your server:

%systemdrive%\program files\common files\system\msadc\samples

If you do, then this means that you have the Microsoft RDS tutorial samples loaded. This is not a good idea because one of the sample program files, vbbusobj.dll , exposes your system to hackers. Microsoft recommends deleting this samples folder entirely from your production server.

When you have taken the above measures, you will have enabled RDS to run on your server securely. With a firewall in place, your database is no more insecure than without RDS. The only channels you provide to users to your database are those you allow.

Closing Remarks

For an interesting introductory tutorial seminar on RDS, you can view one at: http://www.microsoft.com/Seminar/1033/BobandLarryRDS/Seminar.htm?WROXEMPTOKEN=1585035ZQdCmDnEoJyYkndGbqI

For any additional security concerns you may have, or any that I have not covered based on certain environments, refer to the Microsoft articles in the links section below. For a fuller description of RDS objects, methods, and properties, (but not customization handlers for some strange reason) refer to John Papa’s book "Professional ADO 2.5 RDS Programming with ASP 3.0" available from Wrox Press.

With RDS, you can create extremely functional client-side applications. RDS allows you to move to a true 3-tier environment where the presentation layer is where it belongs: on the client machine. It is easy to go overboard and load up a client page with a lot of business rule logic, so you have to think carefully about how to structure things. But RDS also allows for you to call your own custom data factory modules on the server to keep the tiers distinct, and Papa’s book mentioned above has a good description of that.