Stuff I'm Up To

Technical Ramblings

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

 

Advertisements
Azure IPSec VPN Ups and Downs —

Azure IPSec VPN Ups and Downs

Following our IPSec connection setup for Azure and the Juniper SRX we were seeing regular disconnections and a failure to re-establish a tunnel for extended period. This was very frustrating as about every 7 hours and 20 minutes we’d lose connection. We’d then have to restart the IPSec service on the SRX and it would come back up.

As our SRX is hosted inside another firewall the IPSec traffic is NAT’ed and we began to wonder if that was the problem.

So we did some log watching on the external firewall and grabbed some tcpdump information as the tunnel was down and saw nothing to indicate that packets were being dropped on the external firewall.

# tcpdump -nei any host [IP Address]

We monitored the internal and external IP’s and could see IPSec traffic.

Continue reading

Microsoft Office 365: Day One — January 28, 2018

Microsoft Office 365: Day One

So today’s been the first day following the consultants departure. They configured our Exchange 2013 estate to act as a hybrid solution to allow us to migrate our mail box users onto Outlook 365.

The config and setup certainly seemed more straight forward on the cloud side than the “on premise” parts. We had plenty to do to setup autodiscover DNS records internal and external, reverse proxying and ActiveSync setups with Sophos Mobile Control.

But now the consultants have gone we’re left picking up the pieces. As it seems no job is left finished.

Continue reading

Sophos Mobile Control EAS Proxy — January 25, 2018

Sophos Mobile Control EAS Proxy

Up until this week we’ve been able to get away with a very simple SMC installation that proxies Exchange ActiveSync (EAS) from the one server with the base Sophos Mobile Control program without using a Standalone EAS Proxy.

But now we’re moving towards Office 365 on the cloud the Microsoft ActiveSync gets messy. As we’re in a hybrid setup where we have most users mailboxes on an internal Exchange 2013 instance and only a few on Office 365 the EAS Proxy part of SMC needs to know about more than one server/service to proxy to.

Continue reading

Mysql Broken After Apt Upgrade — January 18, 2018

Mysql Broken After Apt Upgrade

My local install of mysql-community-server decided to fail today after applying some updates. I’m running Debian buster/sid so these kind of things are to be expected. But this was a totally new one to me.

Mysql failed to start so the update wouldn’t install.

Unpacking mysql-community-server (5.7.21-1debian9) ...
Setting up mysql-community-client (5.7.21-1debian9) ...
Setting up mysql-client (5.7.21-1debian9) ...
Processing triggers for systemd (236-3) ...
Processing triggers for man-db (2.7.6.1-4) ...
Setting up mysql-community-server (5.7.21-1debian9) ...
Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.
invoke-rc.d: initscript mysql, action "start" failed.
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: activating (auto-restart) (Result: exit-code) since Thu 2018-01-18 08:54:36 GMT; 9ms ago
  Process: 11123 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid ^[[0;1;31m(code=exited, status=1/FAILURE)^[[0m
  Process: 11088 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
dpkg: error processing package mysql-community-server (--configure):
 installed mysql-community-server package post-installation script subprocess returned error exit status 1
Errors were encountered while processing:
 mysql-community-server
Log ended: 2018-01-18  08:54:36

Then in my syslog file I saw lots of activity constantly trying to start and failing.

Continue reading

Microsoft Azure and Juniper SRX — January 12, 2018

Microsoft Azure and Juniper SRX

We’re getting on the Microsoft Office 366 and band wagon. I’m not a Microsoft fan, and think it’s overpriced for the functionality we’ll actually use. This means we need to setup an IPSec VPN between the Juniper SRX and Azure.

Microsoft have a Github page with not just guidance, but specific configuration examples to help do this. Not just with Juniper, but a range of firewalls.

https://github.com/Azure/Azure-vpn-config-samples

We’ve got some consultants in setting up the Azure side of the VPN and once I got into the portal I laughed at how much they were charging for turning on the VPN feature and setting a private key – that’s it! There’s very little control to be able to do anything else and if you want logs to see why things aren’t going to plan, you’d better rely on your own device for that.

After a couple of hours they’d written some PowerShell to gather some information that was stale because we’d already moved on past that particular error.

But that said, the Azure side just works. Get your device side right and do your debugging from there and let Azure sit and just do it’s thing. You have to assume that Azure just works.

Continue reading

DFS – Access Denied — January 5, 2018

DFS – Access Denied

Whilst trying to add a new cluster for file shares to take over from the previous one we found that whilst replication worked to migrate the files, we could not remove or disable the old paths from the Folder Targets.

Access Denied – obviously some kind of permission issue, but try as we might comparing ACL’s between systems we couldn’t see where the issue was.

It all came down to the power of my Google Fu.

Continue reading

Mobility Printing from a Guest Network — January 4, 2018

Mobility Printing from a Guest Network

Today I have been mostly fumbling around in DNS trying to get untrusted devices on our Guest network to print to our PaperCut Pull Printing system using NAT.

All our WiFi users connect to the Guest VLAN which is isolated from the main production network. There are very few services that need to come from the Guest network into the Trusted zone, but this pull printing is one of them.

untitled_page

Continue reading