如何从另一台计算机连接到 SQL Server？

How to connect to SQL Server from another computer?

提问by Luisa

I want to connect from home using SQL Server 2005 to another PC.

我想从家里使用 SQL Server 2005 连接到另一台 PC。

I had a look on the msd...but before connecting it says I should connect to another computer using the computer management and it didn't work out....I can only connect to computers from my workgroup?

我查看了 msd...但在连接之前，它说我应该使用计算机管理连接到另一台计算机，但没有成功....我只能从我的工作组连接到计算机？

Thanks, Luisa

谢谢，路易莎

采纳答案by Dani

If you want to connect to SQL server remotly you need to use a software - like Sql Server Management studio.

如果你想远程连接到 SQL 服务器，你需要使用一个软件——比如 Sql Server Management Studio。

The computers doesn't need to be on the same network - but they must be able to connect each other using a communication protocol like tcp/ip, and the server must be set up to support incoming connection of the type you choose.

计算机不需要在同一网络上 - 但它们必须能够使用诸如 tcp/ip 之类的通信协议相互连接，并且服务器必须设置为支持您选择的类型的传入连接。

if you want to connect to another computer (to browse files ?) you use other tools, and not sql server (you can map a drive and access it through there ect...)

如果你想连接到另一台计算机（浏览文件？），你使用其他工具，而不是 sql server（你可以映射一个驱动器并通过那里访问它......）

To Enable SQL connection using tcp/ip read this article:

要使用 tcp/ip 启用 SQL 连接，请阅读这篇文章：

For Sql Express: expressFor Sql 2008: 2008

对于 Sql Express：express对于 Sql 2008：2008

Make sure you enable access through the machine firewall as well.

确保您也启用了通过机器防火墙的访问。

You might need to install either SSMS or Toad on the machine your using to connect to the server. both you can download from their's company web site.

您可能需要在用于连接服务器的计算机上安装 SSMS 或 Toad。两者都可以从他们的公司网站下载。

回答by dyslexicanaboko

Disclamer

This is just some additional information that might help anyone. I want to make it abundantly clear that what I am describing here is possibly:

免责声明

这只是一些可能对任何人有所帮助的附加信息。我想非常清楚地表明，我在这里描述的可能是：

• A. not 100% correct and
• B. not safe in terms of network security.

• A. 不是 100% 正确并且
• B. 在网络安全方面不安全。

I am not a DBA, but every time I find myself setting up a SQL Server (Express or Full) for testing or what not I run into the connectivity issue. The solution I am describing is more for the person who is just trying to get their job done - consult someone who is knowledgeable in this field when setting up a production server.

For SQL Server 2008 R2 this is what I end up doing:

我不是 DBA，但每次我发现自己设置 SQL Server（Express 或 Full）进行测试时，我都会遇到连接问题。我所描述的解决方案更适合那些只是试图完成工作的人 - 在设置生产服务器时咨询该领域知识渊博的人。

对于 SQL Server 2008 R2，这就是我最终要做的：

1. Make sure everything is squared away like in this tutorialwhich is the same tutorial posted above as a solution by "Dani" as the selected answer to this question.
2. Check and/or set, your firewall settings for the computer that is hosting the SQL Server. If you are using a Windows Server 2008 R2 then use the Server Manager, go to Configuration and then look at "Windows Firewall with Advanced Security". If you are using Windows 7 then go to Control Panel and search for "Firewall" click on "Allow a program through Windows Firewall".
   • Create an inbound rule for port TCP 1433 - allow the connection
   • Create an outbound rule for port TCP 1433 - allow the connection
3. When you are finished with the firewall settings you are going to want to check one more thing. Open up the "SQL Server Configuration Manager" locate: SQL Server Network Configuration - Protocols for SQLEXPRESS (or equivalent) - TCP/IP
   • Double click on TCP/IP
   • Click on the IP Addresses tab
   • Under IP1 set the TCP Port to 1433 if it hasn't been already
   • Under IP All set the TCP Port to 1433 if it hasn't been already
4. Restart SQL Server and SQL Browser (do both just to be on the safe side)

1. 确保所有内容都像本教程中一样，这 与上面发布的教程相同，作为“Dani”的解决方案作为此问题的选定答案。
2. 检查和/或设置托管 SQL Server 的计算机的防火墙设置。如果您使用的是 Windows Server 2008 R2，则使用服务器管理器，转到配置，然后查看“具有高级安全性的 Windows 防火墙”。如果您使用的是 Windows 7，则转到控制面板并搜索“防火墙”，然后单击“允许程序通过 Windows 防火墙”。
   • 为端口 TCP 1433 创建入站规则 - 允许连接
   • 为端口 TCP 1433 创建出站规则 - 允许连接
3. 完成防火墙设置后，您将要检查另一件事。打开“SQL Server 配置管理器”找到：SQL Server 网络配置 - SQLEXPRESS 协议（或等效协议） - TCP/IP
   • 双击 TCP/IP
   • 单击 IP 地址选项卡
   • 在 IP1 下将 TCP 端口设置为 1433（如果尚未设置）
   • 在 IP All 下，如果尚未设置 TCP 端口为 1433
4. 重新启动 SQL Server 和 SQL Browser（为了安全起见，两者都做）

Usually after I do what I mentioned above I don't have a problem anymore. Here is a screenshot of what to look for - for that last step:

通常在我做了我上面提到的事情之后，我就不再有问题了。这是要查找的内容的屏幕截图 - 最后一步：

Again, if someone with more information about this topic sees a red flag please correct me.

同样，如果有关于此主题的更多信息的人看到危险信号，请纠正我。

回答by Mujassir Nasir

all of above answers would help you but you have to add three ports in the firewall of PC on which SQL Server is installed.

以上所有答案都会对您有所帮助，但您必须在安装了 SQL Server 的 PC 的防火墙中添加三个端口。

1. Add new TCP Local port in Windows firewall at port no. 1434

2. Add new program for SQL Server and select sql server.exe Path: C:\ProgramFiles\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr.exe

3. Add new program for SQL Browser and select sqlbrowser.exe Path: C:\ProgramFiles\Microsoft SQL Server\90\Shared\sqlbrowser.exe

1. 在端口号的 Windows 防火墙中添加新的 TCP 本地端口。1434

2. 为 SQL Server 添加新程序并选择 sql server.exe 路径：C:\ProgramFiles\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr.exe

3. 为 SQL Browser 添加新程序并选择 sqlbrowser.exe 路径：C:\ProgramFiles\Microsoft SQL Server\90\Shared\sqlbrowser.exe

回答by Simon Hughes

I'll edit my previous answer based on further info supplied. You can clearely ping the remote computer as you can use terminal services.

我将根据提供的更多信息编辑我以前的答案。您可以像使用终端服务一样清楚地 ping 远程计算机。

I've a feeling that port 1433 is being blocked by a firewall, hence your trouble. See TCP Ports Needed for Communication to SQL Server Through a Firewallby Microsoft.

我有一种感觉，端口 1433 被防火墙阻止了，因此您遇到了麻烦。请参阅Microsoft通过防火墙与 SQL Server 通信所需的 TCP 端口。

Try using thisapplication to ping your servers ip address and port 1433.

尝试使用此应用程序 ping 您的服务器 IP 地址和端口 1433。

tcping your.server.ip.address 1433

And see if you get a "Port is open" response from tcping.

并查看您是否从 tcping 收到“端口已打开”响应。

Ok, next to try is to check SQL Server. RDP onto the SQL Server computer. Start SSMS. Connect to the database. In object explorer (usually docked on the left) right click on the server and click properties.

好的，接下来尝试是检查SQL Server。RDP 到 SQL Server 计算机。启动 SSMS。连接到数据库。在对象资源管理器（通常停靠在左侧）中，右键单击服务器并单击属性。

alt text http://www.hicrest.net/server_prop_menu.jpg

替代文字 http://www.hicrest.net/server_prop_menu.jpg

Goto the Connections settings and make sure "Allow remote connections to this server" is ticket.

转到连接设置并确保“允许远程连接到此服务器”是票证。

alt text http://www.hicrest.net/server_properties.jpg

替代文字 http://www.hicrest.net/server_properties.jpg

回答by Shakaib Akhtar

Here is the link ... You must read it.

这是链接......你必须阅读它。