visual-studio 在 Visual Studio 或 Sql Server Management Studio 中连接到 Web 主机上的数据库

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

提问by Anders

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.

我有一个使用本地 Sql Server 数据库在本地开发的网站。我还有一个 Web 主机，它为我的站点提供一个 Sql Server 数据库。现在我想部署应用程序，我希望能够从 Visual Studio 中的服务器资源管理器管理远程数据库。我在应用程序中使用了连接字符串，它可以很好地将数据源添加到控件等。但我不知道是否有任何方法可以使用它来连接服务器资源管理器中的数据库，以便我可以添加表等。我已经读到你应该能够做到这一点，而不是使用 Sql Server Management Studio，但我没有读到任何关于如何连接到其中的远程数据库的信息。

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.

到目前为止我尝试过的是：我在服务器资源管理器中选择了添加数据库。这首先会出现一个对话框，我在其中选择了 Sql Server。然后我得到一个对话框，我可以在其中设置服务器名称（我尝试使用下面连接字符串中的 ip 地址）和身份验证（我选择了 Sql Server 身份验证，使用下面的用户 ID 和密码）。但是当我测试连接时它失败了。

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:

好吧，我现在已经完成了下面 lewiguez 建议的所有事情，但这没有任何区别。我不敢相信这会这么难……我在 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)

与 SQL Server 建立连接时发生与网络相关或特定于实例的错误。服务器未找到或无法访问。验证实例名称是否正确以及 SQL Server 是否配置为允许远程连接。（提供程序：命名管道提供程序，错误：40 - 无法打开与 SQL Server 的连接）（Microsoft SQL Server，错误：53）

回答by lewiguez

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.

1. 在 VS 2008 中的服务器资源管理器选项卡下，右键单击“数据连接”，然后单击“添加连接”
2. 将数据源更改为“Microsoft Sql Server”
3. 输入完全限定的“服务器名称”（IP 地址、域名等），选择“Sql Server 身份验证”并输入数据库用户的“用户名”和“密码”
4. 在“选择或输入数据库名称”下选择要连接的数据库
5. 点击“测试连接”进行测试，然后点击“确定”接受
6. 此时，您应该能够通过展开连接和“表”文件夹来与数据库进行交互。例如，右键单击“表格”文件夹，您将在对话框中看到“添加表格”选项。右键单击现有表将显示“打开表定义”选项，您可以在其中更改任何列等。

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.

如果您还没有到这一步（听起来好像还没有），我建议您从您正在使用的任何机器上检查您的 Sql Server 连接。

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).

我通常通过进入 Management Studio 并连接我尝试使用的任何凭据来执行此操作。如果它们不起作用，请确保它们实际上是作为 Sql Server 用户和数据库用户添加的（它们必须同时是）。

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.

另外，我会检查 Sql Server Surface Area Configuration Manager。您必须允许 TCP 连接，并且必须设置为允许来自远程主机的 Sql Server 身份验证连接。如果这一切都设置好了，但您仍然无法连接，我会再次检查以确保您的防火墙允许 Sql Server 连接。默认端口为 1433。

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

此外，如果您可以连接，但无法写入任何内容，请仔细检查您的表权限。希望这可以帮助！

回答by Anders

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.

我终于明白了这个问题：因为我怀疑我自己安装中的设置不是问题，而是我的 web 主机上 Sql Server 安装的设置。事实上，我在我的 Web 主机的控制面板中找到了一个地方，我可以在其中为我的 ip 地址设置一个例外，以便我的 ip 可以访问 Sql Server 的端口 1433，否则出于安全原因，默认情况下该端口是关闭的.

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/)

我没有运气在互联网上找到任何关于此的信息，我觉得这很奇怪。我能找到的所有信息都与在您自己的 Sql Server 本地安装上更改这些设置有关。但我确信有很多人像我一样使用网络主机来部署他们的网站，然后这些信息似乎并不适用。（也许除了 TCP 设置，我认为必须设置，这里有关于它的详细信息：http: //www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server -2008-快递/)

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.

希望这可以帮助像我一样使用网络主机的其他人。现在至少对我来说一切正常，在 Management Studio 和 Visual Studio Server Explorer 中。