Adding Windows Firewall Exception for Accessing SQL Server Database Engine Running on Dynamic Port
1. In the Windows Firewall dialog box, click Exceptions Tab, and then click Add Program….
2. In the Add a Program dialog box, click Browse, and nagivate to the instance of SQL Server that you would like to access through the firewall, and then click Open. By default the SQLServr.exe will be loacted in C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLBinn location.
3. To close the Windows Firewall dialog box, click OK twice
Configure SQL Server to Accept Remote Connections
When users try to connect to an instance of SQL Server 2005 or SQL Server 2008 from a Remote computer, they may receive an error message as mentioned below. This happens when you try to connect to a SQL Server Instance which is not configured to accept Remote Connections. By default SQL Server Express and SQL Server Developer Editions don’t allow remote connections.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Enabling Remote Connections in SQL Server 2005
Database Administrators can enable remote connections by configuring SQL Server using the SQL Server Surface Area Configuration (SqlSAC.exe) tool.
1. Open SQL Server Surface Area Configuration Tool by click Start | Programs | Microsoft SQL Server 2005 | Configuration Tools | SQL Server Surface Area Configuration:
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3. On the SQL Server 2005 Surface Area Configuration for Servers and Connections page, expand MSSQLSERVER, expand Database Engine Node and select Remote Connections. On the right side panel you need to select Local and remote connections and select the appropriate protocol option among the three choices available depending upon your requirement. In the below snippet you could see that Using both TCP/IP and named pipes is selected:
4. Click OK to save the changes. This will open up Connection Settings Change Alert, informing you to restart the Database Engine Service for the change to take effect, Click OK:
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:
