3

Note: Question updated to narrow the scope a bit.

Scenario

Client <-> Application server <-> Data server

  • Users use the client to connect to the application server and build queries for execution.
  • Application server handles authentication and authorization.
  • Application server sends query definitions (not SQL) along with authorization information to the data server.
  • Data server does what it is told.

Authorization dictates which tables and columns from tables a user is allowed or not allowed to see. And can also specify column values that a user is allowed or not allowed to see. A user may for example be restricted to see only order from client X. Another user may be allowed to see employee data for him/herself and her/his team members except salary information (columns).

This is a software suite which will be installed on our customer's machines. The installation may consist of multiple application servers and multiple data servers.

Requirements

As the data server does not do any authentication or authorization of actual users, we need to ensure that:

  • query execution requests are only originating from our own application servers
  • get result data requests are only accepted from the same application server that sent that query execution request
  • the authentication and encryption methods have as little overhead as possible (connections may be established on a per request basis and each query execution request can be followed by many get result data requests)
  • the application server and the data server may be running on physically different machines

Question

What would be the best way to set up the communication between the application server and the data server to ensure the requirements are met?

We are currently using HTTPS, but are open to other communication methods if these would make authentication and security of the communication between application and data server easier/better/faster.

Update:

We would like to steer clear of anything that a security conscious IT department would frown upon (trusting another CA) or would make the installation a lot harder/more painful (client certificates). But authentication of processes and the security of the data flowing between them is essential, so if they are the only way, we'll just have swallow the pill.

Marjan Venema
  • 131
  • 5
  • There is a lot here. Perhaps distill this down into one or two simple questions. related: (https://www.owasp.org/index.php/Authentication_Cheat_Sheet) – rook Nov 06 '13 at 16:36
  • @Rook: Thanks for the helpful link. I would distill it down if I knew how to do that without repeating myself a lot... I guess I need to stew some more over the possible scenario's. – Marjan Venema Nov 06 '13 at 17:52
  • @Rook - not sure how relevant that link is. The link talks about human to computer authentication, while the question is about computer to computer authentication. – paj28 Feb 22 '14 at 14:05

1 Answers1

1

A practical and secure approach is to use SSL with client certificates, which is probably similar to your existing approach.

One way of doing this is that every server has a private key and a self-signed certificate, and also the certificate of every server it needs to talk to. So when an application server connects to a data server, both ends present their certificate, which the other ends checks against their copy.

This is good for a handful of servers. If you have more than that you need to create your own internal certificate authority. Each server then has a private key, a certificate signed by your internal CA, and the certificate for your internal CA itself.

In this model, authentication between servers does have a moderate server load, but you mitigate this by keeping the SSL connections open for a long time, so authentication is only done occasionally.

paj28
  • 32,736
  • 8
  • 92
  • 130
  • Thanks for your answer If at all possible we would prefer to steer away from client certificates as that would entail a whole lot of hassle when installing our software suite at a customer's location. Their IT departments usually are our biggest hurdle in the sales process. Finding secure ways of installing client certificates or requiring them to accept us as a trusted authority is going to be a major pain in the behind. – Marjan Venema Feb 22 '14 at 15:30
  • @MarjanVenema - You have the app and data servers on client sites? Rather than use the system certificate store you could have a certificate store within your application; almost all languages allow you to do this. So you install the certs as part of your standard install process. A slightly more radical alternative is to ship "virtual appliances" i.e. a VMWare image which contains your application running on a configured OS (probably Linux to avoid licensing issues). I don't see the concerns you raise as a reason to avoid client certs. – paj28 Feb 22 '14 at 19:00
  • Certificate store in my application? Wow. That could indeed help a lot. If you happen to have a link "at your fingertips" I would really appreciate that. A general one or one specific to C# (either will make the digging for Delphi/Indy easier). – Marjan Venema Feb 23 '14 at 10:05
  • @MarjanVenema - You usually store the cetificates as files. Here's a link that explains how to load a cert from a file and pass it to the SSL functions. http://msdn.microsoft.com/en-us/magazine/cc163454.aspx – paj28 Feb 23 '14 at 11:27