A similar question was answered here, and the option of opening an SSH tunnel was lightly touched, but the consensus was that VPN tunnels would be the easiest solution, which is true if the client is an "internal" user. This question is a little more specific for external customers. Some problems with the VPN solution are:
- Other trafic trying to run through the VPN connection
- Customers could potentially "see" each others networks (can be
resolved by closing most ports on the firewall) - Windows Updates often break the VPN connectivity
All of these issues can be fixed, but it has proven to be a clunky solution.
The most obvious option would be to wrap all database access into API calls or use a 3rd-party ODBC driver. I am looking for a solution using TLS tunnels (SSL has been deprecated) in the same way Azure database connections are established.
I have previously used the netsh command in Windows to change the port of a local service. Could TLS tunneling be added in a similar fashion?
On the client side there already is native support by adding Encrypt=True to the connection string. This is a screenshot of the SSMS client options:
So my main questions are:
- What are the steps to enable a TLS tunneled (non-standard) port to SQL server
- On the SQL server service (if any)
- On the Machine running SQL server (can we use something like the
netshtool or do we need to install a proxy of some sort?) - On the Firewall
- Would we need to purchase any "special" type of certificate (we don't want to use self-signed in order to prevent man-in-the-middle attacks) or would the same kind as regular websites use also work?
- Anything else we should keep in mind in order to smoothly roll this out to thousands of clients?
Best Answer
Essentially Azure (assuming you mean Azure SQL, and not SQL Server installed in an Azure VM or other options) opens the virtual SQL server's interface address to the world and you control access with settings in the security/firewall section of the portal (or the related API calls).
To mimic this with an on-prem SQL server set a port-forwarding rule on your edge firewall to allow connections to the SQL instance's TCP port (1433 for the default instance, unless you've changed it), with rules to only allow connections from specific source addresses (to mimic the rules settable in Azure). If you have multiple instances you will want to have them on fixed ports or you will need to check/reconfigure your port forwarding rules every time instances are restarted. If you have multiple public IP addresses you can make each instance be on the standard port 1433 if you desire, by having each on a different address, or mimic the same port numbering seen internally.
Opening SQL Server to your clients directly is usually not recommended though, a VPN or tunnelling arrangement is preferred. Even if a direct connection to SQL Server is secure enough, a zero-day SQL authentication flaw or a leaked SQL password doesn't immediately expose you to danger as an attacker will need to authenticate at the prior level before launching any exploits against you.
What do you mean by that? Ruled out by your company's policies? SSH itself is very much not deprecated, and is in fact gaining wider support as MS are including a native port of OpenSSH in future Windows Server versions so there will be no need to use other options (Cygwin, a Linux or other unix-a-like VM, Windows Subsystem for Linux, a separate unix-a-like machine) if you don't need them for other reasons.
The ODBC driver you link to specifically mentions support for SSH based tunnelling, so yes. You'll have to check their documentation for how to go about this.
To use SSH tunnels more manually instead of a 3rd party driver you just need an SSH server running on the same network, configured to allow tunnelling, and the SSH port open via your firewall the same way you would do for allowing direct SQL Server connections from the outside. You will then need to manage the accounts/passwords/keys on that SSH service. Accessing the SQL instance(s) via the tunnel is then as simple as
ssh user@111.111.111.111 -L1433:222.222.222.222:1433where 111.111.111.111 is the relevant public address (or DNS name for it) and 222.222.222.222 is the internal address of the SQL server. The client then connects tolocalhost(127.0.0.1). If they have a local instance listening on port 1433 then they'll need to change the tunnel specification to something else, for instance-L12345:222.222.222.222:1433then they would connect tolocathost:12345.I'll not go into further detail as we are off-topic for your question title "using TLS tunnels" as SSH is not TLS-based.
You might need to flesh out your question to include what you mean by this, as the phrase could refer to one or more of several things (multiple clients using the same DBs, using different DBs on the same instances, using different instances on the same servers/network, ...).
TLS Tunneling via Proxy (with regard to the updated question)
Simple TLS proxies for plain services do exist, I've heared of a number inthe past but I'm no expert as I've never had the need to use them (using SSH or VPNs instead for non-web traffic and HTTP specific tools for HTTPS). With that sort of solution you need one half of the proxy installed on the client machines, as well as the server half inside the firewall (with port mapping(s) through to it) at the server end. The client-side accepts the plain connection, makes an encrypted link to its server side through which the transfers are proxied, and the server-side makes the real (plain) connection to the server.
https://github.com/square/ghostunnel is the first example that came from a quick search for "TLS proxy". You'll no doubt find others with a deeper search.
But, the need for client-side installation may make this sort of solution a no-go for your clients.
Built In Encryption Support
A quick test locally suggests that the encryption options "just work". If you have not installed a "proper" certificate on the server the client's connection will need to use the "trust server certificate" option but this leaves you vulnerable to MitM attacks.
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine suggests setting it up to use a real validated certificate is not difficult (the "Server Authentication" requirement is covered by certificates as normally issued for HTTPS). SSL certificates for single names are cheap these days, in fact another quick search suggests that it isn't difficult to use LE's free certificates: https://sqlsunday.com/2017/11/22/encrypting-tds-with-letsencrypt/
As MS seem to think this feature is secure enough for Azure SQL and I've not heard the hoo-hah you'd expect from it not being, this is probably the way to go unless your clients are using software that doesn't support encrypted TDS connections.
Make sure that you enable the "force encryption" option for each SQL Server instance otherwise your clients may forget and leave their transmissions open.