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 —
Ubiquiti Amplifi – Update — April 25, 2019

Ubiquiti Amplifi – Update

Following my previous article – Ubiquiti Amplifi. My brother decided he needed to improve his wireless LAN. He’d been using some Netgear range extenders from the ISP Router to get the signal around the house and out to the garden. He lives next door and we share the ISP and network. The only difference was the Wifi SSID’s.

I convinced him to replace the extenders with two more of the Ubiquiti Mesh Points and turned off all the ISP Wifi and had him join my Wifi network.

Now we have a mesh covering both houses and gardens with great signals throughout. The setup couldn’t have been easier. Just plug in the Mesh Points and then connect them to the SSID using a mobile phone.

Amplifi Mesh Point

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
Ubiquiti Amplifi — March 21, 2019

Ubiquiti Amplifi

Bye, bye Asus RT66U you served me well. Until all of a sudden the connection speeds on my 802.11ac would fluctuate wildly between 3Mbps and 150Mbps when previously I was seeing rock solid 877Mbps. If I switched to 802.11n I’d be stable at 216Mbps, but who wants that when I previously had more than three times that?

Time for something new. I checked out some of the reviews of newer routers and settled on the Amplifi. It’s a great looking design with some great reviews, but not the cheapest. I bought the Amplifi HD R and one of the MeshPoint HD’s. Both UK versions, with UK three pronged mains plugs.

My needs were near Gigabit Wi-Fi to my PC located upstairs from an Access Point located downstairs. I say Access Point because the actual ISP router is located elsewhere and I wanted to leave that as is, with it handling the upnp, port forwarding and firewall stuff – the router here would be used as an Access Point.

Ubiquiti Amplifi

Installation couldn’t be easier. In fact as an IT professional it’s a little daunting to have something so easy that is setup from a mobile phone and sits well in the realms of the end-user!

That said it has a wealth of features only accessible from the mobile phone config, that make it a highly capable Wi-Fi router.

In my chosen configuration I am using the Amplifi in bridge mode, so I lose the firewall and some of the features for managing clients. But it was important I leave my DHCP and internal network bridged to maintain the functionality of my Lifx lighting, Google Home and more importantly my scoRPIon device.

Once connected to my LAN (using the WAN port) I still have four Gigabit ports available for my wired network devices. The Amplifi sits under my TV and feeds my Tivo, XBox One X and Kodi with ease.

The wireless connection to my upstairs PC is now rocking a 1.1Gbps connection – still using the same Asus PCE-AC68 network card.

I added the MeshPoint in the kitchen, and will only really come into it’s own in the summer when the added signal range for the garden will be used. Right now it’s pretty redundant as the router pretty much covers everything I need in the house. The adding of the MeshPoint is again done from the mobile phone and is extremely straightforward.

Thankfully the brightness of LED base light and LCD display can be controlled from the phone app, as they can be a bit bright and distracting.

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
Spectre and VMWare — March 8, 2019

Spectre and VMWare

For some time we’ve suffered a problem with our Windows 7 VDI systems that has prevented us from applying Windows Updates.

If we applied any of the rollups from March 2018 onward the VDI session would reboot itself under one special condition. If a user/client used the Cisco AnyConnect VPN software within the VDI Guest then almost exactly 2 minutes and 10 seconds after connecting, the VDI machine would throw a fatal error and reboot. Instantly terminating the users session.

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

Sophos Mobile 9.0 — February 28, 2019

Sophos Mobile 9.0

Today saw me upgrading our Sophos Mobile Control v8 server.

Mandatory Upgrade Notice: Sophos Mobile 9.0

Dear Customer,

Please be advised that, effective April 2019, management of Android devices will cease to function with versions of Sophos Mobile older than 8.6. All instances of the Sophos Mobile management server should be upgraded to the latest version, 9.0, to ensure continuous management. Read how to easily upgrade to Sophos Mobile 9.0 and find out what’s new.

Continue reading