SQL 使用excel连接远程sql数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10056560/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Connect to remote sql database using excel
提问by MyKuLLSKI
I'm using SQL Server 2008 R2 and Microsoft Excel.
我正在使用 SQL Server 2008 R2 和 Microsoft Excel。
- I have the server set up to be logged in via Windows user ordatabase user
- I canlog into the database fine with the database user
- I canconnect to the database with my Silverlight app
- I cannotfigure out how to connect from a remote PC in Excel
- 我已将服务器设置为通过 Windows 用户或数据库用户登录
- 我可以用数据库用户登录数据库
- 我可以使用我的 Silverlight 应用程序连接到数据库
- 我不知道如何在 Excel 中从远程 PC 连接
I went to Excel -> Data - > From other sources -> From SQL Server
我去了 Excel -> 数据 -> 从其他来源 -> 从 SQL Server
My server name is WIN2K8-01\DATABASENAME, so in Excel I typed in IP\WIN2K8-01\DATABASENAMEwith the username and password.
我的服务器名称是WIN2K8-01\DATABASENAME，所以在 Excel 中我输入IP\WIN2K8-01\DATABASENAME了用户名和密码。
回答by JotaBe
There can be several reasons for which you cannot connect to your SQL Server:
无法连接到 SQL Server 的原因可能有多种：
A) It doesn't admit remote connections
A) 它不允许远程连接
B) There's a firewall or anything else that avoids remote computers connections
B）有防火墙或其他任何东西可以避免远程计算机连接
C) You don't have SQL Browser service running
C) 你没有运行 SQL Browser 服务
Troubleshooting steps:
故障排除步骤：
1) In a remote computer open cmd and run ping win2k8-01You should get responses from your computer. if not, there's a network connectivity problem
1) 在远程计算机上打开 cmd 并运行ping win2k8-01您应该从您的计算机获得响应。如果不是，则存在网络连接问题
2) Check that the "SQL Browser service" is running in your computer (Services, in Administrative tools, in Control panel) If not set it start type as automatic and start it. This allows remote computers to get information about available instances and how to connect to them. If in doubt, look here
2) 检查“SQL Browser 服务”是否在您的计算机中运行（服务，在管理工具中，在控制面板中）如果没有将它的启动类型设置为自动并启动它。这允许远程计算机获取有关可用实例以及如何连接到它们的信息。如有疑问，请看这里
3) Verify that TCP/IP is enabled. If not enable it How to: Enable the TCP/IP Protocol for a Database Instance
3) 验证是否启用了 TCP/IP。如果未启用如何：为数据库实例启用 TCP/IP 协议
4) Verify that your server allows remote connections. To do this open SSMS, connect to your server, right click, open Properties, and look for "Allow remote connections" on "Connections" tab. get sure this option is checked. If in doubt, look here
4) 验证您的服务器是否允许远程连接。为此，请打开 SSMS，连接到您的服务器，右键单击，打开“属性”，然后在“连接”选项卡上查找“允许远程连接”。确保选中此选项。如有疑问，请看这里
5) Disable firewall in your computer (windows firewall or any 3rd party firewall like those installed by antivirus software)
5) 禁用计算机中的防火墙（windows 防火墙或任何 3rd 方防火墙，如防病毒软件安装的防火墙）
Try connecting using the same connection configuration that worked in your local computer. It should work now.
尝试使用在本地计算机中工作的相同连接配置进行连接。它现在应该可以工作了。
If you cannot connect using Excel you should try connecting with another tool from the remote computer (you can install SSMS on that computer and try connecting).
如果您无法使用 Excel 进行连接，您应该尝试使用远程计算机上的其他工具进行连接（您可以在该计算机上安装 SSMS 并尝试连接）。
This should work. if not let me know what is failing.
这应该有效。如果不让我知道什么是失败的。
If it's working, enable firewall again and try connecting again. If you cannot, you need to configure your firewall to allow SQL Server connections. Do that and try again. Don't forget to allow connections to SQL Server browser on UDP port 1434, apart form the DB instance port on TCP.
如果它正常工作，请再次启用防火墙并尝试再次连接。如果不能，则需要配置防火墙以允许 SQL Server 连接。这样做，然后再试一次。不要忘记在 UDP 端口 1434 上允许连接到 SQL Server 浏览器，除了 TCP 上的数据库实例端口。
回答by Richard Briggs
Here is an article by Lean Software demonstrating the steps to securley connect Excel to a remote Web Server to either Report/Edit/Transfer data.
这是 Lean Software 的一篇文章，演示了将 Excel 安全连接到远程 Web 服务器以报告/编辑/传输数据的步骤。
The article refers to the EDT Excel tool - however the technical steps in terms of connection string and setting firewall and SQL Server options are generic.
这篇文章提到了 EDT Excel 工具 - 但是连接字符串和设置防火墙和 SQL Server 选项方面的技术步骤是通用的。
回答by cairnz
The syntax for connecting through excel is <SERVERNAME OR IP>\INSTANCENAME(Where INSTANCENAME is not needed if you have installed a default instance).
通过 excel 连接的语法是<SERVERNAME OR IP>\INSTANCENAME（如果您安装了默认实例，则不需要 INSTANCENAME）。
This means in Excel you should go Data - From other sources - SQL Server - type in IP only as what you want to connect to. Once there, you will get options to connect to a specific database and table.
这意味着在 Excel 中，您应该转到数据 - 来自其他来源 - SQL Server - 仅输入要连接的 IP。在那里，您将获得连接到特定数据库和表的选项。
回答by Bogdan Gorenak
you can just insert into EXCEL server name: WIN2K8-01\sqlexpress, 1433
你可以直接插入EXCEL服务器名：WIN2K8-01\sqlexpress,1433
