使用代码或脚本(查询)启用到 sql server express 已安装数据库的 tcpip 远程连接 [英] Enable tcpip remote connections to sql server express already installed database with code or script(query)

问题描述

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

或者我可能需要在 Visual Studio 中创建一个SQL Server 2008 服务器项目".

编辑 1

我在这里发布了同样的问题，但我想在已经安装的 sql express 实例上做同样的事情.中安装sql express时.该 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.120SQLEXPRESS,1433

在命令行中输入:C# 中的连接字符串将非常相似.我将不得不为 user 替换 -U ，为密码替换 -P ，为数据源替换 -S .我不记得确切的名字.

解决方案

我使用 SQL Server 2008 R2 Express，我相信我们应该为您概述的所有 6 个步骤提供解决方案.让我们一一接受:

1 - 启用 TCP/IP

我们可以使用 WMI:

set wmiComputer = GetObject( _winmgmts:"_&"\.
ootMicrosoftSqlServerComputerManagement10")设置 tcpProtocols = wmiComputer.ExecQuery( _从服务器网络协议中选择 *"_&"其中 InstanceName = 'SQLEXPRESS' 和 ProtocolName = 'Tcp'")如果 tcpProtocols.Count = 1 那么' 设置 tcpProtocol = tcpProtocols(0)' 我希望这能奏效，但不幸的是' 在这种类型中没有整数索引的 Item 属性' 改为这样做对于 tcpProtocols 中的每个 tcpProtocol昏暗的 setEnableResultsetEnableResult = tcpProtocol.SetEnable()如果 setEnableResult <>0 那么Wscript.Echo "失败了！"万一下一个万一

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

我相信您的解决方案会奏效，只需确保指定正确的端口即可.我建议我们选择一个与 1433 不同的端口，并将其设置为 SQL Server Express 将侦听的静态端口.我将在这篇文章中使用 3456，但请在实际实现中选择一个不同的数字(我觉得我们很快就会看到很多应用程序使用 3456 :-)

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

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

set wmiComputer = GetObject( _winmgmts:"_&"\.
ootMicrosoftSqlServerComputerManagement10")设置 tcpProperties = wmiComputer.ExecQuery( _从 ServerNetworkProtocolProperty 中选择 *"_&其中 InstanceName='SQLEXPRESS' 和"_&"ProtocolName='Tcp' 和 IPAddressName='IPAll'")对于 tcpProperties 中的每个 tcpProperty昏暗的 setValueResult，requestedValue如果 tcpProperty.PropertyName = "TcpPort" 然后请求值 = "3456"elseif tcpProperty.PropertyName ="TcpDynamicPorts" 然后请求值 = ""万一setValueResult = tcpProperty.SetStringValue(requestedValue)如果 setValueResult = 0 那么Wscript.Echo "" &tcpProperty.PropertyName &"放."别的Wscript.Echo "" &tcpProperty.PropertyName &"失败的！"万一下一个

请注意，我无需启用任何单个地址即可使其工作，但如果您的情况需要，您应该能够轻松扩展此脚本以实现此目的.

提醒一下，在使用 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_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.SQLEXPRESSMSSQLServer.

5 - 更改用户 (sa) 默认密码

你已经解决了这个问题.

6 - 最后(连接到实例)

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

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

请告诉我这是否适合您(手指交叉！).

I am deploying sql express with my application. I will like that database engine to accept remote connections. I know how to configure that manual by launching the sql server configuration manager, enabling tcp/ip connections, specifying the ports etc.. I am wondering if it will be possible to do the same thing from the command line.

Or maybe I will have to create a "SQL Server 2008 Server Project" in visual studio.

EDIT 1

I posted the same question in here but I will like to do the same thing on a instance of sql express that is already installed. Take a look at the question in here

EDIT 2

I found these links that claim on doing something similar and I still cannot make it work.

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

---

EDIT 3

As Krzysztof stated in his response I need (plus other things that I know that are required)

1 - enable TCP/IP

I have managed to do this when installing a new instance of SQLExpress passing the parameter /TCPENABLED=1. When I install sql express like in this example. that instance of sql express will have TCP/IP enabled

2 - Open the right ports in the firewall

(I have done this manualy but I belive I will be able to figure it out how to do it with c#) So far I have to play aroud with this console command:

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

3 - Modify TCP/IP properties enable a IP address

I have not been able to figure out how to enable a IP, change a port etc.. I think this will be the step more complicated to solve

4 - Enable mixed mode authentication in sql server

I have managed to do this when installing SQL Express passing the parameter /SECURITYMODE=SQL refer to step 1's link.

SQL Server express requires this authentication type to accept remote connections.

5 - Change user (sa) default passowrd

By default the sa account has a NULL passowrd. In order to accept connections this user must have a password. I changed the default passowrd of the sa with the script:

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

6 - finally

will be able to connecto if all the last steps are satisied as:

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

by typing that in the command line: the connection string in C# will be very similar. I will have to replace -U for user , -P for password and -S for data source. I dont recall the exact names.

解决方案

I tested below code with SQL Server 2008 R2 Express and I believe we should have solution for all 6 steps you outlined. Let's take on them one-by-one:

1 - Enable TCP/IP

We can enable TCP/IP protocol with WMI:

set wmiComputer = GetObject( _
    "winmgmts:" _
    & "\.
ootMicrosoftSqlServerComputerManagement10")
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 - Open the right ports in the firewall

I believe your solution will work, just make sure you specify the right port. I suggest we pick a different port than 1433 and make it a static port SQL Server Express will be listening on. I will be using 3456 in this post, but please pick a different number in the real implementation (I feel that we will see a lot of applications using 3456 soon :-)

3 - Modify TCP/IP properties enable a IP address

We can use WMI again. Since we are using static port 3456, we just need to update two properties in IPAll section: disable dynamic ports and set the listening port to 3456:

set wmiComputer = GetObject( _
    "winmgmts:" _
    & "\.
ootMicrosoftSqlServerComputerManagement10")
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

Note that I didn't have to enable any of the individual addresses to make it work, but if it is required in your case, you should be able to extend this script easily to do so.

Just a reminder that when working with WMI, WBEMTest.exe is your best friend!

4 - Enable mixed mode authentication in sql server

I wish we could use WMI again, but unfortunately this setting is not exposed through WMI. There are two other options:

1. Use LoginMode property of Microsoft.SqlServer.Management.Smo.Server class, as described here.

2. Use LoginMode value in SQL Server registry, as described in this post. Note that by default the SQL Server Express instance is named SQLEXPRESS, so for my SQL Server 2008 R2 Express instance the right registry key was HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.SQLEXPRESSMSSQLServer.

5 - Change user (sa) default password

You got this one covered.

6 - Finally (connect to the instance)

Since we are using a static port assigned to our SQL Server Express instance, there's no need to use instance name in the server address anymore.

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

Please let me know if this works for you (fingers crossed!).

这篇关于使用代码或脚本(查询)启用到 sql server express 已安装数据库的 tcpip 远程连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！