Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

All right, here is the deal:

I'm connecting to a Windows 2003 Server using VPN. On this server, there is a remote SQL Server 2005 Express engine.

I can connect to the database using Visual Studio 2008.

What I can't do though, is connect to this same database with SQL Server 2005 Management Studio (Standard).

I have checked the connection info a hundred times and still nothing.

One thought: do VS ans SSMS use the same sql provider?

Note: I'm running Windows 7 RC. I had absolutely no problem using the same config under Vista.

share|improve this question

4 Answers 4

up vote 3 down vote accepted

I'm assuming you've remembered to include the instancename (server\SQLEXPRESS)?

Try to create an ODBC against the same server. If you get the same error; create another ODBC, but use regular "SQL Server" driver, and on the "Client Configuration" settings choose "Named Pipes".

Also, SQLChickens tip about making sure the SQL Browser is started on the server is valid.

Also, try using IP-address instead of the NetBios name.

share|improve this answer
    
I don't what was wrong but I tried everything you suggested and now it woks. Thanks a million. –  Julien Poulin May 29 '09 at 19:53

If its a SQL Express engine you need make sure the SQL Browser service is running otherwise the client won't be able to see named instances on the server side.

share|improve this answer
    
I checked, this service is up and running. –  Julien Poulin May 29 '09 at 19:04

This post on MSDN has an a lot of other links and checklist to help solve the issue:

http://blogs.msdn.com/sql%5Fprotocols/archive/2007/03/31/named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server.aspx

From the post:

The various causes fall into five categories:

  1. Incorrect connection string, such as using SqlExpress.
  2. NP was not enabled on the SQL instance.
  3. Remote connection was not enabled.
  4. Server not started, or point to not a real server in your connection string.
  5. Other reasons such as incorrect security context.

Summary Checklist:

  1. Is your target server started?
  2. Is your target server listening on NP? Which Pipe?
  3. Has your client enabled NP? Use the same pipe to connect as Server?
  4. Are you making local connection? If so, what is the instance, default or remote?
  5. Did you put correct instance name in the connection string? Remember, Sqlexpress is a named instance.
  6. Did you enable remote connection? Firewall? IPSec? "File and Printer Sharing" opened? Can access server?
  7. Can you make basic connection by using or \? Use sqlcmd or osql.
  8. What is your repro step? What was your client APP doing during this error occuring? Which DB operation, detail?
share|improve this answer

I verified that in my case (with a near-identical problems statement), the server was fine, and I could get to it by disabling the Public profile of the Windows 7 firewall. (I could also reach a different SQL Server 2005 server, not on my VPN, so it was clearly a problem on my end, but also some sort of interaction with the server's configuration.)

The Inbound rules had 2 pair of rules for SQL Server Management Studio, allowing any UDP and any TCP ports, for the "Private" and "Domain" profiles, program SqlWb.exe. None of these was enabled.

I changed one pair of rules to serve for all three profiles (Public, Private, Domain), enabled them, and I was able to connect.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.