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.

Beaulahbeaulieu

This is an old question, but I guess, every once in a while it pops up since it is rarley covered in books. It is not uncommon to create a web service in between your clinet application and teh database. However, as with many senarios, there may be security concenrns to check. – Call

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.

Cox

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

Callahan

Answer 3

It is possible, but a lot of things need to align for you to be able to connect.

Enterprise Manager and SQL Management Studio just use a standard client connection to SQL. The default TCP/IP port is 1433, but the host needs to allow that port through the firewall. If you are using a named instance, then I believe you also need to be able to connect to port 1434.

Most administrators do not allow direct access to the SQL Server from outside the firewall. In that case, if you can connect to the host over VPN then you should be able to connect directly to the server with Enterprise Manager or SQL Management Studio.

Loisloise