May 16, 2009 at 7:36 pm
I just got a VPS with Windows Server 2008 on which I installed SQL Server 2008 Express. I am able to connect to this server fine with Shared Memory (default for localhost-running tools). However, I want to manage it remotely using SQL Server Management Studio on a remote workstation.
I have opened SQL Server Configuraion Manager, selected SQLEXPRESS as the service, and enabled Named Pipes and TCP/IP. I tried restarting the database and browser services and even tried rebooting the computer.
The user of all SQL services is Network Service.
I have verified in SQL Server Management Studio on the server itself that 'Allow Remote Connections' is enabled.
I have opened the Windows Firewall control panel and opened 1433/TCP and 1434/UDP (scoped to my remote workstation's IP), and have even tried turning the entire firewall off altogether.
On another machine, running Windows Vista and SQL Server 2008, I am able to remotely use telnet.exe to see if I can connect to its IP on port 1433. This works successfully, it connects fine. But I am unable to do the same thing when trying to use the IP of the Windows Server 2008 VPS I'm trying to set up.
In fact, if I open a cmd console while Remote Desktop'd on the server, I can't even connect to localhost on port 1433 with telnet.exe.
I've Googled and scoured and I can't find anything else to try.
-----
UPDATE: It was pointed out to me that there both "Active" and "Enabled" settings in the Configuration Manager for TCP/IP. I had one set to true and one set to false. I don't know which is which, but I now have both set to true. However, after doing that and setting Port to 1433 and restarting the services and turning off the firewall again, I am still unable to remotely connect.
May 17, 2009 at 5:18 am
Hello,
I couldn’t spot it mentioned in your post (may be I just missed it), so I just wanted to check if Dynamic Ports is turned on e.g. is there a “0” in the TCP Dynamic Ports property?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
May 17, 2009 at 5:25 am
what error do you get?
---------------------------------------------------------------------
May 17, 2009 at 12:47 pm
OK it's working now. This is what the VPS provider support said he did:
I changed the scope for the firewall to include the Internet and in SQL Configuration Manager added the TCP port (1433) to both IP1 and IP All and set the Dynamic Port to blank.
I'm still really confused because ..
I changed the scope for the firewall to include the Internet and
This shouldn’t have mattered because in all my tests I was temporarily turning the entire firewall OFF. I’ve restored the limit to my IP, but added the VPS IP and localhost IP (127.0.0.1). I am still able to connect with his other changes.
in SQL Configuration Manager added the TCP port (1433) to both IP1 and IP All
I thought I did this too, but regardless this shouldn’t have mattered because with it blank it defaults to 1433 (this is well-documented).
and set the Dynamic Port to blank.
Initially he did this on the first config item (IP1), and then I saw that the other config items still had '0'. I thought I followed through and updated all of them to blank, but apparently I didn't as this sounds like he had to go back and do that himself.
I'm not familiar with dynamic ports, it sounds like that was the culprit, but I don't recall running into this problem before while using the default config settings. Is this something new that was just introduced with SQL 2008 or that works differently when SQL runs on Win 2008?
Thanks,
Jon
May 17, 2009 at 1:07 pm
Hello Jon,
SQL Server Express (2005 & 2008) defaults to Dynamic Ports, whereas the Default Instance of other Editions listen on Static Port 1433 (by default).
Having a Zero in the Dynamic Port configuration will have been overriding the Static Port that you entered, and therefore causing your connection problems.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
May 17, 2009 at 1:13 pm
OK. I guess I've always used the MSDN developer edition and that's why I've never run into this problem. Thanks for the insight!!
Jon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply