使用代码或脚本（查询），启用已安装的SQL Server Express数据库的TCP/IP远程连接。

Question

我正在将sql express与我的应用程序一起部署。 我希望数据库引擎接受远程连接。 我知道如何通过启动SQL Server配置管理器手动配置，启用TCP/IP连接，指定端口等。 我想知道是否可以从命令行执行相同的操作。

或者也许我需要在Visual Studio中创建一个“SQL Server 2008服务器项目”。

编辑1

我在这里发布了相同的问题，但我希望在已安装的sql express实例上执行相同的操作。请查看此处的问题 编辑2

我找到了这些链接，声称可以执行类似的操作，但我仍然无法使其正常工作。

1）http://support.microsoft.com/kb/839980

2) http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/c7d3c3af-2b1e-4273-afe9-0669dcb7bd02/（链接：http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/c7d3c3af-2b1e-4273-afe9-0669dcb7bd02/）

3) http://www.sql-questions.com/microsoft/SQL-Server/34211977/can-not-connect-to-sql-2008-express-on-same-lan.aspx（链接：http://www.sql-questions.com/microsoft/SQL-Server/34211977/can-not-connect-to-sql-2008-express-on-same-lan.aspx）

4) http://datazulu.com/blog/post/Enable_sql_server_tcp_via_script.aspx（链接：http://datazulu.com/blog/post/Enable_sql_server_tcp_via_script.aspx）

---

编辑 3

正如Krzysztof在他的回复中所述，我需要（以及其他我知道需要的事情）

1 - 启用TCP/IP

我已经成功地在安装新的 SQLExpress 实例时传递参数 /TCPENABLED=1 来实现此目标。当我像 this example 中的示例那样安装 SQL Express 时，该实例将启用 TCP/IP。

2 - 在防火墙中打开正确的端口

（我已经手动完成了这个过程，但我相信我可以通过 C# 找到解决方法） 到目前为止，我必须使用这个控制台命令进行操作：

netsh firewall set portopening protocol = TCP port = 1433 name = SQLPort mode = ENABLE scope = SUBNET profile = CURRENT

3 - 修改 TCP/IP 属性以启用 IP 地址

我还没有弄清楚如何启用IP，更改端口等。我认为这将是解决问题最复杂的步骤。

4-在SQL Server中启用混合模式身份验证。

我已经成功实现了在安装SQL Express时传递参数/ SECURITYMODE = SQL来完成此操作，请参阅步骤1的链接。

SQL Server Express需要此身份验证类型才能接受远程连接。 5- 更改用户（sa）默认密码 默认情况下，sa帐户具有空密码。为了接受连接，这个用户必须有一个密码。我使用以下脚本更改了sa的默认密码：

ALTER LOGIN [sa] WITH PASSWORD='*****newPassword****' 

6 - 最后

如果所有最后的步骤都得到满足，将能够连接，这些步骤包括：

SQLCMD -U sa -P newPassword -S 192.168.0.120\SQLEXPRESS,1433

通过在命令行中输入此命令：连接C#字符串将非常相似。我必须用用户替换-U，密码替换-P，数据源替换-S。我不记得确切的名称。

- Tono Nam

---

您需要完成两件事情：（1）启用 SQL Server 实例上的 TCP/IP 并进行配置（包括端口），以及（2）在防火墙上打开正确的端口。您能描述一下您尝试如何完成这两个步骤吗？是否遇到了任何错误或者它根本无法工作？当您打开 SQL Server 配置管理器和 Windows 防火墙设置 UI 时，您能否看到您的配置更改？ - Krzysztof Kozielczyk

是的，我成功地在安装数据库时启用了TCP/IP。感谢您的答案，我通过执行以下操作成功安装它：https://dev59.com/jGDVa4cB1Zd3GeqPZQqI#9154540。请查看我的编辑，我将更详细地解释我正在做的事情中缺少的东西... - Tono Nam

发生了一些不好的事情，因为你的第三次编辑显示为空。 - Krzysztof Kozielczyk

我花了一些时间来编辑它。现在应该已经上传了...非常感谢你的帮助。 - Tono Nam

我有两条更多的评论，可能会有所帮助，但不足以确定答案... 对于端口配置，我建议使用WMI，因为PowerShell在一些SQL Server Express安装中可能无法工作（由于.NET 2的依赖性）。这篇文章似乎包含了您应该查看的脚本：http://blogs.msdn.com/b/sqlserverfaq/archive/2009/08/11/how-to-change-the-ip-all-tcp-port-to-a-static-port-from-a-dynamic-port-using-wmi.aspx。另外，一旦设置了端口，您就不再需要指定实例名称，只需使用这个地址：`-S 192.168.0.120,1433`。否则，您还需要启用SQL Browser。 - Krzysztof Kozielczyk

我刚刚在使用命令行安装SQL Server 2014 Express SP1时遇到了“3-修改TCP/IP属性启用IP地址”的问题，尽管我已经指定了/TCPENABLED=1。我想知道为什么会出现这种情况？ - Jaans

Answer 1

我用SQL Server 2008 R2 Express测试了下面的代码，我相信我们应该有解决你列出的6个步骤的方案。让我们逐一解决它们：

1-启用TCP/IP

我们可以使用WMI启用TCP/IP协议：

set wmiComputer = GetObject( _
    "winmgmts:" _
    & "\\.\root\Microsoft\SqlServer\ComputerManagement10")
set tcpProtocols = wmiComputer.ExecQuery( _
    "select * from ServerNetworkProtocol " _
    & "where InstanceName = 'SQLEXPRESS' and ProtocolName = 'Tcp'")

if tcpProtocols.Count = 1 then
    ' set tcpProtocol = tcpProtocols(0)
    ' I wish this worked, but unfortunately 
    ' there's no int-indexed Item property in this type

    ' Doing this instead
    for each tcpProtocol in tcpProtocols
        dim setEnableResult
            setEnableResult = tcpProtocol.SetEnable()
            if setEnableResult <> 0 then 
                Wscript.Echo "Failed!"
            end if
    next
end if

2-在防火墙中打开正确的端口

我相信您的解决方案将起作用，只需确保指定正确的端口。我建议我们选择与1433不同的端口，并使其成为SQL Server Express将侦听的静态端口。在本文中，我将使用3456，但请在实际实现中选择一个不同的数字（我感觉我们很快会看到许多应用程序使用3456）。

3-修改TCP/IP属性以启用IP地址

我们可以再次使用WMI。由于我们使用静态端口3456，因此我们只需要更新“IPAll”部分中的两个属性：禁用动态端口并将侦听端口设置为3456：

set wmiComputer = GetObject( _
    "winmgmts:" _
    & "\\.\root\Microsoft\SqlServer\ComputerManagement10")
set tcpProperties = wmiComputer.ExecQuery( _
    "select * from ServerNetworkProtocolProperty " _
    & "where InstanceName='SQLEXPRESS' and " _
    & "ProtocolName='Tcp' and IPAddressName='IPAll'")

for each tcpProperty in tcpProperties
    dim setValueResult, requestedValue

    if tcpProperty.PropertyName = "TcpPort" then
        requestedValue = "3456"
    elseif tcpProperty.PropertyName ="TcpDynamicPorts" then
        requestedValue = ""
    end if

    setValueResult = tcpProperty.SetStringValue(requestedValue)
    if setValueResult = 0 then 
        Wscript.Echo "" & tcpProperty.PropertyName & " set."
    else
        Wscript.Echo "" & tcpProperty.PropertyName & " failed!"
    end if
next

请注意，我不需要启用任何单个地址即可使其工作，但如果在您的情况下需要，请轻松扩展此脚本以执行此操作。

提醒一下，在使用WMI时，WBEMTest.exe是你最好的朋友！

4 - 在SQL Server中启用混合模式身份验证

我希望我们再次使用WMI，但不幸的是，此设置未通过WMI公开。有两个其他选项：

1. 使用Microsoft.SqlServer.Management.Smo.Server类的LoginMode属性，如此处所述。

2. 使用SQL Server注册表中的LoginMode值，如此帖子所述。请注意，默认情况下，SQL Server Express实例名为SQLEXPRESS，因此对于我的SQL Server 2008 R2 Express实例，正确的注册表键是HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQLServer。

5-更改用户（sa）默认密码

您已经掌握了这个问题。

6-最后（连接到实例）

由于我们正在使用分配给我们的SQL Server Express实例的静态端口，因此不再需要在服务器地址中使用实例名称。

SQLCMD -U sa -P newPassword -S 192.168.0.120,3456

如果这对你有用，请让我知道（祈祷！）。

- Krzysztof Kozielczyk

---

哇，SQL Server让使用它的数据库部署应用程序变得如此困难。就好像他们不希望人们使用他们的数据库来部署应用程序一样。我想知道是否有人选择了这个选项。或者也许他们想让安装它的人感到困难... - Tono Nam

免责声明：我在使用SQL Server Express。尽管如此，为了找到答案，我需要进行大量挖掘。SQL Server首先是一个数据库服务器，这意味着它有时没有针对其他方案进行优化。新的LocalDB版本适用于嵌入式数据库场景，但它不会为您工作，因为它不接受远程连接。然而，在下一次需要嵌入式数据库且不需要远程访问时，请查看它：http://blogs.msdn.com/b/sqlexpress/archive/2011/11/28/simple-installer-for-localdb-has-shipped.aspx - Krzysztof Kozielczyk

这是一个很好的答案！但我有点困惑，这些脚本是用什么语言编写的？我可以将这些WMI脚本保存到文本文件中，并通过输入类似于“wmi.exe myscript.txt”的命令来运行它吗？它看起来像C#代码，但问题没有标记为C#，所以我不知道如何实现这个答案。谢谢！ - Arvo Bowen

@ArvoBowen 谢谢！这段代码是VB脚本。如果我没记错的话，将代码保存在一个扩展名为.vbs的文件中应该可以让你执行它。不过最好还是重写成PowerShell，因为现在它已经广泛使用了（我的回答是在PowerShell刚开始流行的时候给出的）。 - Krzysztof Kozielczyk

那将是我的第二个猜测。;) 那应该很容易实现。我找到了这个（https://technet.microsoft.com/en-us/library/dd206997(v=sql.105).aspx），它告诉如何通过powershell启用SQL的TCPIP（这是不必要的，因为2016年有一个/TCPENABLED=1开关在安装期间可以完成）。我需要设置TCP/IP端口并可能（如果需要）将本地主机IP#设置为enabled=true。 - Arvo Bowen

Answer 2

我建议使用SMO (启用SQL Server的TCP/IP网络协议)。但在我的情况下不可用。

我将Krzysztof Kozielczyk的WMI命令重写为PowerShell。

# Enable TCP/IP

Get-CimInstance -Namespace root/Microsoft/SqlServer/ComputerManagement10 -ClassName ServerNetworkProtocol -Filter "InstanceName = 'SQLEXPRESS' and ProtocolName = 'Tcp'" |
Invoke-CimMethod -Name SetEnable

# Open the right ports in the firewall
New-NetFirewallRule -DisplayName 'MSSQL$SQLEXPRESS' -Direction Inbound -Action Allow -Protocol TCP -LocalPort 1433

# Modify TCP/IP properties to enable an IP address

$properties = Get-CimInstance -Namespace root/Microsoft/SqlServer/ComputerManagement10 -ClassName ServerNetworkProtocolProperty -Filter "InstanceName='SQLEXPRESS' and ProtocolName = 'Tcp' and IPAddressName='IPAll'"
$properties | ? { $_.PropertyName -eq 'TcpPort' } | Invoke-CimMethod -Name SetStringValue -Arguments @{ StrValue = '1433' }
$properties | ? { $_.PropertyName -eq 'TcpPortDynamic' } | Invoke-CimMethod -Name SetStringValue -Arguments @{ StrValue = '' }

# Restart SQL Server

Restart-Service 'MSSQL$SQLEXPRESS'

- Der_Meister