Stuff I'm Up To

Technical Ramblings

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

Advertisements
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 ...
php 7.0 on Debian Buster — January 2, 2019

php 7.0 on Debian Buster

Actually this is more about any version of php (5.6, 7.0, 7.1, 7.2) on buster. Php source has taken on a bit of a split and the standard repositories only deal with the one supported version for the current release of Debian you are using.

This means that on Debian 9 (buster/sid) the only version available from the Debian repository is php 7.3.

Our current production systems are Debian 9 stretch and only support php 7.0 and therefore only Laravel 5.5. In order to bring my development platform down to php 7.0 I must use a non-standard repository.

Ondřej Surý has been packaging php for Debian and Ubuntu and distributing them. To get them you need to add his key and repository into your aptitude:

$ wget -q https://packages.sury.org/php/apt.gpg -O- | sudo apt-key add -
$ echo "deb https://packages.sury.org/php/ stretch main" | sudo tee /etc/apt/sources.list.d/php.list

Now you can add in whatever version of php you’d like even 5.6. eg.

$ sudo apt-get install php7.0-fpm php7.0-mbstring php7.0-zip php7.0-mysql php7.0-sqlite3 php7.0-dev php-pear

If you already had 7.3 installed nothing will have changed yet and when you type php from the command line you’ll see it still runs version 7.3.

$ php -v

PHP 7.3.0-2+0~20181217092659.24+stretch~1.gbp54e52f (cli) (built: Dec 17 2018 09:26:59) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.3.0-dev, Copyright (c) 1998-2018 Zend Technologies
with Zend OPcache v7.3.0-2+0~20181217092659.24+stretch~1.gbp54e52f, Copyright (c) 1999-2018, by Zend Technologies

To switch back to 7.0 use the following and you’ll see your php go back to 7.0. Switch back in the same way, but replace 7.0 with 7.3.

$ sudo update-alternatives --set php /usr/bin/php7.0

update-alternatives: using /usr/bin/php7.0 to provide /usr/bin/php (php) in manual mode
$ php -v

PHP 7.0.33-1+0~20181208203126.8+stretch~1.gbp2ff763 (cli) (built: Dec 8 2018 20:31:26) ( NTS )
Copyright (c) 1997-2017 The PHP Group
Zend Engine v3.0.0, Copyright (c) 1998-2017 Zend Technologies
with Zend OPcache v7.0.33-1+0~20181208203126.8+stretch~1.gbp2ff763, Copyright (c) 1999-2017, by Zend Technologies

VMWare Horizon Load Balancing — November 21, 2018

VMWare Horizon Load Balancing

We’re in the process of installing a new Horizon 7 infrastructure  and as part of the process the vendor added load balancers all over the place. I asked with question of why not use an Open Source solution for that?

My go to web server, proxy, load balancer is Nginx and as we already have a HA pair setup I thought we’d try to use that – even if it meant putting in a new one dedicated to the task in the longer term.


As the plan is to use a load balancer in front of the connection servers and the only tunnelling that will take place will be for external systems, our requirement will be to LB the https traffic (TCP 443) for the authentication. The PCoIP/Blast traffic will be directed straight to the ESX Host/client.

The previous document on load balancing with Nginx means I only need to add in the config needed for horizon. By using the same syncing of config it immediately becomes available on the secondary load balancer.

I created a new config file /etc/nginx/sites-available/horizon and then as standard, symbolic link it to sites-enabled to make it live.

upstream connectionservers {
ip_hash;
server 192.168.0.236:443;
server 192.168.0.237:443;
}
server {
listen 443 ssl;
server_name horizon.domain.tld;
location ~ / {
proxy_pass https://connectionservers;
}
}

This adds our two connection servers into an upstream group called connectionservers which I then point the  proxy_pass  directive to.

The ip_hash directive ensures we have session stickiness based on the clients IP address. When a client connects they’ll stay directed to the connection server they were given until and unless the connection server becomes unavailable.

nginx.conf

Within the nginx.conf ensure you have the reverse proxy options set in the http {} section:

enable reverse proxy
proxy_redirect off;
proxy_set_header Host $http_host;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwared-For $proxy_add_x_forwarded_for;
client_max_body_size 10m;
client_body_buffer_size 128k;
client_header_buffer_size 64k;
proxy_connect_timeout 90;
proxy_send_timeout 90;
proxy_read_timeout 90;
proxy_buffer_size 16k;
proxy_buffers 32 16k;
proxy_busy_buffers_size 64k;

The SSL configuration on the HA pair is standard throughout all of our servers that it “proxies” for. We have a wildcard certificate and the HA proxies only services under *.domain.tld – our horizon.domain.tld fits this pattern so no changes necessary.

All the standard Nginx SSL related security settings for certificate, stapling, ciphers, HSTS are located in our /etc/nginx/snippets/ssl.conf file and is included in the nginx.conf using:

include snippets/ssl.conf

snippets/ssl.conf

ssl_certificate /etc/ssl/certs/wildcard.pem;
ssl_certificate_key /etc/ssl/private/wildcard_key.cer;
ssl_dhparam /etc/ssl/private/dhparam.pem;

add_header Strict-Transport-Security "max-age=31536000; includeSubDomains" always;
ssl_session_timeout 1d;
ssl_session_cache shared:SSL:50m;
ssl_session_tickets off;

# modern configuration. tweak to your needs.
ssl_protocols TLSv1.2;
ssl_ciphers 'ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA256';
ssl_prefer_server_ciphers on;

# OCSP Stapling ---
# fetch OCSP records from URL in ssl_certificate and cache them
ssl_stapling on;
ssl_stapling_verify on;

add_header X-Content-Type-Options nosniff;
add_header Accept "*";
add_header Access-Control-Allow-Methods "GET, POST, PUT";
add_header Access-Control-Expose-Headers "Authorization";
add_header X-Frame-Options SAMEORIGIN;
add_header X-XSS-Protection "1; mode=block";

proxy_cookie_path / "/; HTTPOnly; Secure";

Note: Depending on your requirements for other system you may need to include content security policy settings to satisfy CORS (Cross Origin Resource Sharing). In fact you MUST do this to allow Chrome and Firefox to work with Blast over HTML.

In our PCoIP client we add the new server as horizon.domain.tld and we get through the authentication and on to the selection of the available pools. So clearly the load balancing is doing the job. You can check the /var/log/nginx/access.log to confirm.

If you miss out the ip_hash directive for session stickiness you’ll find you can’t get past the authentication stage.