I am currently enjoying my familiarity with ssh tunnels, making me feel all smug and secure. Today, I ran up a new AWS SQL Server Instance and this box runs SQL Server 2008 Web Edition. I didn't particularly choose it but it was one of the pre-built appliances so decided to go with it.
I then did what I have done 10s of times and setup a new ssh tunnel via a Linux box on AWS to the RDP and SQL Server ports so I can securely remotely access the box. I do this so that only the boxes on AWS are allowed to access the database/web servers directly and anything else therefore must securely tunnel in.
Once setup, I ran up RDP with no problems but I couldn't get management studio to play ball and having a system with lots of variables just made it a little harder to track down.
This is what I ended up doing to make it work.

  1. Checked that the box was generally accessible by logging into the tunnel server and pinging the expected IP address - this demonstrated that ICMP/Echo Request is not enabled by default on the Windows firewall.
  2. Doing the same thing from the tunnel server by running nmap -PN -p 1433 which ensures that the port is opened by Windows Firewall (which it was)
  3. Logged into the new SQL box and setup the sa user and a normal web site user. sa is disabled by default so I enabled it and set the password to a known value.
  4. This was a biggie: Choose your database properties menu and change the authentication to use BOTH windows and SQL authentication, it just uses Windows by default which means you won't be able to login even with the correct sa credentials. Once changed, you must restart the SQL Server Service for the change to take effect.
  5. Setup a local tunnel (in my case using PuTTY) to redirect a local port via the tunnel server to my SQL box port 1433.
  6. Run up sql management studio and choose "connect". Put in the local address and port using a COMMA between host and port e.g. 127.0.0.1,2008 and then choose options and select TCP/IP as the connection mechanism. I would suspect named pipes would not work (and this is chosen because 127.0.0.1 is localhost and the system assumes it can connect directly).
  7. If you still can't connect, you can check the Windows firewall on the SQL box. Note it had port 1433 already open (and not locked down, you might want to tighten it up (or not)). Also note that ping - which is listed under File and Printer Sharing - Echo Request is not enabled by default and this might be useful for you when fault finding.
  8. Also, you might need to enable the SQL Server Browser service on the SQL box which for me was not enabled by default and I think is needed if you don't specify an instance name in your connect box (which I didn't). The service itself was disabled so needs to be enabled and started.
As with all fault finding, the best way is to try and reduce the number of variables involved. For instance, because I could connect via a tunnel to the new box for RDP, that told me that a lot of things were working OK and reduced it to a SQL server issue.