Wednesday, April 27, 2011

SFTP vs FTPS: tough choices

Las week, I had to design in a hurry a secure file transfer mechanism between two DMZs on a zero budget which, in a nutshell, meant reusing the Windows servers that are already there, and not purchase any third party software.

I had to choose between using SFTP, a nice protocol, and FTPS, which I've been comparing to a bastard child for years.

I don't like FTPS mostly because it's a patch on FTP. For one, FTPS is harder to firewall than SFTP; it behaves exactly like the standard FTP with a control and data connection, the difference being that TLS is used to encrypt them. Like with standard FTP servers, the server must be configured with a fixed range of passive ports, and the firewall must let these ports through. Why? Because the firewall has no way of knowing what dynamic port has been assigned to a passive data connection... it can't sniff it out the control connection either, as it's encrypted!

Even though it's not exactly what I would call an elegant protocol, is FTPS actually easy to work with? The answer is yes: I was able to install IIS 7.5's FTP publishing service in 2008 R2 and have an FTPS server working within minutes. That is good enough. And in IT, good enough is, well, Good Enough.

So, here are my thoughts:

If your server will be hosted on any kind of Unix, choose SFTP. It has been built-in with OpenSSH for years. The drawback of OpenSSH is that it doesn't support virtual users, and this can make high availability tricky; you'll need to synchronize /etc/passwd entries, even if using AD authentication.

On the other hand, if you will host the service on Windows, you might be better off going with FTPS as it is included with IIS 7.5 and there is even high availability that is possible. To support SFTP on Windows, you either need to install unsupported open-source software (unacceptable in many secure, enterprise environments) or purchase a third-party product such as WS_FTP Server (which carries a premium if you need SFTP functionality).

As for CLI clients that support automation, no matter the platform you use, there are plenty to choose from. For SFTP, on Unix just use the sftp command and on Windows, try Putty's excellent psftp.exe. For FTPS, I suggest you try cURL which is multi-platform on Unix and Windows.

So, to conclude: SFTP if using a Unix server, FTPS if using a Windows server.

In my case, I'm going with FTPS.


Wednesday, April 13, 2011

Gabriel Consulting Group survey on Oracle and HP-UX

As many HP-UX admins still read this blog, I thought I'd post this. GCG is running a survey to have some insights on what you're thinking about Oracle's decision to stop developing products on Itanium, and Oracle in general:

I got this link from an article that Dan Olds posted on The Register.

I takes maybe 10-15 minutes to answer the survey and I think it is worth it, as the results will no doubt end up being published by HP somewhere down the road. Even though I'm sure they're independent, the questions and tone of the survey are not, er, I'd say, totally objective. I answered it not as an HP-UX admin (which I'm no longer), but as a systems architect for an enterprise that runs a mixture of HP-UX, AIX, Solaris, and Windows. So I tried to stay unbiased. You should do the same.


Wednesday, April 6, 2011

PI DataLink Server and Excel Web App: A wedding cake dilemma

The project I'm attached to has in its list of technical requirements the installation of Excel Web App (EWA) along with PI DataLink Server (DLS). It is not clear what the customer intends on doing with it, but my guess is that it will be used to show PI data to end users using a web-based interface.

The DLS manual describes four user roles, two of which are directly related to Excel: a publisher and a reader. This pretty much resumes what it is designed for: some people, who are PI experts, develop and publish workbooks using a real Excel with PI DataLink, while common end users read them, using a browser. This apparently read-only nature of DataLink Server (which I need to confirm) is an important one, as from my understanding, it is positioned to be a simple web reporting platform.

I've recently had some time to experiment with these web features to try to predict what the developers will end up doing in the long run. I also had the hope of leaving the marketing pitch to marketers and finding what were the real advantages of going in that direction instead of sticking with a deployment based on the standalone Excel application.

I'm not an Excel whiz kid, and I'm even less a SharePoint expert. That being said, after a few mishaps, I've managed to make a proof of concept with DLS and EWA using the most dummy report I could build with my limited knowledge of PI:

The wedding cake dilemma

I'm glad to announce PI Datalink Server works as designed within the Excel Web App. However, when playing with it, I couldn't stop thinking about a three-layer wedding cake. Why? Because you see, pitting EWA against standalone Excel is like comparing that wedding cake to a slab of brownies. Both will easily feed dozens of people, but the wedding cake will take longer to assemble, be more expensive, and each layer will need to be supported by the one underneath (I also think the brownies will be tastier, but that is beyond the scope of this article).

I had no doubt that the combination of the three layers consisting of DataLink Server, Office Web Apps and SharePoint involved lots of other subsystems too. This presentation done by Microsoft last year confirmed my suspicions. IT Operations would have a hard time supporting all that if the dependency hell between all those subsystems ever hit the fan. Understandably, as a systems architect, I wasn't very comfortable in greenlighting the use of DataLink Server at first glance. Is it safe to assume that if an architecture is made like a wedding cake, it better offer something big in return or else it's not worth it?

I think that in that particular case, it will be worth it if your experts use PI DataLink a lot and they need to deploy ad hoc reports quickly to a controlled (i.e. not massive), read-only audience.

Using EWA and DLS for ad hoc reporting

The ugly sample report pictured above is what I would call an ad hoc report: It's a quickie, made in a hurry to fulfill an unexpected business need. These can be done in a matter of minutes and published as a web spreadsheet to be consumed by users who have no technical knowledge of PI. There is no need for these users to have Excel on their client, as everything runs in a stripped-down version of Excel straight in the browser. This could prove extremely useful when dealing with mobile devices in the future as I don't expect Excel and DataLink to be running on the iPad anytime soon.

Furthermore, since you don't have a bunch of standalone Excels running around in the wild, you don't have to:
  1. Ensure all users have the correct Excel version;
  2. Install PI Datalink on each of these Excels and maintain this installed base which can be substantial;
  3. Deal with the security hassles of opening up network access to the PI infrastructure to every laptop in your WAN (you only need to open it to the server running DLS).
Interesting. One might expect a lot of reports to be created that way.

Preventing ad hoc report sprawling

Now comes a question: what do we do to prevent "ad hoc report sprawling"?

I think that ad hoc reports should be deployed to VIP users as prototypes, until the time comes to move to something better if they ever need to reach a wider audience. By "something better", I'm talking about a dedicated reporting system such as Crystal Reports for the kind of reports that pull data not only from PI, but also from AF and other sources. The kind of reports that are read daily by people who make business decisions based on their contents. The kind that end up on a printer, to be read to/by upper management.

These official reports should still be designed, deployed and stored on a dedicated platform. Why? Because:
  1. EWA and and DLS have their limits; my understanding is that they can pull out data only from PI points, not AF (on the other hand, there are ways to combine web parts with DataLink Server, but I'm not good enough to try that out);
  2. I also have a feeling that using EWA as a reporting solution might cause a performance impact both on your SharePoint and PI infrastructure as nothing will prevent John Doe from pressing CTRL-ALT-SHIFT-F9 (in caps, of course) all the time to be updated on the second. It's much slower on DLS than within the real Excel, so I think there is a performance hit. This impact needs to be evaluated, and thus why I talked about a controlled audience above.


The possibility of deploying ad hoc reports to read-only users who don't need to have Excel at all is the main advantage I've seen up to now to deploying an architecture based on PI DataLink Server, Excel Web App and SharePoint. However, as this might be a complex solution that your IT Operations will need to take care of in the long run, you need to be sure you really need it.


Am I off the track on this? Have any comments? Please post below and I'll be glad to write an update to this article.