Friday, September 12, 2008

DB2 UDB and SOAP: Database Web Services Follow-up with DataSnap

Original Source click here

Level: Introductory

Bob Swart (, Author, Trainer, Consultant and Webmaster, Bob Swart Training and Consultancy

08 May 2003

This article shows how to add authentication to DataSnap applications using a special Login method; points out that the importance of combining this with SSL certificates for increased security; and describes how to deploy the DataSnap server application on a Web server machine.
Show developerWorks content related to my search: DelphiShow developerWorks content related to my search: Delphi


A few months ago, I wrote two articles about using IBM DB2® Universal DatabaseTM as the backend database in distributed applications, where you split the original Windows application into two different tiers: a thin client and a database server tier. The first article used DataSnapTM with DelphiTM 7 as the multi-tier architecture, while the second article added SOAP as the communication protocol to end up with a solution that could be used on Windows (with Delphi and C++Builder®) as well as Linux (with KylixTM for Delphi and C++).

I received a lot of feedback on these articles, mainly about deployment issues, and I want to address them in this follow-up article, extending the DataSnap SOAP example. Apart from deployment steps, I also want to cover some login and authentication details that may help you increase security for the DB2 backend database. But let's start off with the latter topic first.

Back to top

Add a little security

In the two earlier articles, I showed that a DataSnap client could connect to the DataSnap server and perform all kinds of actions—such as retrieve records, update records, and even delete records—all without the need for identification or authentication. This is good for demos, but not good for real-world situations, so this time you'll add a little layer of security around the DataSnap server and clients (if only to get you started into building even more sophisticated layers of security around it).

The TSQLConnection component is the only one that directly connects to the DB2 database, and I've built the DataSnap servers in such a way that the TSQLConnection component already contains the correct username and password to connect to this database. This means that the database username and password never need to go "over the wire" and are safe (unless someone hacks the Web server and examines the DataSnap server executable looking for the username and password properties). Instead of sending the database username and password over the internet, you should send a special "client" password over the wire (disabling a single client is easier and has less impact than disabling a database username/password).

There are a number of places where the DataSnap server can request such an authentication, most specifically the OnBefore event handlers of the TDataSetProvider component. This is the component that actually sends data (records) from the server to the client, or updates the DB2 tables based on information received from the client. You can use the OnBefore event handlers to check if the client is allowed to request records or update information.

Next, you need to "secure" the server using the OnBeforeGetRecords and OnBeforeApplyUpdates event handlers (the former to request records, the latter to modify records at the server again). Both these event handlers contain a parameter called OwnerData of type OleVariant. The client application can pass a custom "OwnerData" value in the parameter of the corresponding OnBefore event handler of the TClientDataSet component. Assuming the client application has allowed the end user to enter a username/password, this code shows the OnBeforeGetRecords event handlers of the TClientDataSet (client side):

procedure TForm1.ClientDataSet1BeforeGetRecords(Sender: TObject;
var OwnerData: OleVariant);
OwnerData := CodeWord;

At the server side, you first need to check that the incoming OwnerData isn't null or empty, and then assign it to a WideString and compare it to the special CodeWord:

CodeWord = 'Open Sesame';

procedure TCustomerOrders.dspCustomerBeforeGetRecords(Sender: TObject;
var OwnerData: OleVariant);
Str: WideString;
if VarIsEmpty(OwnerData) or VarIsNull(OwnerData) then Str := ''
else Str := OwnerData;
if Str <> CodeWord then
raise Exception.Create('No permission to Get Records!');

Back to top

Implement a Login method

In practice, you may want to check against more than just a single CodeWord and have a real list of usernames and passwords. This means that you should extend the interface of the DataSnap SOAP Web service and add a method Login to it, like this:

ID7DB2SAMPLE = interface(IAppServerSOAP)
function Login(const UserName, Password: WideString): WideString; stdcall;

The implementation of the Login method should check the username and password (which can be stored in a list, .ini file, or another secure database, for example), and returns only the CodeWord for a correct login. The client should save the CodeWord and use it during the session:

function TD7DB2SAMPLE.Login(const UserName, Password: WideString): WideString;
if (UserName = 'Bob') and (Password = 'Swart') then
Result := 'Open Sesame'
Result := 'Permission Denied'; // a wrong CodeWord

You can add a special tag to the CodeWord to control the lifetime or validity—like the IP-address of the client. (This is left as an exercise you can try.)

At the client side, you now have to call a custom method from the ID7DB2SAMPLE Web service. But the TSoapConnection component only exposes the IAppServerSoap methods. To get your hands on the additional functionality, you have to import the Web service from the registered URL (in this case that might be http://localhost/scripts/D7DB2CGI.exe/soap/ID7DB2SAMPLE).

To import a Web service with Delphi 7, you can use the WSDLIMP utility from the Delphi7\bin directory:

wsdlimp http://localhost/scripts/D7DB2CGI.exe/wsdl/ID7DB2SAMPLE

or use the WSDL Importer from the Object Repository (do File | New - Other and go to the WebServices tab):

WSDL Importer

Both cases result in a new unit ID7DB2SAMPLE1.pas that contains the interface definition for ID7DB2SAMPLE with the Login method. In order to use this interface and call the Login method, you have to add a THTTPRIO component from the WebServices tab of the Component Palette to our client form, and set its URL property to http://localhost/scripts/D7DB2CGI.exe/soap/ID7DB2SAMPLE.

With this THTTPRIO component, you can connect to the Web Services, call the Login method, and save the resulting CodeWord so you can use it for subsequent OnBeforeGetRecords and OnBeforeApplyUpdates event handlers. Here is the code for the call to Login (full code is available in the source archive):

procedure TForm1.FormCreate(Sender: TObject);
with (HTTPRIO1 as ID7DB2SAMPLE) do CodeWord := Login('Bob', 'Swart');
cdsEMP.Active := True

Obviously, where I pass the strings 'Bob' and 'Swart', the end user needs to use a dialog to enter the username and password for that session (another exercise you can try).

A Note on Delphi 7 and DB2 v8.1

I've been using the latest version of DB2 v8.1 for my recent articles, and I have had few problems with it. However, this time, I encountered a number of meta data issues between the Delphi 7 dbExpressTM driver and DB2 v8.1 Universal Database Personal Edition. Specifically, I get an exception with the message "Could not convert variant of type (Null) into type (String)" when I try to use BLOB fields (such as the image field in the EMP_PHOTO table and memo field in the EMP_RESUME table). If I don't use these fields, everything works fine.

These and other related issues are caused by the fact that the dbExpress driver that comes with Delphi 7, C++Builder 6, and Kylix 3 officially "only" supports DB2 v7.1, because that was the version of DB2 at the time the dbExpress driver was released. For the newer version of DB2 v8.1, the dbExpress driver experiences some meta data incompatibilities (with BLOB fields), causing the effects described above. (See the actual report.)

Of course, Borland is aware of the situation and has been working on an updated dbExpress driver for DB2 v8.1, which is expected to be available soon.

Back to top

Use an SSL connection

Sending a username and password over a non-secure connection is not a good idea—not even when sending it just once, as I do in the final example. You should always try to use a secure connection to send authentication information, and fortunately you can use a Secure Sockets Layer (SSL) connection for that. It involves two steps: the first consists of installing an SSL certificate on the Web server where the DataSnap SOAP server is installed (if there isn't an SSL certificate on that machine in the first place). And the second step is even simpler: go to the SOAP Web module, select the TWSDLHTMLPublish component and set the poPublishLocationAsSecure flag of the PublishOptions property to True. From that moment on, the Web service will require https:// to be able to connect to the server (instead of http://), encrypting all information between the clients and this server.

Contact your Web master or administrator for more information regarding the availability of SSL certificates on your Web server.

Back to top

Deploy the DataSnap SOAP server

When it comes to deployment of the DataSnap SOAP server, there are actually several choices depending on the target of the Web server application (a choice you can make when you start a new SOAP server application: see the previous article).

The easiest to deploy is the CGI standalone executable, which is also the target of the D7DB2SAMPLE server application that I used in this article. A CGI standalone executable needs to be deployed in a (virtual) directory of a Web server where the scripting rights of that directory have been set (on Windows) or where the execute bit of the executable has been set (on Linux).

Alternately, on Windows you can select an ISAPI dynamic link library target. The difference between a CGI executable and ISAPI DLL is that the latter is loaded only once (for the first request) and will remain loaded in memory until you actually shut down the Web server. The advantage of this is performance: you don't need to create the SOAP data module and connect to the database for every incoming request. The downside is flexibility: if you want to update or change the ISAPI DLL (with new functionality, for example), you first need to shut down the Web server process before you can replace the ISAPI DLL (a CGI executable can be overwritten, but the ISAPI DLL will be "in use" and the operating system will refuse to overwrite it).

You can use an ISAPI DLL with Microsoft's Internet Information Services (IIS), whereas you can create an Apache shared module for the Apache Web server on Windows or Linux (see for more information about that option).

Finally, the Web App Debugger target that you can build is specifically meant as a debugging target, where you can test the DataSnap SOAP server without actually needing to deploy it. In all cases, the DataSnap SOAP server must be able to connect to the DB2 database (the SAMPLE database in our example), so make sure the correct database name, username, and password are specified in the TSQLConnection properties, and that the DataSnap SOAP server can find the DB2 Client db2cli.dll.

Back to top


In this article, I've shown how to add authentication to DataSnap applications using a special Login method that returns an ID (or CodeWord) that has to be passed as part of the OwnerData in subsequent OnBeforeGetRecords and OnBeforeApplyUpdate event handlers. I've also pointed out the importance of combining this with SSL certificates for increased security.

Finally, I've described in a bit more detail how to deploy the DataSnap server application on a Web server machine—which can be Windows or Linux. This opens up your DB2 database to thin clients that can run just about anywhere.

In future articles, I'll explore the use of DB2 UDB from Borland C#BuilderTM IDE for .NET, so stay tuned.

Back to top