ssh chain tunnelling

Suppose you have

- a local PC at which you are sitting
- a gateway server which allows ssh connections from outside the firewall
- a remote PC of your own, also behind the firewall, also running sshd
- a target server which your remote PC can connect to, but the gateway can't

and you want to connect from the local PC - which is outside the firewall - to a port (say 1433 for SQL Server) on the target server.

Firstly set up a tunnel to your remote PC

plink -v -N dugeen@gateway.dugeenswork.co.uk -L 127.0.0.2:3381:remotepc.dugeenswork.co.uk:22

And then tunnel through the first tunnel onto the target server

plink -N -v -P 3381 dugeen@127.0.0.2 -L 127.0.0.2:3382:target.dugeenswork.co.uk:1433

(NB that -P 3381 dugeen@127.0.0.2 is saying 'connect to ssh on 127.0.0.2:3381 as dugeen')

Port 3382 on your local PC (127.0.0.2:3382) is now connected to port 1433 on target.

NB that you run both commands on your local PC, you don't ssh to remotepc to issue the second command.

3381 and 3382 are arbitrary choices, you can use any free ports on your local PC. And 1433 would be replaced by whatever port you were interested in on target.

plink

Don't give plink a port to connect to using the colon syntax -

plink dugeen@dugeenspc.dugeenswork.co.uk:2220

as it will silently fail. Use the -P switch

plink -P 2220 dugeen@dugeenspc.dugeenswork.co.uk

SQL Server functions

If a function returns a table you must grant SELECT permission on it, not EXECUTE.

If a function has a default parameter, caller cannot omit it from the parameter list, but must put the keyword default in its place. (Not terribly useful)

You can't join to a table-valued function, as you can only supply a function with constants or variables as parameters. (You can of course insert its result into a temp table or table variable, and then join to that).

Window functions in updates

Window functions aren't allowed in updates any more than other aggregates are.

Turn off OpenOffice Calc AutoCorrect

To get rid of all the autocorrect nonsense in OO Calc, use Tools/AutoCorrect/Options and untick everything.

These settings don't appear to be cross-OO, I think you have to do them individually for each app.

Perl missing comma gotcha

Suppose you set up a window thusly

$self->{ww} = Win32::GUI::Window->new(
-name => 'Main',
-text => 'Vstatscheck',
-width => 100,
-height => 100 ,
-onTimer => sub { $self->cycle(@_); } # oops missed out the comma
-onTerminate => sub { $self->terminate(); }
);

the -onTerminate bit will be interpreted as a subtraction and neither your Timer or Terminate handlers will be properly set.

If, unlike me, you heed the advice to use the -w switch, you'll get a warning of this. Otherwise you won't. Foolishly perhaps I hardly ever use -w because I can't tell the useful warnings from the useless ones.

Perl Win32::GUI Terminate

Effect of returning different values from the onTerminate event handler of your main window:

-1 ('terminate message loop') - window closes, Win32::GUI::Dialog returns.
1 ('default action') - window closes, Win32::GUI::Dialog continues.
0 ('don't take default action') - window does not close, Win32::GUI::Dialog continues.

Is this different from Perl-Tk? A Tk MainWindow only closes from the X box if you write a WM_DELETE_WINDOW protocol handler which calls destroy(), but that destroy makes MainLoop terminate.

Perl::Tk hide window with withdraw

If you withdraw your MainWindow and then raise it again, you must apparently also deiconify it (like the concept of 'withdraw' includes 'minimise'):

$in->{window}->withdraw();
...
$in->{window}->deiconify();
$in->{window}->raise();

You may also need to update() it after the raise to get included widgets to show.

Sockets with select

In the context of a single-threaded server such as you might construct in the absence of fork() on Win32, using mingw and winsock2.h; and suppose you only intend to cater for a single connection, so instead of having a sockets[] array for all the client sockets, you just have one int thesocket.

Do not do an FD_SET(thesocket, &read_fdset), when preparing to select, if thesocket is 0 (as it might be if you haven't had a connection yet or have closed one). This does not, as I expected, harmlessly AND 0 onto the fd_set, it appears to bugger it up completely so select() returns -1 without even waiting.

('Harmlessly AND 0' indeed. I was mixing up AND and OR there)

Also note that a single-threaded server that only expects one connection has to actively reject further connection attempts - you will find that FD_SET(serversocket, &read_fdset) is true after select() on every such attempt. To reject them, just closesocket() them.

Perl easy extensions

Inline::C doesn't seem to be workable on Win32 with ActiveState Perl unless you have the same C compiler that your Perl build was made with. It looks like if you want to use Inline with mingw, you have to build Perl with mingw.

Win32::API does work however, certainly with simple cases like

use Win32::API;
Win32::API->Import("kernel32", "int GetCurrentProcessId()");
Win32::API->Import("kernel32", "DWORD GetTickCount()");
$PID = GetCurrentProcessId();
print "pid=$PID tick=" . GetTickCount() . "\n";

Perl Win32::OLE

NB that OLE collections are indexed starting at 0.

Although Item may be referred to as a property in documentation, you access it like a method:

print $errorcollection->Item($n)->{Description};

OpenSSH with a domain

Suppose you want to get openssh working on a work machine that's part of a domain THEDOMAIN. You want to run ssh sessions using your usual username THEDOMAIN\dugeen.

Just do the 'domain' steps from the quick start guide:

mkgroup -d >> ..\etc\group
mkpasswd -d -u dugeen thedomain >> ..\etc\passwd

(mkgroup will take a while if THEDOMAIN has a large number of groups).

Then start the service and there you go.

One thing about ssh on windows in general - you won't get command line editing with the arrow keys unless your ssh client supports them itself.

I wonder if having all the groups from THEDOMAIN in etc\group is necessary?

SSH servers for Windows

Don't bother with freesshd, it only works if you can log in as literally 'Administrator'. You won't be able to install it if you use another account with admin rights, or if the name for Administrator in your locale is 'Administrateur' etc.