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.

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.

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.

Now That We’ve Got a Cumulative Update…Updating SQL Server on Linux

A few weeks’ ago I blogged about installing SQL Server 2022 on Linux. It just so happens to be that Cumulative Update 8 for SQL Server 2022 came out this week. This gives us the perfect opportunity to talk about applying those all important CUs to the SQL Server we built a couple of weeks back.

As discussed applying SQL Server Cumulative Updates on Linux works differently to Windows. On Windows you generally either get CUs by manually downloading the update via Microsoft’s KB on that particular update or accidentally let one install via Windows update if there’s a security fix. On Linux you generally get updates via the repositories you have configured for your server.

Applying Cumulative Updates for SQL Server (of any supported version) ensures that you have the latest fixes for the SQL Server platform. Each update is certified to the same degree as a service pack used to be and Microsoft generally recommends you keep up to date with their installation. That being said you still should test carefully in UAT before applying to a live system.

The first thing you need to do of course is backup your SQL Server. Something could go wrong so you must make sure you have a rollback plan in case that does happen. This could be: taking a snapshot of the server’s VM in your hypervisor, performing a full backup of all the system and user databases, initiating a full server backup using your favourite backup agent or something completely different.

Once you have a test plan in place which has been approved plus your backups you are now ready to install the latest Cumulative Update.

Step 1: Check to see if you’ve got the correct apt source in your repos:

sudo apt edit-sources

You will then get a 1-4 choice for which editor you want to use. I chose nano which is option #1. At which point you’ll see nano appear. Scroll right down and find your sources.

If you don’t see Microsoft’s sources for SQL Server in there it’s not all bad news as Microsoft have made a guide on how to resolve that problem.

Once you are done reviewing the sources list press CTRL + X to close nano.

Step 2: Run apt update to fetch the current package list from the repositories:

sudo apt-get update

This takes a few minutes and for my server resulted in a 14 MB download. This command is vital to ensure that the next command runs properly otherwise you’ll be using an outdated package list.

Step 3: Perform an upgrade of msssql-server using apt:

--To update everything on the system at once
sudo apt-get upgrade 

--To do MS SQL Server only
sudo apt-get upgrade mssql-server

This will then list out all the packages that need upgrading based on what’s installed to your Server vs what’s available in the repository. If you decided to upgrade everything one of those packages should be…yes you guessed it…mssql-server. Cumulative Update 8 is approximately 268 MB to download:

You are of course going to answer ‘Y’ to this question. Or press enter (note that the “Y” is capitalised. This means that it’s the default answer if you smack return).

This will then run through all the updates to go through. Highlighted here is apt setting up the mssql-server package version 16.0.4075.1-1 which is indeed Cumulative Update 8.

Step 4: Verify the mssql-server service is alive:

systemctl status mssql-server --no-pager

As you can see we’re onto a winner:

At this point it’s also probably a good idea to open SQL Server Management Studio or Azure Data Studio to check you have a working connection. To double check you have Cumulative Update 8 you could also execute the following command in whatever SQL query tool you are using:

SELECT @@VERSION AS VersionString
Microsoft SQL Server 2022 (RTM-CU8) (KB5029666) - 16.0.4075.1 (X64)  	Aug 23 2023 14:04:50  	Copyright (C) 2022 Microsoft Corporation 	Developer Edition (64-bit) on Linux (Ubuntu 20.04.6 LTS) <X64>

At this point you should start performing your acceptance tests on your databases and applications. Aim to verify that the usual business and system processes are working before declaring a success!

But What If I Wanted A Specific Version of SQL Server?

Whilst it’s usually recommended that a new SQL Server instance should go through a UAT phase with whatever application(s) will be running against it using the latest CU available at the time. However there could be situations where a specific version of SQL Server is required. Whatever that reason in order to do this run the following commands:

sudo apt-get install mssql-server=<version_number>
sudo systemctl start mssql-server

Where <version_number> is the version string you need. For example Cumulative Update 7 is 16.0.4065.3-4. To find the version number you need consult the release notes for SQL Server 2022 on Linux.

WFH…Is It Over?

This week video conferencing specialists Zoom announced that they expect employees back in the office at least for some of the week. At least if you are working within “commutable distance” of their offices.

It’s not just a Zoom – which strangely was the weapon of choice when we were all sent home for the COVID-19 pandemic – but also the likes of Amazon have decided that they will track and penalise employees working from home too much. And it’s not just them either. Over the past year the likes of BT Group, Apple and Twitter/X have also decided on some form of return to work. 

So what’s the big fuss? 

Work from home was a necessity during the pandemic for obvious reasons but now that’s in some kind of endemic phase it’s not really a barrier to bringing people back to the office any more. Top level management are seemingly intent on using that office space they’ve spent the cash on to reap the benefits of bringing teams back together. Or so the theory goes right?

I read different articles and different studies on the effect from working from home. Some in favour, some against. Personally I think it’s very much down to the individual and their circumstances. I do work from home but I don’t really like doing it as I feel there should be a separation of work and home space but also interacting with people face-to-face is critical important. I do live alone so that’s a big reason for the aforementioned mind

There are MANY different benefits to working from home polices in terms of environmental benefits (less cars on the road and/or stuck on the M62 might be nice for some), parental responsibilities, focus in busy environments (who on earth likes working in open offices?!) and countless others.

Despite the fact I personally wouldn’t work from home if I chose to I would still discourage an employer from withdrawing the facility. Fact is that it’s outdated to think that computerised work can only take place in one location and that location must be the company offices. Not everybody wants to work in an office and be closely supervised on work they can do easily at home. Just why am I working this hard just so that employers can decide working from a beach bar is a bad idea?

Powered by Pasties

Last week I’ve been back at work after a week scoffing pasties down in Cornwall with the family. I had a Parkrun and a few sea swims in between too which has been great. I also gave myself time to finish reading Diana Nyad’s memoir Find a Way.

Diana was the first person to swim from Cuba to the United States. This incredible achievement over a distance of around 180km (110 miles) took 5 attempts, a lot of heartache and dedication to the dream.

I wanted to read this book after watching an interesting Ted Talk by Diana titled “Never, ever give up”. In the 15 minute appearance she talks about the challenges and motivation she found for taking on extreme distance swimming. Right at the end she calls to the crowd “find a way!”. Not only did I want to read her full story I wanted to find out who exactly Badass Bonnie was too!

I found Find a Way well worth the read and interesting as an open water swimmer. I would definitely recommend it to anyone whether a swimmer, sports fan or in need of some inspiration.

That is also to say that other than doing “catch up” not in the water that’s as much as I’ve done professionally following my return.

I’M STILL ALIVE!!!1!!ONE

I remembered this week that I’ve not posted here for a while so in the interest of (1) confirming I am alive, (2) making some fluff up to prevent the blog from looking “abandoned pending domain expiry” and (3) to get back in some kind of rhythm.

Honestly it’s been busy. Nobody wants to stand still and as we all work out what’s going to be the “new normal” there’s a lot of push for cloud, automation and remote connectivity. That means a lot of work to be done so less time to have a natter on the blog.

Hopefully I will have a new laptop to review pretty soon so watch out for that.

Lick Of Paint

Speaking to a lot of people right now everyone’s at the stage where they’ve not much to talk about seen as they can’t leave their homes and…you know…do interesting things. I am looking forward to being back swimming and for a pint at the pub (not together though).

In the meantime I put a fresh lick of paint on the blog. Enjoy.

There’s Not Much To Say!

(Well there is but you know what I mean.)

In the UK we have gone into Lockdown 3: The Winter Crisis so there’s not much Round Tabling, swimming or pubbing to be done until the COVID-19 virus is brought under control. Fortunately consultancy is back in full swing so the bills are getting paid.

Priority number one and two for 2021 will be to stay safe and get the vaccine respectively. As a child my parents would always tell me to play outside and avoid any drugs especially needles. In 2021 they’re telling me to stop inside and get a jab as soon as my turn comes up. How times change.

Over Twixmas I did try my hand at building a PiHole on a Raspberry Pi 3 Model B that has been lurking in my drawer but I scrapped it after a week. I felt the omission of DNS over HTTP/TLS by default is too important to overlook. I plan to build a WiFi 6 core network at my house sometime in 2021.

Meanwhile I’ve been discussing PC builds with some good friends of mine as we’re enjoying Cyberpunk 2077. I’m currently drawing up a parts list for my next custom build I’ve provisionally called Build-V. Yes that is indeed because it’s the 5th custom build I’m intending to put together and yes that’s where all that petrol money I’ve saved is going.