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

时间:2021-05-10 16:30:57

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

谢谢,路易莎

4 个解决方案

#1


15  

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

如果要远程连接到SQL服务器,则需要使用软件 - 如Sql Server Management工作室。

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: express For Sql 2008: 2008

对于Sql 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。你可以从他们公司的网站上下载。

#2


22  

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:

Disclamer这只是一些可能对任何人有帮助的其他信息。我想非常清楚地说明我在这里所描述的可能是:

  • A. not 100% correct and
  • A.不是100%正确和
  • B. not safe in terms of network security.
  • 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 tutorial which is the same tutorial posted above as a solution by "Dani" as the selected answer to this question.
  2. 确保所有内容都像本教程中那样平方,这是上面作为“Dani”作为此问题的选定答案的解决方案发布的相同教程。
  3. 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
    • 为端口TCP 1433创建入站规则 - 允许连接
    • Create an outbound rule for port TCP 1433 - allow the connection
    • 为端口TCP 1433创建出站规则 - 允许连接
  4. 检查和/或设置托管SQL Server的计算机的防火墙设置。如果您使用的是Windows Server 2008 R2,请使用服务器管理器,转到“配置”,然后查看“具有高级安全性的Windows防火墙”。如果您使用的是Windows 7,请转到“控制面板”并搜索“防火墙”,然后单击“允许程序通过Windows防火墙”。为端口TCP 1433创建入站规则 - 允许连接为端口TCP 1433创建出站规则 - 允许连接
  5. 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
    • 双击TCP / IP
    • Click on the IP Addresses tab
    • 单击“IP地址”选项卡
    • Under IP1 set the TCP Port to 1433 if it hasn't been already
    • 在IP1下,如果尚未将TCP端口设置为1433
    • Under IP All set the TCP Port to 1433 if it hasn't been already
    • 在IP All下,如果TCP端口尚未设置,则将其设置为1433
  6. 当您完成防火墙设置后,您将要再检查一件事。打开“SQL Server配置管理器”找到:SQL Server网络配置 - SQLEXPRESS的协议(或等效的) - TCP / IP双击TCP / IP单击IP地址选项卡在IP1下,如果TCP端口没有,则将TCP端口设置为1433已经在IP下全部将TCP端口设置为1433(如果尚未安装)
  7. Restart SQL Server and SQL Browser (do both just to be on the safe side)
  8. 重新启动SQL Server和SQL浏览器(两者都只是为了安全起见)

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:

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

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

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

同样,如果有关于此主题的更多信息的人看到红旗,请更正我。

Hope this helps.

希望这可以帮助。

#3


2  

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

    在端口号的Windows防火墙中添加新的TCP本地端口。 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

    为SQL Server添加新程序并选择sql server.exe路径: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

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

#4


1  

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 Firewall by Microsoft.

我感觉端口1433被防火墙阻挡,因此你的麻烦。请参阅Microsoft通过防火墙与SQL Server通信所需的TCP端口。

Try using this application 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得到了“Port is open”的回复。

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.

转到Connections设置并确保“允许远程连接到此服务器”是故障单。

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

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

#1


15  

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

如果要远程连接到SQL服务器,则需要使用软件 - 如Sql Server Management工作室。

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: express For Sql 2008: 2008

对于Sql 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。你可以从他们公司的网站上下载。

#2


22  

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:

Disclamer这只是一些可能对任何人有帮助的其他信息。我想非常清楚地说明我在这里所描述的可能是:

  • A. not 100% correct and
  • A.不是100%正确和
  • B. not safe in terms of network security.
  • 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 tutorial which is the same tutorial posted above as a solution by "Dani" as the selected answer to this question.
  2. 确保所有内容都像本教程中那样平方,这是上面作为“Dani”作为此问题的选定答案的解决方案发布的相同教程。
  3. 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
    • 为端口TCP 1433创建入站规则 - 允许连接
    • Create an outbound rule for port TCP 1433 - allow the connection
    • 为端口TCP 1433创建出站规则 - 允许连接
  4. 检查和/或设置托管SQL Server的计算机的防火墙设置。如果您使用的是Windows Server 2008 R2,请使用服务器管理器,转到“配置”,然后查看“具有高级安全性的Windows防火墙”。如果您使用的是Windows 7,请转到“控制面板”并搜索“防火墙”,然后单击“允许程序通过Windows防火墙”。为端口TCP 1433创建入站规则 - 允许连接为端口TCP 1433创建出站规则 - 允许连接
  5. 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
    • 双击TCP / IP
    • Click on the IP Addresses tab
    • 单击“IP地址”选项卡
    • Under IP1 set the TCP Port to 1433 if it hasn't been already
    • 在IP1下,如果尚未将TCP端口设置为1433
    • Under IP All set the TCP Port to 1433 if it hasn't been already
    • 在IP All下,如果TCP端口尚未设置,则将其设置为1433
  6. 当您完成防火墙设置后,您将要再检查一件事。打开“SQL Server配置管理器”找到:SQL Server网络配置 - SQLEXPRESS的协议(或等效的) - TCP / IP双击TCP / IP单击IP地址选项卡在IP1下,如果TCP端口没有,则将TCP端口设置为1433已经在IP下全部将TCP端口设置为1433(如果尚未安装)
  7. Restart SQL Server and SQL Browser (do both just to be on the safe side)
  8. 重新启动SQL Server和SQL浏览器(两者都只是为了安全起见)

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:

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

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

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

同样,如果有关于此主题的更多信息的人看到红旗,请更正我。

Hope this helps.

希望这可以帮助。

#3


2  

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

    在端口号的Windows防火墙中添加新的TCP本地端口。 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

    为SQL Server添加新程序并选择sql server.exe路径: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

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

#4


1  

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 Firewall by Microsoft.

我感觉端口1433被防火墙阻挡,因此你的麻烦。请参阅Microsoft通过防火墙与SQL Server通信所需的TCP端口。

Try using this application 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得到了“Port is open”的回复。

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.

转到Connections设置并确保“允许远程连接到此服务器”是故障单。

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

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