These instructions will help you properly configure SQL Server to allow connection of client computers to a server computer in a network with the Windows Firewall enabled. These instructions do not extend to any antivirus firewalls that may also be installed on your workstations. For help with these, refer to vendor specific support literature or a local computer technician.
Configuring SQL Server
To connect client computers in a network environment, the SQL Server instance you’ll be using needs to be properly configured to allow client computer connections.
On the Server computer navigate to SQL Server Configuration Manager. Click Windows Start > All Programs > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Configuration Manager. If you cannot find it under that path, open File Explorer and navigate to C:\Windows\SysWOW64 and then select SQLServerManager.
In the left pane, double-click on SQL Server Network Configuration and highlight Protocols for CMJ.
In the right pane, right click on TCP/IP and select Enable if it’s not already. You should get a warning window to restart SQL Server. Click OK. Right click on TCP/IP again and select Properties.
In the TCP/IP Properties window, click the IP Addresses tab. Scroll to the bottom and in the IP All section, delete whatever contents are in the TCP Dynamic Ports row and type 1433 in the TCP Port row. Click Apply once completed.
You should get a warning to restart SQL Server. Click OK.
Click OK to close the TCP/IP Properties window then click SQL Server Services in the left pane.
Right click on SQL Server (CMJ) and select Restart.
Close the SQL Server Configuration Manager. SQL Server is now configured properly to allow client connections to the server computer.
Configuring Windows Firewall
To connect a client to a server in a network environment, Windows Firewall will likely need added rules and/or exceptions. If you cannot connect your client to the server with Windows Firewall enabled, then follow these steps on all the computers in your network to configure it properly to allow connections.
Go to Control Panel and click on Windows Firewall. If Control Panel is set to Category view, click System and Security then Windows Firewall.
Verify that Windows Firewall is set to On (recommended). If it’s not, select Turn Windows Firewall on or off from the left panel and set the radio buttons to Turn on Windows Firewall for both Home or work (private) and Public network settings. Click OK to accept changes.
Click Advanced Settings in the left panel and select Inbound Rules in the left pane and then New Rule in the Actions panel on the right.
In the New Inbound Rule Wizard select Port then Next.
Select the TCP and Specific local ports radio buttons. Type 1433, 1434 and click Next.
Select Allow the connection and click Next.
Apply the rule to Domain, Private, and Public by checking all three boxes and click Next.
Name the rule SQL TCP (1433,1434) and click Finish.
Click New Rule in the Actions panel again.
This time select the UDP and Specific local ports radio buttons. Type 1433, 1434 again and click Next.
Select Allow the connection and click Next.
Apply the rule to Domain, Private, and Public by checking all three boxes and click Next.
Name the rule SQL UDP (1433,1434) and click Finish.
Scroll down the list of Inbound Rules until you fine File and Printer Sharing (Echo Request - ICMPv4-In). There will be multiple of this rule, if they do not already have a green circle with a check mark, right click on each of the rules individually and select Enable Rule.
Select Outbound Rules in the left pane and then New Rule in the Actions panel on the right.
In the New Outbound Rule Wizard select Port then Next.
Select the TCP and Specific local ports radio buttons. Type 1433, 1434 and click Next.
Select Allow the connection and click Next.
Apply the rule to Domain, Private, and Public by checking all three boxes and click Next.
Name the rule SQL Server (TCP) and click Finish.
Click New Rule in the Actions panel again.
This time select the UDP and Specific local ports radio buttons. Type 1433, 1434 again and click Next.
Select Allow the connection and click Next.
Apply the rule to Domain, Private, and Public by checking all three boxes and click Next.
Name the rule SQL UDP (1433,1434) and click Finish. All the Outbound Rules needed are now created.
Scroll down the list of Outbound Rules until you fine File and Printer Sharing (Echo Request - ICMPv4-Out). There will be multiple of this rule, if they do not already have a green circle with a check mark, right click on each of the rules individually and select Enable Rule.
These are all the exceptions Windows Firewall requires to be enabled and allow client connections to the server. If you still cannot connect with Windows Firewall disabled and you are certain your network is functioning properly, then it is likely you will need to create exceptions in other anti-virus or hardware firewalls installed on your workstations. The same ports (1433 & 1434) detailed herein are the ones that will need to be opened on those firewalls.