9

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?

nihil0
  • 359
  • 2
  • 10

2 Answers2

14

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. username@my-db.database.windows.net. 

sqlcmd -S 127.0.0.1,41433 -U username@yourdb.database.windows.net -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.

Veikko
  • 3,372
  • 2
  • 21
  • 31
  • 1
    I know this is 3 years later... I've done everything thats explained above. But now I get this error Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Cannot open server 'my-dev-sqlserver' requested by the login. Client with IP address 'xxx.xxx.xxx.xxx' is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.. – Madhav Shenoy Jun 16 '21 at 04:36
  • This should be still valid, 3 years is nothing for a robust answer, not even in SOF. I assume your ssh tunnel is working but you have an issue with Azure SQL Database firewall. Make sure your jump box public ip address (the "xxx.xxx.xxx.xxx" part in your comment) is whitelisted in your SQL DB instance firewall settings. If possible, you can also debug that everything else is working properly by temporarily allowing wider traffic to db. – Veikko Jun 16 '21 at 09:10
  • 1
    Actually the xxx.xxx.xxx.xxx is my local ip not the jump box ip :| – Madhav Shenoy Jun 16 '21 at 09:37
  • 1
    I've been losing my mind for days with this and finally figured it out. Since this is the top result on google, here's the answer: https://learn.microsoft.com/en-us/azure/azure-sql/database/connectivity-architecture We're connecting to a gateway which redirects our connection to the actual cluster. This redirection takes place outside our ssh tunnel, which exposes our real IP to the database server. The easiest solution is to change the connection policy to "proxy". – Leonardo Nov 12 '21 at 01:43
  • @Leonardo, thank you. Switching to Proxy fixed the issue for me. – cudima Jan 31 '22 at 19:20
  • @Veikko, thanks very much! ~5 years, this is still golden. I used these instructions to connect to an Azure SQL Database using Azure Data Studio on a mac, jumping over two Red Hat servers. I additionally needed to set 'Trust server certificate' to True, from Advanced Properties. – rothbart May 19 '22 at 11:07
3

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.

Abhishek
  • 31
  • 1