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
ESPAsyncWebServer — March 28, 2019

ESPAsyncWebServer

This is a great ESP8266 component that greatly simplifies the deployment of a web server. It’s very capable and can handle websockets, compressed files and compared to other ESP web servers is streets ahead in terms of performance and abilities – It’s not without it’s challenges though.

One challenge in particular is the use of the ArduinoJson module. Or more correctly the use of ArduinoJson v5, when the most current is v6. As I discovered When I needed the AsyncJson functions, I found that I was using ArduinoJson v6 by default. This prevented my project from compiling because ESPAsyncWebServer is coded using v5 in its AsyncJson module.

A downgrade of ArduinoJson to v5 should have solved the issue, but up popped another issue. Looking through the Github issues history I found exactly my problem, but relating to PlatformIO not Arduino IDE.

error: expected class-name before '{' token
class AsyncJsonResponse: public AsyncAbstractResponse {

https://github.com/me-no-dev/ESPAsyncWebServer/issues/475#issuecomment-464770544

The solution was simply reorder my includes so AsyncJson is after the ESPAsyncWebServer. eg.

#include <ESPAsyncWebServer.h>
#include <AsyncJson.h>
#include <ArduinoJson.h>

The next quirk I encountered was the handler for receiving and processing json in the request body. The example given in the docs:

AsyncCallbackJsonWebHandler* handler = new AsyncCallbackJsonWebHandler("/api", [](AsyncWebServerRequest *request, JsonVariant &json) {
  JsonObject& jsonObj = json.as<JsonObject>();
});
server.addHandler(handler);

Never triggered even when my body had json in it. In the Github issues I found that this is down to the AsyncJson.h code not allowing for a Content-Type header that also included the code page data. There are two choices, modify the AsyncJson.h or set your client to match the exact Content-Type requirement of application/json; and not application/json; charset=UTF-8.

In my case I was able to modify the clients JavaScript axios call to specify the precise header without the code page. If you look at the AsyncJson.h file you’ll find


if (!request->contentType().equalsIgnoreCase(JSON_MIMETYPE))

So there’s no allowance for anything but the specific mime type and no code page. (See jnicolson’s comment)

Hopefully there’ll be an upgrade to ESPAsyncWebServer that moves it to ArduinoJson v6, but right now using v5 is the answer.

References

Scorpion Lite

Scorpion Lite – ESP8266 — March 27, 2019

Scorpion Lite – ESP8266

Looks like my friend likes to try to go one better. When he saw how I’d taken his original Wake-On-Wi-Fi idea and turned it into project Scorpion he moved to using ESP8266.

At the time I started looking into using a Raspberry Pi for the project I did also look at some very lightweight alternatives using the ESP8266 chip set. These are small devices perfect for use in a plethora of IoT projects. Their size is their strength and, for me, their weakness.

Moving onto a small chip means I have a few issues to solve that the Raspberry solves easily. 1) Power supply 2) File storage 3) My skills.

Adafruit Huzzah and FTDI Cable

Something I already had in my box of wires and gadgets is an Adafruit Huzzah ESP8266 board. I bought it a while ago and needed to combine it with a few other bits to make it work. To program it I needed a USB FTDI cable – which I also have.

But then my friend discovered the Wemos D1 Mini – now re-branded Lolin. The big benefit here is that it solves problem #1 (power supply) and means I don’t need an FTDI cable, just a regular micro USB cable which also supplies power.

Wemos also have a relay “shield” that means I can create a tidy package that builds in the ESP8266 and includes the relay required to turn on the PC.

Continue reading
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
PostGIS and geoJson — February 22, 2019

PostGIS and geoJson

On with this current theme of spacial systems and mapping, one of the interesting challenges I faced was querying spacial data.

My particular problem was trying not to publish masses of polygons up onto an Esri map layer and make the data layer too cumbersome for our intended visitors purpose.

We have a lot of plots of land that we care for and needed to check that when a web site visitor clicks on the Esri map that we check to see if the location they have clicked actually belongs to a plot we manage.

Continue reading
ESRI ArcGIS — February 21, 2019

ESRI ArcGIS

Over the past few weeks I’ve entered into the world of spacial systems and mapping. We have a couple of members of staff who look after our GIS (Geographic Information Systems) that plot all kinds of spacial data onto maps for properties, water courses, streets, etc. The data they produce is used widely through many of our services, but now we’re starting to provide more interactive online mapping for the public.

ESRI ArcGIS Online

Continue reading
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
PHPUnit – Version Mismatch — February 4, 2019

PHPUnit – Version Mismatch

As our codebase matures we return to develop unit tests to ensure our QA process captures any code changes that may have altered the functionality of the product.

When calling PHPUnit on Windows or Linux we ran into some issues relating to the version of PHPUnit we had installed.

On Windows it was an ancient PHPUnit version 3 and on Linux It was running version 7. Neither of which were compatible with our Laravel 5.5 project which uses php version 7.0.

In order to use PHPUnit with our project we must use PHPUnit version 6 (see Supported Versions)

What I hadn’t realised is that we had installed PHPUnit both locally into the OS and with our Laravel project so it exists in composer.json and gets installed under the projects ./vendor. The version installed in the OS path is the version that isn’t compatible with our project, but because it’s in our path it’s taking precedence over our project installed version.

To run the project version we just need to be specific in how we call it.

$ ./vendor/phpunit/phpunit/phpunit
PHPUnit 6.5.13 by Sebastian Bergmann and contributors.

....F                                                               5 / 5 (100%)

Time: 345 ms, Memory: 16.00MB

There was 1 failure:

1) Tests\Unit\Finance\CostCodeTest::testApiGetCostCodes
Expected status code 401 but received 200.
Failed asserting that false is true.

/home/user/itsm/vendor/laravel/framework/src/Illuminate/Foundation/Testing/TestResponse.php:78
/home/user/itsm/tests/Unit/Finance/CostCodeTest.php:37

FAILURES!
Tests: 5, Assertions: 14, Failures: 1.

Laravel 5.5 HMR and Windows — January 15, 2019

Laravel 5.5 HMR and Windows

Using HMR in Chrome on Linux is faultless, but on Windows HMR fails to start in the browser.

Looking at the entries in the bowsers script tags they seem a bit goofy. There’s leading slashes and spaces before the script filename.

It seems this is a popular issue. We hunted around for quite a few pointers to resolve this.

https://github.com/JeffreyWay/laravel-mix/issues/1437

The only thing we changed was line 90 of Entry.js to add on the extra replace(/^\//, ''); A restart of yarn hot and a browser refresh and we were good to go. HMR and WDS show in the Chrome console as expected and changes to code are now dynamic.

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

Damn that Proxy! — December 12, 2018

Damn that Proxy!

In Windows when you run into an application that doesn’t use proxy settings and doesn’t look at the environmental variable,  IE or netsh settings, then you’re kind of stuck when you must send web traffic through a proxy.

That was until we discovered proxycap.

Proxy cap is a very flexible solution that can add specific rules for various requirements. It will then intercept matching traffic and direct it to the proxy without the application even realising there is a proxy.

The example we based this on is the application Bluestacks, not being able to proxy. When we Goggled a solution we came up with posts about using proxycap. We could then add in rules only for the programs bluestacks.exe and hd-player.exe using https to be intercepted and Bluestacks would then work – even though it knew nothing about proxies.

Proxycap seems very clever in that it seems to just modify the Windows firewall to make the magic happen. It’s very flexible in that you could even set different apps to use different proxies. It also supports authentication.

It’s a commercial product, but sometimes you just have to pay the price.

Cross Origin Resource Sharing and Content Security Policy — December 4, 2018

Cross Origin Resource Sharing and Content Security Policy

Got to love having a vendor carrying out half a job… again.

Having installed a new VMWare Horizon environment for Windows 10, I thought we’d at least have Blast available via HTML  – which we don’t currently have in our Windows 7 Horizon setup.

During the install I setup a load balancer which only really handles the authentication process. This worked fine using IE or Edge, at which point I guess the vendor decided that’s enough testing and it’s considered functional. After they left I fired up my Chrome browser and found it didn’t work. So I tried Firefox with the same non-functional result.

Checking the console log in Firefox I see:

Content Security Policy: The page's settings blocked the loading of a resource at wss://192.168.61.12:22443/d/36BC344E-DAD5-4EA5-A44C-12456F74432D/?vauth=LaQJrs2RppeiZGX9gOtj75vekprtuEDcgD2C6tba ("default-src").

A trawl of VMWare documentation results in: https://docs.vmware.com/en/VMware-Horizon-7/7.6/horizon-security/GUID-FD679D1D-E037-4EDF-A96F-F0CD85FFE724.html

Now all I have to do is translate that to Nginx so I can put that into the config.

Editing my ssl/snippets.conf file and changing the CSP header, I added the missing parts for wss: and blob: to end up with:

add_header Content-Security-Policy "default-src 'self' wss:; script-src 'self' 'unsafe-inline' 'unsafe-eval'; img-src 'self'; style-src 'self' 'unsafe-inline'; font-src 'self'; frame-src https://horizon.domain.tld blob:; object-src 'none' blob:; connect-src 'self' wss:; child-src 'self' blob:;";

A reload of Nginx and a refresh/reload on the browser and I’m into the Horizon Desktop!

References

https://docs.vmware.com/en/VMware-Horizon-7/7.6/horizon-security/GUID-94DAC7B8-70A3-4A91-8E70-2B2591B82866.html

https://www.owasp.org/index.php/Content_Security_Policy_Cheat_Sheet