Stuff I'm Up To

Technical Ramblings

VCSA root Locked Out! — June 7, 2019

VCSA root Locked Out!

This gave me cause for tears today. The VCSA (vCenter Server Appliance) management Web UI (https://vcsa:5480) decided not to let me in as root. I’m guessing I spannered the password a few too many times.

It’s a very good job that at some point in the past I put my public key onto the system so I could use my plain old no password required private key to logon to the system using ssh!

SSH Logon with Private Key

Now I’m logged onto the console how do I go about getting access back to the Web UI? I discovered that the VCSA system uses pam_tally2 to lockout sessions. What I needed to do was reset the root account:

# pam_tally2 --user=root
 Login           Failures Latest failure     From
 root               10    06/07/19 14:12:11  unknown
# pam_tally2 --user=root --reset
 Login           Failures Latest failure     From
 root               10    06/07/19 14:12:11  unknown
# pam_tally2 --user=root
 Login           Failures Latest failure     From

Now I can logon to the Web UI!

The lesson to learn here is to install your public key onto your precious Linux boxes!

Advertisements
VS Code Extensions — May 31, 2019

VS Code Extensions

I’m an Atom fan and have used it on Linux for ages. I probably still will, but our other developers tend to use VS Code because of their use of MS Windows. I thought I’d revisit VS Code and see if I can use it effectively like Atom.

First thing I needed to make sure of was that some of the Atom features I use are available in VS Code. Standard stuff like dark colour theme, ESLint, syntax highlighting, auto-completions and Emmet.

One pleasant surprise was that VS Code has Emmet built right in! The usage is a little different, keyboard shortcuts etc. but it’s native to VS Code so that’s pretty good.

Other Extensions that I’ve added to support the languages I use are:

Vetur – https://vuejs.github.io/vetur/

ESLint – https://github.com/Microsoft/vscode-eslint

Toggle Excluded Files – https://marketplace.visualstudio.com/items?itemName=eamodio.toggle-excluded-files

Bracket Pair Colorizer – https://marketplace.visualstudio.com/items?itemName=CoenraadS.bracket-pair-colorizer

phpcs – https://github.com/ikappas/vscode-phpcs requires PHP CodeSniffer

PHP DocBlocker – https://github.com/neild3r/vscode-php-docblocker

FiraCode font https://github.com/tonsky/FiraCode

rest-client https://marketplace.visualstudio.com/items?itemName=humao.rest-client

Settings Sync https://marketplace.visualstudio.com/items?itemName=Shan.code-settings-sync

Linux and MS SQL ODBC Authentication — May 16, 2019

Linux and MS SQL ODBC Authentication

My recent troubles spawned from connecting my Laravel application to a Microsoft SQL Server database. My username and password are correct but the connection fails.

In the storage/logs/laravel.log file I found the following:

[2019-05-16 08:22:46] work.ERROR: SQLSTATE[HY000]: [Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: No Kerberos credentials available (default cache: FILE:/tmp/krb5cc_1000) (SQL: select * from holidays where holiday_date > '2019-05-16 08:22:46') {"exception":"[object] (Illuminate\Database\QueryException(code: HY000): SQLSTATE[HY000]: [Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: No Kerberos credentials available (default cache: FILE:/tmp/krb5cc_1000) (SQL: select * from holidays where holiday_date > '2019-05-16 08:22:46') at /PATH/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664, Doctrine\DBAL\Driver\PDOException(code: HY000): SQLSTATE[HY000]: [Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: No Kerberos credentials available (default cache: FILE:/tmp/krb5cc_1000) at /PATH/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:47, PDOException(code: HY000): SQLSTATE[HY000]: [Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: No Kerberos credentials available (default cache: FILE:/tmp/krb5cc_1000) at /PATH/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:43)

I haven’t setup any Kerberos on my Linux side to authenticate and it would appear that my MS SQL wants me to use that. So instead of setting up Kerberos I opted for getting the ODBC connection NOT to use Kerberos.

To do this for my specific user account on my development system I created an ~/.odbc.ini file and added details to disable Kerberos:

.odbc.ini

[my_database]
 Driver = ODBC Driver 17 for SQL Server
 Server = myserver.domain.local,1433
 Database = my_database
# If NOT using Kerberos authentication:
 Trusted_Connection = No
 ServerSPN = MSSQLSvc/myserver.domain.local:my_database
# If using SSL encryption:
 Encryption = Yes
# If using SSL and not importing the server certificate into your certificate store:
 TrustServerCertificate = Yes

To ensure that Laravel makes use of this config the only way I could do it was to NOT use the eloquent models and had to use PHP PDO directly.

// We need to use a direct PDO here because of the
// Trusted Connection=No / SSPI required in .odbc.ini
$pdo = new \PDO('sqlsrv:Server='.env('DB_HOST'),
  env('DB_USER'), env('DB_PASSWORD'));

// Create the number of token parameters
$params = implode(',', array_fill(0, count($tokens), '?'));
$sql = "select data from my_table where something in ($params);";
$stmt = $pdo->prepare($sql);
$stmt->execute($tokens);

$rows = $stmt->fetchAll(\PDO::FETCH_ASSOC); // Associated Array only

MSSQL ACCEPT_EULA — May 1, 2019

MSSQL ACCEPT_EULA

My automated apt updates failed to update the Microsoft SQL components because they don’t accept the terms and condition in the EULA.

To resolve this I added the following line in /etc/environment that allows the EULA to be accepted by default.

ACCEPT_EULA=Y

MySQL Repository Key Expired – 8C718D3B5072E1F5 —
Laravel Echo and Redis — March 18, 2019

Laravel Echo and Redis

The corporate app just got made a bit smarter.

In order to alert users that something has happened that they should be aware of we’ve added real time notifications. We could have polled a table for notifications, but a far smarter development is to use real time communications with socket.io.

Laravel already has these capabilities, but needs to leverage a couple of other services outside of your web server. Notably, Laravel Echo and a message broker such as Pusher or Redis. We chose Redis.

Continue reading
PHPUnit Code Coverage — March 5, 2019

PHPUnit Code Coverage

The more development time we spend on the corporate Laravel app the more mature the code becomes and the more our development practices evolve.

One of the introductions was to ensure we carried out unit testing on our modules and classes to ensure we don’t break any existing functionality by introducing new features.

We started using PHPUnit to carry out the testing for our Laravel/PHP API’s. Now when we run our tests can we really be sure we haven’t broken anything? All we’re really doing is proving that we get consistent results to our tests. What we aren’t sure of is if the tests we have built are sufficient to cover all eventualities handled by our code eg. we know the test works when we pass in valid parameters, but did we write a test that passed in bad parameters, or test that we get a failure when we should?

This is where PHPUnit’s code coverage plugin comes into it.

Continue reading
PostGIS Query Optimisation — March 1, 2019

PostGIS Query Optimisation

I started to do some work on how efficient the spacial SQL queries I was using are. That meant looking at the indexing that is used on the geometry column and understanding the functions I used a bit more.

I made a simple change from using ST_Contains to ST_Within and made a tenfold increase in the performance of my query. Similarly with a change from ST_Distance to ST_DWithin.

Now if only I read the documentation more!

Prior to 1.3, ST_Expand was commonly used in conjunction with && and ST_Distance to achieve the same effect and in pre-1.3.4 this function was basically short-hand for that construct. From 1.3.4, ST_DWithin uses a more short-circuit distance function which should make it more efficient than prior versions for larger buffer regions.

https://postgis.net/docs/ST_DWithin.html

Apt Version Pinning — February 7, 2019

Apt Version Pinning

Today after running some apt upgrades my Laravel development environment failed to compile because of a newer version of nodejs than I currently require.

Module build failed: ModuleBuildError: Module build failed: Error: Missing binding /home/paulb/itsm/node_modules/node-sass/vendor/linux-x64-64/binding.node
Node Sass could not find a binding for your current environment: Linux 64-bit with Node.js 10.x

Found bindings for the following environments:
  - Linux 64-bit with Node.js 8.x

This usually happens because your environment has changed since running `npm install`.
Run `npm rebuild node-sass` to download the binding for your current environment.
Continue reading
Gitahead — February 1, 2019
apt-get Hash Sum Mismatch #2 — January 16, 2019

apt-get Hash Sum Mismatch #2

I’m still not sure why I’m getting this problem occur again. But when running apt-get upgrade the upgrades fail with a message like this:

Get:8 http://security.debian.org stretch/updates/main amd64 libudev1 amd64 232-25+deb9u8 [125 kB]
Err:8 http://security.debian.org stretch/updates/main amd64 libudev1 amd64 232-25+deb9u8
Hash Sum mismatch
Hashes of expected file:
SHA256:189bfac6bfeda64bc16c74614bf524b2c431e7b6c4e3a4f786b927b84afdc889
SHA1:6590379bbc85f8d90c05a1b32cd27dac49431b7a [weak]
MD5Sum:40ace91d2e4c633f89d1571b3022dcdd [weak]
Filesize:125364 [weak]
Hashes of received file:
SHA256:7e4f1f0e1cbcb164ddf5fd1a6d22641d91fff812220f28654a1a007749be6bac
SHA1:7c501c7b49f4fe93d78309f5b5c635f1db487989 [weak]
MD5Sum:9b8faa999b5db9581ef0df62f697e4df [weak]
Filesize:877368 [weak]
Last modification reported: Sat, 08 Dec 2018 08:05:18 +0000

To resolve it I resorted to bypassing any caching and use apt to pull the update and upgrade:

$ sudo apt -o Acquire::https::No-Cache=True -o Acquire::http::No-Cache=True update
$ sudo apt -o Acquire::https::No-Cache=True -o Acquire::http::No-Cache=True upgrade
Public Key from Private Key — January 3, 2019

Public Key from Private Key

I fall over this every so often. I have the private key file but would either have to trawl servers for authorized_keys files to get the public password or remember how to obtain the public key from the private key.

Time to document it here so I don’t have to hunt for it with Google again.

For an RSA PEM format public key

$ openssl rsa -in private.key -pubout

-----BEGIN PUBLIC KEY-----
MIIBIDA ...
-----END PUBLIC KEY-----

For an SSH putty friendly version

$ ssh-keygen -y -f private.key

ssh-rsa AAAAB3NzaC1yc2EAAAABJQAAAQE ...