We were working with a client recently and their ODBC connection worked fine when the Windows Firewall was disabled.  However, when we turned the Windows Firewall on, the ODBC connection did not work.

This problem can be rectified by creating an Outbound and Inbound Rule as follows:
Creation of Outbound Rule
1. In the Windows Firewall with Advanced Security, in the left pane, right-click Outbound Rules, and then click New Rule in the action pane.

2. In the Rule Type dialog box, select Port, and then click next.

3. In the Protocol and Ports dialog box, select TCP. Select Specific local ports, and then type the port number of the instance of the Database Engine, such as 1433 for the default instance. Click Next.

4. In the Action dialog box, select Allow the connection, and then click Next.

5. In the Profile dialog box, select any profiles that describe the computer connection environment when you want to connect to the Database Engine, and then click next.

6. In the Name dialog box, type a name and description for this rule, and then click Finish.

 

Creation of Inbound Rule
The Inbound Rule will allow Sqlserver.exe to be accessed through the firewall. To do this, please follow the steps below:

1. In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule in the action pane.

2. In the Rule Type dialog box, select Program, and then click Next.

3. In the Program dialog box, select This program path. Click Browse, and navigate to the instance of SQL Server that you want to access through the firewall, and then click Open. By default, SQL Server is at

C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBinnSqlservr.exe.

4. Click Next.

5. In the Action dialog box, select Allow the connection, and then click Next.

6. In the Profile dialog box, select any profiles that describe the computer connection environment when you want to connect to the Database Engine, and then click Next.

7. In the Name dialog box, type a name and description for this rule, and then click Finish.

 

Confirm Communication with Server
At this point, you should also verify that you are able to ping the server. To do this, please follow the steps below:
1. Click Start > RUN
2. Type in CMD
3. The Command Prompt window will open type the following.
Ping ServerName (Enter the SQL Severs servername after Ping)
Ping ServersIPAddress (Enter the IP address of the SQL Server for ServersIPAddress)

Verification

Finally, you will need to verify this solution by trying to create the ODBC connection using both the server name and the IP address.

If you need further assistance, please contact Complete Software at 410-740-1090; by email at support@completesoftware.net.

Complete Software is a Microsoft Dynamics GP Value Added Reseller (VAR) specializing in Microsoft Dynamics and custom software solutions since 2004. To learn more about us, please visit our website at www.completesoftware.net.