Adding SQL Server Express 2012 Advanced Services to Existing

SQL Express 2012 Remote Connections

Connection / May 30, 2020

SQL Express Connections

Important Background

SQL Server Express is by default installed as a named instance. That’s the “SQLEXPRESS” in the SQL server name, e.g. if your server is named SVR2012E, your default SQL Express instance will be named SVR2012E\SQLEXPRESS.

That’s important not only because you need that name to make network connections, but because named instances by default use dynamic TCP ports for their connections, i.e. the port number can change whenever SQL starts. You can reconfigure it to use a fixed port, but I wanted to see if I could get it to use dynamic ports.

SQL Server Setup

This part seems pretty well documented but I’ll repeat it here for completeness.

1. In SQL Management Studio, right-click on the server name and select Properties. Click on Connections, and in the right pane, check Allow remote connections to this server.

SQL Express Services

2. In SQL Server Configuration Manager, on the left side, highlight the SQL Server Services node. On the right side, highlight the SQL Server Browser service, right-click, and select Properties. On the Service tab, set Start Mode to Automatic. That will start it automatically whenever the server boots. Go ahead and start it manually now (right-click on SQL Server Browser and select Start).

SQL Express Network Protocols

3. Still in SQL Server Configuration Manager, on the left side, click on the SQL Server Network Configuration node, then highlight the Protocols for SQLEXPRESS node. On the right side, right-click on TCP/IP and click Enable. You can also look at the Properties, but they should already be set to allow dynamic connections.

4. After you enable TCP/IP connections, you’ll be advised that you need to restart the SQL Server for the new setting to take effect. You can do that from the SQL Server Services node by right-clicking on SQL Server (SQLEXPRESS) and selecting Restart.

SQL Express Firewall 1Windows Firewall Setup

This is the area where things seemed a little ambiguous.

1. This TechNet article says that when using dynamic ports, the inbound firewall on the server needs to allow access to the slqservr.exe program, not a port. So for SQL 2012 Express, create an inbound program exception allowing access to

C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\Binn\sqlservr.exe

Source: www.mcbsys.com