On-demand SSH Socks proxy through systemd user units with socket-activation doesn't restart as wished

Still testing this (as I am writing this answer, I am using it), but I think the missing ingredient is -o ExitOnForwardFailure=yes as an option to your ssh binary.

For future reference, i’m pasting below the systemd --user configuration files
for on-demand ssh-tunnel using systemd-socket-proxyd daemon,
with various enhancements and explanation comments:

~/.config/systemd/user/ssh-tunnel-proxy.socket

[Unit]
Description=Socket-activation for SSH-tunnel

[Socket]
ListenStream=1000

[Install]
WantedBy=sockets.target

~/.config/systemd/user/ssh-tunnel-proxy.service

[Unit]
Description=Socket-activation proxy for SSH tunnel

## Stop also when stopped listening for socket-activation.
BindsTo=ssh-tunnel-proxy.socket
After=ssh-tunnel-proxy.socket

## Stop also when ssh-tunnel stops/breaks
#  (otherwise, could not restart).
BindsTo=ssh-tunnel.service
After=ssh-tunnel.service

[Service]
ExecStart=/lib/systemd/systemd-socket-proxyd --exit-idle-time=500s localhost:1001

~/.config/systemd/user/ssh-tunnel.service

[Unit]
Description=Tunnel to SSH server

## Stop-when-idle is controlled by `--exit-idle-time=` in proxy.service
#  (from `man systemd-socket-proxyd`)
StopWhenUnneeded=true

[Service]
Type=simple
## Prefixed with `-` not to mark service as failed on net-fails;
#  will be restarted on-demand by socket-activation.
ExecStart=-/usr/bin/ssh -kaxNT -o ExitOnForwardFailure=yes  hostname_in_ssh_config  -L 1001:localhost:2000
## Delay enough time to allow for ssh-authentication to complete
#  so tunnel has been established before proxy process attaches to it,
#  or else the first SYN request will be lost.
ExecStartPost=/bin/sleep 2

Customizations

In the scripts above, you have to replace the following strings:

• 1000 – (ssh-tunnel-proxy.socket file)
  what (host:)port to listen locally for socket-activation of the tunnel,
  e.g. to emulate a local MYSql port.
• 1001 – (ssh-tunnel-proxy.service & ssh-tunnel.service files)
  what local (host:)port to use when proxy-forwarding to ssh service-process;
  just choose an unused port.
• hostname_in_ssh_config – (ssh-tunnel.service file)
  the host-group to connect to, referred in your ssh-config
  (any SOCKS configuration belong there).
• localhost:2000 – (ssh-tunnel.service file)
  the ssh tunnel’s remote host:port endpoint, e.g. where the remote MYSql binds to.
• x2 delay timings (ssh-tunnel-proxy.service & ssh-tunnel.service files)
  they are explained in the comments.
• ssh-tunnel-... – (the prefix of all unit-files)
  make it descriptive of your need for the tunnel, e.g. mysql-tunnel-....
• SSH – (in the Description= directives of all unit-files)
  make it descriptive of your need for the tunnel, e.g. MYSql.

Commands for controlling the tunnel

# After any edits.
systemctl --user daemon-reload
# If socket's unit-file has been edited.
systemctl --user restart ssh-tunnel-proxy.socket

# To start listening for on-demand activation of the tunnel
systemctl --user start ssh-tunnel.socket

# To enable on-demand tunnel on Boot
systemctl --user enable ssh-tunnel-proxy.socket

# To gracefully stop tunnel (any cmd will do)
systemctl --user stop ssh-tunnel.service
systemctl --user stop ssh-tunnel-proxy.service

# To gracefully stop & disable tunnel (till next reboot)
systemctl --user stop ssh-tunnel-proxy.socket

# To view the health of the tunnel
systemctl --user status ssh-tunnel-proxy.{socket,service} ssh-tunnel

# To reset tunnel after errors (both cmds may be needed)
systemctl --user reset-failed ssh-tunnel ssh-tunnel-proxy
systemctl --user restart ssh-tunnel-proxy.socket

Due to the reputation system, I’m not able to comment @ankostis solution…

I suspect that systemd has options to avoid the use of a bash sleep-loop

Yes, there is systemd-notify.

Also incorporating @noam suggestion to use use a socket between systemd-socket-proxyd and ssh to not waste local ports, it would look like the following:

~/.config/systemd/user/ssh-tunnel-proxy.service

[Unit]
Description=Socket-activation proxy for SSH tunnel

## Stop also when stopped listening for socket-activation.
BindsTo=ssh-tunnel-proxy.socket
After=ssh-tunnel-proxy.socket

## Stop also when ssh-tunnel stops/breaks
#  (otherwise, could not restart).
BindsTo=ssh-tunnel.service
After=ssh-tunnel.service

[Service]
ExecStart=/lib/systemd/systemd-socket-proxyd --exit-idle-time=500s ${XDG_RUNTIME_DIR}/ssh-tunnel-proxy

~/.config/systemd/user/ssh-tunnel.service

[Unit]
Description=Tunnel to SSH server

## Stop-when-idle is controlled by `--exit-idle-time=` in proxy.service
#  (from `man systemd-socket-proxyd`)
StopWhenUnneeded=true

[Service]
Type=notify
NotifyAccess=all
## Prefixed with `-` not to mark service as failed on net-fails;
#  will be restarted on-demand by socket-activation.
ExecStart=-/usr/bin/ssh -kaxNT -o ExitOnForwardFailure=yes -o ControlMaster=no -o StreamLocalBindUnlink=yes -o PermitLocalCommand=yes -o LocalCommand="systemd-notify --ready" hostname_in_ssh_config -L ${XDG_RUNTIME_DIR}/ssh-tunnel-proxy:localhost:2000

The ssh_config man page says about LocalCommand "Specifies a command to execute on the local machine after successfully connecting to the server".

ControlMaster=no ensures that the ssh session never gets a master in case multiplexing is used, since it’s town down automatically and would kill all other multiplexed ssh sessions to the same host.

Finally, it might be a good idea to bind the socket port to localhost.

~/.config/systemd/user/ssh-tunnel-proxy.socket

[Unit]
Description=Socket-activation for SSH-tunnel

[Socket]
ListenStream=127.0.0.1:1000
ListenStream=[::1]:1000

[Install]
WantedBy=sockets.target