Mr Bates vs The Post Office, or Why Integrity Matters In This Industry

A TV programme is not usually the subject of this blog. What is occasionally the subject of this blog is the application of IT and the real human impact it has. People like me got into IT with a view to the betterment of society. I am not a TV watcher myself but after being asked many times about the Mr Bates vs The Post Office I decided that I would take the time watch it to understand the content of it.

The programme rightly focuses on the human impact of the scandal. Whilst the technical explanations for Horizon’s failings are not explained in great detail there are some points pertinent to the IT industry.

The Software Is Robust?

This claim is made by the then head of the post Office Paula Vennells in a phone conversation. This is a problematic claim to make; all software has the potential to contain bugs. As above the programme does not go into great depths regarding the technical aspects of the Horizon IT system but issues with multiple POS terminals and a PIN pad are raised.

The IT industry should always assume room for error within a system and the processes by which people use it. Concerns and patterns of issues should have been picked up by both Fujitsu and the Post Office long before this became a national scandal.

Remote Access?

There one particular theme running throughout the programme which is of remote access to systems. Remote access is a normal part of supporting modern IT systems however in the case of the Post Office, Fujitsu and Horizon things went badly wrong.

It is explained in the programme that Fujitsu employees made remote connections to Post Office branch systems and made “corrections” without the knowledge of the sub-postmasters. Under no circumstances should remote access be made to a system be made without consent. The results of the remote access session should be recorded and any corrective action made to a ledger should be made under an administrative or support account so that the action is not logged as the user reporting the fault instead.

It is inevitable that as a result of the programme that the idea of remote access to systems will be challenged more however given the trade off between rapid support and information confidentiality, accuracy and legitimacy I would expect we are in a fully “remote accessible” world.

Non-disclosure Agreements?

The use of Non-disclosure Agreements (NDAs) are brought up at several times in the programme. In the case of the Horizon system they were used by the Post Office to prevent victims of the scandal from discussing their settlements with sub-postmasters. The ethics of NDAs continue to be an issue for the industry. Whistleblowers should always be empowered and protected by law to ensure that they are able to raise issues to their employer or the authorities.

Lack of Training & Support

In the programme one of the victims Jo Hamilton states that she is no confident with either technology or accountancy. There is another lady later on in the series who joins the JFSA group and reports issues with the Horizon system reporting losses when the power is repeatedly going out at her branch.

In the case of both of these victims this highlights the lack of support given to the sub-postmasters for using the system which is supposed to help them run a Post Office. There is also a constant theme raised in the programme over helpline staff telling victims that “they are the only ones having this problem”.

I have often thought that by labelling someone a “user” outside of a design context is problematic. We often use words in IT that dehumanise. In the case of the Horizon scandal this also underlines why effective training and support should always be available to people using the systems.

The Future?

The themes discussed in Mr Bates vs The Post Office should be a wake-up call to anyone in the IT industry – regardless of their job description – that cover-ups and complacency with the truth has real human impact. Sub-postmasters have been wrongly convicted as criminals based on faulty evidence of fraudulent accounting and theft. As a result livelihoods have been lost, and some have tragically taken their own lives as a result. It is only the right thing to do that these people should be compensated.

For further reading I recommend Computer Weekly’s Everything you need to know article. As a publication they first investigated the story after receiving letters from Mr Bates and other sub-postmasters. They have done great work tracking the story for a considerable amount of time.

There is a character in the programme Robert Rutherford – I understand to be a compound character of two Second Sight forensic accountants – who when interviewing Jo Hamilton tells her in response to her asking: “can I ask a stupid question?” responds with “There are no stupid questions, only stupid answers”. Damn right.

Updating VMware Tools for VMware ESXi

A bit of a segue from the usual SQL Server posts this week but I wanted to share a recent challenge I encountered with a VMware ESXi host. I needed to to ensure that all high CVSS vulnerabilities were resolved for upcoming Cyber Essentials compliance. This necessitated an upgrade of a VMware ESXi host as a first step as the version in use had known vulnerabilities.

Post upgrade of the ESXi host what I found however was that this did not fully resolve the security concerns. Our Microsoft Defender for Endpoint dashboard picked up that all the servers on the host were still using a vulnerable version of the VMware tools. Upon further investigation despite that the tools had upgraded to a higher version included with the ESXi release there were still unresolved issues with that particular version. It would be necessary to upgrade to a even more recent version of VMware Tools from VMware’s downloads site to fully resolve the vulnerability findings.

You most certainly could manually logon to each server and perform the tools upgrade manually however if your ESXi host has as many servers as ours then the process to upgrade each one might take some time and cause service disruption. After all this is IT and we pursue the noble art of automation in all areas right?

I have to apologise for the lack of screenshots in this post as this was done on a server I’m not privileged to take screenshots of but hopefully you can make sense of the steps below. Comment below for any clarifications required.

  1. Ensure compatibility of the updated VMware tools using the VMware Compatibility Guide and then test on an isolated server – you don’t want to risk potential downtime by installing a version of VMware tools that has a compatibility problem.
  2. Enable SSH access to the VMware Server – to do this open the Host tab in the ESXi web front-end then go to actions > services > Enable Secure Shell (SSH).
  3. Logon to the ESXI host using an SCP tool such as WinSCP – once in navigate all the way to /vmimages/tools-isoimages. You’ll notice that this contains ISO images and manifests for Windows and Linux versions of the VMware tools. Note that his folder actually is a symlink to a folder at /vmfs/volumes/<GUID>/packages/<version>/vmtools.
  4. Backup the contents of the folder – just in case y’know.
  5. Get a copy of the VMware Tools and upload into /vminmages/tools-isoimages. Make sure you overwrite what’s in the folder and include all the files from the download.
  6. Disable SSH access by repeating step #2 – reducing the ESXi host’s attack surface area is always a good idea.

Shortly after completing the above process the ESXi host will automatically pickup that there’s been a change in the VMware Tools in the store. You can from that point upgrade using one of the following methods:

  1. Right click the VM > Guest OS > Install/Upgrade VMware Tools…
  2. If the option to automatically upgrade tools is selected for a VM then reboot and it will handle things itself.

You should at this point have VMs running the latest VMware Tools which you can check the version in the list of hosts. You can add in the column for the VMware Tools version to check all VMs without logging onto each one.

Merry Christmas and a Happy New Year!

If you’re reading this I’ve temporarily closed off SQL Server Management Studio for the year and I’m spending time with my family & friends.

I hope you are having a wonderful Christmas too and that Santa brought you all the things in the world you wished for or at least everything Santa could do with the SQL login privileges he had at the time.

Thank you for reading the blog and I look forward to writing more content into 2024.

Compatibility Is Key!

This will be the final semi-regular post of the year so before I get into the subject I’d like to say a brief thanks for your readership over the year!

This week I have been focused more on the platform side of things: organising updates on our VMWare ESXi host with our data centre and preparing for an upcoming attempt at Cyber Essentials certification. In other words not that much on SQL Server this week but still had a dabble when called for.

It has come to my attention that you can’t licence SQL Server 2016 or 2017 through distributors any more. You’ll have to pardon my ignorance on licensing as through my main job we don’t do the SQL Server licensing. Either there’s already an SQL Server there or a new one is deployed.

I recently did a rapid-fire upgrade for a client which called for a move of an existing application from Windows Server 2012 R2 and SQL Server 2012 infrastructure. Because of the age of the application running there which wasn’t to be upgraded the design called for Windows Server 2016 and SQL Server 2016 to be used as these were the highest supported. Unfortunately the client’s IT were not able to source SQL Server 2016 from their distributors any more.

This is a nice segue into SQL Server compatibility levels, what they are and what they are used for and how that could save us.

Every database connected to a SQL Server instance has a compatibility level set. By default for a new database it will be the compatibility level for that particular version of SQL Server that’s been installed (unless the instance has been upgraded but that’s a different matter). The database compatibility level is used by the Database Engine to control what specific SQL Server behaviours and changes are used for that database. If you want to know more then Microsoft have that in their documentation if you fancy a detour.

The official word from Microsoft is that you certify an application against the database compatibility level and not the SQL Server instance version. In other words it’s valid to have a SQL Server 2022 (160) instance and connect databases with a SQL Server 2014 (120) database to it with a corresponding compatibility level to maintain backwards compatibility. Your application vendor might begrudgingly disagree with that policy though so check in with them first.

Examining and Changing the Database Compatibility Level

In SQL Server Management Studio the easiest way to determine the database compatibility level is by right clicking your target database > properties > Options tab. The current compatibility level is displayed in the window.

SELECT [name],[compatibility_level]
FROM sys.databases

You’ll notice that the compatibility level is not 2022, 2019, 2017 etc but a number corresponding to the product version of the SQL Server instead.

If you determine that a compatibility level change is required you can set a new level using the following command:

ALTER DATABASE [MyDatabase]
SET COMPATIBILITY_LEVEL = 160 | 150 | 140 | 130 | 120 | etc

You should note that you may experience query regressions when moving up compatibility levels. The usual reminder to test carefully before deploying to production counts here!

Working With Constraints and Problematic Data

The challenge this week concerns constraints. In an SQL database constraints are used to enforce referential integrity on the data held within it. For example you can use foreign keys to ensure that a sales order in a database always has a reference to a valid customer ID in a customers table or use a table constraint to enforce that a purchase order number must be entered (i.e.: does not have a NULL value).

One thing you can do with a SQL database which is a bit naughty is disable the checking of the data on a constraint. Useful if you need to rework some underlying data in a pinch. Hold that. It’s not. What that does is store the problem up for later and probably for someone else to work out.

Constraints and foreign keys are there for a good reason which is to make sure your data is…you know…referential.

We have a software product that – when moved up to the cloud – now requires that referential constraints are enforced. As part of the upgrade procedure with the “designer” edition of the software (the environment that the data has to be upgraded to before getting shipped off to the cloud in this case) the consultant needs to enable an option in the software which effectively builds a SQL statement to do it across the database.

Sadly when that was enabled all hell broke loose. Well. Many foreign key violations at least. the software itself did not report any issues enabling trusted constraints but another tool which checks the database for correct preparation did.

After some SQL Profiler tracing I found 5 instances where data had escaped the foreign key constraint that was written. This happens usually when someone disables the constraint using the NOCHECK option, changes the data as they see and then re-enable the constraint with the CHECK option.

When you find a constraint violation within a SQL database you can query the problematic data by using the following command.

DBCC CHECKCONSTRAINTS (<constraint_name>)

To check every constraint on the database – either enabled or disabled – use the following but be aware that this may take some time.

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS

Once I then identified and resolved all the data issues I was then able to take the easy route and have the software enable all the constraints within the database and pass the upgrade check.

Seriously, Stop Using Windows Server 2012 & 2012 R2!

(Also SQL Server 2012 please)

Extended support for Windows Server 2012 and 2012 R2 expired on October 10th 2023. We’re coming up to November 2023’s Patch Tuesday which means that there’s really, really, really no life in Server 2012 or 2012 R2 any more in case that first deadline wasn’t important enough. Hacking crews out there will highly likely be able to spot a vulnerability in Server 2012 / R2 by checking out the vulnerabilities for Server 2016 and newer. So in other words if you’ve not planned to be off Windows Server 2012 / R2 by now you’re a bit stuffed. That is unless your organisation’s forking out for Extended Security Updates in which case you can breathe easy a bit longer.

If you are in the UK have Cyber Essentials renewals coming up you either need to be shut of the servers or segregate them somewhere off the main network to their own retirement VLAN before the audit starts otherwise you’ll fail it. Don’t say I didn’t warn you.

Don’t Just Move It To Azure!

Yes it’s true that you can move your server to Azure and get an extra three years of security updates included in the price of the VM service. Three years sounds a lot of time but that will run down before you know it. So don’t kick the proverbial can down the proverbial road.

Moving a series of servers from a private cloud or IT infrastructure to a hyperscaler can also be costly in direct costs for the VM (CPU, memory, Operating System, disks, etc) but may also result in hidden fees in terms of having to build remote access solutions bring in consultants and even patch the application. It’s generally cheaper to run VMs in a private cloud if they are needed 24/7 so check costs carefully.

Mark Your Calendars for Windows Server 2016 End of Extended Support

January 12th 2027. It’ll be here before you know it.

Discussing Authentication Modes in SQL Server

I have been on holiday for the past two weeks but just before I left the subject came up with a client regarding authentication methods in SQL Server. Whether you are logging in using SQL Server Management Studio (SSMS) to make queries or configuring an application to work with SQL Server the authentication method is important to consider. 

By authentication we mean a process a user must complete to gain access to a system. SQL Server roles and rights fall under authorisation – determining what access a user has in a system – which I’ll discuss another time.  

When you install a fresh SQL Server instance on Windows you’ll be prompted regarding the authentication mode to have it in: 

  • Windows – Default and enables Active Directory authentication only. 
  • Mixed – Enables both Active Directory and SQL Server’s built in authentication method 

You also get to specify what AD users and groups to add to the sysadmin role. When I do this for clients I make sure to identify the users or DBA groups to add. Adding the domain admins group I would advise avoiding unless as a last resort because given that sysadmin has read permissions on data in all databases there should still be a separation of roles between an IT admin and someone in an organisation who has a both requirement & trust to access that information. 

If you change your mind later on after the install you can switch between modes in the instance properties in SSMS or using the registry. 

I recommend if possible that an SQL Server instance uses Windows Authentication. This is because it’s simply put the most secure form of authentication of the two. 

Windows Authentication makes use of an organisation’s existing Active Directory user directory. This mode is always active and is often referred to as “Trusted”. I’ll not go into the exact workings of Active Directory authentication in this post but the critical point is that SQL Server is not performing the authentication itself. Instead the authentication is done against Active Directory by passing back authentication tokens. A slight downside would be that this creates a dependency on a working and redundant Active Directory Domain Services setup (read: ideally two servers with AD replication working and healthy).  

When using an SQL Server authenticated login this will use SQL Server’s own authentication scheme which is much weaker than Active Directory. An example of how much weaker this is would be that in the communication between the client and SQL Server instance you’ll find the password sent in plain text. 

Special Note On The sa Account 

There’s a default account in SQL Server which by default and on installation is named ‘sa’. This is the most powerful account in any SQL Server instance and has the SID 0x01. 

I often get asked the question either in handover or in a support query about the sa account and my answer is always that I don’t use it. Applications, MSPs and consultants that ask for this account lose a few points for doing so. Using the sa account to configure an application or for general use is an absolute no. 

I highly recommend leaving the sa account disabled. It’s also recommended to rename this account as an attacker can usually assume there is an account named sa on the instance to try for. 

As a safer alternative giving users named logins is highly recommended for accountability purposes instead of a shared account. 

Note that if you install SQL Server in Windows Authentication mode the sa account is still created but with a random password and left disabled. 

Database Configuration Checks With The sys.databases View in SQL Server

Recently building a system and preparing to handover the system to a colleague who will be completing the build they asked how they SQL Server databases were configured for the transaction logs, compatibility levels and other things before before other data was loaded in.

To answer questions on that regard you could go through each database one-by-one in SQL Server Management Studio to check the configuration but that’s going to get fairly tedious really fast.

As I’ve said before it’s often best to try and script things out to save them for later rather than poking through the GUIs all the time so this is where building something you can repeatedly use comes in. In this instance the view sys.databases is a really useful one to know.

I’ve built myself a T-SQL script that I use to bring out useful information I can then copy out for documentation or just a quick check of the databases. This script is available in my GitLab repo for you to try. My recommendation would be to modify to your requirements or be an inspiration to build your own.

Your SQL login needs to have ALTER ANY DATABASE, VIEW ANY DATABASE or CREATE DATABASE permission at server level or be in the SYASADMIN server role. If you call the view any database you have the db_owner role over will appear too.

This script I use to give a quick check following building a new system. Just an an example it’s usually a really good idea to have AUTO CLOSE and AUTO SHRINK set to OFF on production databases. Sometimes clients might set these on as they sound like a good idea just by reading them. With sys.databases this is an example of option you can check to see what’s configured for either general checks or troubleshooting purpose.

My invitation to you is to check out my script, leave me a few comments and have fun building your own.

Making Yourself A Quick Backup in SQL Server

A nice a and simple one to share this week. Often when giving SQL Server consultancy I have to make backups at points before I’m doing something hazardous in a client’s database. This might be patching or running a script for example. It’s best to take your own backups just in case something doesn’t go to plan.

I occasionally see people fumbling around the GUI of SQL Server Management Studio to do backups and when there’s multiple databases to do it can really get clunky. There’s an argument to say that often it’s better to learn to script rather than work through GUIs. I certainly agree. Learning to do things in SQL Server via T-SQL commands means you can save them for later and also save a lot of time.

Your most basic command to backup a SQL database is as so:

BACKUP DATABASE [MyDatabase] TO DISK = N'Backup_Mydatabase.bak' WITH COPY_ONLY, COMPRESSION, STATS= 10

That’s really it for you GUI fans.

Let’s break this down further and look at the arguments after the BACKUP DATABASE command:

TO DISK = N’Backup_Mydatabase.bak’ – instructs SQL Server to make the backup to the disk with the name as specified. In SQL Server 2008 R2 and above the backup path can be seen in SSMS by right click the instance name > Properties > Database settings tab and note the path for Backups right at the bottom. Prior to (if I remember correctly) SQL Server 2008 R2 this backup path needed to be specified in full. You can specify a full path if you want to backup elsewhere but you need to ensure that SQL Server has permissions there.

COPY_ONLY – this option instructs SQL Server to only make a copy of the database and not record it in the backup chain. What this means is that if you have a disaster and want to recover then the backup you have just taken isn’t considered the last full backup in the chain. You should consider using this option for copying databases to another server and also for backups outside the normal backup routine.

COMPRESSION – applies compression to the backup. Leaving this off will mean SQL Server will follow the server’s default setting. I have the habit of doing this because unplanned backups take space somewhere and adding unplanned backups can tip the balance if you’re not careful. This being said you should always check space before making backups and restores.

STATS = 10 – this is a useful argument to add on as in the messages tab of SQL Server Management Studio this will give you a progress message at every interval which is a multiple of the number you have specified. So for 10 for example it will say when it’s reached 10%, 20%, 30% and so on up to 100%. This is handy to know as the backup progresses so you can estimate how far off completion you are.

Why Managing Transaction Logs in SQL Server Is Important

This past week I had a situation arise where the SQL Server transaction logs for a particular site grew out of control. This ended up consuming an entire disk volume for the log files which eventually caused the SQL Server to stop processing and by extension caused application downtime. This can be a common cause of unplanned downtime for an application when the transaction log was not managed correctly. Just what is the transaction log in SQL Server for and what do you need to know about it?

What is the transaction log for?

In SQL Server each database consists of two types of files: the first is the data and the second is the log file. Each database may have one of each or multiple data and/or log files.

The database file contains the tables, views, functions, indexes, stored procedures, etc. Basically all the objects in the database. This file normally has the extension .mdf.

The log file on the other hand contains a record of transactions that have occurred in the database. The transaction log file is important to consider for many reasons including performance, data integrity, disaster recovery and high-availability. This file normally has the extension .ldf for primary log files or .ndf for secondary logs.

Note a common myth is that in SIMPLE recovery mode the transaction log is not switched off. All operations are recorded in the transaction log whether that be FULL or SIMPLE mode. The difference is in SIMPLE recovery mode once the transaction is committed into the database then the log is truncated.

Why is it important to manage the transaction log?

The most important answer is that the transaction log forms part of the disaster recovery plan of any SQL Server database. If a disaster occurs then backups of the transaction logs will be required to restore to the last known good point in time.

An immediate unwanted effect of uncontrolled transaction logging is that the log file will continuously grow if autogrowth is enabled. The risk is that this can eventually grow to consume the entire disk volume the transaction log occupies. If SQL Server cannot grow the log file of the database then the transaction it is trying to process will fail.

Why and when should I use SIMPLE or FULL transaction logging?

When deciding on the most appropriate transaction log the decision should be focused around the recovery requirements and acceptable loss window.

For example if you have a live ERP system you should consider FULL transaction logging so that you are able to restore the database using transaction log backups if a failure occurs after a full or differential backup of the database is done.

On the other hand if you had copied the same database into a temporary test system and you are not using the test environment with intention it should be a perfect replica of live then consider using the SIMPLE transaction logging method.

Generally use SIMPLE:

  • Test/training databases.
  • Data warehouses.
  • Settings databases (i.e: lists of usernames, application configuration where the data does not change frequently)

Generally use FULL:

  • Production databases for live OLTP applications.
  • UAT and Pre-Production systems where there is a requirement to mimick the live environment configuration exactly for proving purposes.

You should also consider switching a production system with databases in FULL recovery mode to SIMPLE on a temporary basis whilst you are performing an application upgrade or patch which updates the database. This is to avoid the transaction log growing out of control during the operation. After finishing the upgrade the recovery mode should be switched back to FULL.

Footnote: Switching between recovery modes.

  1. Stop all dependent applications for the databases.
  2. Take a full backup of the databases you are switching the transaction log for. Safety first.
--Set the database to single user mode so that further transactions are stopped.
ALTER DATABASE [ExampleDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--Switch the recovery mode
ALTER DATABASE [ExampleDatabase] SET RECOVERY FULL | SIMPLE 

--Finally set the database back to multiple user mode.
ALTER DATABASE [ExampleDatabase]  SET MULTI_USER

You can then start any application services that depend on these databases.