Support connections over SSH

[steffan-c]

Great extension!

A common solution for production environments is to require a connection over an SSH tunnel. It is possible to achieve this by opening the tunnel on the command-line and then creating the DB connection to connect to localhost and whatever port has been used for the near end of the tunnel. However, it would be far more convenient to specify the tunnel as part of the DB connection (and so have SQLTools manage opening and closing the tunnel).

Most any other database UIs support this - for example, see the UI in Valentina Studio for a free example.

[cbertozzi]

+1 votes

[mtxr]

Hey guys, that's something I really want to do, but still have to work in some stuff before that.

Since we have native tunnels as a workaround, I don't consider this high priority for now. See https://github.com/mtxr/vscode-sqltools/issues/230#issuecomment-494624547

I won't close this because I feel we are close to work on this now. But for now, please, try using native tunnels, ok?

[NelsWebDev]

+1

[TsimafeiTsykunou]

+1

[1yuv]

+1 This would be much appreciated. Also have there been any progress or good news for us @mtxr 😄 ?

[ianp]

FYI: at least for Postgres you can do this via the native driver, you'll need to edit the connection in settings.json to add a section like this

"pgOptions": {
  "native": true,
  "ssl": { "rejectUnauthorized": false }
}

(ssl can just be true as well, but those settings are what I use to connect to a DB on Heroku)

[Hipska]

I won't close this because I feel we are close to work on this now. But for now, please, try using native tunnels, ok?

Hi @mtxr, what is the status on this?

[pbassut]

@mtxr well, yeah, you can do ssh tunnels for now but that's just an extra step and it won't be saved into vscode

[mtxr]

Still no updates on this folks, sorry for that.

Has been hard to maintain the project for the past few months, but I'm trying to get back on track.

PR's are welcome if anyone want's to take a chance on this one

[elpapi42]

This will be a awesome feature, for a normal workflow the workaround is suboptimal

[govindasharma1986]

+1 It would much appreciated!

[pbelosa]

+1 Needed this feature today and have installed beekeeper instead for now. Would love to see this!

[lionslair]

+1

[gjsjohnmurray]

How about using https://marketplace.visualstudio.com/items?itemName=ms-vscode-remote.remote-ssh and connecting VS Code over SSH to your remote server?

[jbwl]

+1

[kfirufk]

+1 still really need this

[sluxzer]

Looking for update for this one :)

I would love to help

[tlil]

How about using https://marketplace.visualstudio.com/items?itemName=ms-vscode-remote.remote-ssh and connecting VS Code over SSH to your remote server?

This works great for me :+1:

[hunsakerbn]

For 1 connection, native tunnels might be OK, but when I have many database sources, and want several of them to be active at the same time, then the tunnel information should be part of each databases connection information. Please add this feature.

Thanks.

[TheNathanBlake]

For 1 connection, native tunnels might be OK, but when I have many database sources, and want several of them to be active at the same time, then the tunnel information should be part of each databases connection information. Please add this feature.

Thanks.

+1 I'm in the same boat. Multiple hosts for multiple DB's means using shell to SSH is a mini pain that we don't have to endure with other DB tools.

[gitlui]

Hi there, as this is a feature I would need myself, and it is marked as "help wanted" I would like to try implementing it.

I was thinking about a simple, solution where one can just put some parameters in the configuration that are then passed to https://www.npmjs.com/package/tunnel-ssh to establish a tunnel.

It doesn't sound too bad right now.

What do you think? If you are fine with me trying, I will start on the Weekend.

[gjsjohnmurray]

@gitlui my initial thoughts:

1. Add an sshTunnel object to the connection settings object, initially with optional properties port and username, e.g.

{
  "name": "MySQL through tunnel",
  "server": "dbhost",
  "driver": "MySQL",
  "port": 5433,
  "database": "test_db",
  "username": "root",
  "askForPassword": false,
  "password": "root",
  "connectionTimeout": 15,
  "sshTunnel": {
    "port": 22, // default to 22 if not set
    "username": "jdoe" // default to same as connection username if not set
  },
  "mysqlOptions": {
    ...
  },
}

2. Enhance this function: https://github.com/mtxr/vscode-sqltools/blob/f7a7b998ab1c14000733a567dfcc1438d0f7f887/packages/plugins/connection-manager/extension-util.ts#L87-L97

adding something like this pseudocode right after line 95, conditional on connInfo.sshTunnel not being undefined and connInfo.server not being localhost (in order to prevent recursion in the event that the function is called on an already-tunnelled connection):

• use connInfo.sshTunnel.port (default 22) to set up a tunnel to connInfo.port on connInfo.server, letting the OS assign the local port. For SSH credentials, use connInfo.sshTunnel.username (fall back to connInfo.username) and get the password by invoking our built-in AuthenticationProvider in the same way as drivers such as the MySQL one do when configured to:

https://github.com/mtxr/vscode-sqltools/blob/f7a7b998ab1c14000733a567dfcc1438d0f7f887/packages/driver.mysql/src/extension.ts#L105-L120

except that I think the scopes (L105) should be ['sshTunnel', connInfo.name, sshUsername)] and the session.accessToken retrieved (L119) would be used for setting up the tunnel, not to update connInfo.password.

• update connInfo.server and connInfo.port to point to localhost and the OS-assigned local port.

[gitlui]

Nice. I will try to do this! (sry, I used the wrong account first)

[Dees7]

I think you should use both server and ssh-host separately. Cause usually DB listens localhost (not public name\ip). And you can't use localhost for ssh connecting. For me we have jump server (not DB host proper)

"server": "localhost",
"sshTunnel": {
   "sshhost": "my_jump_host",
   "port": 22, // default to 22 if not set
   "username": "jdoe" // default to same as connection username if not set
},

[gitlui]

I already started a bit with this topic but had not much time the last weeks. At work, we often have constellations where I need an SSH tunnel to a server which has a connection to the SQL DB on another server. So I would have treated them like 2 different IPs anyway. Additionally, I would like to add the possibility to select an ssh file to use, as I usually have different ones for different customers I work for. But I still need to invest a bit more time into understanding the project and how the connection will/should work.

[lamualfa]

+1

[steal9pro]

+1

[ozkankirik]

+1