Blog of spblinux

Receive your own ads-b signals and display them on a map like flightradar24.com does.

Required hardware: tv-usb-receiver with rtl2832u chipset (like this offer with rafael r820t2 tuner); router tplink tl-mr3020; small portable charging device. – In this blog entry you find a photography of a similar setup.

Software: openwrt (14.07 barrier braker)  with libsdr and dump1090 (both available as openwrt packages).

Installation:

• Install openwrt on tl-mr3020 as decribed by openwrt wiki here.
• Connect with telnet to 192.168.1.1 and set a root password with passwd.
• Change network settings to enable (preferably wired) internet access; (login as root with ssh and/or use the web interface); you might want to disable the dhcp server on interface “LAN”. – Configuration files are in /etc/config/.
  (In case you get locked out: power off; power on again; while led blinks quickly move the switch on the router; blinking gets faster and you are in safe mode with telnet login and default ip 192.168.1.1; run mount_root and repair settings with command uci)
• Install packages (as described here and here): opkg update; opkg install librtlsdr; opkg install dump1090
  The router has only 4MB onboard flash storage; 640k is configured as writable overlay; librtlsdr (+dependencies) and dump1090 increased used overlay space from 216k to 364k.
• run dump1090 without arguments to check if the tv-stick gets recognized

Configuration as wireless access point serving data only from 10.0.0.1 (no internet access using this accesspoint):

• Enable wifi in web interface (open accesspoint without password in my case)
• define a new “interface “WIFI” inside the web interface. Use the physical settings tab of interface “WIFI” and of interface “LAN” to ensure that only eth0 is bound to “LAN” and only wireless is bound to “WIFI”
• use setting network / firewall, create a new zone wifi with input: accept, output: accept, forward; reject (no masquerading, no clamping); add interface “WIFI” to this zone; do not allow interzone forwarding:
  Clients of the access point are not allowed to get  internet access and they cannot access the lan zone.
• Enable the dhcp server on interface “WIFI”, use advanced dhcp server settings to send empty dhcp options 3 and 6 by writing 3 instead of 3,1.2.3.4 into the first dhcp-options field and 6 into the second option-field. This tells the client that this access point does not offer a default gateway or a dns server: that is, no internet access through this access point (reference).
• Connect your mobile phone to the access point using dhcp and enable internet access by mobile data.
  The mobile browser should have access to 10.0.0.1 (openwrt config page) and to any internet page.

Display ads-b messages of airplanes in reach of your antenna:

• simple terminal view: connect by ssh to the router and run
  dump1090 –interactive
• browser view:
  first connect with ssh and run
  dump1090 –net –quiet &
  then open http://10.0.0.1:8080 with your browser
  and use ssh to stop dump1090 with command killall dump1090
• the cpu load on the router seen by command top is about 60%. So cpu power seems to be sufficient.

Refinement: move openwrt web interface to port 8088 and run dump1090 on port 80:

• ssh into the router and run
  sed “s,:80′,:8088′,g” /etc/config/uhttpd
  if the output is correct run sed with option -i (=edit in place)
  sed -i “s,:80′,:8088′,g” /etc/config/uhttpd
• to create a shell script called dump which starts dump1090 with options run (inside /root directory)
  echo “#!/bin/sh” >dump
  echo “#” >dump
  echo “dump1090 –net –quiet –net-http-port 80 &” >dump
  chmod +x dump
• now dump1090 can be started after ssh login as root by running
  ./dump
  and the webpage produced by tcpdump1090 is available at http://10.0.0.1

Some screenshots of output of dump1090 can be found on this page (written in german).

Using the buttons of the router:

The current states of all buttons can be shown with
cat /sys/kernel/debug/gpio
3 way switch positions:

• cat /sys/kernel/debug/gpio |grep gpio-18 |sed ‘s,^.*in *,,’
  lo = middle, hi = left or right
• cat /sys/kernel/debug/gpio |grep gpio-20 |sed ‘s,^.*in *,,’
  lo = left, hi = right or middle

and to monitor the button change events the directory /etc/hotplug.d/button has to be created. Then any script inside this directory gets sourced by the script /sbin/hotplug-call. The environment variables $BUTTON, $ACTION and $SEEN are set

• $ACTION: pressed, released
• $BUTTON:
  wps: push button with led,
  BTN_0: switch: released = middle position, pressed = left or right
  BTN_1: switch: released =left, pushed = middle or right
  fast switching from left to right: no event for BTN_0 (remains pressed)
• $SLEEP: seconds since last action for this button

Example script to monitor button events:

• mkdir /etc/hotplug.d/button
• echo ‘echo button $BUTTON with action $ACTION and seen $SEEN>>/tmp/buttons.log’ >buttonlog.sh
• tail -f /tmp/buttons.log

Scripts to toggle wifi with wps button and to start and stop programs (reference)

• /etc/hotplug.d/button/00-button.sh
• /usr/sbin/BTN_0_pressed and /usr/sbin/BTN_1_pressed
• /usr/sbin/handler_wifi_toggle.sh
• /usr/sbin/handler_dump1090.sh
• /usr/sbin/handler_rtl_tcp.sh
• /usr/sbin/handler_stop.sh
• all scripts have to be made executable with chmod +x
  and the suffix .txt should be removed
• to attach the handler scripts to hardware buttons wps/left/middle/right
  (left: triggered by quickly moving the mode switch from right to left
  right: triggered by quickly moving the mode switch from left to right
  middle: triggered by moving the mode switch to the middle)
  uci add system button
  uci set system.@button[-1].button=wps
  uci set system.@button[-1].action=released
  uci set system.@button[-1].handler=handler_wifi_toggle.sh
  uci set system.@button[-1].min=1
  uci set system.@button[-1].max=3
  uci commit system

Additions:

• Install rtl-sdr package from this source: download to your computer, then copy it per scp to the router. Install it with opkg install rtl-……_ar71xx.ipk
  • look at the bottom of this page to stream the signal of the tv-receiver with rtl_tcp
  • rtl_tcp -a 192.168.4.1 -n 8 -b 8
• as receiver osmocom fft analyzer can be used (osx install requires macports: port install gr-osmosdr +full; port install gr-fosphor; port install Gqrx)
  osmocom_fft -W -s 2000000 -f 144000000 -a ‘rtl_tcp=192.168.4.1:1234’
  optionally with -W or -S or -F
  (sample rate 2 MHz gives 30% cpu load on the router)
  Tested with wired connection.
• fm radio receiver gqrx runs with
  Device settings:
  device: rtl_sdr spectrum server
  device string (as displayed by rtl_test): rtl_tcp=192.168.4.1:1234
  sample rate: 1500000 (automatically set by gqrc)
  bandwidth: 0 MHz
  LNB LO: 0 MHz
  (Reboot router if transmission does not work properly)
  Receiver options: WFM (stereo)
  Input controls LNA gain: sensitivity of tuner
  Audio: Gain 0 dB
  Wired connection
• fm radio receiver sdr# on windows 8.1:
  wireless connection
  sample rate 1.024 MSPS
  start rtl_tcp to listen on wireless interface
  rtl_tcp -a 10.0.0.1 -n 8 -b 8

Features of Mediawiki extended with free version of BlueSpice:

• open uploaded documents with one click
• possibility to switch between wikitext and visual editor (based on tinymce)
• search includes contents of uploaded pdf files (with tomcat and apache solr/lucene)
• upload files by drag and drop (with msupload 10.0 extension msupload-bluespice.patch)
• user login using existing http digest auth login (with extension auth_remoteuser)

code sourcery is now part of of mentor.com:

– command line binary arm toolchain is somehow hidden in favour of the (non free) codebench product of mentor.com

. os x binary toolchain based on code sourcery sources offered by http://www.carlson-minot.com/

– on their download page they offer a build script which reference the code sourcery source archives
arm-2014.05-28-arm-none-eabi.src.tar.bz2
arm-2014.05-28-arm-none-eabi-i686-pc-linux-gnu.tar.bz2

– using google reveals download links for these source archives at http://sourcery.mentor.com/public/gnu_toolchain/
http://sourcery.mentor.com/public/gnu_toolchain/arm-none-linux-gnueabi/
http://sourcery.mentor.com/public/gnu_toolchain/arm-none-eabi/

Rooting: there are some fine guides like this. The first step, unlock bootloader, resets the phone to factory: backup data first. (To see developer options on the phone: settings / about / tap 7 times on the last line / build number). In developer options: activate oem-unlock to unlock the boot loader and later usb debug mode to allow adb shell access.

• fastboot and adb shell (reference): windows installer (=minimal fastboot and adb; tested: flashing with fastboot needs cmd window with admin rights); linux: apt-get install fastboot; osx: google mfastboot-v2 download.
• twrp for moto e 2nd 4g: reference; download (version 2015-03-08)
• supersu 2.46 (2015-03-29) from chainfire.eu: download
• search for moto e 2nd logo.bin to hide the unlocked bootloader warning (fastboot flash logo logo.bin)

USB OTG: moto e 2nd 4G (xt1524) does support usb on the go (check settings / storage with attached usb storage device)

Tools (from google playstore):

• Android Terminal Emulator (even tapping works with mc, if enabled in settings, using terminal type xterm). On first start run
  su -c ” supolicy –live ‘allow untrusted_app untrusted_app_devpts chr_file setattr’ “
• sshelper ssh daemon (comes with busybox)
• Midnight Commander Installer (installation requires busybox in /system/xbin/)
  Use terminal emulator to copy busybox to system app directory /system/xbin/:
  su; mount -o remount,rw /system
  cd /data/data/com.arachnoid.sshelper/bin; cp -a * /system/xbin/
  Then edit shell script /system/xbin/mc (e.g. with mc editor)
  export TERM=xterm
  /system/xbin/mc.real $@
• juicessh ssh client
  currently needs a policy modification
  su -c ” supolicy –live ‘allow untrusted_app untrusted_app_devpts chr_file setattr’ ”
  (or permissive selinux mode) to access /dev/pts. Run in terminal emulator:
  su; setenforce 0 (and reenable with setenforce 1; getenforce shows status)
  or  with ssh:
  su -c sh -i; setenforce 1
  then logout and login again
• Root Explorer: file manager

Screencast:

• YouMap: duplicates screen to rplay software on raspberry pi
• with raspberry pi configured as wlan access point and connected to the internet by ethernet both screencast and internet access use the same wlan connection
• transmission is very stable and efficient in terms of battery life. But the first connection might have to be tried multiple times: turn on server, connect to rplay device (stop server, disconnect device, repeat from beginning). – Much better than google chromecast app and stick (phone not in list of supported devices, 2015-03).

Compiler

• C4droid with gcc and sdl plugin
• recommended to use bluetooth keyboard
• working directory is:
  /data/data/com.n0n3m4.droidc/files/
  and uses temp.c and temp as compiled binary
• storage directory is
  /storage/emulated/legacy/
• in depth tutorial for qt app
• selinux support:
  copy libselinux.so from /system/lib/ to
  /data/data/com.n0n3m4.droidc/files/gcc/arm-linux-androideabi/lib/
• download selinux header files from android.googlesource.com: platform/external/libselinux: include (e.g. android-5.1.0_r3)
• create directory selinux inside
  /data/data/com.n0n3m4.droidc/files/gcc/arm-linux-androideabi/include
• copy the header files into this directory
• In C4droid settings / G++ arguments add after -lz: -lselinux; stored in
  /data/data/com.n0n3m4.droidc/shared_prefs/com.n0n3m4.droidc_preferences.xml
• test selinux with (idea from serverfault.com)
  #include <stdio.h>
  #include <stdlib.h>
  #include <selinux/selinux.h>
  int main() {
  security_context_t con;
  if (getcon(&con) < 0) {
  perror(“Cannot getcon”);
  return 1;
  }
  printf(“%s\n”, con);
  freecon(con);
  return 0;
  }
• this program should compile and run with output:
  u:r:untrusted_app:s0
• if and only if enforcing mode of selinux is active
  su -cn u:r:system_app:s0 test
  allows to run the program test in a different security context (su is part of supersu)

Android build environment:

• tutorial on xda-developers
• personal technical notes about android by David Madore

Mallory is a comfortable python based man in the middle tool. Using a patched version (mallory.diff) version with PyOpenSSL sockets adds SNI support.

Importing the ca certificate (mallory/src/ca/ca.cer) into a mobile device, allows to see all ssl encrypted traffic of the mobile device. (Import: either upload ca.cer to a webserver or send it as an email attachment; clicking on ca.cer installs the ca certificate.)

Installation on a raspberry pi with raspbian and configured access point:

apt-get install mercurial
apt-get install python2.7-dev python-setuptools
apt-get install python-pyasn1 python-netfilter libnetfilter-conntrack-dev
easy_install pynetfilter_conntrack
# ??? apt-get install libnetfilter-conntrack3-dbg

hg clone http://bitbucket.org/IntrepidusGroup/mallory
ln -s /usr/lib/arm-linux-gnueabihf/libnetfilter_conntrack.so /usr/lib/libnetfilter_conntrack.so.1

apt-get install python-pip python-m2crypto python-qt4 pyro-gui
apt-get install libffi-dev
pip install pyopenssl
apt-get install python-twisted-web python-qt4-sql libqt4-sql-sqlite sqlite3
pip install netlib
apt-get install python-imaging
apt-get install python-paramiko

Apply patch (which uses some code of this fork, especially the global config class commit, restoring of iptables when closing mallory gui. additionally it transfers code of mitmproxy into mallory to get SNI support)

Instructions to get started with mallory can be found here and here. – Acquiring of dhcp leases on client side currently only works when mallory gui is closed.

The rules and the streams tab in mallory gui might be unreliable. But mallory writes all packets into a sqlite3 database, which can be browsed on the advanced tab (db view, create sql, then execute).  – You could use sqlitebrowser as gui to view the data (apt-get install sqlitebrowser).

ipt.sh mallory2.diff mallory2cap.py.diffOn the bottom of this article you find a script which converts the mallory sqlite db into a pcap file which can be read by wireshark. (here with some error handling added: patch)

This patch adds a plugin to mallory to download the ca certificate at http://ip_of_mallory_host:8080. (Furthermore this patch fixes some issues in http.py)

The script ipt.sh allows to start and stop port redirection for mallory. This script and mallory (cd mallory/src; python mallory.py) have to be run as root. The mallory gui (cd mallory/src; python launch_gui.py) runs as normal user, if port redirection is done by ipt.sh.

• BerryBoot allows to put multiple operating systems on one sd card.
• the low power micro wifi adapter edimax ew-7811un is used in accesspoint mode
• PirateBox 1.01 exists as arch linux based raspberry pi image

Install BerryBox (reference)

• download latest berryboot zip archive from sourceforge
• reformat an sd card with one fat32 partition
• copy the contents of the zip archive into this partition
• attach screen, mouse and wired ethernet to raspberry pi and boot from sd card
• follow the instructions and install one “standard os”, e.g. “webserver”

Install PirateBox raspberry pi image

• download latest piratebox raspberry pi image (ArchLinuxARM) from downloads.piratebox.de (using a bit torrent client like deluge)
• this image has to modified on a linux machine (e.g. ubuntu 14.04 lts):
  /lib and /sbin which are symbolic links in the image have to be changed to real directories (reference)
  the resulting root partition has to be copied into a squash filesystem (reference)
• unzip the image and use kpartx as helper to mount the second partition
  kpartx -av filename_of_unpacked_image
  mount /dev/mapper/loopNp2 your_mountpoint (with N=0, 1, 2 … as displayed by kpartx
• use mc, navigate to the root directory of the mounted image.
  i) Remove the symlinks lib, bin  and sbin.
  ii) Move usr/lib to the root directory of the image; also move usr/bin to the root directory of the image.
  iii) Inside the root directory of the image rename bin to sbin and make a symlink from sbin to bin (ln -s sbin bin).
  iv) inside usr/ create symlinks for lib, bin and sbin to the root directory (ln -s bin ../bin; ln -s sbin ../sbin; ln -s lib ../lib)
• create the squash filesystem image (excluding the kernel modules because the kernel of BerryBoot is used)
  mksquashfs your_mountpoint archlinux.img -comp lzo -e lib/modules
• copy archlinux.img to an usb stick, plug this usb stick into the usb port of the raspberry pi running berryboot “edit boot”
  and copy archlinux.img to the sd card (using a long mouse click on “Add OS”)
• set archlinux as default boot entry and exit the boot menu configuration
• the raspberry pi should boot to the arch linux boot prompt (alarmpi: user: root; password: root)

Configure PirateBox with edimax ew-7811un wifi as access point (reference)

• replace /usr/bin/hostapd by this version (reference)
• use editor nano and change the line driver=… in /opt/piratebox/conf/hostapd.conf
  driver=rtl871xdrv
• edit /opt/piratebox/www/board/config.pl and uncomment and set
  ADMIN_PASS (=forum administrator password) and SECRET (seed for cryptography)
• workaround: piratebox.service (version 2014-10-10) needs to be started with a delay (else it fails to start hostapd):
  use nano and create /etc/systemd/system/piratebox.timer:
  [Unit]
  Description=piratebox delayed start
  [Timer]
  OnBootSec=1min
  Unit=piratebox.service
  [Install]
  WantedBy=multi-user.target
• activate this timer with
  systemctl enable piratebox.timer
• test if piratebox runs with
  systemctl piratebox status
  and try to rerun it with
  systemctl piratebox stop
  systemctl piratebox start
  systemctl piratebox status
• connect to piratebox access point; reboot; wait about 2 minutes; try again to connect
• use passwd to set your own root password
• reboot without wired lan connection and without keyboard, mouse and monitor

For a clean shutdown without keyboard ssh could be used (use hostname piratebox.lan)

To upload files from ios other than photos and videos from photo roll and camera:

• install icab-mobile (has an affordable price)
• on the settings page of icab-mobile set Network/Upload to icab mobile mode (defaults to ios mode)
• even with this setting the iframed upload page uses ios upload mode, but
  http://piratebox.lan:8080
  opened as a separate page (and not as iframe) allows to upload files (which have been previously downloaded by icab-mobile)
• documents displayed in safari can be transferred to icab-mobile download folder using “open with …” / icab mobile;
  then icab mobile asks if the document should be downloaded.

Raspberry Pi works as airplay receiver using xbmc (only for non copyrighted content)

More or less working (tested on iOS 7.1):

– builtin youtube video app for some content
– fileapp 4 (by fileapp.com) media player (mp4 videos have to be stored with extension m4v; uses builtin iOS player)
– app has to be closed to make freshly uploaded folder content visible (iTunes upload for this app)
– viewing of photos
(videos have a significant time lag between image and sound – but xbmc-kodi is still in alpha status: 2014-10-14)

Not working:
– videos taken by camera of iPhone 5s.
– copyright protected videos (e.g. any iTunes purchase)

Encoders
– handbrake: apple tv1 (format mp4, saved with extension m4v)
– adobe premiere elements 11: iPad high quality
plug player (if content gets played it can be transmitted to airplay)

Only sound:

oplayer (outputs only sound to airplay; maybe always sets bit for copyrighted videos)
builtin video app playing videos synchronized by iTunes

xbmc: raspbmc with kodi built from  here
(kodi-14-20141014-nc4.tar.gz)

To tweak audio settings use this reference

To setup raspbmc as wifi accesspoint refer to this post

Install iPhone app xmbc (official app made by the makers of xbmc)
(sometimes it is useful to connect  an iOS ssh client; currently (build20141012) required to make a clean shutdown:
login as user pi (default password raspberry) run sudo poweroff)

Alternative: rPlay by vmlite.com
(feature rich; needs free beta license; 12 months no change)

Adding a web frontend (LAMP server) to a local windows application with mssql database (Windows 2008R2 server with mssql 2008). ms access 2003 installed on windows 2008 server is used to synchronize data from mssql to mysql.

1. mssql database -> ms access 2003 (passthrough query using odbc)
2. ms access -> mysql (passthrough query using odbc with mysql tunneled by ssh)
3. windows task scheduler runs ms access (vba code to run queries)

Other software which has to be installed:

• MySQL Connector/ODBC 5.1 (32 bit)
• plink.exe, putty.exe (PuTTY)

Configure odbc:

ms access 2003 (usually) is a 32 bit application, stored in program files (x86), so 32 bit odbc connectors have to be used (reference).
To setup 32 bit odbc dsn files open %windir%\SysWOW64\odbcad32.exe (on 64 bit windows searching for odbc opens 64 bit odbc).

• Create a file dsn for mssql
• Run PuTTY, connect to the mysql server host with port forwarding: local 3306 to 127.0.0.1:3306 on mysql server.
  Then create a file dsn for mysql: tcp/ip 127.0.0.1, port 3306
• Run ms access and create linked tables with create table wizard: linked table / file type odbc / open file dsn.
  Select source table(s) in mssql and destination tables in mysql (activate save password option).
• Tools / database tools / linked tables manager has to be run if the layout of a linked table has been changed on the server.

Passthrough queries in ms access:

• Create a new query, do not add tables into the query, switch to SQL mode and set the type of the query to SQL / Pass-Through
• Open the properties window and set ODBC-Connection to
  ODBC;Description=…;DRIVER=…;SERVER=…;UID=…;PWD=…;DATABASE=…
  and additionally for mysql
  PORT=3306;CHARSET=utf8;DFLT_BIGINT_BIND_STR=1
  (The field values, except PWD (password), can be found, if the odbc file dsn is opened with a text editor)
• To run passthrough queries automatically the odbc password has to be stored in clear text(!);
  so both database servers, mssql and mysql, should have a user and password only used for odbc.
• To test the SQL code for passthrough queries the statements should be run on the server:
  mssql with SQL Server Management Studio and mysql with PHPMyAdmin

Transfer of data from mssql to mysql: tblSrc to tblDest

• on mysql server create tblDest_tmp with same layout as tblDest and create a linked table tblDest_tmp in ms access
• in ms access setup a mysql passthrough query with name step1 and SQL code
  TRUNCATE TABLE tblDest_tmp
  (set property return records to false)
• setup a mssql passthrough query with name qrySrc_mssql and SQL code which converts the data of tblSrc to the layout of tblDest, e.g.
  SELECT tblSrc.phonenumber AS phone FROM yourdb.dbo.tblSrc
• setup a ms access append query with name step2 which uses source “table” qrySrc_mssql and destination table tblDest_tmp, e.g.
  INSERT INTO tblDest_tmp (phone) SELECT phone FROM qrySrc_mssql;
  (it is possible to choose fields in ms access design view mode)
• setup a mysql passthrough query with name step3 which uses tblDest_tmp to update tblDest, e.g.
  INSERT INTO tblDest (phone) SELECT t.phone FROM tablDest_tmp AS t
  ON DUPLICATE KEY
  UPDATE phone=t.phone
  (set property return records to false)
• to transfer data start ssh port forwarding and run the queries
  step1 (which removes all data from tblDest_tmp)
  step2 (which copies data from qrySrc_mssql (fetching data from tblSRc) to tblDest_tmp)
  step3 (which updates tblDest with data from tblDest_tmp)
  stop ssh port forwarding
  (bug: if port forwarding has been stopped, ms access has to be closed and reopened;
  else the queries fail with an odbc error (mysql); some reinitialization of odbc seems to be needed.)

Preparations to run the queries automatically by VBA:

• Simple preliminary setup: create a form and use the create button wizard to put 3 buttons on the form
  which run the queries step1, step2, step3.
• Two other buttons can be created which start and stop ssh port forwarding:
  with the create button wizard choose run application and choose as command line
  “your_path\plink.exe” -L 3306:127.0.0.1:3306 -i “your_path2\keyfile.ppk” -ssh -2 -l your_user -N your_server.com
  and (assuming a non administrative user is logged in and only one instance of plink.exe runs in the account of this user)
  taskkill /f /im plink.exe
  With PuTTYgen an openssh public key can be converted into ppk format used by plink.exe.
  To be able to run these commands by windows task scheduler all network drive mappings have to be replaced by unc names
  (e.g. \\yourbox\tools\plink.exe, not N:\tools\plink.exe)

Using VBA code to run queries:

• to make it easier to maintain the SQL code of the queries, VBA makes temporary copies of existing queries
  (vba code based on inspiring work of mdlueck)
  Sub deleteQDF(name)
  On Error Resume Next
  CurrentDB.QueryDefs.Delete(name)
  End Sub
• passthrough query: (step1 and step3)
  Dim daoDB As DAO.Database
  Dim daoQDFbe As DAO.QueryDef
  Dim strQryNameBe As String
  strQryNameBe=”yourTmpQuery”
  deleteQDF(strQryNameBe)
  Set daoDB=CurrentDb()
  Set  daoQDFbe=daoDB.CreateQueryDef(strQryNameBe)
  With daoQDFbe
  .Connect=daoDB.QueryDefs(“yourExistingQuery(replace by step1 or step3)”).Connect
  .SQL=daoDB.QueryDefs(“yourExistingQuery(replace by step1 or step3)”).SQL
  .ReturnRecords=False
  .Execute dbFailOnError
  .Close
  End With
  deleteQDF(strQryNameBe)
  Set daoDB=Nothing
  Set daoQDFbe=Nothing
• append query (step2; with passthrough query qrySrc_mssql as source):
  Dim daoDB As DAO.Database
  Dim daoQDFbe As DAO.QueryDef
  Dim strQryNameBe As String
  Dim daoQDFfe As DAO.QueryDef
  Dim strQryNameFe As String
  strQryNameBe=”yourTmpQuery”
  strQryNameFe=”yourTmpQuery2″
  deleteQDF(strQueryBe)
  deleteQDF(strQryNameFe)
  Set daoDB=CurrentDb()
  Set  daoQDFbe=daoDB.CreateQueryDef(strQryNameBe)
  With daoQDFbe
  .Connect=daoDB.QueryDefs(“qrySrc_mssql“).Connect
  .SQL=daoDB.QueryDefs(“qrySrc_mssql“).SQL
  .ReturnRecords=False
  .Close
  End With
  Set  daoQDFfe=daoDB.CreateQueryDef(strQryNamefe)
  With daoQDFfe
  .SQL=daoDB.QueryDefs(“step2“).SQL
  .Execute dbFailOnError
  .Close
  End With
  deleteQDF(strQryNameBe)
  deleteQDF(strQryNameFe)
  Set daoDB=Nothing
  Set daoQDFbe=Nothing
  Set daoQDFfe=Nothing
• run application (start plink.exe for ssh port forwarding; run taskkill to terminate plink.exe; use Chr(34) to insert double quotes into commandline):
  Dim strAppName as String
  Dim qq
  qq=Chr(34)
  strAppName= qq & “path1\plink.exe” & qq & ” -L 3306:127.0.0.1:3306 -i ” & qq & “path2\key.ppk” & qq & ”  -ssh -2 -l dbUser -N dbserver.com”
  Call Shell(strAppName, 1)
  …
  strAppName=”taskkill.exe /f /im plink.exe”
  Call Shell(strAppName,1)

Autostart version of ms access database mdb file:

• Evaluate command line argument:
  open mdb database with:
  “yourPathToMsOffice\msaccess.exe” “yourPathToMdbFile\yourDb.mdb” /cmd autostart
• Create a macro with name AutoExec by new macro wizard of ms access, choose macro type run code and write to field function name:
  AutoExec(“autostart”)
  and create a VBA function with VBA-Editor (inside a standard module of VBA editor; not inside a form_module)
  Function AutoExec(ByVal strCmd As String) As Boolean
  If strCmd=Command Then
  ‘ autostart VBA code
  …
  DoCmd.Quit
  End If
  AutoExec=True
  End Function
• The code inside the if condition of the VBA function Autostart(strCmd) gets only executed
  if the command line of ms access.exe ends with /cmd autostart. (DoCmd.Quit closes the ms access application.)
  Else the macro AutoExec calls the VBA function AutoExec(strCmd) and returns immediately.

Use Windows Task Manager to run the queries:

• For security reasons a normal non administrative user account should be used to autorun the queries.
  This user needs the privilege to login as batch user (reference):
  Start / Administrative tools / Local Security Policy
  Security Settings / Local Policies / User Rights Assignment / Log on as a batch job
  click right mouse button and choose Properties
  Click Add user or Group to give the privilege to the user.
• Batch user login does not map network drives. Use UNC names instead:
  replace N:\yourSharedFolder by \\yourServer\yourShare\yourSharedFolder
  (e.g. ssh port forwarding: path of plink.exe and path of ppk file in VBA code)
• Batch tasks do not display any window. So code run by task manager has to be checked on the desktop.
  Single stepping through VBA code inside VBA editor is recommended.
  Batch tasks are only shown in task manager if show all users is active.
• Open Windows task manager with administrative privileges, else new tasks cannot be created.
  Use right mouse click run now to immediately try out a scheduled task.
• Create a new task (not simple task):
  – define user to run the task
  – run independently of user login (=run with batch login) and store password
  – run with high priority
  – trigger by timer, e.g. daily
  – action: run program:
  “C:\Program Files (x86)\Microsoft Office\OFFICE11\MSACCESS.EXE”
  use arguments
  “yourPath\yourDatabase.mdb” /cmd autostart
  working directory can be left blank
  – properties: stop after 1 hour and force exit

Watch how local windows application data automagically displays in web frontend.

Hardware: Acer Aspire One D270 with Atom N2600, 2GB RAM, 1TB harddisk, only bios boot mode

Software:

• Ubuntu 14.4 64bit: runs out of the box (has been installed after the installation of windows)
• Windows 8.1 pro 64bit: only partly supported (video runs only with generic vga driver)

Installation notes: Windows:

• Update bios for model AOD270 (a freedos usb stick may be used to run the dos bios installer: boot with option 3 ‘xms’)
• Clean install of Windows 8.1 with a Windows 8 update product key:
  – needs dvd iso of Windows 8.1
  – iso to usb: use windows diskpart to create a primary ntfs formatted partition on the usb stick (compare with this post)
  and copy iso to usb with Windows 7 usb/dvd download tool; run bootsect.exe /nt60 X: /mbr  to make the stick bios bootable
  – run install with generic windows 8.1 key : XHQ8N-C3MCJ-RQXB6-WCHYG-C9WKB (installation only; cannot be updated)
  – after installation activate windows and enter your personal key; (might show error message (wrong key)  but updates anyway);
  windows 8 update keys are valid for windows 8.1.
• Install updates (2014-05): KB2919355 does not install if Ubuntu has been installed. Workaround:
  – Run ubuntu and save first sectors of disk: fdisk -l /dev/sda displays in my case that sda1 beginns at sector 2048
  dd if=/dev/sda bs=512 count=2048 of=sectors_sda.bin (this saves bootloader grub)
  – Boot from Windows 8.1 install media, choose repair, extended, command line and run
  bootrec /fixmbr
  bootrec /rebuildbcd
  – now update kb2919355 should install (with disabled windows defender the update runs faster); then install all pending updates
  – boot from ubuntu install media, save windows mbr with dd and restore bootloader grub with dd (any error here might destroy all harddisk data!)
• Install drivers: intel inf-drivers for chipset, realtek pci-express card reader, synaptics touchpad (see this reference)
• after installation of ubuntu 14.4 the system has dual boot with grub2. But booting with grub2 into windows 8.1 breaks hibernate and shutdown from windows. So the boot menu of Windows 8.1 has to be used (as described here):
  – boot into Ubuntu and run dpkg-reconfigure grub-pc (reference 1, reference 2)
  (this allows to change location of grub boot code from sda to sdaN (=linux partition)
  – use dd to save the bootsector of sdaN (dd if=/dev/sdaN bs=512 count=1 of=sdaN.bin) and copy file sdaN.bin to a device
  which can be accessed from Windows
  – boot into Windows and copy sdaN.bin to the root of drive C:
  – now use bcdedit from admin console to create the Windows boot menu ({I} is a shortcut of the id returned by bcdedit /create)
  bcdedit /create /d “Ubuntu 14.04” /application BOOTSECTOR
  bcdedit /set {I} device partition=c:
  bcdedit /set {I} path \sdaN.bin
  bcdedit /displayorder {I} /addlast
  bcdedit /timeout 10
  bcdedit /default {I} (if Ubuntu should be the default boot menu entry)

Installation notes: Ubuntu 14.4

• internet access by iPhone tethering:
  – by usb cable: install ipheth-utils
  – by bluetooth: install blueman and use usb bluetooth dongle (only some aspire one d270 models have builtin bluetooth).
  Pairing works with builtin bluetooth apple of ubuntu 14.4; connecting to network (pan) works only with blueman.

Skolelinux installation with workstations instead of thin clients. Hardware equipped with intel celeron 1007u (2×1.5 ghz) gives better performance if used as workstation and not as thin client. Below are some notes describing this setup.

Because these workstations replace a thin client setup, the workstations are placed in 192.168.x.0 subnets (x=0, 1, 2) and ip forwarding is enabled on tjener (/etc/sysctl.conf); tjener is installed without ltspserver packets. On the gateway (10.0.0.1) a static route has to be set (route add -net 192.168.0.0 netmask 255.255.252.0 gw 10.0.2.2; e.g. for ipfire in /etc/sysconfig/rc.local). On tjener every 192.168.x.0 network needs its own statically configured NIC (/etc/network/interfaces). And the dhcp server has to listen on all interfaces (/etc/default/isc-dhcp-server).

To add subnet02.intern ldap has to be edited with phpldapadmin:
systems / servers / tjener: zoneName=subnet0x.intern (x=0, 1, 2); inside this ldap entry there are further zoneName entries which have to be edited as well.
(bug: default config uses a trailing dot here which should be removed to make equal to zoneName intern;
with trailing dot the nis netgroup triple with fqdn name gets a trailing dot when added by GOsa
and command innetgr -f myhost.subnet00.intern fails. )
and
systems / servers / tjener / dhcp: cn=subnet0x.intern (x=0, 1, 2)

/etc/bind/named.conf.ldap2zone:
replace existing zone “subnet00.intern.” by “subnet00.intern” (without trailing dot, same for zone file name)
add subnet01.intern and subnet02.intern

/usr/sbin/ldap2bind (runs automatically every hour)
/etc/init.d/bind9 restart (maybe not required)
/etc/init.d/isc-dhcp-server restart

To add a workstation to tjener use GOsa, set name, ip-address and mac-address, enable dhcp, add tjener as time server
and enable dns (using the zone which matches the ip-address) and add the workstation to nis netgroup workstation-hosts.

Enable auto update on workstations: /etc/apt/apt.conf.d/50unattended-upgrades:
uncomment MinimalSteps and InstallOnShutdown

Install plymouth on the workstation to get a nice splash animation:
apt-get install plymouth plymouth-drm plymouth-x11 plymouth-themes-all
edit /etc/initramfs-tools/modules (intel_agp drm i915 modeset=1)
edit /etc/default/grub: GRUB_GFXMODE=1280×1024 and GRUB_CMDLINE_LINUX_DEFAULT=”quiet splash”
copy a background png image to /boot/grub, unpack AzenisSkole.tar.gz (based on AzenisBuntu) inside
/usr/share/plymouth/themes/ and then run
update-grub2 and select a theme with
plymouth-set-default-theme –list; plymouth-set-default-theme AzenisSkole
preview the theme with
plymouthd;  plymouth –show-splash; sleep 15; plymouth quit
and put the theme to initramfs with
update-initramfs -u

Use grub.pxe to boot from network. This allows to keep pxe boot always on for the workstations which makes it easier to use clonezilla server.
The pxe boatloader grub.pxe can be created with grub-mkimage (details, embedded config and binary grub.pxe). In GOsa
systems / tjener / services / dhcp /subnet0x.intern (x=0, 1, 2): Bootup Filename: has to be changed (from default pxelinux.0) to
“/var/lib/tftpboot/ltsp/i386/grub.pxe” and grub.pxe has to be copied to this directory.
(This setting gets active after clicking on Save / Save / Apply and running /etc/init.d/isc-dhcp-server restart)

Bugfix: sometimes freshly booted workstation do not allow login. What seems to help is:
/etc/rc.local: /etc/init.d/nscd restart

Configure workstations for classroom use: chmod -R o-rwx  ()and smae for /skole/tjener/home0) to get private home directories
and add to /etc/skel: ./kde/share/config/dolphinrc:
[General]
ShowSelectionToggle=false
and add to /etc/skel: ./kde/share/config/kwalletrc:
[Wallet]
Enabled=false

Install apache-openoffice because libreoffice coming with debian wheezy is outdated and buggy (crashes). Install google chrome to get its builtin flash and pdf support.

If tjener runs in xen /etc/fstab needs the option nobarrier. (This avoids I/O errors in domU.)

Clonezilla server (DRBL): Install minimal system of debian-edu and drbl. Then run drblsrv -i (no drbl or system install images; clonezilla box mode). Save and restore with drbl-ocs -j2 -q2 startdisk save; drbl-ocs stop; drbl-ocs -j2 startdisk multicast_restore; restore with -icds if the target disk is smaller than the source disk and if the source partitions fit on the target disk. – To “plug”/”unplug” network cables of xen vm machines use brctl addif/delif for vif-interfaces (displayed by xm network-list domU_name).

Administration of windows workstations: add existing admin user with GOsa /groups to domain-admins group. On tjener use commands
net -S 10.0.2.2 rpc group …; net -S 10.0.2.2 rpc user …
to display groups and users (as described here). Use smbpasswd to set samba password for user root. Samba user Administrator can be enabled with
smbpasswd -e Administrator.

Update to debian jessie and ipfire 2.19-110

Tjener: update as described on skolelinux.org. Workstation: new install from usb with debian-edu1 image as “workstation” (guided harddisk setup, whole disk, maybe use console to mkfs.ext4 because automatic formatting might error out when previous filesystem exists, bootloader to /dev/sda); manually instal: apt-get install myspell-de-ch); after cloning with drbl: adjust hostname using mcedit /etc/hostname. (step by step: drbl-ocs -j2 -sc0 –clients-to-wait 5 startdisk multicast_res; ssh 10.1.0.[1 or 2 … 6]; then on client mount /dev/sda1 /mnt; mcedit /mnt/etc/hostname; poweroff) – Updated ipfire: static route on ipfire to 192.168.x.0 subnets does not work; instead use nat on tjener:
iptables -t nat -A POSTROUTING -j MASQUERADE -s 192.168.0.0/24
iptables -t nat -A POSTROUTING -j MASQUERADE -s 192.168.1.0/24
iptables -t nat -A POSTROUTING -j MASQUERADE -s 192.168.2.0/24