SQL Server Management Studio - Error connecting to remote DB

Question

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.

score 3 · Accepted Answer · answered May 29 '09 at 19:08

3

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.

answered May 29 '09 at 19:08

Frode Lillerud

1,656
3
18
20

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

score 1 · Answer 2 · answered May 29 '09 at 18:56

1

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.

answered May 29 '09 at 18:56

SQLChicken

1,307
8
10

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

score 0 · Answer 3 · answered Mar 24 '10 at 18:37

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.

score 0 · Answer 4 · answered Aug 28 '09 at 00:28

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

http://blogs.msdn.com/sql_protocols/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:

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

Summary Checklist:

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

SQL Server Management Studio - Error connecting to remote DB

4 Answers4