Trying to connect to a WIMS/OPS SQL installed instance of MSDE from a client computer get error: SQL Server does not exist or access denied

(example screenshot)

The following is a list of possible causes:

• Data Source is set incorrectly in the hach_wims_client.ini connection setting.  Verify it is correct.
• Windows Firewall is blocking traffic on port (default 1433) . Cick here for info on how to disable Windows Firewall
• Other Firewall software is blocking traffic to server (Zonealarm, Norton, etc)
• MS SQL 2005 was running when MSDE was installed.

Troubleshooting Steps:

1. Make sure the SQL Server is in running state on the server that you are attempting to connect to.
   
2. Check the data source setting.  Check that the Data Source = ServerName\OPSSQL.  On the server, verify the ServerName is correct.  From the client ping the server from the command line:  PING Servername.  If you cannot ping it you may have DNS problems.  Substitute the IP Address of the server for the servername. 
   
   Example hach_wims_client.ini Data Source Setting:
   HACHWIMS_CONNECTION_DATASOURCE=192.168.19.1\OPSSQL
   
   Example OPSSQL.INI connection string setting:
   2=Provider=SQLOLEDB;Data Source=192.168.19.1\OPSSQL;initial catalog=@@@;User Id=OPSDBA;Password=###;OLE DB Services =-2;
   
3. Make Sure the Windows Firewall is turned off.  Click here for info on how to disable Windows Firewall.  Make sure other firewall software is not interfering in communication.
   
4. If MS SQL 2005 was running when MSDE was installed, we may have a problem between the  SQL Server Service Manager (MS SQL 2000 service that handles client request to connect to the server) and the SQL Server  Browser (MS SQL 2005 service that handles client requests).  Follow these steps:

Shutdown the MSDE service using Services.MSC.  (Name MSSQL$OPSSQL)

Shutdown SQL Server Service Manager

Shutdown the Browser

Start the Browser

Start MSDE

Test Connection from client computer.

NOTE:  We cannot verify that all steps are required, only that this solved the problem in one instance where these conditions existed.  The MS SQL 2005 instance was running as part of Wonderware InSQL.

Also, note that if you specify the port number in the connection string (supported in OPS SQL v 6.9.4 and later) you can connect.  However this may be dangerous as ports can be randomly assigned when the Server is rebooted.  Example Connection String:  

2=Provider=SQLOLEDB;Data Source=192.168.19.1\OPSSQL,1737;initial catalog=@@@;User Id=OPSDBA;Password=###;OLE DB Services =-2;

Screenshot below shows SQLConsole connecting using the Port Number option in the connection string:

 

NOTE:  To better understand the SQLBrowser you may want to start it from the command line with the -c option to see how incoming requests are handled:

5. Particularly if you provided your own MSDE database or it already exists, you need to make sure the Named Pipes and TCP/IP protocols are enabled. To do this go to command prompt and run svrnetcn. After you enable these protocols you must restart the windows service for the SQL server instance.