使用Excel连接到远程SQL数据库 [英] Connect to remote sql database using excel

问题描述

我正在使用SQL Server 2008 R2和Microsoft Excel.

I'm using SQL Server 2008 R2 and Microsoft Excel.

• 我已将服务器设置为通过Windows用户或数据库用户
登录
• 我可以用数据库用户正常登录数据库
• 我可以使用我的Silverlight应用连接到数据库
• 我无法弄清楚如何从Excel中的远程PC连接

• I have the server set up to be logged in via Windows user or database user
• I can log into the database fine with the database user
• I can connect to the database with my Silverlight app
• I cannot figure out how to connect from a remote PC in Excel

我去了Excel->数据->来自其他来源->来自SQL Server

I went to Excel -> Data - > From other sources -> From SQL Server

我的服务器名称是WIN2K8-01\DATABASENAME，所以在Excel中，我用用户名和密码键入了IP\WIN2K8-01\DATABASENAME.

My server name is WIN2K8-01\DATABASENAME, so in Excel I typed in IP\WIN2K8-01\DATABASENAME with the username and password.

推荐答案

有几种原因导致您无法连接到SQL Server:

There can be several reasons for which you cannot connect to your SQL Server:

A)它不允许远程连接

A) It doesn't admit remote connections

B)有防火墙或其他任何可以避免远程计算机连接的东西

B) There's a firewall or anything else that avoids remote computers connections

C)您没有运行SQL Browser服务

C) You don't have SQL Browser service running

故障排除步骤:

1)在远程计算机中，打开cmd并运行ping win2k8-01.您应该从计算机获得响应.如果没有，则说明存在网络连接问题

1) In a remote computer open cmd and run ping win2k8-01 You should get responses from your computer. if not, there's a network connectivity problem

2)检查计算机上是否正在运行"SQL Browser服务"(服务"，在管理工具"中，在控制"面板中).如果未将其设置为自动"，请启动它并启动它.这允许远程计算机获取有关可用实例以及如何连接到它们的信息.如有疑问，请在此处查看

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

3)确认已启用TCP/IP.如果未启用它，请如何:为数据库实例启用TCP/IP协议

3) Verify that TCP/IP is enabled. If not enable it How to: Enable the TCP/IP Protocol for a Database Instance

4)验证服务器是否允许远程连接.为此，请打开SSMS，连接到服务器，右键单击，打开属性"，然后在连接"选项卡上查找允许远程连接".确保选中此选项. 如果在怀疑，请看这里

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

5)禁用计算机中的防火墙(Windows防火墙或任何第三者防火墙，如防病毒软件所安装的防火墙)

5) Disable firewall in your computer (windows firewall or any 3rd party firewall like those installed by antivirus software)

尝试使用与本地计算机相同的连接配置进行连接.现在应该可以使用了.

Try connecting using the same connection configuration that worked in your local computer. It should work now.

如果无法使用Excel进行连接，则应尝试与远程计算机上的其他工具进行连接(可以在该计算机上安装SSMS并尝试进行连接).

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

这应该有效.如果没有，请让我知道失败的原因.

This should work. if not let me know what is failing.

如果正常，请再次启用防火墙，然后再次尝试连接.如果不能，则需要配置防火墙以允许SQL Server连接.这样做，然后再试一次.不要忘记允许连接到UDP端口1434上的SQL Server浏览器，除了TCP上的数据库实例端口.

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.

这篇关于使用Excel连接到远程SQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！