Activer tcp \ip connexions à distance à la base de données sql server express déjà installée avec code ou script(requête)

Question

Je déploie sql express avec mon application. J'aimerais que ce moteur de base de données accepte les connexions à distance. Je sais comment le configurer en lançant le gestionnaire de configuration de sql server, en activant les connexions tcp/ip, en spécifiant les ports, etc. Je me demande s'il sera possible de faire la même chose à partir de la ligne de commande.

Ou peut-être vais-je devoir créer un "Projet de serveur SQL Server 2008" dans Visual Studio.

EDIT 1

J'ai posté la même question ici mais je voudrais faire la même chose sur une instance de sql express qui est déjà installée. Jetez un coup d'œil à la question ici

EDIT 2

J'ai trouvé ces liens qui prétendent faire quelque chose de similaire et je n'arrive toujours pas à le faire fonctionner.

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

Comme Krzysztof l'a indiqué dans sa réponse, j'ai besoin (plus d'autres choses que je sais nécessaires) de

1 - activer TCP/IP

J'ai réussi à le faire en installant une nouvelle instance de SQLExpress en passant le paramètre /TCPENABLED=1 . Lorsque j'installe sql express comme dans cet exemple . cette instance de sql express aura TCP/IP activé.

2 - Ouvrez les bons ports dans le pare-feu.

(J'ai fait cela manuellement mais je pense que je pourrai trouver comment le faire avec c#). Jusqu'à présent, j'ai dû jouer avec cette commande console :

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

3 - Modifier les propriétés TCP/IP activer une adresse IP

Je n'ai pas réussi à trouver comment activer une IP, changer un port, etc. Je pense que ce sera l'étape la plus compliquée à résoudre.

4 - Activer l'authentification en mode mixte dans sql server

J'ai réussi à le faire en installant SQL Express en passant le paramètre /SECURITYMODE=SQL se référer au lien de l'étape 1.

SQL Server express requiert ce type d'authentification pour accepter les connexions à distance.

5 - Changer le passowrd par défaut de l'utilisateur (sa)

Par défaut, le compte sa a un passowrd NULL. Afin d'accepter des connexions, cet utilisateur doit avoir un mot de passe. J'ai changé le passowrd par défaut du sa avec le script :

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

6 - enfin

sera capable de se connecter si toutes les dernières étapes sont satisfaites :

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

en tapant cela dans la ligne de commande : la chaîne de connexion en C# sera très similaire. Je devrai remplacer -U pour user, -P pour password et -S pour data source. Je ne me souviens pas des noms exacts.

Answer 1

J'ai testé le code ci-dessous avec SQL Server 2008 R2 Express et je crois que nous devrions avoir une solution pour les 6 étapes que vous avez décrites. Prenons-les un par un :

1 - Activer TCP/IP

Nous pouvons activer le protocole TCP/IP avec 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 - Ouvrez les bons ports dans le pare-feu.

Je pense que votre solution fonctionnera, assurez-vous simplement de spécifier le bon port. Je suggère de choisir un port différent du 1433 et d'en faire un port statique sur lequel SQL Server Express écoutera. J'utiliserai 3456 dans cet article, mais veuillez choisir un numéro différent dans la mise en œuvre réelle (je pense que nous verrons bientôt beaucoup d'applications utilisant 3456 :-)

3 - Modifier les propriétés TCP/IP activer une adresse IP

Nous pouvons à nouveau utiliser WMI. Puisque nous utilisons le port statique 3456, il nous suffit de mettre à jour deux propriétés dans le fichier IPAll section : désactiver les ports dynamiques et définir le port d'écoute à 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

Notez que je n'ai pas eu à activer l'une des adresses individuelles pour que cela fonctionne, mais si cela est nécessaire dans votre cas, vous devriez pouvoir étendre ce script facilement pour le faire.

Je vous rappelle que lorsque vous travaillez avec WMI, WBEMTest.exe est votre meilleur ami !

4 - Activer l'authentification en mode mixte dans sql server

J'aimerais que nous puissions à nouveau utiliser WMI, mais malheureusement, ce paramètre n'est pas exposé via WMI. Il existe deux autres options :

1. Utilisez LoginMode propriété de Microsoft.SqlServer.Management.Smo.Server classe, comme décrit ici .

2. Utiliser la valeur de LoginMode dans le registre du serveur SQL, comme décrit dans ce poste . Notez que par défaut l'instance de SQL Server Express est nommée SQLEXPRESS Ainsi, pour mon instance de SQL Server 2008 R2 Express, la bonne clé de registre est la suivante HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQLServer .

5 - Changer le mot de passe par défaut de l'utilisateur (sa)

Tu t'occupes de ça.

6 - Enfin (se connecter à l'instance)

Puisque nous utilisons un port statique attribué à notre instance SQL Server Express, il n'est plus nécessaire d'utiliser le nom de l'instance dans l'adresse du serveur.

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

Faites-moi savoir si cela fonctionne pour vous (je croise les doigts !).

Answer 2

Je recommande d'utiliser SMO ( Activer le protocole réseau TCP/IP pour SQL Server ). Cependant, elle n'était pas disponible dans mon cas.

J'ai réécrit les commandes WMI de Krzysztof Kozielczyk en 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'