Most efficient way to pass SQL Login credentials t

Question

Using Delphi XE to build a relatively straightforward database app using Datasnap.

Since some security in my application is handled at the database level, I need to pass a user's SQL credentials from my client app to my Datasnap server.

(I'm trying to make the Datasnap server stateless if possible, so recognise that I will have to do this for every call.)

I'm using ClientDatasets (CDS) on the client side so I could use OnBeforeGetRecords to pass the data in the OwnerData OleVariant from the CDS on the client to the corresponding TDataSetProvider on the server. But that means every single CDS on every data module has to have an event that does this, which seems messy and unwieldy. I can't help feeling there must be a way to pass messages to the server at a higher level than that.

What I'd really like is something like this at the DSServerClass level on the server side:

Procedure TMyServerContainer.MyServerClassCreateInstance(DSCreateInstanceEventObject: TDSCreateInstanceEventObject); 
begin 
// Server detects request for data from client app 
fUsername := GetUsernameFromClientSomehow; 
fPassword := GetPasswordFromClientSomehow;

// create data modules and initialise
MyDataModule := TMyDataModule.Create(nil); 
MyDataModule.InitialiseWithSQLCredentials(fUsername, fPassword); 
DSCreateInstanceEventObject.ServerClassInstance := MyDataModule; 
End;

Could the Authentication Manager component help me here? Any other ideas? Or am I stuck with OnBeforeGetRecords?

Many thanks.

Answer 1

You can use the SQL credentials as UserName and Password for connecting to the DataSnap server. These values can be verified in the Authentication Manager and/or simply forwarded to the underlying SQLConnection component for connecting to the SQL server.

Answer 2

The most secure way would be to pass along the user security token (encrypted) and then use integrated security on the server side impersonating in a thread the calling user security context. This way no user/password would ever be sent across the wire. Unluckily while MS/DCE RPC can do this for every call (and DCOM, being built above RPC), Datasnap can't (SPNEGO/GSSAPI/SSPI looks to complex for the guys at Embarcadero, they like simple, unsecure protocols). Otherwise be very careful the way you send credential across the network, they could be easily sniffed unless properly protected.

I would advise you anyway to send them only once, if you need to (and in the most protected way you can), and then store them protected on the server side (suing Windows protected storage facilities), and send back to the client an handle/session token (tied to the originating IP), to be used in subsequent calls instead of resending credentials each time. Informations are cleared when the user logs off or the session timeouts.