For number of reasons, instead of migration, I should write migraine. First is about moving from an old system into a new one. Latter is about medical condition causing severe headaches. In this case, there seems to be not much of a difference.

Google's own Make the switch to Google Analytics 4 -migration guide has 12 steps. Given complexity, the time I have to invest on this and lack of really diving into this, I probably missed most of them.

Going into GA4-console, I have this:

Clicking on Get Tagging Instructions, I'll get further information:

There is a snippet of JavaScript, which I can inject into this site.

<script async="" src="https://www.googletagmanager.com/gtag/js?id=G-859QZJED94"></script>
<script>
  window.dataLayer = window.dataLayer || [];
  function gtag(){dataLayer.push(arguments);}
  gtag('js', new Date());
  gtag('config', 'G-859QZJED94');
</script>

Note: My GA4-stream ID is G-859QZJED94. Make sure to use yours, not mine. As the stream ID cannot be a secret, it's ok to publish it here. The same ID can be found from HTML-code of this page.

To verify installation success, I went to GA4 portal, chose Reports and Realtime. It didn't take long, less than a minute, for that report to indicate user activity. Many other places keep saying "No data received from your website yet", "No data received in past 48 hours" or "Data collection isn’t active for your website". IMHO all of those are incorrect, as I do have stats. We'll see if I managed to do that right.

Previously I wrote a Wordle-solver, Sanuli-Konuli. The command-line -based user-interface isn't especially useful for non-programmers, so I took the solver a bit further and automated the heck out of it.

There is a sample Youtube-video (13+ minutes) of the solver solving 247 puzzles in-a-row. Go watch it here: https://youtu.be/Q-L946fLBgU

If you want to run this automated solver on your own computer against Sanuli, some Python-assembly is required. Process the dictionary and make sure you can run Selenium from your development environment. Solver-script is at https://github.com/HQJaTu/sanuli-konuli/blob/master/cli-utils/sanuli-solver.py

The logic I wrote earlier is rather straightforward and too naive. I added some Selenium-magic to solver to write a screenshot of every win (overwriting previous one) and every failure (timestamped). A quick analysis of 5-6 failures revealed the flaw in my logic. As above screenshot depicts, applying improved logic in a scenario where four out of five letters were known and two guesses were still left, changing only one letter may or may not work, but depends on luck before running out of guesses. In improved approach would be to write detect code and when this scenario is detected, doing a completely different kind of "guess" throwing away those four known letters, determining a set of potential letters from set of words and finding a word from dictionary containg most of the unknown letters which would fit would be much smarter approach. Then one letter should be green and rest not, then doing a last guess with that letter should solve the puzzle. So, my logic is good until a point where one letter is missing and then its fully luck-based one.

In above game, guess #4 was "liima" with M as not being correct. At that point of the puzzle, potentially matching words "liiga, liina, liira" would result in potential letters G, N and R, then guess #5 woud be "rangi" (found in dictionary). Making such a guess would reveal letter N as the correct missing letter making guess #6 succeed with "liina".

As I said, this logic has not been implemented and I don't think improving the guessing algorithm any further is beneficial. I may take a new project to work with.

Standard disclaimer applies: If you have any comments or feedback, please drop me a line.

Last November I created CentOS 8 -based Databricks containers.

At the time of tinkering with them, I failed to realize my base was off. I simply used the CentOS 8.4 image available at Docker Hub. On later inspection that was a failure. Even for 8.4, the image was old and was going to be EOLd soon after. Now that 31st Dec -21 had passed I couldn't get any security patches into my system. To put it midly: that's bad!

What I was supposed to be using, was the CentOS 8 stream image from quay.io. Initially my reaction was: "What's a quay.io? Why would I want to use that?"

Thanks Merriam-Webster for that, but it doesn't help.

On a closer look, it looks like all RedHat container -stuff is not at docker.io, they're in quay.io.

Simple thing: update the base image, rebuild all Databricks-images and done, right? Yup. Nope. The images built from steam didn't work anymore. Uff! They failed working that bad, not even Apache Spark driver was available. No querying driver logs for errors. A major fail, that!

Well. Seeing why driver won't work should be easy, just SSH into the driver an take a peek, right? The operation is documented by Microsoft at SSH to the cluster driver node. Well, no! According to me and couple of people asking questions like How to login SSH on Azure Databricks cluster, it is NOT possible to SSH into Azure Databricks node.

Looking at Azure Databricks architecture overview gave no clues on how to see inside of a node. I started to think nobody had ever done it. Also enabling diagnostic logging required the premium (high-prized) edition of Databricks, which wasn't available to me.

At this point I was in a full whatta-hell-is-going-on!? -mode.

Digging into documentation, I found out, it was possible to run a Cluster node initialization scripts, then I knew what to do next. As I knew it was possible to make requests into the Internet from a job running in a node, I could write an intialization script which during execution would dig me a SSH-tunnel from the node being initialized into something I would fully control. Obiviously I chose one of my own servers and from that SSH-tunneled back into the node's SSH-server. Double SSH, yes, but then I was able to get an interactive session into the well-protected node. An interactive session is what all bad people would want into any of the machines they'll crack into. Tunneling-credit to other people: quite a lot of my implementation details came from How does reverse SSH tunneling work?

To implement my plan, I crafted following cluster initialization script:

LOG_FILE="/dbfs/cluster-logs/$DB_CLUSTER_ID-init-$(date +"%F-%H:%M").log"
exec >> "$LOG_FILE"

echo "$(date +"%F %H:%M:%S") Setup SSH-tunnel"
mkdir -p /root/.ssh
cat > /root/.ssh/authorized_keys <<EOT
ecdsa-sha2-nistp521 AAAAE2V0bV+TrsFVcsA==
EOT

echo "$(date +"%F %H:%M:%S") Install and setup SSH"
dnf install openssh-server openssh-clients -y
/usr/libexec/openssh/sshd-keygen ecdsa
/usr/libexec/openssh/sshd-keygen rsa
/usr/libexec/openssh/sshd-keygen ed25519
/sbin/sshd

echo "$(date +"%F %H:%M:%S") - Add p-key"

cat > /root/.ssh/nobody_id_ecdsa <<EOT
-----BEGIN OPENSSH PRIVATE KEY-----
b3BlbnNzaC1rZXktdjEAAAAABG5vbmUAAAAEbm9uZQAAAAAAAAABAAAA
1zaGEyLW5pc3RwNTIxAAAACG5pc3RwNTIxAAAAhQQA2I7t7xx9R02QO2
rsLeYmp3X6X5qyprAGiMWM7SQrA1oFr8jae+Cqx7Fvi3xPKL/SoW1+l6
Zzc2hkQHZtNC5ocWNvZGVzaG9wLmZpAQIDBA==
-----END OPENSSH PRIVATE KEY-----
EOT
chmod go= /root/.ssh/nobody_id_ecdsa
echo "$(date +"%F %H:%M:%S") - SSH dir content:"

echo "$(date +"%F %H:%M:%S") Open SSH-tunnel"
ssh -f -N -T \
  -R22222:localhost:22 \
  -i /root/.ssh/nobody_id_ecdsa \
  -o StrictHostKeyChecking=no \
  nobody@my.own.box.example.com -p 443

Note: Above ECDSA-keys have been heavily shortened making them invalid. Don't copy passwords or keys from public Internet, generate your own secrets. Always! And if you're wondering, the original keys have been removed.

Note 2: My init-script writes log into DBFS, see exec >> "$LOG_FILE" about that.

My plan succeeded. I got in, did the snooping around and then it took couple minutes when Azure/Databrics -plumbing realized driver was dead, killed the node and retried the startup-sequence. Couple minutes was plenty of time to eyeball /databricks/spark/logs/ and /databricks/driver/logs/ and deduce what was going on and what was failing.

Looking at simplified Databricks (Apache Spark) architecture diagram:

Spark driver failed to start because it couldn't connect into cluster manager. Subsequently, cluster manager failed to start as ps-command wasn't available. It was in good old CentOS, but in base stream it was removed. As I got progress, also ip-command was needed. I added both and got the desired result: a working CentOS 8 stream Spark-cluster.

Notice how I'm specifying HTTPS-port (TCP/443) in the outgoing SSH-command (see: -p 443). In my attempts to get a session into the node, I deduced following:

As Databricks runs in it's own sandbox, also outgoing traffic is heavily firewalled. Any attempts to access SSH (TCP/22) are blocked. Both HTTP and HTTPS are known to work as exit ports, so I spoofed my SSHd there.

There are a number of different containers. To clarify which one to choose, I drew this diagram:

In my sparking, I'll need both Python and DBFS, so my choice is dbfsfuse. Most users would be happy with standard, but it only adds SSHd which is known not to work. ssh has the same exact problem. The reason for them to exist, is because in AWS SSHd does work. Among the changes from good old CentOS into stream is lacking FUSE. Old one had FUSE even in minimal, but not anymore. You can access DBFS only with dbfsfuse or standard from now on.

If you want to take my CentOS 8 brick-containers for a spin, they are still here: https://hub.docker.com/repository/docker/kingjatu/databricks, now they are maintained and get security patches too!

Wordle is a popular word game. In just a couple months the popularity rose so fast New York Times bought the whole thing from Josh Wardle, the original author.

As always, a popular game gets the cloners moving. Also another thing about Worlde is the English language. What if somebody (like me) isn't a native speaker. It is very difficult in the middle of a game to come up with a word like "skirl". There is an obvious need for localized versions. In Finland, such a clone is Sanuli. A word-game, a clone of Wordle, but with Finnish words.

From software engineering point-of-view, this is a simple enough problem to solve. Get a dictionary of words, filter out all 5-letter words, store them to a list for filtering by game criteria. And that's exactly what I did in my "word machine", https://github.com/HQJaTu/sanuli-konuli/.

The code is generic to support any language and any dictionary.

Example Wordle game

Attempt #1

To get the initial word, I ran:
./cli-utils/get-initial-word.py words/nltk-wordnet2021-5-words_v1.dat.

Command's output was a randomly selected word "mucor".

As seen above, that resulted all gray tiles. No matches.

Attempt #2

Second attempt with excluded lettes:
get-initial-word.py words/nltk-wordnet2021-5-words_v1.dat "mucor".

Command's output was a randomly selected word "slake".

This time game started boucing my way! First letter 'S' is on green and two yellow ones for 'L' and 'K'.

Attempt #3

Third attempt wasn't for initial word, this time I had clues to narrow down the word. A command to do this with above clues would be:
find-matching-word.py words/nltk-wordnet2021-5-words_v1.dat "s...." "ae" ".l.k."

Out of multiple possible options, a randomly selected word matching criteria was: "skirl"

Nice result there! Four green ones.
Note: Later, when writing this blog post, I realized the command has an obvious flaw. I should have excluded all known gray letters "mucore". Should my command have been:
find-matching-word.py words/nltk-wordnet2021-5-words_v1.dat "s...." "mucorae" ".l.k."
The ONLY matching word would have been "skill" at this point.

Attempt #4 - Win!

Applying all the green letters, the command to run would be:
find-matching-word.py words/nltk-wordnet2021-5-words_v1.dat "ski.l" "ar" "....."

This results in a single word: "skill" which will win the game!

Finally

I've been told this brute-force approach of mine takes tons of joy out of the word-game. You have to forgive me, my hacker brain is wired to instantly think of a software solution to a problem not needing one.

In my previous post, I gave some statistics about which countries have issued how many X.509 certificates for signing the COVID Certificates. Also, I realized how adoption of this open-source system in non-EU -countries is wide. Actually in the list of countries using the system, there are more non-EU -countries than EU-countries.

As I wanted to learn how to progammatically alter SVG-files (they are essentially XML), I decided to write some code. Here is the result:

Updated map is available at https://blog.hqcodeshop.fi/vacdec-map/, also in SVG-format. Source code of how all this is created is at: https://github.com/HQJaTu/vacdec-map

The process begins with importing maps of the World from Wikipedia page Gallery of sovereign state flags (using Wikipedia API, of course). There is one shortcoming, Faroe Islands map isn't in the list as it is not a sovereign state. It is downloaded separately.

Then the flags are mapped against ISO-3166 country list. Again, some manual helping is needed, list of flags doesn't use all of the exact country names from ISO-standard. Good thing about this is, the need to do this only once. Flags and countries don't change that often.

Fetching the X.509 certificates for signing is something my code has successfully done since August -21. So, a list of known COVID Certificate signing certificates is fetched. A simple iteration is done to those X.509 certificates to find out the oldest date of any country. That date is assumed to be the system adoption date. In reality it is not, but for this coarse visualisation that accuracy will do.

Now there is a repository of all the flags with their appropriate ISO-3166-2 codes and all COVID signing certificates with their issuing dates. Then all country flags are placed on top of a World map, which has a second part in the bottom, a timeline for adoption date for non-EU -countries. Drawing EU-countries there is non-interesting as they pretty much did it all at the same time. The map SVG comes from Wikimedia Commons Carte du monde vierge (Allemagnes séparées).svg.

For optional PNG-rendering, I'm using Inkscape. There are failing attempts to do SVG-rendering with CairoSVG, PyVips and Html2Image. First two failed on accuracy, the rendering result was missing parts, had incorrect scales on various parts and was generally of low quality. Html2Image kinda worked, but wasn't applicable to be run on a server.

It seems vacdec is one of my most popular public projects. I'm getting pull requests, comments and questions from number of people.

Thank you for all your input! Keep it coming. All pull requests, bug fixes and such are appreciated.

Briefly, this is about decoding EU digital COVID certificate QR-codes. For more details, see my blog post from August -21 or the Python source-code at https://github.com/HQJaTu/vacdec.

Quote from my original post: "the most difficult part was to get my hands into the national public certificates to do some ECDSA signature verifying".

My original code has tool called fetch-signing-certificates.py to do the heavy lifting. It fetches a list of X.509 certificates which are used in signing the QR-code's payload. That list is stored into your hard drive. When you do any COVID certificate decoding, that list is iterated with purpose of finding the key used when the QR-code was generated. If the trust-list contains the cert, signature will be verified and a result from that will be given.

To state the obvious: That precious trust list will contain all signature certificates from all countries using the same EU COVID certificate QR-code system. The list of countries is long and includes all 27 EU-countries. Among participating non-EU countries are: Andorra, United Arab Emirates, Swizerland, Georgia, Iceland, Liechtenstein, Morocco, Norway, Singapore, San Marino, Ukraine, Vatican. Looking at the country-list, it is easy to see how EU COVID passport is a triumph of open-source!

On the flipside, getting signing certificates from all those countries requires some effort. A participating country first needs to generate and secure (emphasis: SECURE the private key) required number of ECDSA public/private key -pairs to all organizations/parties doing the COVID certificate issuing. Of those key-pairs COVID certificate signing X.509 certificates can be created and public certificate will be added to the common trust-list.

As an example, in my home country of Finland up until recently there used to be one (1 as in single) X.509 cert for entire nation of 5,6 million people, now there is two. Ultimately, pretty much everybody gets their COVID certs signed from a single cert. This is because in Finland issuing the certs is centralized. On the other hand bigger countries need to have multiple X.509 certs as their government organization may be different and less centralized. A good example is Germany, the most populous countru in Europe and a federal state consisting of 17 constituent states. Having single cert would not make sense in their government.

Moving on. Countries deliver their public certificates into some EU "central repository". It is not known how and where this is, but I'm assuming one has to exist. From this repository the participating countries are allowed the retrieve the trust-list for their verification needs. Subsequently the list is distributed by the country to those organizations and people needing to do COVID certificate verification. Remember "get my hands into the national public certificates"? Not all countries do the distributing very easily nor publicly. Some do, and that's where I get my data from.

Now that there is access of trust-lists, factor in time. That little devil is always messing everything up!
In August, when I first download the Austrian list, the entire list had 150+ valid signing certs. Early December -21 there was 192. Early January -22 there was already 236. Today, the Austrian list contains 281 signing certificates. However, Swedish list has only 267. Argh! I'd really really need access to that EU endpoint for trust lists.

As the Austrian list is larger, here are the per-country statistics of 9th January 2022, expect this to change almost daily:

Any test data provided by participating country is at: https://github.com/eu-digital-green-certificates/dgc-testdata

Things to note from above table:

• Countries have their government arranged with lot of different variations.
• More certificates --> more things to secure --> more things to be worried about.
  • In Germany somebody got access to a cert and issued QR-codes not belonging to actual persons.
• Lot of countries outside EU chose to use this open-source the system.
  • Assume more countries to join. No need for everybody to invent this system.
• Lack of test data
  • In EU, it is easy to make test data mandatory. Outside EU not so much.
  • Way too many countries have not provided any sample certificates.
• Faroe Islands have 50k people, two certs.
  • Okokok. It makes sense to have already issued two. If one cert leaks or gets "burned" for any reason, it is fast to re-issue the COVID certificates by using the other good cert.
• Country stats:
  • Liechtenstein has 40k people, 6 certs! Whooo!
  • Malta and Cabo Verde have 500k people. Maltese need 12 certs for all the signing! In Cabo Verde they have to do with only one.
  • Vatican is the smallest country in the entire World. Single cert needed for those 800+ persons living there.
  • Italy has 60M people, they manage their passes with only 4 certs. Nice!
• In total 59 countries participating this common system. Adoption is wide, system is well designed.

Also note how "EU" COVID passport has more participating countries outside EU.
True testament of open-source, indeed!

Anybody who has written any significant amount of code into a software project will (painfully) eventually learn how the code should have been written in its early days.This phenomenon has happened to all of us and will keep happening as long as any code is being maintained.

Experienced developers know how to fight this code "rot", or debt. Here is a really good diagram on how to maintain code quality.

Credit: Artem Diashkin, 2020, https://medium.com/litslink/solid-software-design-principles-ac5be34a6cd5

So, four things:

1. You must work with you code all the time to improve it. Preferably not the same exact code, but your codebase in general.
2. Without automated tests, you'll be lost. Getting 100% coverage isn't easy and not the actual target even. Have SOME coverage at minimum.
3. Implement sensible design patterns, have the factory factory if its applicable for what you're doing. Avoid over-kill, you don't need a factory for everything as it makes your #2 difficult requiring more #1.
4. S.O.L.I.D.
   • Single Responsibility Principle
   • Open Closed Principle
   • Liskov Substitution Principle
   • Interface Segregation Principle
   • Dependency Inversion Principle

Especially #4 is very difficult. Nevertheless, you need to aim towards it.

As reality bites, what happens if you don't do all of the above. Well ... shit will hit the fan. A really good example of things going wrong is Log4Shell.

Christine Dodrill in her post "Open Source" is Broken or: Why I Don't Write Useful Software Unless You Pay Me explains how huge corporations use work of non--paid volunteers while failing to direct any money towards them. When things go wrong or seriously wrong, everybody will blame those non-paid people for not doing their jobs properly. Note how I write "jobs". It was never their job, it was their hobby, something they do in their spare time.

Whatever code you work on, eventually your end result will look as this XKCD #2347 depicts:

When that tiny piece crumbles, the poor Nebraskan will get the blame. Nobody offered to help before it happened, though. We had a taste of that in 2016, How one programmer broke the internet by deleting a tiny piece of code. Last month it happened again.

Everybody wanted to use Apache Log4j, nobody wanted to pay for it (actually, three users did). What these non-paid volunteers maintaining the code didn't do was have the discipline. Pyramid of clean code wasn't applied, or wasn't properly applied. They didn't bother, entire project was simply something they did on their free time. With lack of discipline, legacy version's and what not, the worst was not applying S.O.L.I.D.'s S. The same piece of code that should write the requested log entry to a log destination also does parsing of the message. That's two responsibilities, not single.

From proof-of-concept Java-code:

log.info("This is what we got:{}", someData);

Whatever the variable someData contains, by Single Responsibility Principle, no parsing must be done and any input, for example ${jndi:ldap://127.0.0.1/a}, must be taken as literal. Because of test code coverage, lack of refactoring, not using design patterns properly and lack of S.O.L.I.D. design all hell broke loose.

Internet burst into flames!

Next time you, as a software architect, designed, developer, whatever plan on adding an implicit functionality to your existing code, make sure everybody who will ever use your new toy knows of this dual-responsbility. Inform all of this implicit functionaly and how it will effect. Don't hide a parser somewhere where there should be no parser, have it a separate operation even if it will break prople's legacy applications. Doing this will keep your days boring and Internet not bursting into flames. The bad thing, obviously, is your notoriety will reamain low as nobody will ever know of your really cool feature.

My advice is:
Have it that way rather than being the underpaid guy whose code broke everything. Really, broke everything.

Since I started working with vacdec, something has been bugging me. I quite couldn't put my finger on it at first. As a reader asked me to decode some unix timestamps from CBOR payload into human-readable format, it hit me. The output needs to be easily understandable!

Now there exists a version with that capability (see: https://github.com/HQJaTu/vacdec for details).

Using Finnish government provided test data, this is the raw CBOR/JSON data:

{
  "1": "FI",
  "4": 1655413199,
  "6": 1623833669,
  "-260": {
    "1": {
      "v": [
        {
          "ci": "URN:UVCI:01:FI:DZYOJVJ6Y8MQKNEI95WBTOEIM#X",
          "co": "FI",
          "dn": 1,
          "dt": "2021-03-05",
          "is": "The Social Insurance Institution of Finland",
          "ma": "ORG-100001417",
          "mp": "EU/1/20/1525",
          "sd": 1,
          "tg": "840539006",
          "vp": "J07BX03"
        }
      ],
      "dob": "1967-02-01",
      "nam": {
        "fn": "Testaaja",
        "gn": "Matti Kari Yrjänä",
        "fnt": "TESTAAJA",
        "gnt": "MATTI<KARI<YRJAENAE"
      },
      "ver": "1.0.0"
    }
  }
}

Exactly the same with improved version for vacdec:

{
  "issuer": "Finland",
  "expiry:": "2022-06-16 20:59:59",
  "issued:": "2021-06-16 08:54:29",
  "Health certificate": {
    "1": {
      "Vaccination": [
        {
          "Unique Certificate Identifier: UVCI": "URN:UVCI:01:FI:DZYOJVJ6Y8MQKNEI95WBTOEIM#X",
          "Country of Vaccination": "Finland",
          "Dose Number": 1,
          "Date of Vaccination": "2021-03-05",
          "Certificate Issuer": "The Social Insurance Institution of Finland",
          "Marketing Authorization Holder / Manufacturer": "Janssen-Cilag International",
          "Medicinal product": "EU/1/20/1525: COVID-19 Vaccine Janssen",
          "Total Series of Doses": 1,
          "Targeted disease or agent": "COVID-19",
          "Vaccine or prophylaxis": "COVID-19 vaccines"
        }
      ],
      "Date of birth": "1967-02-01",
      "Name": {
        "Surname": "Testaaja",
        "Forename": "Matti Kari Yrjänä",
        "ICAO 9303 standardised surname": "TESTAAJA",
        "ICAO 9303 standardised forename": "MATTI<KARI<YRJAENAE"
      },
      "Version": "1.0.0"
    }
  }
}

Much easier on the eye. Raw data can still be displayed, but is not the default. Use switch --output-raw to get original result.

There are comments in my Python-code, but for those wanting to eyeball the specs themselves, go see https://github.com/ehn-dcc-development/ehn-dcc-schema and https://ec.europa.eu/health/sites/default/files/ehealth/docs/digital-green-certificates_v1_en.pdf for exact details of CBOR header and payload fields. The certificate JSON-schema describes all used value-sets.

Note: Especially JSON-schema is a living thing. If you read this in the future, something might have changed.
Please, drop me a line when that happens.

In my previous post, I mentioned writing code into Databricks.

If you want to take a peek into my work, the Dockerfiles are at https://github.com/HQJaTu/containers/tree/centos8. My hope, obviously, is for Databricks to approve my PR and those CentOS images would be part of the actual source code bundle.

If you want to run my stuff, they're publicly available at https://hub.docker.com/r/kingjatu/databricks/tags.

To take my Python-container for a spin, pull it with:
docker pull kingjatu/databricks:python

And run with:
docker run --entrypoint bash

Databricks container really doesn't have an ENTRYPOINT in them. This atypical configuration is because Databricks runtime takes care of setting everything up and running the commands in the container.

As always, any feedback is appreciated.

Yeah, a mouthful. Running CentOS 8 Linux, in Java (JRE) a connection to MySQL / MariaDB there seems to be trouble. I think this is a transient issue and eventually it will resolve itself. Right now the issue is real.

Here is the long story.

I was tinkering with Databricks. The nodes for my bricks were on CentOS 8 and I was going to a MariaDB in AWS RDS. with MySQL Connector/J. As you've figured out, it didn't work! Following errors were in exception backtrace:

com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

javax.net.ssl.SSLHandshakeException: No appropriate protocol (protocol is disabled or cipher suites are inappropriate)

Weird.

Going to the database with a simple CLI-command of (test run on OpenSUSE):

$ mysql -h db-instance-here.rds.amazonaws.com -P 3306 \
  -u USER-HERE -p \
  --ssl-ca=/var/lib/ca-certificates/ca-bundle.pem \
  --ssl-verify-server-cert

... works ok.
Note: This RDS-instance enforces encrypted connection (see AWS docs for details).
Note 2: Term used by AWS is SSL. However, SSL was deprecated decades ago and the protocol used is TLS.

Taking a look on the wire:

Two details popped out instantly: TLSv1 and TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA cipher. Both deprecated. Both deemed highly insecure and potentially leaking your private information.

Why would anybody using those? Don't MySQL/MariaDB/AWS -people remove insecure stuff from their software? What! Why!

Troubleshooting started. First I found SSLHandShakeException No Appropriate Protocol on Stackoverflow. It contains a hint about JVM security settings. Then MySQL documentation 6.3.2 Encrypted Connection TLS Protocols and Ciphers, where they explicitly state "As of MySQL 5.7.35, the TLSv1 and TLSv1.1 connection protocols are deprecated and support for them is subject to removal in a future MySQL version." Well, fair enough, but the bad stuff was still there in AWS RDS. I even found Changes in MySQL 5.7.35 (2021-07-20, General Availability) which clearly states TLSv1 and TLSv1.1 removal to be quite soon.

No amount of tinkering with jdk.tls.disabledAlgorithms in file /etc/java/*/security/java.security helped. I even created a simple Java-tester to make my debugging easier:

import java.sql.*;

// Code from: https://www.javatpoint.com/example-to-connect-to-the-mysql-database
// 1) Compile: javac mysql-connect-test.java
// 2) Run: CLASSPATH=.:./mysql-connector-java-8.0.27.jar java MysqlCon
class MysqlCon {
  public static void main(String args[]) {
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
      Connection con = DriverManager.getConnection("jdbc:mysql://db.amazonaws.com:3306/db", "user", "password");
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery("select * from emp");
      while (rs.next())
        System.out.println(rs.getInt(1) + " " + rs.getString(2) + " " + rs.getString(3));
      con.close();
    } catch (Exception e) {
      System.out.println(e);
      e.printStackTrace(System.out);
    }
  }
}

Hours passed by, but no avail. Then I found command update-crypto-policies. RedHat documentation Chapter 8. Security, 8.1. Changes in core cryptographic components, 8.1.5. TLS 1.0 and TLS 1.1 are deprecated contains mention of command:
update-crypto-policies --set LEGACY

As it does the trick, I followed up on it. In CentOS / RedHat / Fedora there is /etc/crypto-policies/back-ends/java.config. A symlink pointing to file containing:

jdk.tls.ephemeralDHKeySize=2048
jdk.certpath.disabledAlgorithms=MD2, MD5, DSA, RSA keySize < 2048
jdk.tls.disabledAlgorithms=DH keySize < 2048, TLSv1.1, TLSv1, SSLv3, SSLv2, DHE_DSS, RSA_EXPORT, DHE_DSS_EXPORT, DHE_RSA_EXPORT, DH_DSS_EXPORT, DH_RSA_EXPORT, DH_anon, ECDH_anon, DH_RSA, DH_DSS, ECDH, 3DES_EDE_CBC, DES_CBC, RC4_40, RC4_128, DES40_CBC, RC2, HmacMD5
jdk.tls.legacyAlgorithms=

That's the culprit! It turns out any changes in java.security -file won't have any effect as the policy is loaded later. Running the policy change and set it into legacy-mode has the desired effect. However, running ENTIRE system with such a bad security policy is bad. I only want to connect to RDS, why cannot I lower the security on that only? Well, that's not how Java works.

Entire troubleshooting session was way too much work. People! Get the hint already, no insecure protocols!

If you live in EU, you most definitely have heard of COVID Passport.

Practically speaking, it is a PDF-file A4-sized when printed and can be folded into A6-pocket size. In Finland a sample would look like this:

What's eye-catching is the QR-code in the top right corner. As I'm into all kinds of nerdy stuff, I took a peek what's in it.

After reading some specs and brochures (like https://www.kanta.fi/en/covid-19-certificate) I started to tear this apart and deduced following:

• An A4 or the QR-code in it can be easily copied / duplicated
• Payload can be easily forged
• There is a claim: "The certificate has a code for verifying the authenticity"

My only question was: How is this sane! Why do they think this mechanism they designed makes sense?

QR-code

Data in QR-code is wrapped multiple times:

This CBOR Object Signing and Encryption (COSE) described in RFC 8152 was a new one for me.

Payload does contain very little of your personal information, but enough to be compared with your ID-card or passport. Also there is your vaccination, test and possible recovery from COVID statuses. Data structure can contain 1, 2 or 3 of those depending if you have been vaccinated or tested, or if you have recovered from the illness.

Python code

It's always about the details. So, I forked an existing git-repo and did some of my own tinkering. Results are at: https://github.com/HQJaTu/vacdec/tree/certificate-fetch

Original code by Mr. Hanno Böck could read QR-code and do some un-wrapping for it to reveal the payload. As I'm always interested in X.509, digital signatures and cryptography, I improved his code by incorporating the digital signature verification into it.

As CBOR/COSE was new for me, it took a while to get the thing working. In reality the most difficult part was to get my hands into the national public certificates to do some ECDSA signature verifying. It's really bizarre how that material is not made easily available.

Links:

• Swagger of DGC-gateway: https://eu-digital-green-certificates.github.io/dgc-gateway/
  • This is just for display-purposes, this demo isn't connected to any of the national back-ends
• Sample data for testing verification: https://github.com/eu-digital-green-certificates/dgc-testdata
  • All participating nations included
  • QR-codes and raw-text ready-to-be-base45-decoded
• Payload JSON_schema: https://github.com/ehn-dcc-development/ehn-dcc-schema/
  • Just to be clear, the payload is not JSON
  • However, CBOR is effectively binary JSON
• List of all production signature certificates: https://dgcg.covidbevis.se/tp/
  • Austrian back-end trust-list in JSON/CBOR: https://greencheck.gv.at/api/masterdata
  • Swedish back-end trust-list in CBOR: https://dgcg.covidbevis.se/tp/trust-list
  • The idea is for all national back-ends to contain everybody's signing certificate

Wallet

Mr. Philipp Trenz from Germany wrote a website to insert your QR-code into your Apple Wallet. Source is at https://github.com/philipptrenz/covidpass and the actual thing is at https://covidpass.eu/

Beautiful! Works perfectly.

Especially in Finland the government is having a vacation. After that they'll think about starting to make plans what to do next. Now thanks to Mr. Trenz every iOS user can have the COVID Certificate in their phones without government invovement.

Finally

Answers:

• Yes, duplication is possible, but not feasible in volume. You can get your hands into somebody else's certificate and can present a proof of vaccination, but verification will display the original name, not yours.
• Yes, there is even source code for creating the QR-code, so it's very easy to forge.
• Yes, the payload has a very strong elliptic curve signature in it. Any forged payloads won't verify.

Ultimately I was surprised how well designed the entire stack is. It's always nice to see my tax-money put into good use. I have nothing negative to say about the architecture or technologies used.

Bonus:
At very end of my project, I bumped into Mr. Mathias Panzenböck's code https://github.com/panzi/verify-ehc/. He has an excellent implementation of signature handling, much better than mine. Go check that out too.

There is a tool, I've been running for a few years now. In 2018 I published it into GitHub and wrote a blog post about it. Later, I wrote it to support Azure DNS.

As this code is something I do run in my production system(s), I do keep it maintained and working. Latest changes include:

• Proper logging wia logging-module
• Proper setup via pip install .
• Library done as proper Python-package
• Python 3.9 support
• Rackspace Cloud DNS library via Pyrax maintained:
  • Supporting Python 3.7+
  • Keyword argument async renamed into async_call
• Improved documentation a lot!
  • Setup docs improved
  • systemd service docs improved

This long-running project of mine starts to feel like a real thing. I'm planning to publish it into PyPI later.

Enjoy!

Python, one of the most popular programming languages today. Every single even remotely useful language depends on extensions, libraries and stuff already written by somebody else you'll be needing to get your code to do its thing.

In Python these external dependencies are installed with command pip. Some of them are installed as eggs, some as wheels. About the latter, read What are wheels? for more information.

Then there is the third kind. The kind having cPython in them needing a C-compiler on your machine to build and subsequent install when the binaries are done. What if your machine doesn't have a C-compiler installed? Yup. Your pip install will fail. This story is about that.

Duh, it failed

I was tinkering some Python-code and with some googling found a suitable library I wanted to take for a spin. As I had a newly re-installed Windows 10, pip install failed on a dependency of the library I wanted:

building 'package.name.here' extension
    error: Microsoft Visual C++ 14.0 is required. Get it with "Build Tools for Visual Studio": https://visualstudio.microsoft.com/downloads/

Yes, nasty error that. I recall seeing this before, but how did I solve it the last time? No recollection, nothing.

The link mentioned in the error message is obsoleted. There is absolutely nothing useful available by visiting it. I'm guessing back in the days, there used to be. Today, not so much.

What others have done to navigate around this

Jacky Tsang @ Medium: Day060 — fix “error: Microsoft Visual C++ 14.0 is required.” Nope. Fail.

Stackoverflow:

• Microsoft Visual C++ 14.0 is required. Get it with “Microsoft Visual C++ Build Tools”: http://landinghub.visualstudio.com/visual-cpp-build-tools Nope. Fail.

• How to install Visual C++ Build tools? Nope. Fail.

• Microsoft Visual C++ 14.0 is required (Unable to find vcvarsall.bat) Yesh!

This problem is spread far and wide! Lot of people suffering from the same. Lot of misleading answers spreading for multiple years back.

Visual Studio 2019 Build Tools

Page is at https://visualstudio.microsoft.com/visual-cpp-build-tools/
(The link is in the vast river of comments in the last Stackoverflow-question)

Click Download Build Tools, get your 2019 installer and ...

BANG! Nope, it won't work. Failure will indicate a missing include-file:

c:\program files\python38\include\pyconfig.h(205): fatal error C1083: Cannot open include file: 'basetsd.h': No such file or directory

My solution with 2017 tools

Download link as given to you by Microsoft's website is https://visualstudio.microsoft.com/thank-you-downloading-visual-studio/?sku=BuildTools&rel=16 (2019). As it happens, 2019 will contain 2015 build tools, we can assume 2017 to do the same.

If you hand edit to contain release 15 (2017): https://visualstudio.microsoft.com/thank-you-downloading-visual-studio/?sku=BuildTools&rel=15

Running the 2017 installer:

Yaaash! It works: pip will end with a Successfully installed -message.

Finally

Tricky subject, that. Looks like all the years have done so many changes nobody is able to keep a good track of all. What a mess! Uh.

On a non-AD environment, this is what your average Windows 10 Remote Desktop client will emit on a connection:

For those who are AD-domain admins, they may have seen this rarely. If an AD has Certification Authority installed, it is easy and typical to deploy certs from it to workstations and never see the above message. The Net is full of instructions like Replace RDP Default Self Sign Certificate.

Problem that needs to be solved

For anybody like me, not running an AD-domain, simply having couple of Windows 10 -boxes with occasional need to RDP into them, that popup becomes familiar.

Note: I'll NEVER EVER click on Don't ask me again -prompts. I need to know. I want to know. Suppressing such information is harmful. Getting to know and working on the problem is The WayⓇ.

Gathering information about solution

If this was easy, somebody had created simple instructions for updating RDP-certificates years ago. Decades even. But no. No proper and reasonable easy solution exists. Searching The Net far & wide results only in bits and pieces, but no real tangible turn-key(ish) solution.

While in quest for information, given existence of The Net, I find other people asking the same question. A good example is: How to provide a verified server certificate for Remote Desktop (RDP) connections to Windows 10.

As presented in the above StackExchange answer, the solution is a simple one (I think not!!). These five steps need to be done to complete the update:

1. Purchase a genuine verified SSL-certificate
   • Note: It's a TLS-certificate! The mentioned protocol has been deprecated for many years. Even TLS 1 and TLS 1.1 have been deprecated. So it's a X.509 TLS-certificate.
   • Note 2: Ever heard of Let's Encrypt? ZeroSSL? Buypass? (and many others) They're giving away perfectly valid and trusted TLS-certificates for anybody who shows up and can prove they have control over a domain. No need to spend money on that.
2. Wrap the precious certificate you obtained in step 1) into a PKCS#12-file. A .pfx as Windows imports it.
   • Note: Oh, that's easy! I think PKCS#12 is the favorite file format of every Regular Joe computer user. Not!
3. Install the PKCS#12 from step 2) into Windows computer account and make sure user NETWORK SERVICE has access to it.
   • Note: Aow come on! Steps 1) and 2) were tedious and complex, but this is wayyyyyy too hard to even begin to comprehend! Actually doing it is beyond most users.
4. Open a Registry Editor and add the SHA-1 fingerprint of the certificate into HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Terminal Server\WinStations\RDP-Tcp\ into a binary value called SSLCertificateSHA1Hash.
   • Note: Oh really! Nobody knows what's a SHA-1 fingerprint nor how to extract that from a certificate in a format suitable to a registry binary value!
5. Reboot the Windows!
   • Note: As all Windows-operations, this requires a reboot.

Mission accomplished! Now the annoying message is gone. Do you want to guess what'll happen after 90 days passes? That's the allotted lifespan of a Let's Encrypt -certificate. Yup. You'll be doing the all of the above again. Every. Single. Painstaking. Step.

Problems needing to be solved

Let's break this down. As the phrase goes, an elephant is a mighty big creature and eating one is a big task. It needs to be done one bit at a time.

0: Which cert is being used currently?

If you simply want to get the SHA-1 hash of the currently installed RDP-certificate, a simple (or maybe not?) command of:

wmic /namespace:"\\root\cimv2\TerminalServices" PATH "Win32_TSGeneralSetting" get "SSLCertificateSHA1Hash"

... will do the trick. No admin-permissions needed or anything fancy.

To state the obvious problem: you'll be presented a hex-string but you have zero idea to where it points to and what to do with this information.

Hint:
You can to browse Windows Certificate Mchine Store. System certificates are not stored in your personal Certificate Store, so carefully point to a correct container. By default certificates are listed by subject, not SHA-1 hash. Luckily the self-signed RDP-cert is located in a folder "Remote Desktop" narrowing down the set.

To state the second obvious problem: WMI is a tricky beast. Poking around it from CLI isn't easy.

1: The certificate

Ok. With that you're on your own. If you cannot figure how Let's Encrypt works, doing this may not be your thing.

2: PKCS#12 wrapping

In most scenarios, a certificate is typically delivered in a PEM-formatted file or set of two files (one for public and second for private keys). PEM-format is native in *nix environment and all of tooling there can handle the data with ease. Converting the PEM-data into an interim (Microsoft created) format for Microsoft-world can be done, but is bit tricky in a *nix. Usage for this PKCS#12-formatted data is ephemeral, the certificate will stay in the file for only short while before being imported to Windows and there is no need for it anymore. A cert can be re-packaged if needed as long as the original PEM-formatted files exist. Also, the certificate data can be exported from Windows back to a file, if needed.

As PEM-format is native in *nix for certs, it is completely unsupported in Windows. A simple operation of "here is the PEM-file, import it to Windows" is literally impossible to accomplish! There is a reason why instructions have a rather complex spell with openssl-command to get the job done.

2.1: What others have done

When talking about PowerShell-scripting and PEM-format, I'll definitely have to credit people of PKISolutions. They publish a very nice library of PSPKI (source code is at https://github.com/PKISolutions/PSPKI) with PEM-import capability. As Windows Certificate Store is a complex beast, that code doesn't directly work as I'd need it to be for importing into Machine Store. Given existence of source code, the logic they wrote can be lifted, modified and re-used to do what is needed for RDP-cert installation process.

Among PKISolutions' excellent work is blog post by Vadims Podāns, Accessing and using certificate private keys in .NET Framework/.NET Core. There he explains in detail dark/bright/weird ages about how Microsoft's libraries have approached the subject of PKI and how thing have evolved from undefined to well-behaving to current situation where everything is... well... weird.

Why I mention this is imperative for practical approach. PSPKI-library works perfectly in PowerShell 5.x, which is built on Microsoft .NET Framework 4.5. That particular framework version is bit old, and given its age, it falls into bright bracket of doing things.

However, not living in past, the relevant version of PowerShell is PowerShell Core. At the time of writing the LTS (or Long-Term-Support) version is 7.0. A version of 7.1 is in preview and version 6 is still actively used. Those versions run obviously on modern .Net Core, an open-source version of DotNet running in Windows, Linux and macOS. In transition of Microsoft .Net into open-source .Net Core, most operating system -dependant details have changed while bumping things from closed-source-Windows-only thingie. This most definitely include implementation and interface details of Public Key Infrastructure.

In short: PSPKI doesn't work anymore! It did in "bright ages" but not anymore in current "weird ages".

2.2: What I need to get done

Sticking with an obsoleted PowerShell-version is a no-go. I need to get stuff working with something I actually want to run.

In detail, this involves figuring out how .Net Core's System.Security.Cryptography.CngKey can import an existing key into Machine Store as an exportable one. That was bit tricky even in .Net Framework's System.Security.Cryptography.RSACryptoServiceProvider. Also note, that when I talk about PKI, I most definitely mean RSA and other practical algorithms like ECDSA, which is fully supported in Cryptography Next Generation (CNG). The biggest problem with CNG is, there is lack of usable documentation and practical examples.

Having elliptic curve support is important. RSA is absolutely not obsoleted and I don't see it being so in near future. It's the classic: having options. This is something I already addressed in a blog post this spring.

Most people are using only RSA and can get their job done using old libraries. I ran out of luck as I needed to do something complex. Using new libraries was the only way of getting forward. That meant lots of trial and error. Something R&D is about.

3: Import the PKCS#12-packaged certificate into a proper certificate store of a Windows-machine

When the very difficult part is done and a PKCS#12-file exists and contains a valid certificate and the private key of it, importing the information is surprisingly easy to accomplish with code.

On the other hand, this one is surprisingly complex to accomplish manually. Good thing I wasn't aiming for that.

4: Inform RDP-services which certificate to use

Getting a SHA-1 hash of a X.509 certificate is trivial. Stamping a well-known value into registry is easy. Having correct permissions set was nearly impossible, but ultimately it was doable.

5: Make sure RDP-services will use the certificate

For this, there are number of ways to do. Many of them will involve reboot or restarting the service with a PowerShell-spell of:

Restart-Service -DisplayName "Remote Desktop Services" -Force

Surprisingly, on a closer look there is a way to accomplish this steop without rebooting anything. It's just not well known nor well documented, but Windows Management Instrumentation (or wmic) can do that too! Assuming the SHA-1 thumbprint of the certificate was in variable $certThumbprint, running this single-line command will do the trick:

wmic /namespace:"\\root\cimv2\TerminalServices" PATH "Win32_TSGeneralSetting" Set "SSLCertificateSHA1Hash=$certThumbprint"

It will update registry to contain appropriate SHA-1 hash, confirm the access permissions and inform RDP-service about the change. All of which won't require a reboot nor an actual restart of the service. Imagine doing the cert update via RDP-session and restarting the service. Yup. You will get disconnected. Running this WMI-command won't kick you out. Nice!

Solution

Set of tools I wrote is published in GitHub: https://github.com/HQJaTu/RDP-cert-tools

As usual, this is something I'm using for my own systems, so I'll maintain the code and make sure it keeps working in this rapidly evolving world of ours.

Example run

On the target Windows 10 machine, this is how updating my cert would work (as an user with Administrator permissions):

PS C:\> .\update-RDP-cert.ps1 `
  -certPath 'wildcard.example.com.cer' `
  -keyPath 'wildcard.example.com.key'

Output will be:

Loaded certificate with thumbprint 1234567890833251DCCF992ACBD4E63929ABCDEF
Installing certificate 'CN=*.example.com' to Windows Certificate Store
All ok. RDP setup done.

That's it. You're done!

Example run with SSH

As I mentioned earlier, I'm using Let's Encrypt. There is a blog post about how I approach getting the certificates in my Linux wth Acme.sh. There is an obvious gap with getting certs from LE with a Linux and using the cert in Windows 10. Files in question need to be first transferred and then they can be used.

Realistic example command I'd run to first transfer the newly issued LE-cert from my Linux box to be used as RDP-cert would be:

PS C:\> .\get-RDP-cert.ps1 `
  -serverHost server.example.com `
  -serverUser joetheuser `
  -serverAuthPrivateKeyPath id_ecdsa-sha2-nistp521 `
  -remotePrivateKeyPath 'certs/*.example.com/*.example.com.key' `
  -remoteCertificatePath 'certs/*.example.com/*.example.com.cer'

The obvious benefit is a simple single command to get and install an RDP-certificate from Linux to Windows. All of the complexity will be taken out. My script will even clean the temporary files to not leave any private key files floating around.

Finally

Enjoy!

Admins/users: If you enjoy this tool, let me know. Drop me a comment.

Developers: If you love my CNG-import code, feel free to use it in your application. The more people know how it works, the better.

When I was a kid growing up with computers, there was one (1) definite medium for a Finnish nerd to read. MikroBitti.

Wikipedia describes MikroBitti as:

a Finnish computer magazine published in Helsinki, Finland

For any youngster it will be exteremely difficult to comprehend an era of computing without Internet. It did exist, I did live (and almost survive) through it.

Among the scarce resources was code. Magazines printed on paper published source code for platforms of that era. Regular people copied the code by typing (mostly incorrectly) in an attempt to obtain more working software. As we know, a single mis-typed character in a 10000 character code will crash the entire thing, at minimum produce unpredictable results. Then came modems and era of BBSes. Before that happened, I was sure to learn everything I could from those magazine-published codes. I did that even for platforms I didn't own nor have access to.

August 1987:

Cover and contents.

Pages 51 & 52:

A trivial C-64 BASIC application by an unknown software engineer wanna-be producing a calendar for a given year. It even could print the calendar if you owned a printer.

Background info:
The code was written with a C-128 in C-64 mode. I was aiming for a larger audience C-64 had at the time. I don't remember the fee I received from this, but in my imagination it must have been something around 300 FIM. By using the Value of money converter @ stat.fi, 300 FIM in 1987 would equal to ~95 € in 2019. At the time, the low amount didn't matter! That was the first ever monetary compensation I received for doing something I was doing anyway. For all day, every day.

The brief intro for the calendar app was cut half by editor. What remains is a brief Finnish introduction about the purpose of the app and for other wanna-be software engineers a description what the variables in the code do.

Enjoy! If you find bugs, please report.