4

I would like to test a website in development hosted in a remote location against a sql server instance on my local machine. I was thinking I could run ngrok locally and update the connection string on the remote website appropriately, but I'm not sure what parameters I need to specify.

I've tried ngrok tcp 12345 and ngrok http 12345

In SQL Server Configuration Manager I've enabled TCP/IP for the SQL Express instance I want to use and set the port to listen on to 12345 (actually, a different number, but we'll say 12345 for the sake of this post). And I then restarted SQL Server.

I don't believe it's necessary for me to make holes in the firewall, but just in case I've temporarily disabled it entirely while I test this.

To test locally, I am taking the url ngrok provides and putting it in the connection dialog in SQL Management Studio, but I can't get it to connect.

What am I missing?

BVernon
  • 3,205
  • 5
  • 28
  • 64

2 Answers2

12
  1. Set up Ngrok
  2. Run ngrok on tcp on the SQL port (by default is 1433) ngrok tcp 1433

You will get an url like tcp://3.tcp.ngrok.io:12345

In the "Connect to Server Dialog" type:

  • Server name: 3.tcp.ngrok.io,12345 Notice the comma between the port and the url. Type the url without tcp://
  • Login: your regular user, in my case sa and your password
  • Connect
Dharman
  • 30,962
  • 25
  • 85
  • 135
KABU09
  • 160
  • 2
  • 7
1

The "URL" that ngrok generates for a TCP tunnel probably shouldn't be used unmodified. The tcp:// scheme part isn't a URI scheme in common use I don't think. Only the hostname and port part of the generated ngrok "URL" would be useful in a SQL Server connection configuration.

Not being intimately familiar with the referenced connection dialog in SQL Management Studio, it's hard to say exactly what input is expected, but maybe stripping out the tcp:// scheme will do the trick?

Andrew Benton
  • 73
  • 5
  • Yes, I tried both ways. Typically with an IP address you just put the IP address with no scheme. But you got me to thinking I can at least try to connect to my SQL instance from another computer on the same network and rule out the SQL Configuration as being an issue. – BVernon Feb 26 '21 at 20:36
  • Were you able to connect from another computer on the same network @BVernon? – Andrew Benton Mar 01 '21 at 22:03
  • I didn't get to it and I no longer need to. At some point when I have time I will probably test it out just because I'm curious, but a bit busy at the moment and it's no longer a blocking issue for me. I'll come update the post if/when I find anything out. I will mention though that the connection string I'm using now starts like this ""Data Source=tcp:subdomain.domian.com;" and I had not previously tried doing it that way without the double slash from management studio so that might be it. – BVernon Mar 02 '21 at 04:05
  • On the other hand I've never started a connection string with "tcp:" at all and suspect that could be omitted but I just used the string my web host specified. – BVernon Mar 02 '21 at 04:07