启用tcp\ip远程连接到sql server express已经安装的数据库使用代码或脚本（查询） [英] Enable tcp\ip 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＃）
到目前为止，我必须使用这个控制台命令aroud：

  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需要此验证类型才能接受远程连接。

sa）default passowrd

默认情况下，sa帐户有一个NULL passowrd。为了接受连接，此用户必须有密码。我用脚本更改了sa的默认passowrd：

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

6 - 最后如果所有最后步骤都满足，则b
$ b

将能够连接：

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

行：C＃中的连接字符串将非常类似。我将替换-U为用户，-P为密码和-S为数据源。我不记得确切的名字。

解决方案

我测试下面的代码与 SQL Server 2008 R2 Express ，我相信我们应该为您列出的所有6个步骤提供解决方案。让我们一个接一个：

1 - 启用TCP / IP

/ IP协议和 WMI ：

  set wmiComputer = GetObject（_ 
winmgmts：_ 
&\\.\root\ Microsoft\SqlServer\ComputerManagement10）
 set tcpProtocols = wmiComputer.ExecQuery（_ 
select * from ServerNetworkProtocol_ 
&其中InstanceName ='SQLEXPRESS'和ProtocolName ='Tcp ''）
 
如果tcpProtocols.Count = 1然后
'set tcpProtocol = tcpProtocols（0）
'我希望这个工作，但不幸的是
'没有int -indexed这个类型的Item属性
 
'这样做而是
为tcpProtocols中的每个tcpProtocol 
 dim setEnableResult 
 setEnableResult = tcpProtocol.SetEnable（）
 if setEnableResult<> 0 then 
 Wscript.Echo失败！ 
 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_ 
&其中InstanceName ='SQLEXPRESS '和_ 
&ProtocolName ='Tcp'和IPAddressName ='IPAll'）
 
 tcpProperties中的每个tcpProperty 
 dim setValueResult，requestedValue 
 
 if tcpProperty.PropertyName =TcpPortthen 
 requestedValue =3456
 elseif tcpProperty.PropertyName =TcpDynamicPortsthen 
 requestedValue =
 end if 
 
 setValueResult = tcpProperty.SetStringValue（requestedValue）
如果setValueResult = 0 then 
 Wscript.Echo& tcpProperty.PropertyName& 设置。 
 else 
 Wscript.Echo& tcpProperty.PropertyName& 失败了！ 
 end if 
 next 
  

注意，我没有启用任何的个人地址，以使其工作，但如果在您的情况下需要，您应该能够轻松地扩展这个脚本这样做。

只是一个提醒当使用WMI时， WBEMTest.exe 是您最好的朋友！

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

我希望我们可以再次使用WMI，但不幸的是，此设置不会通过WMI暴露。还有两个其他选项：

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




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 
  

请让我知道这是否适合你（手指交叉！）。

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.120\SQLEXPRESS,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:" _
    & "\\.\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 - 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:" _
    & "\\.\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

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_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQLServer.

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!).

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