为SQL Server Express 2012启用远程连接

本文翻译自：Enable remote connections for SQL Server Express 2012

I just installed SQL Server Express 2012 on my home server. 我刚刚在家庭服务器上安装了SQL Server Express 2012。 I'm trying to connect to it from Visual Studio 2012 from my desktop PC, and repeatedly getting the well-known error: 我试图通过台式机从Visual Studio 2012连接到它，并反复得到众所周知的错误：

A network-related or instance-specific error occurred while establishing a connection to SQL Server. 建立与SQL Server的连接时发生与网络相关或特定于实例的错误。 The server was not found or was not accessible. 服务器未找到或无法访问。 Verify that the instance name is correct and that SQL Server is configured to allow remote connections. 验证实例名称正确，并且已将SQL Server配置为允许远程连接。 (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) （提供者：命名管道提供程序，错误：40-无法打开与SQL Server的连接）

What I've done to try to fix this: 我已尝试解决此问题：

• Run SQL Server Configuration Manager on the server and enable SQL Server Browser 在服务器上运行SQL Server配置管理器并启用SQL Server浏览器
• Add a Windows Firewall exception on the server for TCP, ports 1433 and 1434 on the local subnet. 在服务器上为本地子网上的TCP，端口1433和1434添加Windows防火墙例外。
• Verify that I have a login on the SQL Server instance for the user I'm logged in as on the desktop. 验证我在SQL Server实例上是否具有我在台式机上登录的用户的登录名。
• Verify that I'm using Windows Authentication on the SQL Server instance. 验证我在SQL Server实例上使用Windows身份验证。
• Repeatedly restart SQL Server and the whole dang server. 反复重新启动SQL Server和整个dang服务器。
• Pull all my hair out. 拉我所有的头发。

How can I get SQL Server 2012 Express to allow remote connections!? 如何获得SQL Server 2012 Express以允许远程连接！？

---

#1楼

参考：https://stackoom.com/question/lJx4/为SQL-Server-Express-启用远程连接

---

#2楼

Well, glad I asked . 好吧， 我高兴地问 。 The solution I finally discovered was here: 我终于发现的解决方案在这里：

How do I configure SQL Server Express to allow remote tcp/ip connections on port 1433? 如何配置SQL Server Express以允许在端口1433上进行远程tcp / ip连接？

1. Run SQL Server Configuration Manager. 运行SQL Server配置管理器。
2. Go to SQL Server Network Configuration > Protocols for SQLEXPRESS. 转到“ SQL Server网络配置”>“ SQLEXPRESS的协议”。
3. Make sure TCP/IP is enabled. 确保已启用TCP / IP。

So far, so good, and entirely expected. 到目前为止，一切都很好，完全值得期待。 But then: 但是之后：

1. Right-click on TCP/IP and select Properties . 右键单击“ TCP / IP”，然后选择“ 属性” 。
2. Verify that, under IP2, the IP Address is set to the computer's IP address on the local subnet. 验证是否在IP2下将IP地址设置为本地子网上的计算机IP地址。
3. Scroll down to IPAll. 向下滚动到IPAll。
4. Make sure that TCP Dynamic Ports is blank . 确保“ TCP动态端口”为空 。 (Mine was set to some 5-digit port number.) （我的设置为5位端口号。）
5. Make sure that TCP Port is set to 1433 . 确保将TCP端口设置为1433 。 (Mine was blank.) （我是空白的。）

(Also, if you follow these steps, it's not necessary to enable SQL Server Browser, and you only need to allow port 1433, not 1434.) （另外，如果你遵循这些步骤，这是没有必要让SQL Server的浏览器，你只需要允许端口1433，而不是1434）

These extra five steps are something I can't remember ever having had to do in a previous version of SQL Server, Express or otherwise. 这些额外的五个步骤是我以前在SQL Server Express，Express或其他版本中必须记住的。 They appear to have been necessary because I'm using a named instance (myservername\\SQLEXPRESS) on the server instead of a default instance. 它们似乎是必需的，因为我在服务器上使用的是命名实例（myservername \\ SQLEXPRESS），而不是默认实例。 See here: 看这里：

Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager) 配置服务器以侦听特定的TCP端口（SQL Server配置管理器）

---

#3楼

You can also set 您也可以设定

Listen All to NO 全部听不听

in the protocol dialog then in the IP address IP1 (say) 在协议对话框中，然后在IP地址IP1中（例如）

set enabled to Yes, 设置为是，

define yr IP address, 定义您的IP地址，

set TCP Dynamic to Blank and 将“ TCP动态”设置为“空白”并

TCP port to 1433 (or whatever) TCP端口到1433（或其他任何端口）

---

#4楼

在使用默认设置安装的SQL Server 2012 Developer Edition安装中，我只需要加载SQL Server配置管理器-> SQL Server网络配置-> MSSQLSERVER的协议，并将TCP / IP从“禁用”更改为“启用”。

---

#5楼

您需要做的就是打开服务器防火墙上的相关端口。

---

#6楼

I had a different problem from what all of the answers so far mentioned! 我有一个与迄今为止所有答案都不同的问题！

I should start off by saying that I had it in Visual Studio, and not SQL Server Express but the solution should be exactly the same. 首先，我说它是在Visual Studio中提供的，而不是SQL Server Express，但解决方案应该完全相同。

Well, god, it's actually really simple and maybe a bit foolish. 好吧，天哪，这实际上很简单，也许有些愚蠢。 When I tried to create a database and Visual Studio suggested the name of the SQL Server it gave me my Windows username and since it's actually the name of the server I went for it. 当我尝试创建数据库并且Visual Studio建议使用SQL Server的名称时，它为我提供了Windows用户名，因为它实际上是我所使用的服务器的名称。

In reality it actually was my Windows username + \\SQLEXPRESS . 实际上，实际上是我的Windows用户名+ \\SQLEXPRESS 。 If you didn't change any settings this is probably yours too. 如果您未更改任何设置，则可能也属于您。 If it works, stop reading; 如果有效，请停止阅读； this is my answer. 这是我的答案。 If it doesn't work maybe the name is different. 如果不起作用，则名称可能不同。

If, like me, you only had this problem in Visual Studio to check what is yours follow these steps: 如果像我一样，如果您仅在Visual Studio中遇到此问题以检查您的问题，请执行以下步骤：

1. Open SQL Server Management Studio ^icon . 打开“ SQL Server Management Studio” ^图标 。
2. If you don't see your server (docked to the left by default) press F8 or go to View -> Object Explorer . 如果没有看到您的服务器（默认情况下停靠在左侧），请按F8或转到View- > Object Explorer 。
3. Right click on the name of the server and choose Properties (The last item) 右键单击服务器的名称，然后选择“ 属性” （最后一项）
4. At the bottom left you can see your server's actual name under " Server " (not Connection, but above it). 在左下角，您可以在“ 服务器 ”下看到服务器的实际名称（不是Connection，而是在其上方）。

This is the name of the server and this is what you should attempt to connect to! 这是服务器的名称，这是您应尝试连接的名称！ not what Visual Studio suggests! 不是Visual Studio所建议的！