I'm working on an application written in VB6 and I'm having trouble connecting to a SQL Server 2008 database that has been set up on a server in our office.
Here are the symptoms:
- When I try to connect using a connection string via ADO in VB6, VB6 hangs and then finally gives me the error: "Run-time error '-2147467259 (80004005)': [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied."
- if I make a blank .udl file and double-click on it, in the Connection tab of the Data Link Properties dialog, in the dropdown where it says "Select or enter a server name", if I click on the dropdown to see the list or click the "Refresh" button, it hangs.
When I try to connect via SQL Server 2005 Management Studio (SQL Server 2005 is installed on the client machine on which I am working on the VB6 program), when I try to connect via:
- TCP/IP: I get the message "This version of Microsoft SQL Server Management Studio can only be used to connect to SQL Server 2000 and SQL Server 2005 servers. (ConnectionDlg)"
- Shared Memory: I get the message "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 41 - Cannot open a Shared Memory connection to a remote SQL server) (Microsoft SQL Server, Error: 87)"
- Named Pipes: "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)"
Here are some of the common proposed solutions I have seen online which I have implemented (and of course have not as of yet solved the problem):
- Remote connections have been enabled in SQL Server 2008
- telnetting to port 1433 gives me a blank screen (i.e., it connects, it doesn't give me an error)
- netstat -a on the server shows that TCP port 1433 is open
- Mixed Authentication is already set up on SQL Server 2008 so it can accept either Windows authentication or SQL Server authentication
I'm really at the end of my rope and have no idea where to go with this, since I don't know where (or even if) there are detailed logs that I can browse that will tell me when a connection attempt starts and what happens each step of the way. I'm hoping there's a simple fix for this that I'm not knowledgeable enough to know of.
Update: Here are answers to the follow-up questions asked so far:
- OS: Vista 64-bit, on both client and server
- SQL Browser Server is running on the server, according to SQL Server Configuration Manager
- Connection String - the line in question in the VB program is thus: fooSQL.Open "SERVER=SERVERNAME;DRIVER=SQL Server;DATABASE=DATABASENAME", username, password (where fooSQL is an ADODB.Connection)
- still happens when Firewall is turned off on either client or server (or both)
re: MDAC
as per This wikipedia article I checked HKEY_LOCAL_MACHINE\Software\Microsoft\DataAccess\FullInstallVer and the value is "6.0.6001.18000". I also ran Component Checker and on startup when I click "Perform analysis of your machine and automatically determine the release version", I get the following message after I click "OK":
"The following product releases were matched:
UNKNOWN"
Update: SQL Services re: gator's comment, here are the SQL Services running on the server in Computer Management > Services
- SQL Active Directory Helper Service
- SQL Full-Text Filter Daemon Launcher (MSSQLSERVER)
- SQL Server (MSSQLSERVER)
- SQL Server Agent (MSSQLSERVER)
- SQL Server Analysis Services (MSSQLSERVER)
- SQL Server Browser
- SQL Server Integration Services 10.0
- SQL Server Reporting Services (MSSQLSERVER)
- SQL Server VSS Writer