How do I get this SQL Server ODBC Connection working?

Solution 1:

Named pipes and TCIP protocols for SQL server 2005 are disabled by default. Have you enabled them under "SQL server configuration manager"? You can find the protocols in the SQL Server Network configuration and under SQL Native client xx configuration.

The connection on the server itself works thanks to the "Shared memory" protocol.

Solution 2:

Have you enabled the SQL Server Browser service as per How to configure SQL Server 2005 to allow remote connections?:

If you are running SQL Server 2005 by using an instance name and you are not using a specific TCP/IP port number in your connection string, you must enable the SQL Server Browser service to allow for remote connections. For example, SQL Server 2005 Express is installed with a default instance name of Computer Name\SQLEXPRESS.

---

Solution 3:

This thread fixed the same error message for me when using Access to connect to a MSSQL 2008 server. MSSQL 2005 and earlier worked fine just using the computer name in the connection string, but I had to change to the connection string in Access to the full form when the instance was upgraded to 2008:

servername\instancename,portnumber

---

Solution 4:

I had this same issue and managed to resolve it by changing the SQL driver to the SQL Native Client driver too. This was weird as in my case this had been working fine for years and then just stopped working. I suspect this is something along the lines of the driver being corrupt, perhaps a reinstal of MDAC might assist, but for the time being I'm happy that it is working at all!