Connecting to database on web host in Visual Studio or Sql Server Management Studio

Question

I have a web site developed locally with a local Sql Server database. I also have a web host that provides one Sql Server database for my site. Now I want to deploy the application, and I would like to be able to manage the remote database from the Server Explorer in Visual Studio. I have the connection string used in the application, which works fine for adding, say, a datasource to a control etc. But I don't know if there's any way to use it to connect the database inside the Server Explorer so that I can add tables etc. I have read that you're supposed to be able to this instead of using the Sql Server Management Studio, but I have'nt read anything about how to connect to the remote database in it.

What I have tried so far is this: I have selected Add database in Server Explorer. This brings up first a dialog where I choose Sql Server. And then I get a dialog where I can set Server name (which I tried using the ip address in the connection string below), and Authentication (where I chose Sql Server Authentication, with the user id and password from below). But when I test the connection it fails.

Here's the connection string, which works fine when used for datasources in the application (obviously with different user name and password):

Any help appreciated!

EDIT:

Well, I've done everything suggested by lewiguez below now, but it doesn't make any difference. I can't believe this should be so hard... I keep getting this error message in Sql Server Management Studio:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

What is wrong? Please help...!

Answer 1

Here's the process I follow for working with remote databases:

1. In VS 2008 under the Server Explorer tab, right-click on "Data Connections" and then click "Add Connection"
2. Change the Data Source to "Microsoft Sql Server"
3. Put in your fully-qualified "Server Name" (be it IP Address, domain name, etc.), select "Sql Server Authentication" and put in "User Name" and "Password" for the database user
4. Select the database you want to connect to under "Select or enter a database name"
5. Hit "Test Connection" to test and then "OK" to accept
6. You should be able to interact with the database at this point by expanding the connection and then the "Tables" folder. For example, right-click on the Tables folder and you'll be presented with an "Add Table" option in the dialog. Right-clicking an existing table will bring up the "Open Table Definition" options where you can change any columns, etc.

If you're not getting to this point (and it sounds like you're not), I'd recommend checking your Sql Server connection from whatever machine you're using.

I generally do this by going into the Management Studio and connect with whatever credentials I'm trying to use. If they don't work, make sure they are, in fact, added as a Sql Server user and a database user (they have to be both).

Also, I would check in the Sql Server Surface Area Configuration Manager. You have to allow TCP connections and you have to be set up to allow Sql Server Authentication connections as well from remote hosts. If THAT'S all set up, but you still can't connect, I'd double-check to make sure your firewall is allowing Sql Server connections. The default port is 1433.

Also, if you can connect, but then can't write to anything, double-check your table permissions. Hope this helps!

Answer 2

I finally understood the problem: as I suspected the settings in my own installation was not the problem, but rather it was the settings on the Sql Server installation on my web host. And in fact I found a place in the control panel on my web host where I could set an exception to my ip address so that my ip would be granted access to the port 1433 for Sql Server, which is otherwise closed by default for security reasons.

I've had no luck finding any information about this at all on the internet, which I find strange. All the information I could find had to do with changing these settings on your own local installation of Sql Server. But I'm sure there are loads of people out there who like me use a web host for deploying their web site, and then that info doesn't seem to apply. (Perhaps apart from the TCP setting, which I think must be set, and there's detailed info about that here: http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/ )

Hope this helps someone else who like me uses a web host. Now everything works fine for me at least, both in Management Studio and in Visual Studio Server Explorer.