SQL managed instance with Bastion Host SSH tunnel

Question

hello! I'm looking for some azure help. I am trying to use the azure bastion host and using it as a tunnel to connect to a vm. I want use this tunnel to connect to the sql managed instance. I can ssh into the vm. I can connect to the sql db from the vm. I cant connect to sql using dbeaver or tableplus on my laptop using the vm as an ssh tunnel. the network security groups for bastion, vm, and sql are wide open for incoming and outgoing.

The problem seems to be that when going over 1433 port sql gives back another port for the connection, here's and example of the error:

The TCP/IP connection to the host 127.0.0.1, port 21329 has failed. Error: "The driver received an unexpected pre-login response. Verify the connection properties and check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. This driver can be used only with SQL Server 2005 or later."  

so I have a few questions.
How do you connect to the azure sql from outside the vnet?
Has anyone seen this before and know a solution?
Is there a way to bind ports on SQL managed instance?

things i've tried:
public endpoint on sql (works, just don't want to use it for security reasons)
site-to-site vpn using another vm as bastion
ssh tunnel 1433 on my laptop to the vm that i have an ssh tunnel connected to
sshuttle on my laptop and foward all traffic to the vm on ssh tunnel
multiple sql managements, tableplus, azure data studio, dbeaver
sql-cli (works on vm, not mine)
opened all traffic and ports for incoming and outgoing on network security groups
private endpoint connected to sql and the vm subnet
Proxy and redirect sql connection
Created a SQL server instead of an managed instance
in all instances i have used username and username@Testta for username
Created MSSQL on AWS with similar setup (works)

things i haven't tried yet:
point to site VPN

Things I can't try:
Give the vm a public ip. I have a security policy in place to deny public IPs on network interfaces

Answer 1

Hi @Anonymous ,

How do you connect to the azure sql from outside the vnet?

How about trying connecting by DNS and you can check this link, following the steps on it :https://social.technet.microsoft.com/wiki/contents/articles/36674.access-azure-vm-sql-server-outside-of-network.aspx#:~:text=Access%20Azure%20VM%20SQL%20Server%20Outside%20of%20Network,5%20Step%205%3A%20Access%20from%20different%20machine.%20

Has anyone seen this before and know a solution?

Pls take the following steps:

1. Open SQL Server Configuration Manager, and then expand SQL Server 2005 Network Configuration.
2. Click Protocols for InstanceName, and then make sure TCP/IP is enabled in the right panel and double-click TCP/IP.
3. On the Protocol tab, notice the value of the Listen All item.
4. Click the IP Addresses tab:
   If the value of Listen All is yes, the TCP/IP port number for this instance of SQL Server 2005 is the value of the TCP Dynamic Ports item under IPAll.
   If the value of Listen All is no, the TCP/IP port number for this instance of SQL Server 2005 is the value of the TCP Dynamic Ports item for a specific IP address.
5. Make sure the TCP Port is 1433.
6. Click OK.

Is there a way to bind ports on SQL managed instance?

You can set the ports number in SSCM----SQL Server Nerwork Configuration----TCP/IP

Delete the 'zero' in the dynamic ports window
Then enter TCP port: user-defined

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

Answer 2

I was able to get this to work NOT with managed instances but with SQL Server. I had to do the following:

• use proxy connection instead of "default"
• Add a private endpoint and target the bastion VM
• disable public endpoint
• use az network bastion tunnel
• use TablePlus with the VM as an ssh tunnel

not sure why this works with Azure SQL and not SQL managed instance.

Answer 3

Hi @Anonymous Thank you for sharing the steps above on how to connect Bastion with SQL server.
Bastion is used for connecting to VMs. Since you cannot connect to the os of an MI I don't see that being an option. You could create a VM connect to via bastion and then use your tool of choice on that vm to connect.
Please let me know if that works.

Regards,
Oury

Answer 4

I resolved this by doing the following, note I am using Terraform to complete these:

• use SQL with DB instead of MI
• bastion, VM, and SQL is on the same VNet, separate subnet
• add a private endpoint for SQL
• remove public access on SQL
• add private DNS for the private endpoint
• create a VNet link on the private zone of the DNS
• allow access from the NSG for SQL
• connect to Bastion with az network bastion tunnel with a port (I used 50022)
• use 127.0.0.1 on port 50022 as an SSH tunnel with a SQL management tool (I used TablePlus)
• use the private DNS for the private endpoint as the host

This works. I did not try this with MI and the private endpoint, since it's in preview