4

I have built an extensive 2-tier application in D2010, using ADO and devexpress. I want to upgrade this to using Datasnap mainly to provide HTTPS communication instead of just TCP/IP to the vulnerable SQL server. I have followed all the Datasnap tutorials I could find. I have Cary Jensen's Delphi In Depth: ClientDatasets. All good and well, but the examples are pretty useless because in a REAL database application, grids are populated from joining multiple tables together and almost never from a single table. This obviates the "autoresolve" capability of clientdatasets right off the bat. Even the proposed beforeupdateevent handlers won't work in a datasnap application because the DB is only accessible to the datasnap server. So it seems to me I have to create a method on the datasnap server for EACH insert/update I am going to need, then expose those methods to the client and call them from the client as required to request the datasnap server to perform the required update/inserts. This seems like a lot of work!

Is there an easier way to implement https comms to a SQL Server?

Oh in case you're wondering, the application is already pseudo 3-tier in that grids are wired to TdxMemData, and never directly to TADOQueries. I handle all insert/updates myself in the same way that I would have needed to if I had used TClientdatasets.

Freddie Bell
  • 345
  • 3
  • 6
  • Read [this](http://msdn.microsoft.com/en-us/library/aa226553(v=sql.80).aspx). Also, define REAL DB application opposed to others, your current definition is not satisfying. – Premature Optimization Oct 16 '11 at 11:26
  • 1
    Or take a look at ORMs, including client-server ORM (e.g. our [mORMot](http://blog.synopse.info/tag/mORMot) which implements it over a RESTful mechanism). Using JSON and our supplied grid wrapper, you can join any table and receive JSON content of corresponding rows. – Arnaud Bouchez Oct 16 '11 at 12:53
  • REAL is not defined. WHat is REAL? What does "easy" have to do with "real"? You want good, or you want easy? NO amount of in-app component wiring makes your app "multi-tier". If you have a thick client, and an SQL database, you have a "two tier" app. – Warren P Oct 16 '11 at 17:34

2 Answers2

6

If you think your database is vulnerable think twice about using D2010 Datasnap. It is very, very vulnerable. Don't be fooled by HTTPS, there are still lot of pieces missing to fully protect the channel. For example once you use Datasnap, SQL server Windows integrated authentication (kerberos based...), is gone.

For a full explanation see: Why Datasnap 2010 is a toy library. It's of course my personal opinion, but is is based on my experience using Midas/Datasnap since Delphi 3, and my current work about IT security.

Anyway you're wrong about insert/updates/deletes. You have to use providers' events to control them on the datasnao server side. It's a bit more complex than handling them in a two-tier application, but you don't need ad-hoc methods for each operation.

  • THis misunderstands datasnap, IMO. You shouldn't expose raw MS SQL port 1433 to the Internet. Datasnap is intended to be public facing. IT is still up to you to design your own public facing APIs, but by the extra work that the OP questions the value of, is the very security and integrity layer that a middle tier was intended to provide. – Warren P Oct 16 '11 at 17:26
  • I'm interested in reading more about the vulnerability of datasnap. Do you have some links? – Birger Oct 16 '11 at 17:39
  • I think this is LdSandon's personal opinion and likely no citations are available. – Warren P Oct 17 '11 at 01:57
  • Warren P AFAIK is an Embarcadero employee and thereby its opinion is its company opinion. See the link I added about the several flaws in Datasnap security - it's my pesonal opinion, of course, but if Embarcadero instead of spending time denying the flaws spent it fixing the issues would have been more productive. –  Oct 17 '11 at 10:39
  • Datasnap was never designed to be "public facing". Supporting HTTP/HTTPS doesn't mean to be designed to be "public facing", any application using HTTP(S) can expose big security flaws easily because there's much more to implement than securing somehow the transport. Do it wrong, and it could be more secure to open SQL Server port directly. Adding a tier won't improve security and integrity automatically, actually it could **decrease** it if the middle tier framework is designed the wrong way. –  Oct 17 '11 at 10:57
  • I am not am embarcadero employee. My opinions are my own. – Warren P Oct 17 '11 at 13:21
  • @Warren P: I apologize for my mistake - I am just sorry I can't modify the comment. –  Oct 17 '11 at 14:08
  • I disagree with LDSandon's opinions on DataSnap in the linked article -- especially on the authentication one. Maybe he wishes that it provided a standard authentication technique. I would rather design my own, which I would not necessarily want to document publically. DataSnap is much less of a toy now, in XE2, by the way, it's got some great new stuff, I was just listening in on the CodeRage 6 sessions today about it. – Warren P Oct 17 '11 at 15:49
  • DIY is security is usually a recipe for disaster. There are a few ways to do it right and many to do it the wrong way. "Security by obscurity" is not usually a good approach. Unless you heavily obsfuscate your executable, reversing it is pretty easy. Relying on known, well tested standards is usually far better, unless you have the deep knowledge needed to implement your authentication mechanism. But you may have to take interoperability also into account, if a company deployed AD, why its administrators have to cope with yet-another-user-database? –  Oct 17 '11 at 18:56
  • Lots of people say "security by obscurity is a recipe for disaster" and yet, encryption is simply a form of compute-intensive mathematical obscurity. All security is only partial, temporary, and breakable. How many banks make public all their security arrangements? Zero. There. – Warren P Sep 17 '12 at 18:16
4

[2016 Update: DataSnap in 2016 is even more woefully behind in terms of security and features now than it was when this question was written. I do not recommend its use in any new designs at all, ever.]

DataSnap is a solution to the problem of building multi-tier (Three or more) applications. Directly connecting to SQL over the internet from a thick client that contains all the business logic in the client has many well-understood problems, including the fact that business logic changes then require that you update ALL your clients at once. A middle tier improvement (business logic change) that is inside your data-snap (or other) middle tier logic, is not distributed to each client. The clients are thinner, and contain less of the business logic. Secondly, a well designed data-snap "API" that you build yourself only exposes you to the risks that you create yourself, rather than exposing you to the entire set of MS SQL vulnerabilities.

Frankly, losing Kerberos authentication from your thick client, is not a reason to abandon the idea of a middle tier. I don't understand ldsandon's point at all here. Is he advocating a two-tier application architecture that connects to internet or LAN clients, and that contains all the business logic, as "more secure" than a multi-tier application?

The implicit question suggested by your title is unanswerable, and undefined. What does "real" mean? Many industries deploy two-tier thick clients inside their own corporate LANs. Many have found it beneficial to use a middle tier inside their own LAN, and many have found that external applications that run over the internet should definitely NOT be surfacing the SQL connectivity to thick clients, and so they provide some kind of "web method" (SOAP, REST+JSON, etc) architecture. It has been carefully pointed out that Data-Snap is not a Purely "RESTful" architecture, but it does use JSON, and is in many ways REST-ful in design, although not fully.

If you don't understand the problem that DataSnap was created to solve, it is easy to think DataSnap is worthless, or (alternatively, and equally wrong) some kind of silver bullet. It exists for a particular purpose, one that many people find useful for their development needs. If you intend to take on the work of making a middle tier, DataSnap makes it easier than to do it 100% as a "roll your own middle tier", but it is more work than not having a middle tier.

Warren P
  • 65,725
  • 40
  • 181
  • 316
  • 2
    No, I'm advocating the use of remoting frameworks with security designed the right way. For the matter, the old DCOM-based Datasnap is much more secure, although very complex to configure outside a domain and across firewalls. The new one wasn't designed with security in mind, and the attempts to retrofit them has been very poor, lacking features and using unknown algorithms. Anyway, today a two-tier app through a VPN would be much more secure than a Datasnap applications, and since its debacle SQL Server itself became much more secure, MS understood security, Embarcadero didn't. –  Oct 17 '11 at 11:03
  • Yeah, I can't see myself deploying direct to the internet with DataSnap at all. I have had a great time with RemObjects DataAbstract as a middle tier system, but in a modern secure web, I think I'd be leaning away from Delphi entirely, and towards .net. – Warren P Aug 25 '16 at 20:08