Enabling Remote Connections in SQL Server 2008
In SQL Server 2008, SQL Server Surface Area Configuration Tool is not available. DBA needs to use SQL Server Configuration Manager (SQLServerManager10.msc) tool to enable remote connections in SQL Server 2008.
1. Open SQL Server Configuration Manager Tool by click Start | Programs | Microsoft SQL Server 2005 | Configuration Tools | SQL Server Configuration Manager.
2. In SQL Server Configuration Manager, expand SQL Server Network Configuration and the click Protocols for MSSQLSERVER:
3. In the details panel on the right side, right click TCP/IP and click on Enable. This will issue a warning that the changes will only come into effect once the database engine service is restarted. Click OK:
4. Follow the same steps to enable Named Pipes Protocol This will issue a warning that the changes will only come into effect once the database engine service is restarted:
5. On the Surface Area Configuration for Services and page, expand MSSQLSERVER, expand Database Engine Node and click services. On the right panel the details related to MSSQLSERVER services will be shown. click Stop and wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service to bring change into effect.
Conclusion
Configuring the Windows Firewall Exception for the SQL Server Database Engine will allow users to connect to SQL Server Instances when the Windows Firewall is enabled to secure the SQL Server. Once the exception is created then the Database Administrators can avoid database connectivity issues on SQL Server Developer and SQL Express Editions by proactively enabling the SQL Server Remote Connections, so that SQL Server Users who wanted to connect remotely can connect without any issues.
]]>