Unable to connect

Question

I am unable to connect SQL Server Management Studio to any of the databases on my computer. At this stage I am simply starting the Management Studio and asking it to connect to a database I made a few months ago before this connection trouble arose. I'm specifying the server name "BENCHLAP" which is the name of My Computer, using Windows Authentication.

The error I get is:

An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

The client is local. At least, it is if I understand correctly - the instance of SQL Server is installed on the same machine as the instance of IIS that I intend to use to test my web applications after we have resolved this issue, and the same machine as the one I shall use to view the application on.

I can ping the server if i type "ping -a BENCHLAP". It is successful

I think it also connects via telnet ok as well, by typing "telnet BENCHLAP 135"

All client protocols are enabled, as are the server protocols. Don't know if this is a security risk but I did it to try and solve the problem, to no avail. There are no aliases, according to the Configuration Manager.

The server itself is SQL Server Express 2005, runnning on Windows XP Professional. My anti virus software is AVG.

I'm afraid I don't know anything more, and have been researching this error for quite some time now.

Is anybody able to help me please?

Ben

Answer 1

This doesn't necessarily solve your problem, but try specifying the server as "." or "localhost" and see if you can connect to the local server. I typically use them as the name for the server when doing local development and I always connect fine.

Maybe once you get connected using one of the above, you will see the correct server name or understand the problem.

Regards,

Dave

Answer 2

I'm afraid that didn't make any difference, Dave. Thanks very much for your input but I get the same error message with the server specified as "localhost", "." and "benchlap".

Has anybody got any other ideas please?

Answer 3

Please take a look at the following blog that describes the typical problems customers have encountered when connecting to SQL Express.

http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

Specifically, SQL Express is installed as a named instance.  As such to connect to SQL Express you need to specify "<MachineName>\SQLEXPRESS" as the SQL instance name.  Please also make sure that "SQL Browser" service is running and the Windows OS firewall is not blocking UDP port 1434.

HTH,
Jimmy

Answer 4

Thank you! Specifying BENCHLAP\SQLEXPRESS seems to have solved the problem, although I have to confess, I haven't learnt a thing! What's a named instance? What are its implications? What's SQL Server Browser? Despite being connected via the Management Studio, I'm being told by the SQL Server Configuration Manager that the SQL Browser is "stopped". Will that prevent my web applications working? I've successfully built an entire relational database using the thing but still cannot understand all the configuration issues behind the software.

Can you please point me in the direction of a dummies guide, it seems so desperately complicated, despite being billed as "easy to use"!

Thanks again for your help so far
Ben

Answer 5

Hi Ben,

Have a look at the following website http://msdn.microsoft.com/sql/express/default.aspx for an overview of what SQL Express is.

Regarding named instances, SQL Server 2000 and SQL Server 2005 support the ability to install multiple instances of the product on the same machine.  So, it is possible to have multiple SQL Server services running on the same machine.  What is typically referred to as the default instance is the copy of SQL Server that typically listens on TCP port 1433.  All other SQL Server instances installed on the machine are referred to as "named instance".  These "named instances" do not listen on a standardized TCP port.  They pick one that is available at the time.  Because the TCP port is not fixed, in order for client applications to be able to discover the connectivity information, the SQL Browser service was created.  SQL Browser service is new in SQL Server 2005.  In SQL Server 2000, there is an equivalent service.

Regarding your question on whether your application will work without having SQL Browser service running, as long as your web application is running on the same machine as the SQL Server Express instance, you should not have any problems.  If you start running your web application on multiple IIS machines, but continue to connect to the same SQL Express service (ie. running on a remote box), you will need to make sure that the SQL Server Express instance is configured for remote connectivity and the Windows OS Firewall is open for connectivity to the port.

HTH,
Jimmy

Answer 6

 

Hi,

 

I have the same problem like Ben. Trying to connect to database engine but failed all the time. I use a SQL 2005 Enterprise Eval version. When I connect to database engine, I put <my computer name>\SQLEXPRESS, it gives me this message:

 

An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)

 

I checked the sqlbrowser and it's running. I also enabled port 1434 in Firewall. But it still doesn't work.  However, when I connect to analysis service using just <my computer name>, it lets me connected. I tried many ways to fix this problem but still no luck.  Could anyone help me solve this problem please? Thanks so much.

 

Carl

Answer 7

Hi, I had the same problem with one of my clients. I was able to resolve it by providing the server name as localhost\sqlexpress. Try this if it works.

 

Best luck,

Ivan