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. Select 'SQL Server Network Configuration' → 'Protocols for <instance_name>'
    2. Double-click 'TCP/IP'
    3. On the 'Protocol' tab, set 'Enabled' = 'Yes'
    4. Select the 'IP Addresses' tab and scroll down to the 'IPAll' section
    5. Set 'TCP Dynamic Ports' to blank, 'TCP Port' = 1433
    6. In SQL Server Configuration Manager, select 'SQL Server Services'
    7. Right-click your SQL instance service and select 'Restart'
    8. Make a note of the service's 'Process ID'
    9. 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 service1):
    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”.


1)
Note that SQL Server security best practice suggests that the browser service should be disabled, but you may take a different view, particularly if your server is not public-facing.