configuration - que - permitir conexiones remotas sql server

Habilite las conexiones remotas tcp / ip al servidor sql. Exprima la base de datos ya instalada con código o secuencia de comandos(consulta).

Question

Estoy implementando sql express con mi aplicación. Me gustaría que el motor de base de datos acepte conexiones remotas. Sé cómo configurar ese manual iniciando el administrador de configuración del servidor sql, habilitando las conexiones tcp / ip, especificando los puertos, etc. Me pregunto si será posible hacer lo mismo desde la línea de comando.

O tal vez tendré que crear un "SQL Server 2008 Server Project" en Visual Studio.

EDIT 1

Publiqué la misma pregunta aquí, pero me gustaría hacer lo mismo en una instancia de sql express que ya está instalada. Eche un vistazo a la pregunta aquí

EDIT 2

Encontré estos enlaces que dicen hacer algo similar y todavía no puedo hacer que funcione.

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

Como Krzysztof declaró en su respuesta, necesito (además de otras cosas que sé que se requieren)

1 - habilitar TCP / IP

He logrado hacer esto cuando instalo una nueva instancia de SQLExpress pasando el parámetro /TCPENABLED=1 . Cuando instalo sql express como en este ejemplo . esa instancia de sql express tendrá TCP / IP habilitado

2 - Abra los puertos correctos en el firewall

(Lo he hecho a mano, pero creo que podré descubrir cómo hacerlo con c #) Hasta ahora tengo que jugar aroud con este comando de consola:

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

3 - Modificar las propiedades TCP / IP para habilitar una dirección IP

No he podido averiguar cómo habilitar una IP, cambiar un puerto, etc. Creo que este será el paso más complicado de resolver

4 - Habilitar la autenticación de modo mixto en el servidor SQL

He logrado hacer esto al instalar SQL Express pasando el parámetro /SECURITYMODE=SQL consulte el enlace del paso 1.

SQL Server Express requiere este tipo de autenticación para aceptar conexiones remotas.

5 - Cambiar passowrd predeterminado del usuario (sa)

Por defecto, la cuenta sa tiene un passowrd NULL. Para aceptar conexiones, este usuario debe tener una contraseña. Cambié el passowrd predeterminado de la sa con el script:

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

6 - finalmente

podrá conectarse si se satisfacen todos los últimos pasos como:

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

tipeando eso en la línea de comando: la cadena de conexión en C # será muy similar. Tendré que reemplazar -U para usuario, -P para contraseña y -S para fuente de datos. No recuerdo los nombres exactos.

Answer 1

Probé el código a continuación con SQL Server 2008 R2 Express y creo que deberíamos tener una solución para los 6 pasos que describió. Vamos a abordarlos uno a uno:

1 - Habilitar TCP / IP

Podemos habilitar el protocolo TCP / IP con 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 - Abra los puertos correctos en el firewall

Creo que su solución funcionará, solo asegúrese de especificar el puerto correcto. Sugiero que elijamos un puerto diferente a 1433 y lo convierta en un puerto estático en el que SQL Server Express estará escuchando. Utilizaré 3456 en esta publicación, pero elija un número diferente en la implementación real (creo que veremos un montón de aplicaciones usando 3456 pronto :-)

3 - Modificar las propiedades TCP / IP para habilitar una dirección IP

Podemos usar WMI nuevamente. Dado que estamos utilizando el puerto estático 3456, solo necesitamos actualizar dos propiedades en la sección IPAll : deshabilite los puertos dinámicos y configure el puerto de escucha en 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

Tenga en cuenta que no tuve que habilitar ninguna de las direcciones individuales para que funcione, pero si es necesario en su caso, debería poder extender este script fácilmente para hacerlo.

¡Solo un recordatorio de que cuando trabajas con WMI, WBEMTest.exe es tu mejor amigo!

4 - Habilitar la autenticación de modo mixto en el servidor SQL

Desearía poder usar WMI nuevamente, pero desafortunadamente esta configuración no está expuesta a través de WMI. Hay otras dos opciones:

1. Utilice la propiedad LoginMode de la clase Microsoft.SqlServer.Management.Smo.Server , como se describe here .

2. Use el valor de LoginMode en el registro de SQL Server, como se describe en esta publicación . Tenga en cuenta que, de forma predeterminada, la instancia de SQL Server Express se denomina SQLEXPRESS , por lo que para mi instancia de SQL Server 2008 R2 Express, la clave de registro correcta era HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Microsoft SQL Server/MSSQL10_50.SQLEXPRESS/MSSQLServer .

5 - Cambiar la contraseña predeterminada del usuario (sa)

Tienes esto cubierto.

6 - Finalmente (conectarse a la instancia)

Dado que estamos utilizando un puerto estático asignado a nuestra instancia de SQL Server Express, ya no es necesario usar el nombre de la instancia en la dirección del servidor.

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

Por favor, avíseme si esto funciona para usted (¡con los dedos cruzados!).

Answer 2

Recomiendo usar SMO ( habilitar el protocolo de red TCP / IP para SQL Server ). Sin embargo, no estaba disponible en mi caso.

Reescribí los comandos WMI de Krzysztof Kozielczyk a 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''