Stuff I'm Up To

Technical Ramblings

DBeaver – SQL GUI — November 6, 2018

DBeaver – SQL GUI

I’ve used a few SQL GUI’s over the years, SQuirreL, DBVisualizer, HeidiSQL, MySQL Workbench, but the one that stands out recently is DBeaver.

It’s got a community and enterprise edition. The community does everything I need and connects to all the SQL servers we use, Microsoft SQL, MySQL, Postgres/PostGIS.

Being Java based it’s cross platform, so you can use it in Windows too.

Advertisements
Proxy Fun and Games — October 11, 2018

Proxy Fun and Games

I seem to spend most of may day trying to sort out issues regarding getting different applications through the corporate proxy server. I’m really hoping one day we can setup a transparent proxy if for no other reason than to make our development lives easier.

At present we need use a browser proxy script (http://wpad/wpad.dat) to determine which of the corporate proxy servers to use. We have an internet proxy and a Gov’t gateway proxy. Depending where the user is trying to go determines which proxy they must use.

The script works just fine for 99% of our user base.

However, when it comes to the other 1% there’s need to tell not just the browser what proxy to use, but in the development world we need to inform the various development tools how to use a proxy too. This is where the pain is.

We need to setup a proxy in several places eg. for the operating system, for the browser, for Git, for NPM/Yarn, for Composer, for Java…

Operating System

Windows

Open a CMD/PowerShell window with Administrative permissions

C:> netsh winhttp set proxy http://username:password@192.168.0.117:8080 "<local>"

You may not need the username and password here as the OS will send your Windows credentials.

The <local> means bypass the proxy for any local address. You may add into that for other specific servers eg. "<local>,server.domain.tld"

Also set the Environment variables for the proxy

Windows Key + R

control sysdm.cpl,,3

Click the environment settings and add in the following settings to your user variables.

http_proxy=http://username:password@192.168.0.117:8080
https_proxy=http://username:password@192.168.0.117:8080
all_proxy=http://username:password@192.168.0.117:8080
no_proxy=localhost,domain.local,192.168.56.2

Linux

$ sudo vi /etc/envronment

http_proxy=http://username:password@192.168.0.117:8080
https_proxy=http://username:password@192.168.0.117:8080
all_proxy=http://username:password@192.168.0.117:8080
no_proxy=localhost,domain.local,192.168.56.2

Git proxy settings

$ git config --global http.proxy http://username:password@192.168.0.117:8080

You’ll probably need to ensure this is set for the sudo environment too if you ever have the need to install global requirements with npm.

$ sudo git config --global http.proxy http://username:password@192.168.0.117:8080

NPM proxy settings

$ npm config set proxy http://username:password@192.168.0.117:8080

Again you’ll probably need to ensure it’s replicated into sudo.

$ sudo npm config set proxy http://username:password@192.168.0.117:8080

This actually writes to a file in your home folder called .npmrc which you can edit if you need to put in some backslashes to escape and special characters in your password. eg. c:\Users\myuser\.npmrc or ~/.npmrc and the sudo version will write it into the root users home folder.

Yarn proxy settings

As Yarn is essentially npm on steroids it works the same way but writes to ~/.yarnrc

$ yarn config set proxy http://username:password@192.168.0.117:8080
$ sudo yarn config set proxy http://username:password@192.168.0.117:8080

Composer proxy settings

Thankfully this is capable of using the Operating System proxy environment variables. So if you set them as above for Windows and/or Linux you should be good to go.

Java proxy settings

This has it’s own rules just like all the others. But you may also run into Java applications having their own proxy settings too. Such as gradle which has it’s own properties file to setup the proxy. They all seem to be a similar pattern though, edit a properties file and add in:

http.proxyHost=192.168.0.117
http.proxyPort=8080
http.nonProxyHosts=localhost|127.*|[::1]|*.domain.local

Typically this is done in the JRE’s lib/new.properties file so it applies to Java globally. eg. My net.properties file is located under c:\Program Files\Java\jdk1.80_151\lib and has plenty of helpful commented examples on how to set things.

Under Debian my net.properties is located under /usr/lib/jvm/java-1.8.0-openjdk-amd64/jre/lib

They can also be passed to the Java command line as -D parameters eg.

$ java -Dhttp.proxyHost=192.168.0.117 -Dhttp.proxyPort=8080 -Dhttp.nonProxyHosts="localhost|domain.local"

 

Local Git Repository — October 6, 2018

Local Git Repository

When working on a project at home I don’t necessarily want to host my Git repo online and don’t feel the need for installing a Gitlab server on my home network, but I do want to backup my projects to my cloud backup.

I also would like to not backup all the vendor resources with my project. So I’d like to exclude the node_module folder and other .gitignore content.

Whilst googling around I discovered I could just use a folder as a repo. Most people tend to do this onto a network file share, but my needs were simple. All I wanted to do was include my Git repo within the folders that are automatically backed up to the cloud.

Continue reading

XSLT and SOAP — September 14, 2018

XSLT and SOAP

All of our SOAP interactions with the Lagan CRM send and return SOAP and by association, XML. The normal practice of handling the sent or returned XML is by using XSLT to transform the data to and from the required format.

The forms product will submit XML through an XSL translation taking data from the POST’ed form data and turning it into the XML format/type required. The returned XML data must also be processed via an XSLT to present the data to the form.

How do we go about testing translations and stylesheets without constantly publishing forms and requesting data from the CRM server?

For this I used postman to submit and retrieve sample SOAP envelopes with the required XML soapenv:Body. Then I can take the returned sample data and save it to an XML file. Now I have a local sample of the XML I can use an XSLT tool to process it via a locally created stylesheet. No more repetitive form submissions or having to work with only the form product to develop the XSLT.

xlst_working

XSLT Tools

There are a very few XSLT tools that seem to do the job for free. Certainly when it comes to a GUI environment all the tools are paid for products.

At the command line there are some free options, but each have challenges. But I figured that just because it’s command line, doesn’t mean I can’t use it in a GUI. Atom has a very useful plugin that can be used to interface with the command line XSLT programs – atom-xsltransform. The settings for the plugin just point to the XSLT processor of your choice.

Once installed you press ctrl-shift-p whilst in your XML source file, it prompts you for the path of the XSLT transformation file to use and then returns the output into an edit tab in Atom.

MSXSL

For Windows I came across a very simple command line product from Microsoft MSXSL. It doesn’t look like there’s a recent version as this dates back to 2004. But as XML has been around for 20 years or so this may not be a problem. I did however find it seemed to produce broken output that looked to be to do with unicode. So maybe it’s not capable of handling the UTF-8 files I’m using.

xsltproc

This is from the world of Linux, but there is a port to Windows that works.

For Linux just install it from the repository:

$ sudo apt-get install xsltproc

For Windows, it’s harder work. Not significantly, but frustrating. You need to download a series of files, extract them all into the same place, to let their individual bin folders merge their contents. Then you can run the included xsltproc.exe and it should find all of the dll’s.

ftp://ftp.zlatkovic.com/libxml/

I chose the 64bit 7z files and extracted these files:

  • iconv-1.14-win32-x86_64.7z
  • libtool-2.4.6-win32-x86_64.7z
  • libxml2-2.9.3-win32-x86_64.7z
  • libxslt-1.1.28-win32-x86_64.7z
  • mingwrt-5.2.0-win32-x86_64.7z
  • openssl-1.0.2e-win32-x86_64.7z
  • xmlsec1-1.2.20-win32-x86_64.7z
  • zlib-1.2.8-win32-x86_64.7z

Saxon

This is a Java product and comes in a number of versions from home edition to professional that requires payment.

It’s hosted here on Sourceforge: http://saxon.sourceforge.net/

I downloaded the HE (home edition) and just placed the jar files somewhere I could use them.

From the Linux command line I used it like this:

$ java -jar saxon9he.jar -s:/home/user/lagan/xslt/FWTCaseFullDetails.xml -xsl:/home/user/lagan/xslt/FWTCaseFullDetails.xslt

Atom plugin settings

It’s a simple case of putting in the path of the executable you want to run. Pay attention to the order of the parameters for the tools. MSXML and xsltproc have the XML and XSL options in a different order.

For the Linux xsltproc settings I used:

/usr/bin/xsltproc %XML %XSL

For Saxon I had to be specific about where the jar file was as I haven’t installed it into the java class path.

java -jar /home/home/saxon/saxon9he.jar -s:%XML -xsl:%XSL

Stylesheets

The XSLT stylesheet acts as the instruction set to take the XML input and apply the XSLT logic to transform the XML content into another format such as text or HTML.

W3Schools has some useful guidance here: https://www.w3schools.com/xml/xsl_intro.asp

Another useful intro: https://www.tutorialspoint.com/xslt/

 

Exchange 2013 – Certificate Revocation — May 30, 2018

Exchange 2013 – Certificate Revocation

Using the Exchange Control Panel showed that the certificate being used whilst not expired and valid could not pass a revocation check.

I figured this would be because the server couldn’t get out on the internet to read the necessary CRL. But it wasn’t even trying to get online according to our corporate proxy logs.

The netsh proxy settings were correct, but obviously something wasn’t proxy aware.

The resolution goes back to a 2010 hack that calls Internet Explorer as the Local System account. Only thing is, this didn’t work on Windows 2012. It did however give me the necessary light bulb moment to resolve it.

http://blogs.technet.com/b/bshukla/archive/2012/04/30/certificate-revocation-checked-failed.aspx

By using the Sysinternals PsExec to launch a command prompt as the local system I could then run Iexplorer.exe and set the proxy for the Local System account.

https://specopssoft.com/blog/how-to-become-the-local-system-account-with-psexec/

C:\> psexec -s -i cmd.exe

and up pops a new cmd window that runs as Local System. Now call Iexplorer.exe in that new cmd window.

C:\> "C:\Program Files (x86)\Internet Explorer\iexplorer.exe"

and up pops IE for you to set the proxy as necessary. Give it 15 minutes or so and go back to check the Certificate status and now it shows as “Valid” – Job done!

 

Proftpd and LDAP / Active Directory — May 10, 2018

Proftpd and LDAP / Active Directory

We’ve had a vsftpd server for a while and it’s performed very well for us. But it would appear that it’s not actively maintained. This may not be a problem as it still currently works just fine and we don’t have any obvious vulnerabilities with it, but as the OS it’s running on is Wheezy we need to move on at least up to Stretch. So I figured I’d try deploying a new server but configured with proftpd.

Continue reading

Azure ADFS Certificate Notification — April 18, 2018

Azure ADFS Certificate Notification

We’ve been using Azure for a few months now so it’s about time our certificates would expire right? Well according to the email notification we’ve just received a certificate needs updating or we’ll lose access!

In order to provide your organization with uninterrupted access to Office 365 and Microsoft Azure Active Directory (Azure AD), you need to ensure your certificate for the domain(s) domain.tld is renewed and updated in Azure AD right away.

Our current certificate on file for domain(s) domain.tld expires on 5/5/2018.

If you don’t take action, your users will lose access on this date or, in the default configuration of Active Directory Federation Services, 15 days prior to 5/5/2018.

What you should do right now
If you are using AD FS with the default configuration, or are using a third party STS or a non-default configuration of AD FS, follow the article here.

Continue reading

Access DFS Shares from Linux — March 27, 2018
Sweet Christmas – VS Code — March 13, 2018

Sweet Christmas – VS Code

Ok, so I know Microsoft have been making some big steps in the world of Open Source – I confess to giving them little ear time, mainly because EVERYTHING we seem to do at work ends up a licensing battle that Microsoft always win, and by win I mean empty this years budget in one go.

I’ve been using Atom as my editor of choice for some time now and really like it, but someone suggested I checkout Microsoft Visual Studio Code. I am aware of it. It has the same construction as atom – it’s based on Node.js and built using electron, just the same. So I installed it today… and wowsers! It’s impressive. It uses git and picks up the file changes using git status and I’d almost swear it was atom.

It even has extensions for linting! Of course it also has a PowerShell extension – which to be honest I found missing in atom when coding PS in Linux.

But I’m not 100% sold yet. Atom is going to take some beating. But I’m certainly more surprised by code than I thought I would be.

Code v1.2.1 at the time of writing.

PostgreSQL Copy Data Between Servers — February 20, 2018

PostgreSQL Copy Data Between Servers

Our GIS team use a PostgreSQL server with PostGIS. They recently asked if there was any way we could display some data in a simple web form for our users. So a bit of development work was required.

I didn’t want to code against their live system so thought I’d install a local version of PostGIS and copy the data from their database.

The database they wanted to access has 28 million rows – so it’s going to take a while.

Continue reading

XmlWriter and Encoding — February 1, 2018

XmlWriter and Encoding

This could have saved me some time today!

http://hoolihan.net/blog-tim/2008/10/02/utf-8-encoding-with-xmlwriter-and-a-stringbuilder/

I’m new to PowerShell and this was hard work to find that it’s not my problem.

$enc = [System.Text.Encoding]::GetEncoding(28591) # iso-8859-1

# Using StringBuilder to form a valid XML file with encoding declaration
$builder = New-Object System.Text.StringBuilder
$stringWriter = New-Object System.IO.StringWriter($builder)

$settings = New-Object System.XML.XmlWriterSettings
$settings.Encoding = $enc
$settings.Indent = $true
$settings.CloseOutput = $false
$settings.CheckCharacters = $true

Write-Host $settings.Encoding

$writer = [System.XML.XmlWriter]::Create($builder, $settings)

Write-Host $writer.Settings.Encoding

$xmlDoc.AppendChild($xmlData)

$xmlDoc.Save($writer) # Save the XML into the $writer object
$writer.Close()
$stringWriter.Dispose()

I created a settings object to set the encoding I wanted and then passed that into XmlWriter::Create but it gets totally ignored and reverts back to default UnicodeEncoding.

Output:

System.Text.Latin1Encoding
System.Text.UnicodeEncoding

So the settings value is Latin1Encoding, but once it reaches the other side of the XmlWriter::Create it has become UnicodeEncoding.

The result is every XML file I create has encoding="utf-16" in the declaration.

 

OLEDB Reading a : Delimited Text File — January 31, 2018

OLEDB Reading a : Delimited Text File

Been a long while since I used VBScript – I guess I should get more familiar with PowerShell, but there’s a simple project that requires a script change that reads a colon delimited file and converts it to comma separated.

The original script read the file as a text file one line at a time and split the line into an array using the colon delimiter. Nothing too wrong with this, but the file is fixed length and loads of dead spaces to trim out, date conversions to be done and some special currency handling. So I thought I’d use an OLEDB method to read the data as a recordset.

Should be simple enough right?

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.RecordSet")

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\temp;Extended Properties=""text;HDR=No;FMT=Delimited(:);"""

rs.Open "select * from file.20180131;", cn, adOpenForwardOnly, adLockReadOnly, adCmdText

Well not really. The biggest problem I encountered was the file names in use are suffixed with the date eg. filename.20180131. That shouldn’t be a problem, but the OLEDB text handler fails with anything but .txt, .asc, .csv extensions. Worse still the error messages it comes back with are terrible.

First off I got messages about the file being read only, which it isn’t.

Microsoft Access Database Engine: Cannot update. Database or object is read-only.

Then adding in a ReadOnly=False to the connection string only made things worse!

Microsoft Access Database Engine: Could not find installable ISAM.

All because the extension needs to be .txt!

Then I finally get it working by using the .txt extension it reads the entire line into a single field/column. It ignores the FMT=Delimiter(:)in the connection string. This is because it doesn’t work like that anymore. You MUST create a schema.ini file in the same location as your text file and configure the options that way.

[file.txt]
ColNameHeader=false
CharacterSet=ANSI
Format=Delimited(:)
CurrencySymbol=#
Col1=A Long
Col2=B Long
Col3=C Text
Col4=D Currency
Col5=E Date

Our file includes a # instead of a GBP £ sign so we can even fix that in the schema.ini file by telling it to see the # as a currency symbol.

So we fix the VBScript and now we can read the data without messing with split(), arrays and data conversion.

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.RecordSet")

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\temp;Extended Properties=""text;"""

rs.Open "select * from file.txt;", cn, adOpenForwardOnly, adLockReadOnly, adCmdText

OLEDB Drivers without installing Office

You don’t need office on your server to read data using OLEDB. You can just install the Microsoft Access Database Engine 2016 Redistributable

References

https://docs.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver