Connect to IP-restricted Azure MSSQL database via tunnel
I got access to an Azure database via SQL login. However, the login is restricted to the IP addresses of the live servers and I want to access it from my computer at home from varying IP addresses. My approach was to tunnel the connection through ssh exampleserver -L 1433:example.database.windows.net:1433
However, when establishing a connection to 127.0.0.1:1433, I got the error A connection was successfully established with the server, but then an error occurred during the pre-login handshake. I use the MSSQL client of VS Code. The issue was, that the server certificate does not match the connection URL, which had to be 127.0.0.1 due to the tunneling. In order to trick this unwanted validation, I found out the IP of the database server (example.database.windows.net) and set up the tunnel using the IP instead of the domain. Then I added 127.0.0.1 example.database.windows.net
to the hosts file and connected with the domain name. This approach worked for me.
I tried to go further and set "trustServerCertificate": true
as described here https://github.com/microsoft/vscode-mssql/issues/1806, but that didn't work for me. Editing the settings.json turned out to be tricky, because the password needs to be entered each time before saving. Otherwise, it seems to get erased by VS Code, causing the next login error. Setting the trustServerCertificate option consistently caused login errors.