configuration - 使用代码或脚本(查询)启用到 sql server express 已安装数据库的 tcp\ip 远程连接

Question

我正在使用我的应用程序部署 SQL Express。我希望数据库引擎接受远程连接。我知道如何通过启动 sql server 配置管理器、启用 tcp/ip 连接、指定端口等来配置该手册。我想知道是否可以从命令行执行相同的操作。

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

编辑1

我在这里发布了同样的问题，但我想在已经安装的 sqlexpress 实例上做同样的事情。 Take a look at the question in here

编辑2

我发现这些链接声称可以做类似的事情，但我仍然无法使其工作。

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

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

4) http://datazulu.com/blog/post/Enable_sql_server_tcp_via_script.aspx

<小时/>

编辑3

正如克日什托夫在他的回复中所说，我需要(加上我知道需要的其他东西)

1 - 启用 TCP/IP

我在安装传递参数 /TCPENABLED=1 的 SQLExpress 新实例时成功做到了这一点。当我像this example中那样安装sqlexpress时。该 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 帐户的密码为 NULL。为了接受连接，该用户必须有密码。我用脚本更改了 sa 的默认密码:

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

6 - 终于

如果满足最后所有步骤，则将能够连接:

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

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

Answer 1

我使用 SQL Server 2008 R2 Express 测试了以下代码，我相信我们应该为您列出的所有 6 个步骤提供解决方案。让我们一一分析它们:

1 - 启用 TCP/IP

我们可以使用 WMI 启用 TCP/IP 协议(protocol):

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 是您最好的 friend !

4 - 在 sql server 中启用混合模式身份验证

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

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

2. 使用 SQL Server 注册表中的 LoginMode 值，如 this post 中所述。请注意，默认情况下，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

请告诉我这是否适合您(祈祷!)。