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.

Wooden

You need to do two things (1) enable TCP/IP on SQL Server instance and configure it (including ports), and (2) open the right ports on the firewall. Can you describe how you attempted to do both? Did you see any failures or does it just not work? Can you also say if you can see your configuration changes when you open up the SQL Server Configuration Manager and Windows Firewall settings UI? – Oxbridge

Yeah I managed to enable TCP/IP when Installing the database. Thanks to your answer I managed to install it by doing: https://mcmap.net/q/393268/-enable-remote-connections-to-sql-express-with-a-script . ... take a look at my edit I will explain in more detail the things that I am missing on doing... – Wooden

Something bad has happened, as your EDIT 3 shows up as empty. – Oxbridge

I took a while to edit it. It should be up now... Thanks a lot for the help – Wooden

I got two more comments that might help, not enough to claim the answer though... For the port configuration I would go with WMI, as PowerShell might not work in some SQL Server Express installations (due to .NET 2 dependency). This post seems to include the script you should look at: blogs.msdn.com/b/sqlserverfaq/archive/2009/08/11/…. Also, once you set the port, you don't need to specify instance name anymore, just use this address: -S 192.168.0.120,1433. Otherwise you also need to enable SQL Browser – Oxbridge

I just had the issue described in "3 - Modify TCP/IP properties enable a IP address" on command line install of SQL Server 2014 Express SP1, despite having specified /TCPENABLED=1. I wonder why this happens? – Blaseio

Answer 1

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

Oxbridge

Wow sql server makes it so difficult to deploy an application with it's database. It is as if they don't want people deploying applications with their database. I wonder if people ever chose this option. Or perhaps they want to make it tough for the people installing it... – Wooden

Disclaimer: I work on SQL Server Express. Still, I needed to do a lot of digging to get to the answer. SQL Server is first and foremost a database server, and that means it is sometimes not optimized for other scenarios. The new LocalDB version of SQL Server Express is a better fit for the embedded DB scenario, but it won't work for you as it doesn't accept remote connections. Still, check it out in case the next time you need to embed a database and don't need remote access: blogs.msdn.com/b/sqlexpress/archive/2011/11/28/… – Oxbridge

This is a great answer! I'm confused though, what language are these scripts in? Can I just save these WMI script to a text file and run it by typing something like wmi.exe myscript.txt? It looks like c# code but the question was not tagged with c# so I'm lost on how to implement this answer. Thanks! – Perchance

@ArvoBowen Thank you! This code is VB Script. If I remember it right, saving the code in a file with .vbs extension should let you execute it. Though it would be even better to rewrite it in PowerShell, now that it's widely available (my answer is from the time when PowerShell was just starting). – Oxbridge

That was going to be my second guess. ;) That should be very easy to implement then. I found this (technet.microsoft.com/en-us/library/dd206997(v=sql.105).aspx) that tells how to enable TCPIP for SQL via powershell (which is not needed because 2016 has a /TCPENABLED=1 switch to do that durring install). I need something that sets the TCP/IP port and possibly (if needed) sets the localhost IP# to enabled=true. – Perchance

Answer 2

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'

Gadabout