Enable tcp ip remote connections to sql server express already installed database with code or script query

Question

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

User · Answer

I recommend to use SMO  Enable TCP IP Network Protocol for SQL Server   However  it was not available in my case   I rewrote the WMI commands from Krzysztof Kozielczyk to 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

User · Answer

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          amp       root Microsoft SqlServer ComputerManagement10   set tcpProtocols   wmiComputer ExecQuery         select   from ServerNetworkProtocol          amp   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  lt  gt  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          amp       root Microsoft SqlServer ComputerManagement10   set tcpProperties   wmiComputer ExecQuery         select   from ServerNetworkProtocolProperty          amp   where InstanceName  SQLEXPRESS  and          amp   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     amp  tcpProperty PropertyName  amp    set       else         Wscript Echo     amp  tcpProperty PropertyName  amp    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    Use LoginMode property of Microsoft SqlServer Management Smo Server class  as described here  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

[configuration] Enable tcp\ip remote connections to sql server express already installed database with code or script(query)

Examples related to configuration

Examples related to cmd

Examples related to database-connection

Examples related to sql-server-express

Examples related to remote-connection