Landmarks on a FileMaker Server journey

Our FileMaker Server deployment is externally accessible at last, after weeks of resolutely not being. Here’s the surprisingly complex tale of how to get those green padlocks.

What’s this post all about?

Just to be clear; using FileMaker Pro has nothing whatsoever to do with exhibitions. So this post sticks two fingers up at our own SEO strategy.

FileMaker does, however, underpin a very exciting long-term project we’re working on that will transform many aspects of the business including stock management, electronic job bags, organisation of resources and so on. We’re also planning some client-facing elements, hence the need for external access.

That need prompted the start of an arduous journey to get FileMaker Server up and running, externally visible and certified, which is the story of this post. It traverses a broad technical landscape for which a map was sadly absent.

If you know anything about what you can do – what you can make – with FileMaker Pro, you’ll know why this arduous journey was worth making.

This is a very long post; intentionally so. Lack of detail and explanations falling short were part of my experience. And nowhere did I find a central hub explaining everything I needed to do or the order in which I needed to do it. If you feel like skipping the detail, at the end of the post you’ll find Summary: landmarks on a FileMaker Server journey. A few weeks ago, I would have found this extraordinarily useful.

Fessing up to reality

It seemed like such a simple goal: to host FileMaker Pro database files locally, allowing local and remote login for a variety of internal and external users. That’s what FileMaker does, right? So this should be a piece of cake. Right?

But it turns out the logic behind these statements was based on some rather flimsy assumptions. Like ‘Apple has owned FileMaker since 1988, so surely they will have streamlined this’. Like ‘I’m sure you don’t still need bespoke certification – this is 2018’. Like ‘they won’t force us to upgrade to version 17 – this isn’t North Korea’.

And we decided in our infinite wisdom to start the project’s development at the same time as switching to a different web host, launching a brand new website, dropping the www part of our URL and changing to https (thereby having to update all our certification).

Further down the line, questions as simple as how to open a firewall port would become unnecessarily complicated. In fact, at every step of this journey, complication seems to have been an iron golem standing in our way, massively determined to prevent the end goal from ever happening.

For some FileMaker experts reading this, I’m convinced that all my ignorance will seem hilarious. The narrative of the FileMaker world in general is a little bit… well… smarmy. It’s all problems solved and intractable hurdles overcome. People selling their services as FileMaker developers are very unlikely to write a blog post about how hard some aspect of using the software was. Yet here I am, a FileMaker developer of over 20 years, a fan even, fessing up to just that.

Those enjoying the comfort offered by knowledge sometimes forget how hard-fought their learnings were. For every problem I faced during this journey, there are entrails of opinion, fact, methodology and advice out there, of widely varying quality. I would be gratified if this post saved another me somewhere else even a few frustrating hours of the time spent trawling through it all. Some aspects are well-documented and uncontroversial; I will try not to dwell on those.

Why self-host FileMaker files in 2018?

Installing, deploying and maintaining FileMaker Server hasn’t changed much over the years. I first encountered it in around 1997, when I developed several FileMaker Pro projects in a mixed PC/Mac network. The server element acted as a means of centralising the hosting of files to help with speed, reliable access via disparate network architectures and, to some extent, simplifying security and backup considerations.

I suppose that network setups are so varied that the instructions for deploying FileMaker Server are necessarily a little vague; they cannot hope to home straight in on your specific setup environment. I came across plenty of confused people who thought the official installation and configuration guide was pretty useless, although I personally found it quite useful.

Essentially, you have always needed a dedicated host machine to run the services that host the FileMaker Pro database files. These services were accessed via a command prompt until someone thought of adding an ‘admin console’ in 2007 which makes it a lot easier to see what’s going on. There are still certain actions only possible via command prompt, which seems odd in 2018. But, in general, FileMaker Server does a few simple tasks very well.

Over the years, there was a shift towards shared hosting off-site as people became unwilling to run and support their own server machine. But for reasons of security, or more likely just commerce, FileMaker 15 made it illegal to host multiple clients’ solutions from one server. Then FileMaker unveiled its own cloud-based server hosting, imaginatively called FileMaker Cloud.

Ok, fine. Maybe that’s the future. Unless you’ve got a ‘bells and whistles’ FileMaker project to serve up, the Cloud is going to cope admirably in the majority of situations. But we’re a small company with a spare Mac Mini to use as a host; with a fast internal network; with limited external access requirements for our databases; with internal resource able to develop FileMaker solutions. In short, we’re a company for whom an internal FileMaker Server solution still seems the most appropriate.

Cementing this notion is the fact that our directors shared sufficient enthusiasm for the project to shell out for the FileMaker 16 Pro and Server licenses already.

Payment & license models

We believed when we purchased FileMaker 16 that this would be a one-off charge for the server and clients. So despite the steep up-front outlay, this seemed like a decent investment.

A few months later, FileMaker changed their licensing model, moving to annual subscription as the only choice. Although I was aware of this change for new purchases, I didn’t think for a minute that they could morally or legally force people to join this new model in order to continue to use the software. I was wrong.

The key to understanding why you’d spend thousands on FileMaker, itself only a development environment, is to look at the problems it will solve, the efficiencies it will create and the costs it will avoid further down the line. Without a firm view on this, I think we may have cut our losses and run at this early stage. This is a philosophical point, but also a commercial one in the end.

FileMaker Server host machine considerations

FileMaker Server (FMS) can’t be on the same machine as OSX server because it creates its own Apache web server (which would clash). This is counter-intuitive; you’d think that a server application would want to run on a server platform. But apparently not.

FMS requires ports 80 and 443 (for http and https respectively) to be available for its web-based services (including the thing you administer it with, Admin Console, although it redirects to port 16000). In other words, you can’t use OSX’s in-built web server technology on the same machine as the FileMaker Server software. In fact, to avoid port clashes, it’s much easier all round if you don’t use ports 80 or 443 for anything else on your entire network. In other words, if you already host an intranet or web pages, this is going to be a problem.

It requires ports open at the firewall – easy at the router but impossible in OSX Server. That’s because in OSX Server, port forwarding is tied to individual applications – you can’t just ‘open a port’. So if an application resides on a different machine, as FMS must, this simply won’t work.

That means you can’t use OSX Server’s firewall and sit the FileMaker Server behind it if you want your FileMaker solution to go outside the LAN. So we had to shift to a router-based firewall solution for our network’s security simply to make FileMaker use possible.

Various versions of FileMaker Server have allowed alternative ports to the standard http and https ones to be used, and I’m sure some very geeky people can bypass all these problems with bespoke network setups. But I’m focussing here on a small business that may not want that hassle.

FileMaker Server installation

Installing FileMaker Server is straightforward enough, which is why I’m not going to talk about it much. For me, I just needed to get an admin password to use screen sharing with the host machine and install stuff.

After installing FMS16, I upgraded to FMS17 easily too just by following the instructions. I made a backup of all our database files, but we didn’t have script schedules or backup schedules, so that was it.

It’s worth noting that every change you make to your setup or deployment may or may not require services or the host machine to be restarted. Which would be annoying enough were it obvious what to restart or how to do it. The consensus seems to be that the machine should be restarted ‘just to be sure’.

Within Admin Console/Configuration/General Settings, you can tell it that you want things to automatically start after a machine restart. Either I’ve misunderstood this or it takes so long that it appears not to work. So for me, every tweak or change led to a protracted wait for the machine to restart, re-connection via screen sharing, wondering if anything’s working for a few minutes, then giving up and trying to start things manually using the ‘fmsadmin’ commands in Terminal.

Admin Console worked usually using a browser on the host machine itself and occasionally from other machines on the network. If you can’t use Admin Console, it’s difficult to tell what’s going on, which gets frustrating really fast. However, I believe in retrospect that this odd behaviour was caused by a network issue (see Networking shenanigans below), so if I could start again, I would ensure I sorted my network shiz out first.

Pedantic Java

One thing that is not obvious even to someone with plenty of FileMaker experience is that the Server software requires a specific Java installation on the host machine. Various services within FileMaker’s ecosystem work only with the Java that was current at the time of its release. I did look into why they do this, but the answer was quite boring.

As part of general housekeeping, I had recently updated Java to the latest version. I subsequently discovered by accident that this would scupper things with FileMaker, so I had to find out how to downgrade Java to an older version. In this case, FileMaker Server 17.0.2.203 requires Java jdk1.8.0_171.

Even though the correct Java version is included in the FileMaker Server installation package, re-installing the server software did not solve the problem because Java wasn’t happy about downgrading itself.

Java is supposed to allow multiple runtime versions to sit ready to activate via the Java Control Panel (in the ‘Java’ tab). That’s great if you already had an older runtime installed, but it doesn’t help at all with a new installation of an older runtime. And, for whatever reason, the FileMaker Server installs Java in such a way that the older runtime doesn’t show up as a selectable option.

Various websites purport to have solutions to this, some with apps, some with more strings of Terminal commands. None of this worked for me. I wasted nearly a whole day on this, partially because I simply couldn’t bring myself to accept that it wasn’t possible.

In the end, the only solution that worked was to completely remove Java from the machine, then re-install the correct runtime. Uninstalling Java requires running Terminal commands with root priveleges (hence the ‘sudo’ bit). Here are the commands:

sudo rm -fr /Library/Internet Plug-Ins/JavaAppletPlugin.plugin

sudo rm -fr /Library/PreferencePanes/JavaControlPanel.prefPane

sudo rm -fr ~/Library/Application Support/Oracle/Java

Oracle also recommends subsequently removing the Java cache, which you can do again in Terminal with this command:

rm -r ~/"Library/Application Support/Oracle/Java"

Reinstalling Java requires having the correct installer, which annoyingly I couldn’t extract from the FileMaker Server install packages. Oracle’s website was not helpful either in tracking down a specific older development kit. I eventually found the download here with help from this article.

In hindsight, I suppose that having uninstalled Java, I could have re-installed FileMaker Server to solve this, but in the heat of battle, I didn’t think of that.

Networking shenanigans

We use OSX server to distribute internal IP addresses via DCHP. For reasons that nobody can remember, this included a range of addresses reserved for VPN.

Also for reasons that nobody can remember, the static IP address of the FileMaker Server host machine was within that VPN range.

Also for reasons that nobody can remember, the DHCP lease was set to 1 hour. That means that every hour, everything on the network was trying to get a new IP address.

None of the above should have been particularly problematic, but between them, these three things seemed to conspire to mess things up for FileMaker Server. It was findable by FileMaker Pro clients at ‘Filemaker-Server.local’ (which uses Bonjour technology), but not at its IP address. And I was still having problems accessing Admin Console, presumably for the same reason.

I also noticed that somebody’s smartphone was being served the IP address that was meant to be reserved for the FMS host machine. OSX Server’s DHCP client list was blithely showing me 2 devices with the same IP address, which should obviously be impossible.

I tried to read the server log to work out what the hell was going on, but the log had become such a massive file that it kept timing out when I tried to look at it. It later turned out that this was because our firewall was busy deterring some brute-force attacks from some douche in China. And when access to the log was finally achieved, finding useful stuff about internal IP address distribution was difficult amongst the millions of lines about these attacks.

Without resolving the weird IP address problem, I would not be able to test external access, and the certificate setup would fail (or at least I wouldn’t know if it had succeeded). And how could I port-forward to the host machine without it being the sole user of its IP address?

At this point, I thought I’d better check the set up on the router in case there were more clues as to why things were going Pete Tong. And guess what? I couldn’t access the router. For reasons that nobody can remember, the http port required for access to the router had not been written down.

So I tried doing a port scan at the router’s IP address (which is easy to find – it’s your network’s ‘default gateway’). But port-scanning the router made everyone lose their Internet access (which made me very popular) and long before the scan got anywhere near the correct port, it timed out and crashed. This was the router shutting up shop as a reaction to having its ports scanned. I couldn’t turn this security setting off… because I couldn’t access the router’s admin pages. I think this is what Theresa May refers to as an ‘impasse’.

I’m not usually someone who gives up on something like this, but ultimately my time would be better spent elsewhere. Like developing a database, for example. So we paid nearly £400 + VAT and waited for over a week to have an IT engineer come in for an hour (and I thought solicitors were expensive!). It turns out they had set the router’s bespoke port number on a previous visit several years earlier, so they got into the router straight away. They also sorted out the IP address clash nonsense by making the FMS host machine a static address outside the range served by OSX Server’s DHCP distribution service and changing the address lease time to 1 day. Easy when you know how.

With access to the router finally in place, I spent a day trawling through all its settings and setting it up correctly, including its firewall. That meant that the OSX Server machine no longer had to run its firewall and the router could now port-forward to the FMS machine. Hooray!

Port forwarding

I was unclear about which ports FileMaker Server needs ‘open’ and ‘available’, and what they’re for. This was partially because a lot of my testing was flailing about pointlessly while the networking shenanigans were going on. Actually, one of FileMaker’s own support pages is all you need for this, although even this wasn’t as easy to find as it should be.

If you have a simple, single-machine setup like us, the FMS host is known as the ‘master machine’. Setups with ‘worker machines’ are no doubt more complex. Good luck with that.

So you’ll definitely need ports 80, 443, 5003 and 16002 ‘open’ – as in port-forwarded in your router to your FMS host machine’s static LAN (internal) IP address.

If your FileMaker solution entails ODBC and JDBC connectivity, you also need to open port 2399.

If you want Admin Console to be available externally, you’ll also need to open port 16000. That sounds like it might be a bad idea, but you shouldn’t worry if you have a good password. Maybe just open the port as and when you need it.

If you look at this diagram (taken from the link above), it lists the ports for the master machine as ’80, 443, 2399, 5003, 16000′. It doesn’t mention 16002. Yet in the table above the diagram, it says 16002 should be open on the master machine. It says its purpose is ‘FileMaker Internal, Port 16002 must be open on the Master machine, open in the firewall, and available on the Worker machines’. This mistake is symbolic of the treacle field to be waded through on this journey. It warns you to be alert, and not to 100% trust anything.

There are thousands of routers out there, each different. So look up how to port-forward with respect to your specific router model.

It’s usually quite easy to test if a port is open by doing a port scan. I like to use Network Utility for this. You’d think this app would be in Applications/Utilities. But it isn’t. To find it, you can either browse to System/Library/CoreServices/Applications, or just type ‘Network Utility’ in a Spotlight search. To test port 5003, select ‘Only test ports between 5003 and 5003’, and put the external Internet address in the box at the top. Which brings us neatly to our next section.

A fixed external Internet address

For FileMaker Server to host files accessible from outside your internal network (LAN), it needs to have a fixed address to the outside world (WAN).

That means you need one of two things – a fixed DNS name that resolves a varying IP address or a fixed IP address.

To be honest, I didn’t even look into DNS resolvers. When I found out BT were going to charge us £5.50 per month for a fixed IP address, I just said ‘ok’. Too many decisions on this journey took too long. I needed an easy one.

Turns out BT are absolutely rubbish at communication. After accepting my order, they provided no confirmation whatsoever. When I chased it a few days later, they said they had sent out the details to the registered email address. Lies. They said they would send it again. They didn’t. I asked a third time to just tell me what the new fixed IP address was that we had bought, and they suggested I contacted technical support, who suggested I contacted sales.

3 days later, I managed to verify the IP address was now fixed myself by restarting the router each day and noting the WAN address assigned to it (it would previously have been dynamically assigned a different address each day).

Anyway, having a fixed external IP address means that you can point a domain’s SSL certificate to it, thereby linking the domain to the FMS host machine accessed through the open ports in your router. You are now ready to set up your certificate. We’ll come back to this ‘pointing’ process later (see Certificate redirection).

Free SSL certification?

FileMaker Server comes with its own in-built certification system. But it rather smugly tells you that this is ‘only for testing purposes’. In other words, you can’t use it in the real world.

We had an old GeoTrust SSL certificate protecting a domain that we no longer used, which I could have re-keyed for use with FileMaker Server, but I was tempted by talk of free certification, so I let it expire.

We had just migrated our websites to a new host, SiteGround, who secure our domains with free Let’s Encrypt certification. After a lot of forum research, I had convinced myself that this would work with FileMaker Server. Let’s Encrypt certificates auto-renew every 3 months, but someone had even worked out how to schedule server-side scripts to seamlessly cope with this change. David Nahodyl explains the process here for Mac server installations.

Reading the summary of what we’re going to need to do was, if I’m honest, a little daunting:

Install Homebrew
Install Certbot
Download the GetSSL.sh file
Edit the GetSSL.sh file
Run the Bash script
Change the FileMaker Server SSL Connections settings
Set up a schedule to renew the SSL certificate

But the instructions seemed clear enough. The promise of free certification forever is definitely an encouragement, and roughly 1 in 5 people commenting underneath the article seemed to have got it working. So why not.

Without going into detail, this didn’t work for me, although it was kind of fun trying. To be honest though, I was a little uncomfortable about running superuser commands and shell scripts in the Terminal of a machine I was accessing remotely. When your package manager is called ‘Homebrew’ and you’ve set your Terminal screen to be green text on black, it starts to feel suspiciously like hacking.

I want to develop databases, not become a proficient hacker. In the end though, it seems using Terminal is an integral and unavoidable part of all this. You simply can’t swerve that shiz.

But ultimately, I dropped the idea of free certification, and decided to bite the bullet and buy one from one of FileMaker’s tested providers (see Tested SSL certification authorities below).

Deciding on and securing an FQDN

At this point, you’ll need to decide what ‘fully qualified domain name’ (FQDN) you’re going to use for external access to your FileMaker solutions. This will be a web domain or subdomain that you own.

In other words, if you haven’t already, you now need to purchase the FQDN that the SSL certificate will be used to secure. If you use an FQDN in the certificate setup that it later turns out you can’t buy, you’ll have to re-key it and start again. I haven’t had to do this, but it sounds like a ball-ache.

It’s likely that this FQDN will be a subdomain of your main website. With our web host, SiteGround, it was free and easy to set up a subdomain. Our external FileMaker solutions will provisionally be accessed at https://fm.roundededgestudio.com

One thing to be wary of though; had you (as we had) previously purchased a wildcard certificate covering your domain and all its subdomains, this would clash with certificates you later buy for individual subdomains. So I removed and revoked our wildcard certificate and replaced it with individual Let’s Encrypt certificates for the domain and each of its subdomains (apart from fm.roundededgestudio.com, which would later get the FileMaker-approved certificate).

If, like us, you use your domain to host your email server, changing the certificate securing that domain as I just described will break everyone’s mail client in your business. My popularity tanked again that day. Adjusting everyone’s trust settings for the new certificate was tricky. On some Macs, it just involved clicking the ‘Yes, trust this new thing’ button, whilst on others, I had to dive into Mail account preferences and Keychain to change things manually. Some of the PCs needed repeated reassurance that the change was ok, others didn’t seem to notice anything was different. Bless.

If there’s any setting up to do after securing or purchasing the domain/subdomain to make it ‘public’, do that at this stage. That’s because to verify ownership of the domain, you’ll need to upload an HTML file containing a verification code given to you by whichever CA you end up using to a subdirectory of that FQDN (see Setting up a GoDaddy Certificate below).

Generating a certificate signing request (CSR)

A bit off topic, but I’ve just read a book called The Music Of The Primes by Marcus du Sautoy, which describes how our SSL certification is encrypted based on our current inability to guess sequences of prime numbers. ‘RSA’ are the initials of the 3 men who harnessed this ignorance for the purposes of data encryption back in the 1980s. If someone eventually resolves the Riemann Hypothesis, your encryption will become worthless. But since the world’s finest mathematicians have been trying to do that since 1859, we’re good for now. The probability of guessing the sequence involves a number greater than the total atoms in the universe.

All you really need to know is that you’ll be creating a private key and a public key, each of which is gobbledegook without the other. In this case, FileMaker creates the private key and your certificate authority creates the public key.

To generate a CSR, you’ll need access to the folder structure and Terminal on the FMS host machine (so screen sharing is useful).

There is actually a useful admin tool made by some clever people at DataManix to help with aspects of the CSR request. It’s a FileMaker Pro file that you host on the server, then helps you perform some admin tasks that Admin Console can’t or won’t do. It produces Terminal commands for you to copy and paste, based on the options you put in. It can be downloaded here.

Without this, you need to work out the correct Terminal commands yourself to type on the host machine. And since this is how I did it, I can reveal what that is:

fmsadmin certificate create "/CN=(Domain)/O=(Company)/C=(CountryInitials)" --keyfilepass (PrivateKeyPassword)

In the above syntax, I’ve used brackets to denote a field: don’t include the brackets in what you type. So in our case, ‘CN=(Domain)’ became ‘CN=fm.roundededgestudio.com’. (CountryInitials) are ‘GB’ for a UK company, but I accept that other countries are available. The keyfilepass is going to be important later, so choose something you won’t forget. Maybe write it down or something.

You’ll then be asked for a username and password. These would be the ones you use to login to Admin Console. You can actually add them into the command if you like:

sudo fmsadmin -u (AdminConsoleUserName) -p (AdminConsolePassword) certificate create "/CN=(Domain)/O=(Company)/C=(CountryInitials)" --keyfilepass (PrivateKeyPassword)

When you’ve done that, browse to Library/FileMaker Server/CStore. In this folder, the command you just performed should have created a private key file called ‘serverKey.pem’ and a CSR file called ‘serverRequest.pem’.

You’ll need to copy the contents of the CSR file during the setup of a FileMaker-tested certificate, so open serverRequest.pem in a text editor now (i.e. don’t just double-click the file because that will probably launch Keychain; instead use ‘Open with’ and select TextEdit).

Tested SSL certification authorities (CAs)

Here’s the list of FileMaker’s tested (and therefore sort of approved) CAs:

GeoTrust: True BusinessID
Comodo: Comodo Elite SSL Certificate, EV SSL
Symantec: Secure Site SSL Certificate
Thawte: SSL123 Certificate (under SHA-1 Root)
GoDaddy: Standard SSL

The annual cost of these certificates varies between £54.99 for the GoDaddy one to several hundreds for some of the others. So I got the GoDaddy one.

Setting up a GoDaddy Certificate

Of course, you might already have your website hosted by GoDaddy, in which case this might all be a bit easier (don’t quote me on that). But let’s say you haven’t, like us…

You’ll need to set up an account and then purchase the Standard SSL certificate. This is just a monetary transaction; the details of the certification are set up later. The only choice you’re making here is whether to commit to 1 year or 2.

In the ‘My Products’ area of GoDaddy’s website, you should see your new blank SSL certificate. Click ‘Set up’. Because an FMS certificate is an unusual one (i.e. it has nothing to do with an actual website, hosted by GoDaddy or anyone else), you’ll now have to paste your certificate signing request. This is where you’ll need the text from the serverRequest.pem file, as described in the section Generating a certificate signing request (CSR) above.

This should all be pretty straightforward now. The CSR submission prompts you by email to verify ownership of the domain. I did this by following the Domain Access Control Instructions linked to from that email:

Take the unique ID code in the email, paste it into a TextEdit file and save the text file as ‘godaddy.html’. Then upload this file to your.domain.or.subdomain/.well-known/pki-validation/ – so you’ll have to create the folder ‘.well-known’ in your web host’s file manager (or by FTP) at the HTML root folder for your subdomain and then ‘pki-validation’ as a subfolder of it. At SiteGround, my HTML root folder for the subdomain fm.roundededgestudio.com is public_html/fm, but it may vary depending on your host. I know, for example, that 1&1 uses a proprietary ‘webspace’ system.

Then go back to your GoDaddy email and click the verification link. I received a second email a couple of minutes later confirming my SSL certificate had been issued.

When you subsequently download your certificate from your account area on GoDaddy’s website, it asks you which server type it is for. The correct choice here is ‘Other’. Just trust me on that one. It took some time to find the answer, and unfortunately I can’t remember to whom I should give credit. But before I sought this answer, I tried ‘Mac OS X’ and ‘Apache’, both of which failed.

What you get in your download is a ZIP containing two certificate files. The one named as a long random string is what FileMaker Server calls your ‘Signed Certificate File’. The one with ‘bundle’ in its name is what FileMaker Server calls your ‘Intermediate Certificate File’, and is basically all your intermediates stacked. Like a bundle.

Importing your SSL certificate into FileMaker Server

You now have everything you need to install or ‘import’ the certificate on FileMaker Server, which is done via Admin Console.

Make sure you move the two files from GoDaddy to somewhere on the FMS host machine. The third file you’ll need is serverKey.pem, which you’ll remember is in Library/FileMaker Server/CStore. This is what FileMaker Server calls your ‘Private Key File’.

In Admin Console, go to Configuration/SSL Certificate and click on ‘Import Custom Certificate’. On the next screen, browse to the 3 relevant files and input your private key file’s password (you know – the one I told you to remember).

You’ve now (hopefully) installed your certificate, but part of what SSL certification does is to check that the domain name matches what it was expecting, and you haven’t done that bit yet. What this means is that your FileMaker files will now have an amber coloured padlock and will still only be available locally.

Uploading your FileMaker Server SSL certificate to your web host

Ok – back to your web host now. SiteGround uses a backend administration system called CPanel, in which it was easy to see how to upload or ‘attach’ my GoDaddy SSL certificate to my subdomain (fm.roundededgestudio.com), using what CPanel calls ‘SSL/TLS manager’. Your web host may have its own variation on this, but CPanel is common and seen as an ‘industry standard’.

However, of the 3 certificate files FileMaker Server needed, the two from GoDaddy are fine as they are for your web host, but the Private Key file will not be accepted as-is. I tried pasting this into CPanel’s SSL/TLS manager under ‘Upload a New Private Key’ without success.

I Googled and Googled until I was blue in the face. Nowhere could I find the answer. SiteGround’s help files didn’t help; neither did GoDaddy’s or FileMaker’s. This situation was simply too specific for Google to understand my question. I tried rewording it a hundred times. But in the end, I realised I was on my own.

I genuinely thought for a couple of days that this whole process had been for nothing. The only solutions I could think of were giving up on self-hosting FileMaker Server and switching to FileMaker Cloud or switching our web hosting to GoDaddy (but I wasn’t sure if that would work, and it would be a massive upheaval just to find out).

Finally, going back to CPanel, I had the idea to look at the private keys already there, created by Let’s Encrypt. I noticed that they all started with ‘—–BEGIN RSA PRIVATE KEY—–‘. Then I went back to ‘serverKey.pem’ and looked at its content in TextEdit. It has ‘—–BEGIN ENCRYPTED PRIVATE KEY—–‘ as the first line. That’s odd, I thought.

I frantically Googled some more, and eventually realised that this was happening because of the password we had to apply to the private key. SiteGround could not use the key because it was password-protected. This is the line that finally turned on my light bulb, taken from this article:

If your SSL key is encrypted, you’ll first need to decrypt it before using it to secure your app with HTTPS.

The same article then tells you how to do it (in Terminal again, of course):

openssl rsa -in ssl.key.encrypted -out ssl.key.decrypted

Being a (slightly excited) idiot, I just copied and pasted that line. But ‘openssl’ is a command only recognised after first ‘installing’ OpenSSL, an SSL/TLS cryptography library. And the filename of the private key you’re decrypting needs to match what you put in the command. And the private key file needs to be in the directory from which you run the command.

Downloading OpenSSL directly from here gives you an archive containing a bunch of files and folders. I believe it needs to be ‘compiled’ or something. Once more, compiling seems like a step too far away from my goal of making databases. Must stay focussed.

A bit more Googling got me to this website, which had the kind of instructions I was looking for; do this, type that. So to plagiarise, paste this command into Terminal:

ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)" < /dev/null 2> /dev/null

After you put in an admin password for your Mac, this installs Xcode Command Line Tools and then Homebrew. And with Homebrew installed, you can then install OpenSSL with the command:

brew install openssl

And then you can decrypt your private key certificate file (serverKey.pem). I couldn’t be bothered to find out how to move files or change directories in Terminal, so at this point I just copied serverKey.pem (using Finder) to the folder that Terminal defaults to – your user’s home folder. So, after accommodating the correct filename, I ended up with this Terminal command, which will run after you enter the private key’s password:

openssl rsa -in serverKey.pem -out finallyCrackedTheBastard.txt

You should now have ‘finallyCrackedTheBastard.txt’ in your home folder. It should start with ‘—–BEGIN RSA PRIVATE KEY—–‘. It should be accepted by CPanel as a valid private key. You should then be able to associate this private key in CPanel with the two other files from GoDaddy to create a valid certificate. You should be able to associate this certificate with your subdomain. You should put some champagne on ice.

But we’re still not quite finished. My subdomain, fm.roundededgestudio.com, is still pointing to a folder structure hosted by SiteGround. It’s still expecting a website to be there. It doesn’t know about my FileMaker Server.

Certificate redirection

The final act in this appalling saga is to point your subdomain at your FileMaker Server. You would do this via its ‘CName’ record if you had gone for a fixed DNS option. But I went for a fixed IP address, so I need to change the ‘A’ record.

Changing the ‘A’ record of a domain or subdomain is again pretty easy in CPanel. Its ‘Simple DNS Zone Editor’ isn’t enough though; we need the ‘Advanced DNS Zone Editor’ because we’re changing existing records, not adding new ones.

Select the domain under which the relevant subdomain sits. Then look through all the Zone File Records until you find the A records relating to the subdomain. This is what my list looks like:

fm.roundededgestudio.com.
www.fm.roundededgestudio.com.
webdisk.fm.roundededgestudio.com.
cpcontacts.fm.roundededgestudio.com.
webmail.fm.roundededgestudio.com.
cpanel.fm.roundededgestudio.com.
whm.fm.roundededgestudio.com.
cpcalendars.fm.roundededgestudio.com.

In SiteGround, these records are all created with a TTL (Time To Live) of 14400 seconds. As usual, I discovered the hard way that if you don’t change this setting, any changes you make to the record will not even try to happen for 4 hours. And when your main weapon is trial & error, you often change things more than once.

So, maybe the day before you’re going to do this bit, change the TTLs to 60 seconds. Don’t do what I did and change them to 0. That essentially makes any web process stop before it’s had time to load its instructions. 60 seconds just works (and you only have to wait 1 minute to check that).

When this is all over, you can change the TTLs back to 14400 if you want.

Being clueless and tired, I didn’t know which records to change. However, with more trial & error and a heavy dose of logic, I realised that I only needed to change the first 2, ‘fm.roundededgestudio.com.’ and ‘www.fm.roundededgestudio.com.’. Simply edit the records and change the Address to whatever your new fixed IP address is.

Testing

During this journey I lost count of the things I tried, the Google searches I made, the times I felt advice had been wrong or misleading. The frustration of my own ignorance combined with the lack of useful information almost overwhelmed me. So seeing a little green padlock on my FileMaker Pro file actually brought tiny tears to my eyes. IT isn’t often emotive.

To test things, you can simply go into FileMaker Pro and connect to your Server in the Hosts dialogue using the subdomain address rather than through the local connection. You might find it more reassuring to do this when outside your LAN. I was so eager to test it that I just turned my laptop’s WiFi off and connected via 4G using my phone as a hotspot.

You can also type in your subdomain’s URL into a browser (e.g. https://fm.roundededgestudio.com) to get through to your ‘FileMaker Database Server Website’. Or stick ‘/fmi/webd’ on the end to get to your WebDirect projects. Or, if you’ve opened port 16000, stick ‘/admin-console’ on the end to get to the Admin Console sign in page.

Another thing worth doing is using GoDaddy’s certificate checker, available through ‘SSL Tools’ on its website. Type in your subdomain and click on ‘Scan My Site’. A few seconds later, a bunch of green ticks should appear. If they don’t, then you haven’t finished yet.

Assuming the tests are all fine, you should now have a new think about security. Do any of your test FileMaker files give away account names or passwords (or any other sensitive information)? Do they allow admin level access without a password? Are there any potential vulnerabilities now that those ports are open in your firewall? FileMaker does not itself introduce new vulnerabilities, but the new functionality of external access may uncover them elsewhere.

Summary: landmarks on a FileMaker Server journey

Buy a dedicated host machine. We use a Mac Mini.
Buy your FileMaker Pro and Server licences.
Install the FileMaker software.
Don’t update Java. No, definitely don’t do that.
Use your router’s firewall rather than OSX Server’s. Check the setup carefully.
Fix your FMS host’s internal (LAN) IP address outside the scope of your DHCP distribution.
Open ports 80, 443, 5003 and 16002 in your router and port-forward them to the FMS host’s internal IP address.
Open port 2399 for ODBC/JDBC solutions and 16000 if you want to use Admin Console externally.
Secure a fixed external (WAN) Internet address. We bought a static IP address from our ISP.
Decide on and secure an FQDN with your web host – probably a subdomain.
Generate a CSR in FMS.
Choose a FileMaker-approved CA like GoDaddy and buy an SSL certificate.
Set up the certificate and verify subdomain ownership through your web host.
Download the certificate files from your CA.
Import your SSL certificate into FMS.
Decrypt your private key, which requires OpenSSL, which requires HomeBrew, which requires Xcode Command Line Tools.
Upload your SSL certificate to your web host and attach it to your subdomain.
Change the TTL options for the DNS Zone records you need to update to 60.
Redirect your subdomain to FMS by changing DNS Zone records.
Test everything.

Where next?

Recent post

2 Comments

David Heslam on 26th March 2020 at 9:57 pm
Yes when you speak to their sales people about setting up servers it’s presented as all very straightforward. The reality is far from it!
- Andy Salt on 27th March 2020 at 9:17 am
  I agree. The pain (and cost) would be lessened somewhat if they devised a way of including a permanent certificate for the server, although I understand why this has to be linked to your own domain. But for software that’s been around this long, the whole thing should be a lot easier by now.