Enable remote connections to SQL Server

Based on http://blog.citrix24.com/configure-sql-express-to-accept-remote-connections/.

1. Enable remote connections on the SQL instance:
   1. Right-click the instance node in SSMS
   2. Select 'Properties' and select the 'Connections' pane

   3. Ensure that 'Allow remote connections to the server' is ticked

2. Enable TCP/IP and set SQL Server to listen on a fixed port (this is necessary for later firewall rule configuration):
   1. Open SQL Server configuration manager
   2. Select 'SQL Server Network Configuration' → 'Protocols for <instance_name>'
   3. Double-click 'TCP/IP'
   4. On the 'Protocol' tab, set 'Enabled' = 'Yes'
   5. Select the 'IP Addresses' tab and scroll down to the 'IPAll' section
   6. Set 'TCP Dynamic Ports' to blank, 'TCP Port' = 1433
   7. In SQL Server Configuration Manager, select 'SQL Server Services'
   8. Right-click your SQL instance service and select 'Restart'
   9. Make a note of the service's 'Process ID'

   10. At a command prompt, enter:

       netstat -ano | find /i "<process_id>"

       The returned records should indicate that the process is now listening on port 1433, e.g.:

       

3. Start the SQL Server Browser service¹⁾:
   1. In SQL Server Configuration Manager, select 'SQL Server Services'
   2. Right-click 'SQL Server Browser'
   3. On the 'Service' tab, set 'Start Mode' = 'Automatic' and click 'Apply'

   4. On the 'Log On' tab, click 'Start'

4. Create Windows firewall rules to allow inbound traffic to:
   1. port 1433, TCP;
   2. port 1434, UDP;
   3. program “sqlservr.exe” (you'll find this in “C:\Program Files\Microsoft SQL Server\MSSQL<VERSION>.<INSTANCE_NAME>\MSSQL\Binn\”);
   4. program “C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe”.