通过TCP/IP连接到外部托管计算机上的2014 SQLEXPRESS数据库 [英] Connecting to a 2014 SQLEXPRESS database on an externally hosted computer through TCP/IP

问题描述

我正在使用一个系统，可以从几个位置更新一个小型数据库.我希望将数据库托管在家用计算机上，并允许通过tcp/ip从其他两个位置建立到数据库的连接.

I am working on a system whereby I can update a small database from several locations. I wish to host the database on my home computer and allow connections to be made to the database from two other locations through tcp/ip.

到目前为止，我已经完成了以下操作:

I have done the following thus far:

1. 为SQLEXPRESS启用了TCP/IP
2. 在防火墙上允许使用1433 tcp端口
3. 在防火墙上允许使用端口1434 udp
4. 已启用SQLEXPRESS服务器以允许远程连接

我希望能够通过在其他PC上打开sql management studio并使用以下格式[ExternalIP] \ SQLEXPRESS来连接到数据库，但是到目前为止，我仍然无法这样做.我查看了我的外部IP可能是什么，并且得到了两个不同的值:

I was hoping to be able to connect to the database by opening sql management studio on the other pcs and using the following format [ExternalIP]\SQLEXPRESS but as of yet I have been unable to do so. I have looked at what my external IP might be and i am getting two different values:

86.XX.X.XXX 66.XXX.XX.XX

86.XX.X.XXX 66.XXX.XX.XX

在正确配置所有内容后，是否应该使用这些值中的任何一种以这种格式与其他sql管理工作室建立连接?我缺少任何步骤吗?

Should I be using either of these values with this format to connect from the other sql management studios when everything has been correctly configured? Are there any steps that I am missing?

推荐答案

命名实例不会在端口1433上侦听，而是在每次服务启动时确定的随机端口上侦听.这是 sql浏览器服务的功能，用于为客户端提供每个正在运行的实例的实际端口(并使用固定的1434 udp端口完成此操作).

Named instances of SQL Server don't listen by default on port 1433, but instead on a random port determined at each service startup. It's the function of the sql browser service to provide clients with actual ports of each running instance (and that's done using the fixed 1434 udp port).

必要的步骤是将服务器配置为使用固定端口，然后设置防火墙和路由器以允许该端口.连接时，只需将外部IP地址与端口一起使用，而不要与实例名称一起使用(因为那样会调用浏览器服务，而固定端口则不需要此服务).

The necesary steps would be to configure the server to use a fixed port, then setup the firewall and router to allow that port. When connecting, just use the external IP address with the port but not the instance name (as that would invoke the browser service, which is unneeded with fixed ports).

要继续，需要执行以下步骤:

To resume, necesary steps would be these:

1. 在服务器上启用远程连接.
2. 根据需要设置用户和权限(Windows身份验证在Internet上很麻烦，因此强烈建议使用SQL身份验证)
3. 为实例启用TCP/IP.
4. 配置服务器以使用固定端口进行连接(1433为标准端口). 查看此处以获取说明.
5. 重新启动数据库服务器.
6. 授予防火墙权限，以允许服务器在选定端口上执行可执行的传入连接.此时，LAN中的其他计算机应连接.
7. 在路由器上，将您打开的端口转发到本地计算机专用IP(请注意，在Windows中，必须将计算机配置为使用固定的本地IP而不是DHCP).
8. 通过Internet或仅使用您的外部IP与客户端进行测试.

1. Enable remote connections on the server.
2. Setup users and permissions as needed (Windows authentication is a pain over internet, so SQL authentication is strongly recommended)
3. Enable TCP/IP for the instance.
4. Configure the server to use a fixed port for connections (1433 being the standard). Look here for instructions.
5. Restart the database server.
6. Give permissions on the firewall to allow the server executable incoming connections on your selected port. At this point other computers in the LAN should connect.
7. On your router forward the port you opened to your local machine private IP (note that in Windows you must configure your computer to use a fixed local IP instead of DHCP).
8. Test with a client over internet or just using your external IP.

请注意，对于来自远程客户端的连接，必须使用 86.XX.X.113,1433 作为服务器名称(端口号以逗号分隔，如果是1433，则为可选) .请注意，没有实例名称，即使您将其安装为SQLEXPRESS或其他名称也是如此.这是由于具有固定的侦听端口(如默认情况下的默认实例).这消除了对浏览器服务和udp 1434的需求，以及动态端口转发的麻烦.

Note that for the connection from the remote client, as server name you must use 86.XX.X.113,1433 (the port number is separated with a comma, optional if it's 1433). Notice that there is NO instance name, even if you installed it as SQLEXPRESS or whatever. This is due having a fixed listening port (like the default instance by default). That eliminates the need of the browser service and udp 1434, and the pain of a dynamic port forwarding.

您还应该检查ISP是否最有可能为您提供动态的外部IP，以便在更改时可以重新配置客户端.或者，您可以使用一些动态DNS服务.

You should also check that your ISP most likely will give you a dynamic external IP, so clients might be reconfigured when that changes. Or you can use some dynamic DNS service.

这篇关于通过TCP/IP连接到外部托管计算机上的2014 SQLEXPRESS数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！