Unable to reach SQL database through SSH tunnel using sqlcmd

Question

I need to connect to a an Azure SQL DB from my local machine through a jump box (Azure VM). I set up the port forwarding using the command:

ssh -fN -L 41433:my-db.database.windows.net:1433 me@jump-box

I can confirm the tunnel is set up because in verbose mode i see the message

Local connections to LOCALHOST:41433 forwarded to remote address my-db.database.windows.net:1433

Now, when I run

sqlcmd -S 127.0.0.1,41433 -U username -P password -d db 

I get the following error message

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : [Microsoft][ODBC Driver 13 for SQL Server]
[SQL Server]Cannot open server "127.0.0.1" requested by the login.  The login failed..

I have ensured that /etc/ssh/sshd_config has the lines GatewayPorts yes and AllowTcpForwarding yes

Could someone help me figure out what I am doing wrong?

Answer 1

Azure SQL Database needs to know what database instance your username is for. This is normally done with the server name. If you specify 127.0.0.1 as your server name, Azure cannot know your instance.

The best way is to specify the instance in the login name, e.g. [email protected].

sqlcmd -S 127.0.0.1,41433 -U [email protected] -P password -d db 

This way you do not need to modify your hosts-file and you can have connections to multiple Azure SQL Database instances simultaneously with same tunneling. I hope this helps you connecting securely to Azure SQL Database.

Answer 2

Azure matches on server name sent during login process. Hence your error "Cannot open server "127.0.0.1" requested by the login.".

To work around this try adding the Azure hostname with 127.0.0.1 IP to HOSTS file, and then supply the same hostname to sqlcmd, so that the driver resolves the hostname to and connects to the tunnel entrance, and then sends the same hostname as if it wasn't using a tunnel.