SQL Server 2005: how to allow remote connections

When running instances of SQL Server on laptops/PCs, it can be troublesome allowing others on the network to connect to them. Keith Westcott has written up this guide. Obviously paths may need to be amended to suit your particular environment:

  1. Start SQL Server Surface Area Configuration.
  2. Click Surface Area Configuration for Services and Connections.
  3. Expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.
  4. When the 'Changes to Connection Settings will not take effect...' dialogue appears click OK.
  5. Under Database Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, then click Start.
  6. Set SQL Server Browser Startup type to Automatic.
  7. Click Start, then click OK.
  8. Start SQL Server Configuration Manager.
  9. Right click the SQL Server Browser service and select Properties.
  10. Click the Advanced tab and note the instance ID (MSSQL.#).
  11. Open Windows Firewall, click the Exceptions tab.
  12. Click Add Program and browse to C:\Program Files\Microsoft SQL Server\MSSQL.#\MSSQL\Binn\sqlservr.exe, click Open, click OK.
  13. Repeat steps 11 and 12 for each instance of SQL Server 2005 that needs an exception.
  14. Browse to C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe, click Open, click OK.


Find out more