Home : Products : OPS SQL : Disabling the Windows Firewall when OPSSQL cannot connect to Microsoft SQL server
Q10271 - HOWTO: Disabling the Windows Firewall when OPSSQL cannot connect to Microsoft SQL server

Starting with Windows XP SP2, Windows Firewall is enabled by default. This can cause problems with the SQL Server running OPS, since Windows Firewall by default will block network traffic to the SQL server. When OPS cannot connect to the DB Server, this error message will popup:

OPS SQL failed to connect to Microsoft SQL

Connection string (located in OPSSQL.INI) :
Provider=SQLOLEDB;Data Source=LT024\OPSSQL;initial
catalog=@@@;User ID=OPSDBA;Password=###;OLE DB Services=-2;

Err.Desc :
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied..

 

 

To allow traffic to go through the firewall, the following 3 steps need to taken. Note that you need Administrator priviledges on the computer running the SQL server (or MSDE).

  1. Open the Windows Control Panel. Open the Windows Firewall utility and select the "Exceptions" tab.

    You'll need to add the SQL server instance to the list of exceptions.
    You'll need to locate the server instance installation directory. Assuming the OPS SQL software was installed under C:\OPSSQL, the default SQL Server (MSDE) location is C:\OPSSQL\DBMS\MSDE\MSSQL$OPSSQL
    You are now going to add this SQL Server instance to the list of exceptions. Click the "Add Program" button, then click the "Browse" button and navigate to the directory where the SQL Server (or MSDE) instance is located. Enter the "Binn" folder and select the "sqlserv.exe" executable.
    The program should now show in the Exceptions list. Make sure the checkbox next to it is checked!

  2. You need to know on which ports the SQL server (MSDE) is listening. There are two ports - UDP and TCP. UDP is 1434, and TCP is usually 1433. However, you should make sure. Click the Start button, then Run. Type "svrnetcn.exe" and press Enter. Select "TCP/IP" from the "Enabled Protocols" window and click "Properties".

    The number that appears is the TCP port the SQL Server instance is listening on. Write it down somewhere.

  3. You need to add the UDP and TCP ports to the "Exceptions" tab also. See the first point on how to get to the "Exceptions" tab in the Windows Firewall configuration. Clik the "Add Port" button. Select "TCP", enter the port number you wrote down previously. For name, you can write anything you want, e.g. "SQL TCP".

    Now repeat the same step, but for the UDP port. The port number is 1434. Name it something like "SQL UDP". Make sure the ports are listed in the "Exceptions" list and the check-boxes next to them are checked.

More info:

http://www.docs.com/HelpFiles/WindowsXPServicePack2Fix.htm

If still problems enter \\Datasource (where Datasource is string corresponding to Datasource value in error message) in Microsoft Explorer to check that computer can see server.

If still problems Turn firewall off just to see if this fixes problem

Related Articles
No Related Articles Available.

Article Attachments
No Attachments Available.

Related External Links
No Related Links Available.
Help us improve this article...
What did you think of this article?

poor 
1
2
3
4
5
6
7
8
9
10

 excellent
Tell us why you rated the content this way. (optional)
 
Approved Comments...
excelent article and screenshots Approved: 9/28/2006 9:12 PM
Created on 5/19/2006 12:55 PM.
Last Modified on 8/1/2008 10:02 AM.
Last Modified by No Author Name Available!.
Article has been viewed 7274 times.
Rated 6 out of 10 based on 10 votes.
Print Article
Email Article