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;
[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).
- 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!
- 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.
- 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.
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