How do I connect & administer an SQL Server database remotely over the Internet?

Question

How do I connect to an SQL Server remotely and administer my database objects? I need to connect to my database located on the web hosting company's server. I have the server name, IP address, my database username & password. I have an installation of SQL Server 2000 in my machine.

Can I use SQL Server Enterprise Manager for this? A step by step guide would be very helpful.

Answer 1

Yes, you can use Enterprise Manager (or SQL Server Management Studio, even if it's an Express version) to connect to any SQL Server (of the same or lower version as the tool you're using) you have TCP/IP connectivity to. Just launch it, enter the DNS host name or IP address in the 'Server Name' box and hit Connect.

Two things may prevent this from working:

• Your SQL Server isn't set up for TCP/IP connectivity. This is the default setting from version 2005 onwards, and can be changed using the SQL Server Configuration Manager.

• There is a firewall between you and your SQL Server blocking TCP/IP traffic. This is an entirely sensible construction: you do NOT want your database server to be available from the general Internet, as this is a huge security risk. In fact, if your hosting company allows this kind of access by default, I'd be looking for a different provider...

Anyway, what seems to be needed in your scenario is: a) the hosting company enabling TCP/IP on your SQL Server instance, b) them providing you with secure access to the IP address that instance is running on. This will involve some kind of VPN or SSH port-forwarding soluition, or at least an IP filter, where only 'trusted' IP addresses can access the SQL Server (which is not a great solution, but can be used if nothing else can be implemented). You'll have to discuss these requirements with your hosting company.

Answer 2

Yes you can do this but how you do this will be dependent on your hosting setup.

As Brannon suggests you will need to open port 1433 on the firewall. This would be a dangerous thing to do on its own so you would combine it with a VPN. It can be quite tricky to set this up through and you could end up blocking yourself from access to the server and needing the hosting companies help to reset it.

Another option would be some of the web management tools such as: http://www.microsoft.com/downloads/details.aspx?familyid=C039A798-C57A-419E-ACBC-2A332CB7F959&displaylang=en http://www.mylittleadmin.com/en/welcome.aspx