Enable remote connections to SQL Server
Based on http://blog.citrix24.com/configure-sql-express-to-accept-remote-connections/.
- Enable remote connections on the SQL instance:
- Right-click the instance node in SSMS
- Select 'Properties' and select the 'Connections' pane
Ensure that 'Allow remote connections to the server' is ticked
- Enable TCP/IP and set SQL Server to listen on a fixed port (this is necessary for later firewall rule configuration):
- Select 'SQL Server Network Configuration' → 'Protocols for <instance_name>'
- Double-click 'TCP/IP'
- On the 'Protocol' tab, set 'Enabled' = 'Yes'
- Select the 'IP Addresses' tab and scroll down to the 'IPAll' section
- Set 'TCP Dynamic Ports' to blank, 'TCP Port' = 1433
- In SQL Server Configuration Manager, select 'SQL Server Services'
- Right-click your SQL instance service and select 'Restart'
- Make a note of the service's 'Process ID'
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.:
- Start the SQL Server Browser service1):
- In SQL Server Configuration Manager, select 'SQL Server Services'
- Right-click 'SQL Server Browser'
- On the 'Service' tab, set 'Start Mode' = 'Automatic' and click 'Apply'
On the 'Log On' tab, click 'Start'
- Create Windows firewall rules to allow inbound traffic to:
- port 1433, TCP;
- port 1434, UDP;
- program “sqlservr.exe” (you'll find this in “C:\Program Files\Microsoft SQL Server\MSSQL<VERSION>.<INSTANCE_NAME>\MSSQL\Binn\”);
- 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.