SQL Protocols : SQL Server 2005 connectivity error messages

Comments

Joe SQL said:

What is the value of this blog? You've posted the error messages - likely the messages we are seeing - but there are no suggested resolutions.

# November 10, 2005 12:18 PM

This is a quick list compiled from the early Beta experience with SQL Server 2005. To get it out quickly I tried to list at least the root cause. I plan to update it with resolutions and more error messages moving forward.

If you encountered any specific message, can you let me know?

I will be glad to work with you on resolution, an updating this blog accoprdingly.

You may also want to check additional blogs posted by Ming Lu from our team, which contain resolution to many of the connectivity problems:

http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx

http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx

# November 10, 2005 2:42 PM

Lunchbox027 said:

I've been getting the following message when trying to run Management Studio Express and also a similar error with Altiris Depoloyment Solutions for Clients 6.5 trying yo connect to SQL Server Express.

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: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error: 10061)

My TCP/IP and Named Pipes are both enabled, I've reinstalled everything several times with no luck. I'm just a Tech, and still learning my way around databases so there might be something simple that I missed. :P My boss has only had a couple minutes to help me out and he couldn't come up with anything. Any info you could give would be greatly appreciated, and I will keep you updated as to my failures/success so you can help build your FAQ.

# December 1, 2005 4:28 PM

SQL Protocols said:

Error[provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it] indicates that your TCP/IP was not enabled or your sql server was not listening on the appropriate port.
1) After installing SQL Express, by default, TCP/IP was disabled, please see http://blogs.msdn.com/sql_protocols/archive/2005/11/14/492616.aspx to enable remote connection for SQL Express.
2) Please check errorlog to make sure server TCP was enabled.
- Key words indicates server TCP/IP is enabled –

Server is listening on ['any' <ipv4> <Port Number>] or

[ <ipaddress> <ipv4> <Port Number>].

First one is when enabling server “ListenonAllIPs”, and second one is when server listening on individual IP. To further verification, you can test whether sql server is listening on the exact port using "netstat -ano| findstr <PortNumber>".

3)Try to connect to the exact port that server was listening on and see what happens.

If you still face the same problem, please attach your errorlog info and your connection string(eg, you connect through "."/"(local)"/<hostname>, etc.. and whether you specified any protocol prefix(eg, lpc:/np:/tcp:).

Thanks
Ming.

# December 7, 2005 1:17 AM

Bob Anderson said:

I'm seem to be having an upgrade issue. I have upgraded my client tools to 2005 and am trying to connect to an existing SQL Server 2000 box. I have a named data source pointing to a port on my local machine which is then forwarded through an SSH connection. The 2000 client tools had no trouble connecting; I also have no trouble connecting via a Perl script, but I can't connect using Management Studio. I get the first error you have listed above.

Any ideas what the problem might be? Thanks

# January 2, 2006 6:45 PM

SQL Protocols said:

Hi, Bob

As Peter mentioned in the blog, your sql server might not listen on TCP port properly under this situation.Please follow below steps to troubleshoot:
Go to Server Network Utility, if you enabled TCP on the server,you can see TCP/IP is in the "Enabled Protocols" and click properties of it, get which port configured,then

1)netstat –ano |findstr <PortNum>

2)go to server ERRORLOG see whether server is listening on TCP protocol,you should see "SQL Server is listening on TCP" in the log.

3)or use c:\Program files\Microsoft SQL Server\90\tools\binn\osql /S<Instance>,<PortNum>, see what happens.

4)It sound that you connect to SQL Server using alias, so, please go to SQL Server Client Network Utility,choose "Alias" tab, check whether you configured the same port that server was listening on in the TCP Alias properties.

If you still have further question, please attach the log file and the connection string( how do you make connection).

Thanks!
Ming.

# January 3, 2006 5:58 PM

SqlRick said:

This may be plain silly, but I was struggling with this for 30 minutes because I used a small -s instead of a large -S when specifying the server. You get this connectivity error message instead of a command syntax message and believe your network connectivity is the issue.

# January 20, 2006 1:32 AM

SQL Protocols said:

if you try "osql /?" or "sqlcmd /?", you will see:

-S:server
-s:colseparator

Hence, if you make connection like:
"OSQL /s<machinename> /E" it equals to "OSQL /S /E" without specify server and by default it make connection to local default instance.

Thanks!
Ming.

# January 23, 2006 10:41 PM

Junifer said:

Hello guys,

I just downloaded and installed the VWD 2005 Express Edition, i followed the tutorial on the msdn until i got this exact error:

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

Sorry but i'm in .net i am a asp developer for years now but i use msaccess so no experience in SQL Server.

Thanks,
Junifer.

# February 5, 2006 12:35 AM

SQL Protocols said:

If you need to access the SQL Server back-end from a remote machine you need to enable remote connections. See this for a reference:

http://blogs.msdn.com/sql_protocols/archive/2005/11/14/492616.aspx

Thank you,
Peter

# February 6, 2006 5:59 PM

Nitin said:

I am getting the following error while i try to open a SqlConnection(using C#) if the SQl Server is paused.

Source : .Net SqlClient Data Provider
Message : A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The system cannot open the file.)

The error number for this err is 4. (SqlException.Number)

However if the open this connection again then i get the following error

Source : .Net SqlClient Data Provider
Message : SQL Server has been paused. No new connections will be allowed.

Login failed for user '(null)'.

So this is correct and the error number for this is 17142.

So my question is why does i get the error number 4 in the first place and the subsequent runs gives the correct error.

# February 7, 2006 4:31 PM

Vitgar said:

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) (Microsoft SQL Server, Error: 2)

to correct this error in SQLEXPRESS on servername use [servername]\sqlexpress, this should work

# February 13, 2006 2:04 PM

Aji Jose said:

Please give me a solution for the following error, which occured when i am trying to connect sql server2005 from remotely.

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: 28 - Server doesn't support requested protocol) (Microsoft SQL Server, Error: -1)

# February 13, 2006 4:43 PM

angi said:

Please give me a soluten for the following error when trying to open the migrated DTS-Packages from SQL Server 2000 on the MSDB-Node/Refresh:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

Login timeout expired
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.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (Microsoft SQL Native Client)

------------------------------

Login timeout expired
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.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (Microsoft SQL Native Client)

------------------------------
BUTTONS:

OK
------------------------------

# February 14, 2006 8:32 AM

Gokul said:

hi, i get this error when i run my project. i am using a remote server. it is running. but i am getting the error when i am adding a webpart to the page. otherwise it is running fine. do clear my bug. thanks.

gokul.

# February 28, 2006 5:37 AM

SQL Protocols said:

If the error message contains the string "SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified" it is most likely caused by one of the two following causes:

(1) The SQL Browser process is not running on the server box.

and/or

(2) The SQL Browser's UDP port 1434 is blocked by firewall. You can grant exception either to UDP port 1434 or to SQL Browser process.

# February 28, 2006 12:54 PM

Shahid said:

Can someone please help me with this error.

===================================

Cannot connect to CNF52307XV.

===================================

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: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=10061&LinkId=20476

------------------------------
Error Number: 10061
Severity: 20
State: 0

------------------------------
Program Location:

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

# March 3, 2006 12:57 PM

SQL Protocols said:

Shahid,

This error suggests that (a) your client specifies to use TCP but (b) your server is not listening on the TCP protocol but (or at least not on its default port).

Regarding (a): the TCP protocol is most likely specified in the connection string or in an alias with the name of the target server. Is your intent to use TCP?

If yes, we get to part (b). Most likely you need to enable the TCP protocol on the server, and retsart the server. Instructions on how to do this for SQL Express are in http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx. The steps are the same for other SQL Server editions, just "Click on the Protocols for MSSQLSERVER" instead of "SQLEXPRESS" if your server is installed as a default instance (or for the appropriate instance name if it is a named instance).

Peter

# March 3, 2006 10:48 PM

Saeid said:

Hi
When i install Sqlserver Express it doesn't ask about Default or Instance Setup. After installing it connect as ServerNema\SQLEXPRESS
I want to change connection name to just ServerName. What should i do?
Thank you for helping me.

# March 15, 2006 12:59 AM

SQL Protocols said:

Hi, Saeid

By default, SqlExpress was installed as a named instance and with TCP and Named Pipe are disabled.

You can not change a named instance to a default instance, if you really want to use only servername in the connection string, you can spcify the tcp port like "Server=<machinename>,<port>" after you enabled Tcp; but if you only want to use shared memory or named pipe, we suggest you keep using "Server=<machinename>\sqlexpress".

Here is useful info for SqlExpress

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

# March 15, 2006 12:57 PM

SQL Protocols said:

And if you really want to access it through Server=<machinename> you can create an alias through SQL Server Configuration Manager with the name of the machine connecting to your SQL Express.

Thanks,
Peter

# March 15, 2006 1:07 PM

Aaron Gregory said:

Has anyone tried accessing a SQL 2005 Enterprise from a local machine with Symantec Client Firewall (v 7.1.0) installed? When trying to update diagrams locally, I get the following message with the firewall enabled:

"A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (.Net SqlClient Data Provider)"

* * *

Of course, when disabled, I am able to update diagrams.

Does anyone know of a workaround to update diagrams locally with the firewall enabled? Or, is that just a pipedream?

Thanks.

# March 20, 2006 6:18 PM

SQL Protocols said:

Try specifying the server by its loopback IP address, 127.0.0.1 in the IPv4 case. And let us know the result.

Thanks,

Peter

# March 20, 2006 7:22 PM

SQL Protocols said:

Alternatively, you can use a protocol other than TCP (Shared Memory and/or Named Pipes) if you have it enabled on your SQL Server. By default SqlClient first tries to use Shared Memory on a local machine.

# March 20, 2006 7:33 PM

Christopher Cheng said:

For those who gave up, try one more thing:
Start "SQL Server Configuration Manager",
In "SQL Server 2005 Network Configuration" > "Protocols for SQLEXPRESS", Click on the tab "IP Addresses", Change all TCP Port to 1433.
Yes, I mean all, even "IPAll", then it will work!

# March 21, 2006 12:54 AM

Daryl said:

I am getting a very troublesome error using SQLExpresss 2005. I had a developer write an application utilizing SQLExpress 2005, and I can connect to the server, view all the data (customers, orders, setup tables, etc.), I can connect to the database via Access (ODBC), everything works fine until I try and print an order. Then it comes up and says "An error has occured while establishing a connections to the server. When connecting to SQL Server 2005, this failure may be cuased 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). I have enabled remote connections, both TCP/IP and named pipes, made sure that both loopback and actual machines IP address are not only active, but enabled, and like I said previously, am able to view & update data through application, but can seem to print. Not sure what is going on. ANY help would be greatly appreciated. Thanks in advance.

# April 17, 2006 11:46 AM

Matt Neerincx [MSFT] said:

You need to contact the developer who wrote the Access application. The report you are trying to print is referencing an old linked table most likely. Open the report in design view and select View|Properties and look at Record Source property, this might give you a clue. But in general the report could be referencing an old linked table just about anywhere so you have to dig through the report in design view until you find it.

# April 17, 2006 8:52 PM

Arthur Berlin said:

I also have this error 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)

...And I disabled de firewall, added SQLSERVER to Firewall exceptions, executed aspnet_regsql.exe to add the instance and nothing... configuring in options of visual studio environment to setup the instance of SQL Server 2005... I have this backgrouds: I have installed SQL Server 2000 and Visual Studio .NET 2003 and I have not SQLEXPRESS Edition but SQL Server 2005

What should I do??

Thsnks a lot...

# April 29, 2006 5:20 PM

Paul Nguyen said:

If you are trying to remote connection to Sql Server 2005, you need to enable your TCP/IP Protocol in your SQL Server Configuration Manager.

SQL Server Configuration Manager
-> SQl Server 2005 Network Configuration
-> Protocols for <instance>

After enabling the TCP/IP, you need to restart the SQL Server for the changes to be in affect.

# May 2, 2006 12:27 PM

JInn said:

When my PC connecting the SQL Server 2005,
the Error occurred as follows:
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 setting SQL Server does not allow remote connections.(provider:Named Pipes Provider,error:40 - Could not open a connection to SQL Server)(Microsoft SQL Server,Error:53)

But others pc could connect it!
The TCP/IP is enabled state and Named Piped is enabled state

# May 7, 2006 6:52 AM

Srinivas said:

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)

# May 8, 2006 4:22 AM

SQL Protocols said:

Jinn,

Error code 53 often indicates that the server is not reachable from the client machine. If you run the following two commands on the client machine, what do you get (I assume the server is installed as a default intsnace):

telnet <serverMachineName> 1433

ping <serverMachineName>

# May 8, 2006 8:39 PM

SQL Protocols said:

Srinivas,

Are you able to retrieve the Number member of the SqlError objact in the SqlErrorCollection contained in the SqlException thrown?

# May 8, 2006 8:41 PM

Andrew said:

Does anybody know how to fix the following:

Unable to complete login process due to delay in
prelogin response.

This seems to happen every 1-2 hrs and we have to restart the SQL service, then it is fine.

We usually use tcp/ip for our connections.

When the server starts refusing connection on tcp/ip, we can still connect using namedpipes or the dedicated admin port (tcp 1434).

A few sites mention the erro but no-one can suggest a fix or even further routes for investigation.

Thanks in advance

# May 21, 2006 4:32 AM

Koterpillar said:

"An error has occurred while establishing 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: HTTP Provider, error: 0 - )"

This occurs randomly, but always under heavy _client_ load. I suppose client first tries some other "provider", but fails, and HTTP provider isn't configured.

# May 29, 2006 9:48 AM

Lil Turtle said:

I am getting the following error when trying to run an ASP.NET 2.0 application using a connection to SQL Server 2000,

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: Shared Memory Provider, error: 40 - Could not open a connection to SQL Server)

I don't understand why the application is trying to connect to SQL 2005 server when it is not specified.

Thanks.

# May 30, 2006 11:50 AM

SQL Protocols said:

1) Were you trying to make local or remote connection?
2) What does your connection string looks like, please try removing "lpc:" prefix if you specified.
3) Run *cliconfg* in the command line, check off box "enable shared memory protocol".
4) Double check whether your SQL 2000 instance started successfully, and listen on shared memory / named pipe/ tcp from server errorlog.

If you still face problem, please provide your connection string and server errorlog.

Good Luck!
Ming.

# May 30, 2006 1:58 PM

Matt Neerincx [MSFT] said:

Additional note, this error message tends to confuse alot of customers with the SQL 2005 part. The error message can occur when you attempt to connect to ANY version of SQL Server, it does not mean that the driver is trying to connect to some SQL 2005 server.

# May 30, 2006 2:07 PM

Harold Bright said:

If you are connection to a local sql server using SqlClient try using Server Name as '.' this worked for me.

But i am not able to connect to the remote server running sql server 2000. I also tried modifying the HOSTS file still not able to work out. Please drop a mail on my website if someone finds something. thanks.

# May 31, 2006 7:43 AM

Varghese Cherian said:

I had problems connecting to my SQL Server edition that came with Visual Web Developed 2005 Express Edition version. The error was that a remote connection could not established error: 40. I solved it by opening SQL Server Configuration Manager explan SQL Server 2005 Network Configuration clicked on Protocols for SQLEXPRESS to find that Shared Memory was enabled but TCP/IP wasnt and once I enabled it, the connection was established.
The server is running in the same machine as the development environment.
One additional thing to note is that you should give <<server or machine name>>\SQLEXPRESS as your data source while trying to add a connection

# June 10, 2006 3:28 AM

Nam said:

Hi Ming,

I get the error:
[sqsrvres] printODBCError: sqlstate = 08S01; native error = 2746; message = [Microsoft][SQL Native Client]TCP Provider: An existing connection was forcibly closed by the remote host.

followed by several these other errors
[sqsrvres] printODBCError: sqlstate = 08S01; native error = 40; message = [Microsoft][SQL Native Client]TCP Provider: The specified network name is no longer available.
[sqsrvres] printODBCError: sqlstate = 08S01; native error = 40; message = [Microsoft][SQL Native Client]Communication link failure
[sqsrvres] OnlineThread: QP is not online.
[sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed

and it looked to me like it is potentially related to : A successful TCP connection from SqlClient to the server was broken due to a TCP keep-alive heartbeat failure (typically indicating problems with the underlying network infrastructure)

that you mentioned from above i was wondering what type of network problems could be affecting my cluster? I have disabled Netbios/wins and only have Named Pipes and TCPip on the server...also when i shut down the passive node on my cluster the errors will still pop up... I assume the issue has something to do with the heartbeat on the active machine but thus far have been unable to determine the cause of the error. Also to note...when i enable netbios/wins the problems happen more frequently from once a day to multiple times an hour. Using server 2003 x64 sp1 and sql 2005 sp1 x64

Thanks

# June 21, 2006 11:25 AM

Sonu said:

I want immediate answer on this. Plz help

# June 22, 2006 5:24 AM

Anita said:

Hi Ming,
Me too trying to connect to SQlServer 2000
But am getting the same error.Could u plz tell how to solve it! I tried checking off "enable shared memory protocol".But it didnt worked out

Thanks in advance

# June 22, 2006 5:33 AM

SQL Protocols said:

Hi,Anita

Did you get error: "TCP provider: An existing connection was forcibly closed by the remote host"? Did you make remote connection or local connection. To solve this: you need check several things:

Server side:

1)Go to services.msc, see whether you sql 2000 was started successfully.
2) Go to server error log, see whether TCP was enabled and server is listening on <ip>,<port>
3) netstat -ano | findstr "<port>" double check server was listening on the exact port
4) If you were making remote connection, double check <port> is in the firewall exception list.

Client Side:
5) Try telnet <serverip> <port> see whether it succeeds.
6) Try change your connection string if you have "tcp:" prefix, remove the prefix,see what happens.
7) Try change your connection string to " Server = <machinename>[\<Instance>],<serverport>/ or Data Source = <machinename>[\<Instance>],<serverport>"

If it is local connection, <machinename> is local hostname, if it is remote connection, <machinename> is remot host name; <port> is the portnumber that server was listening on; <Instance> is the instance name of your sql server, your sql 2000 is default instance or named instance? If default instance, the port should be 1433, if named instance, see the errorlog to check.

If you have further question:
1) Please give me what error you saw?
2) Your connection string?
3) Client/Server OS?
4) server errorlog?
5) local/remote connection?

Good Luck!
Ming.

# June 22, 2006 11:39 AM

SQL Protocols said:

Hi, Sonu

Sorry, I did not see the question you posted? Could you give more specific info?

Thanks!
Ming.

# June 22, 2006 11:43 AM

Thomas said:

Hi All

I have been having the same error I have narrowed it down to being the SQL Server Express 2005 not switching on properly.

These are my issues, everytime i go to the sql server configuration manager and switch on the sql server (sqlexpress) it say

"The request failed or the server did not respond in timely fashion. Consult the error or other application error log"

I get a different error when I try and start the SQL server (sqlexpress) in the Sql server 2005 surface interface configuration tool the error is:

"An error occurred while performing this operation"

I then get this error when I try and start SQL SERVER (SQLEXPRESS) in the services tool

"The SQL SERVER (SQLEXPRESS) on your local computer started and then stopped. Some service stop automaticly if they have no work to do, for example, the performance log."

Now when I try and connected to the sql server Management studio express I get this error

"An error has occured while establishing a connection to the sql server. When connecting to SQL sever 2005, this failure may be caused by the fact that the default setting sql server does not allow remote connections. (provider, name pipes provider, error: 40 - Could not open a connection to sql server) (microsoft sql server Error: 2)

Which is the same error I get when I try an use one of my websites which interacts with an sql server 2005 express database.

So what have I tryed so far to fix this problem, well I have enable TCP/IP and Name pipes in the sql sever configuration manager under SQL server 2005 network configuration / protcols for sql server express

I have also set up the sql server browser to automatic which is working fine.

I have setup sql server (slqexpress) to manual and that still switch off like above. There seems to be lots of people out there with this issue.

WHAT I AM TRYING TO FIND IS A WAY OF STARTING SQL SERVER USING THE COMMAND PROMPT?????

I need to sort this problem out ASAP.

Does anyone know how to do this??

# June 25, 2006 8:37 AM

Nam said:

Hi Ming,

I think Sunu is referring to my post on 6/21/2006 immediately above his. Let me know if you need any more information. I have found several forums posting similar errors however have not found a solution to it yet :( Let me know if you would like any additional information ...also i would like to remove the comment that removing netbios helps...it seems like maybe i just had a lucky streak cause the errors are persisting. It seems so random. It doesn't coincide with backups or dbcc commands that we're running and the machine isn't being stressed at all....

plz help

# June 26, 2006 9:29 AM

Nam said:

In response to thomas' post
NET START MSSQLSERVER
NET START SQLSERVERAGENT
also note that if you start SQLSERVERAGENT that it will automatically attempt to start the dependant service MSSQLSERVER

Also i see the improper switching to be a result of the errors not a cause... I have found that sometimes when i get this error my cluster service will be unable to communicate with SQLServer (even though it's on the same machine) and restart the sql service if it can't connect in a reasonable amount of time.

# June 26, 2006 9:46 AM

SQL Protocols said:

Hi, Thomas

1)
By default SQL Express was installed as a named instance, you can find useful info by following the blog:
http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

2)From command line, you can use

net start MSSQL$SQLEXPRESS, since the instance name is fixed for sql express.

Or you can go to services.msc, find MSSQL$SQLEXPRESS, do start/stop/restart..

Good Luck!
Ming.

# June 26, 2006 12:38 PM

SQL Protocols said:

Hi, Nam

From your description, this looks like not a cluster issue. The error you saw probably is due to server close the connection, could you check server errorlog or system event log see whether there is any useful info to provide clue?

Also, we need more detail info about your scenario to identify the problem, could you help follow the below guidline and post your answer to the question?

http://blogs.msdn.com/sql_protocols/archive/2006/04/21/581035.aspx

Thanks!
Ming.

# June 26, 2006 11:45 PM

Thomas said:

Hi All

I fixed the problem I unistalled sql server 2005 express and reinstalled everything is working fine now. Thanks for the advice. Keep up the good work

regards.

# June 27, 2006 7:17 AM

Nam said:

Thanks ming I checked the system Logs and there are a couple of errors that happen around the same time but they don't always happen...

Application popup: Windows - Virtual Memory Minimum Too Low : Your system is low on virtual memory. Windows is increasing the size of your virtual memory paging file. During this process, memory requests for some applications may be denied. For more information, see Help.

The browser service has failed to retrieve the backup list too many times on transport \Device\NetBT_Tcpip_{2367FB22-08A8-453D-9814-778CBC0FFB4D}. The backup browser is stopping.

In light of the paging file i have expanded it to the recommended size on from the OS which is quite large.. 48898MB....we have 32Gigs of ram on this server. Let me know if that's overkill. I read somewhere that recommended is about equivilant to how much ram you have.

Hope this works...just in case :D

[1] Client side:
1. What is the connection string in you app or DSN?
jdbc:inetpool:inetdae7a://serverName?user=User&password=*******&appname=AppProcess&database=DBName
2. If client fails to connect, what is the client error messages? (please specify)
Typically only fails to connect during the minute or two when the errors are happening.
Also if the errors cause a sql server reboot then it will get an error message stating such.

com.nprise.dao.DaoException: com.inet.tds.s: java.net.ConnectExceptionConnection refused

com.nprise.dao.DaoException: com.inet.tds.s: Msg 6005, Level 14, State 2, Line 1, Sqlstate 01000
[DIDATABASE1]SHUTDOWN is in progress.

3. Is the client remote or local to the SQL server machine? Remote
4. Can you ping your server? YES
5. Can you telnet to your SQL Server? YES
6. What is your client database provider? merlia java database driver Or/And, what is your client application? SQL Management Studio, Query Analyzer
7. Is your client computer in the same domain as the Server computer? Same Domain
8. What protocol the client enabled? TCPIP
9. Do you have aliases configured that match the server name portion of your connection string? YES

[2] Server side:

1. What is the MS SQL version? SQL Server 2005 x64
2. What is the SKU of MS SQL? Standard
3. What is the SQL Server Protocol enabled? TCPIP, NamedPipes
4. Does the server start successfully? YES
5. If SQL Server is a named instance, is the SQL browser enabled? YES
6. What is the account that the SQL Server is running under? Domain Administrator
7. Do you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider? NA
8. Do you make firewall exception for SQL Browser UDP port 1434? NA

[3] Platform:

1. What is the OS version? Windows 2003 x64
2. Do you have third party antivirus, anti-spareware software installed? NO

# June 28, 2006 12:05 PM

Ritu said:

Hi,
I'm getting error msg:(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) while using webparts in my web app.I guess this has something to do with the point that connection string while connecting with aspnetdb in case of web parts uses windows authentication.
I'm also not able to connect to any other db if i use windows authentication in my connection string.
Can u plz suggest me any solution for it?

Thanks
Regards,
Ritu

# June 29, 2006 3:15 AM

SQL Protocols said:

Hi, Ritu

Can you provide the connection string? Please following the below blog and search the error message to see the troubleshoot tips.

http://blogs.msdn.com/sql_protocols/archive/2005/12/22/506607.aspx

Good Luck!
Ming.

# June 29, 2006 1:36 PM

SQL Protocols said:

Hi, Nam

Thanks for your detail info.
So,

First, it is remote connection, how do you telnet? telnet <server> <sqlport>? If your sql instance is a named instance, you should go to server errorlog to check server TCP/IP was enabld and listening on which port; if your sql instance is default instance, you just try port 1433. Did you try to telnet the correct port? Plus, the <server> must be the hostname of your remote server.

Secondly, if your sql instance is named instance, did you specify the instance name in your connection string? Normally, you should specify "<remoteserver>\<instancename>"

Thirdly, you mentioned you specify the alias, how does it looks like? did you make connection by using alias or use "<servername>\<insatncename>"? Please double check whether you spcify the correct sql port number in your alias, that might cause client connect to a tcp port that sql instance was not listening on.

Fourthly, to make remote connection and your remote server has firewall enabled, you need add sql port and browser udp port to the exception list, so, either you add sqlservr.exe and sqlbrowser.exe to exception list or edit the port directly to the exception list. After this done, see whether you can make connection.

Finally, for a quick workaround is by specifying the port number in your connection string, namely, "<servername>\<instancename>,<sqlport>", on your server " netstat -ano |findstr <sqlport> " to see server was listening on the port.

Let me know if you have further question.

Good Luck!!
Ming.

# June 29, 2006 1:55 PM

rape movies said:

Asaspal. Memrano tu es besta. Amigo.

# June 30, 2006 5:40 AM

Nam said:

Hey Ming,

default port and default instance also i can telenet into the machine. Not using an alias just the virtual server name that we set up with the cluster. Also no firewall everything is connecting from in the same domain.

The problem isn't that we can't connect, we can connect 99% of the time. It's just that about once a day we lose connection for about 1 minute and get the errors listed above.

Also since i expanded the page file the problem still persists. :(

# June 30, 2006 9:31 AM

Brian said:

Hi

I fixed my error 40 by turning off user instances and re editing the connection string in my C# project.

Hope this helps.

# June 30, 2006 2:40 PM

SQL Protocols said:

Hi, Nam

One way to identify the 1% connection lose is to open SQL trace file, and see when you got repro, what operation are your client app doing and what was going on on the server...

Thanks!
Ming.

# July 3, 2006 2:22 PM

rape videos said:

Your article is quite right, thanks.

# July 5, 2006 9:10 AM

jmcnamera said:

Changing the OSQL command switch from -S to -s -S fixed this issue for me. Not sure why, but with -S server\instance we got the can't find instance error message but with -s it worked.

Thanks to SqlRick who pointed this out early in the thread.

# August 1, 2006 2:24 PM

Kamate said:

hi ming,
I am getting the error pasted below when I connect to SQL express with SQL server authentication.
Can u plz help?
TITLE: Connect to Server
------------------------------

Cannot connect to PS0654\SQLEXPRESS.

------------------------------
ADDITIONAL INFORMATION:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

# August 10, 2006 10:42 AM

Kamate said:

Hi Ming,
This is further to my previous query,I get the following error when i try to login with sa.

Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

Thanks

# August 10, 2006 10:45 AM

SQL Protocols said:

Kamate,
"Cannot connect to PS0654\SQLEXPRESS. .."
Mostlikely, this is because you are connect to the SQLExpress remotely and the remote protocol is not enable for sqlexpress. Please use SQL Server Surfuce Configuration Tool to enable remote protocol.

"Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)"

What is you connection string, you can either use SQL login with UID and Password or use windows integrated authentication, not both. Make sure that SQLExpress authentication mode is "mix" and you have correct SA password is you decide to use SQL login.

# August 10, 2006 2:25 PM

Ramesh Babu V said:

Strange might it sound that, I have this issue with the SQL server connectivity from the .net application. The application seems to connect fine fromthe staging system. but throws up an exception from the development

Error number : 18452
Error : User not associated with the SQL Server trusted connnection..

Checked for the authentication its configured to mixed as mentioned in the microsoft website. Is there any other option i am missing here for i am getting this error????

# August 11, 2006 7:06 PM

Charlie B said:

"Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)"

I have the same problem -- how does one specify "mix" for SQLExpress authentication mode? I already allow both remote and local connections via the Surface Area Configuration -- and "Using both TCP/IP and Named Pipes" is set.

# August 13, 2006 2:02 PM

masaki said:

In SQL Server Management Studio, when I tried to connect to the Analysis Services database located on a remote server, it gives me the following error:
A connection cannot be made. Endure that the server is running.
(Microsoft.AnalysisServices.AdomdClient)

Additional Information:
-- Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System)
-- An existing connection was forcibly closed by the remote host (System)

What should I do?

Thanks.

# August 16, 2006 12:31 AM

SQL Protocols said:

Hi, masaki

The error indicated that
1) you might have no TCP protocol enabled on remote server, double check SQL configuration manager, see whether remote server was successfully started.

2) See server error log, check which tcp port server is listening on? Try telnet <remoteserver> <port>, and double check whether the port was added to firewall exception list.

3) Try osql /S<remoteservername>,<port> /E, see whether it works? If so, open trace file see what operation cause server reset connection, and see eventlog and server errorlog to get clue.

Good Luck!
Ming.

# August 16, 2006 10:39 PM

SQL Protocols said:

Hi, marsaki

I read some discussion about AS issue, so, here I collect helpful troubleshooting tips from those:

1)Is this AS 2K or AS 2005?
2)Have you enabled AS to accept remote connections (SAC utility - 2005)?
3)How do you make connection? You connected through DNS name?
4)Did you try ping your AS server or ip?
5)Can you double check the following registry key?

A. Locate the following key in the registry:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa

B. Does this exist?

Value name: DisableLoopbackCheck

Data type: REG_DWORD

Radix: Decimal

Value: 1

Good Luck!
Ming.

# August 16, 2006 11:01 PM

Sergey said:

OLE DB Provider returns error “General network error (80004005)” – Communication link failure when Job scheduler runs long taking DTS package via TSQL command. DTS package makes UPDATE against big table. This error can issue any time during long taking UPDATE. We have
Win2k3 sp1 64-bit for IA, SQL 2005 EE 64-bit for IA.

# August 18, 2006 7:26 AM

SQL Protocols said:

Hi, Sergey

Could you try enlarging query timeout?

# August 19, 2006 7:54 PM

Sergey said:

Thank's a lot for you answer!
We increased "general timeout" and "connect timeout. SQL server still drops local connection. We can see in server error log next record:
Date 21.08.2006 12:39:20
Log SQL Server (Current - 21.08.2006 12:51:00)

Source spid60

Message
A fatal error occurred while reading the input stream from the network. The session will be terminated.

# August 21, 2006 5:12 AM

SQL Protocols said:

Hi, Sergey

This is because your client send invalid request and got rejected by server. Could you describe more spcifically what you did?

1) what is your client provider?
2) what is your server, sql 2k5 or 2k?
3) what is your client request?

You can turn on sql trace to see which client operation caused this failure?

Also, from error log, you should be able to see the error state, can you provide it?

Thanks!

# August 21, 2006 11:18 PM

M.Seenuvasan said:

Hi i am new member of this fourm.I am also geting the following problem,Kindly help me to solve the problem

Problem:

I am developing one web application with vb.net, we have uploaded our pages in online with the sqlserver version 7. But some pages only showing the following error while view the page.But the online sql server also showing the same version.That we tested. Kindly help to solve the problem and aslo let me know how to post new topic.

Error:

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)

# September 4, 2006 2:25 AM

SQL Protocols said:

Hi,

Please see the following blog to troubleshoot error 40:

http://blogs.msdn.com/sql_protocols/archive/2005/12/22/506607.aspx

If you still face problem, please go through our questions w/ connectivity issue:

http://blogs.msdn.com/sql_protocols/archive/2006/04/21/581035.aspx

Thanks!
Ming.

# September 6, 2006 12:44 PM

Chandan said:

Hi,

I am trying to establish the Database Mirror with Sql server 2005. I am trying to configuring it with witness. I am getting error while clicking on start mirroring button as follows
"The server network address "%.*ls" can not be reached or does not exist. Check the network address name and reissue the command".
I am trying to use certificate based authentication. Please advice.

Regards,
Chandan

# September 13, 2006 12:04 PM

DOGAD DOGOUI said:

j'install actuellement une application et je dois créer une base de donnée à partir d'un script. mais quelle a été ma suprise quand je me suis rendu compte que cette la connexion a SQL 2005 n'arrivait pas à s'etablir: message d'erreur: POUR VOUS CONNECTER A CE SERVEUR VOUS DEVEZ UTILISER SQL Server Management Studio ou SMO. or tenez vous bien j'arrive a me connecter à SQL 2000 avec les même parametre. bon sang SQL 2005 est très...... HELP ME

# September 13, 2006 5:33 PM

SQL Protocols said:

Pouvez-vous nous donner un exemple de la corde de raccordement que vous employez et le message d'erreur exact avec le nombre exact d'erreur ? Il pourrait être possible que le SQL ne soit pas installé pour écouter sur les raccordements à distance. Si c'est un raccordement à distance vous devrez permettre la connectivité à distance en utilisant le magicien de configuration de superficie.

Si vous pouvez me montrer le code ou le script où vous essayez de vous relier je peux pouvoir aider.

C'est un problème avec le SQL ne permettant pas les raccordements à distance ou un problème avec de la votre corde de raccordement. Ceci travaillé avec SQL 2000 ainsi qu'il devrait fonctionner avec 2005 si des raccordements à distance sont permis.

excuses pour mon Français faible

Brad Sarsfield
Microsoft SQL Server

# September 13, 2006 6:07 PM

Firoz Ahmad Khan said:

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)"}

plz help i think this is very common error when we connect .net(2005) fron 2000 sql server database

# September 18, 2006 7:03 AM

SQL Protocols said:

Hi, Firoz

Please refer following blog to troubleshooting your problem:

http://blogs.msdn.com/sql_protocols/archive/2005/12/22/506607.aspx

Good Luck!
Ming.

# September 18, 2006 1:21 PM

Firoz Ahmad Khan said:

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)

# September 22, 2006 6:53 AM

LocoTx said:

Jinn,

Error code 53 often indicates that the server is not reachable from the client machine. If you run the following two commands on the client machine, what do you get (I assume the server is installed as a default intsnace):

telnet <serverMachineName> 1433

ping <serverMachineName>

THIS WAS A LIFE SAVER !!!

Turns out our DNS was not resolving to the correct IP address. Although my web.config said "Server=Web4"

When you pinged it went to the wrong IP.

DO THIS FIRST !!!

# October 5, 2006 11:38 AM

Jeremy said:

Hi Ming,

I hope that you can help, having no luck with a 2005 connectivity issue.

I've installed SQL2005 on a server already running SQL2K. SQL2K is the default instance, so I'm trying to connect to SQL2005 using MYSERVER\SQL2005 as the instance.

The steps that I've taken are to apply SP1 and enable remote connections (TCP & Named pipes). I've also made sure the SQL Server Browser service is running and enabled.

The error message that I get via SQL2005 client tools 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)"

I can connect to Analysis Services, using SERVERNAME\SQL2005, if that helps.

I dont see anything stange in the error logs, looking under Management->SQL Server Logs.

Do you have any ideas?

Many thanks

Jeremy

# October 16, 2006 8:26 AM

ratheesh said:

How to correct sql server 2005 Error No :40

# October 26, 2006 6:28 AM

Denis said:

Hi,

I am unable to connect to my SQL Server Express engine with the SQL Server Management Studio Express, although it works with ODBC. I am working on the very same server.

When I connect with WinSQL using ODBC, everything is fine. When I try connecting with Mgmt Studio Express, I get the typical Named Pipes Provider, error 40: ... SQL Server, error 53.

This used to work before. I installed a TV tuner and got a MS Video network adapter (whatever it might be). It is disabled now and pinging (it's localhost anyway) works. Telnet shows no reaction.

I have a netstat showing the SQL server listening on 0.0.0.0:1433. The firewall log does not show any connect attempt (it is still localhost).

The only thing I haven't tried yet is connecting from another computer. Since ODBC does work, my guess is that the Mgmt Studio is giving the trouble, since the service is working.

Any hint?

Thanks,

# October 29, 2006 5:48 PM

Linkin said:

Please somebody help me,i install sql server 2005 in local machine but no servers name are avaible to me,and when i try to connect to (local),the error shows to me:

TITLE: Connect to Database Engine

------------------------------

Cannot connect to sqlserver2005.

------------------------------

ADDITIONAL INFORMATION:

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) (Microsoft SQL Server, Error: 53)

Please someone help me,in configuration manager i dont see any instances....whats wrong?!?!

# November 2, 2006 8:58 AM

twentzler said:

I need help with this error.

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

I went to Ming's links and did not find help there so I came back here. I read this error is typically indicating problems with the underlying network infrastructure... OK. How ever this is SQL express standing alone on a laptop. I have one dB one SQL one laptop no network involved. Since it has been working fine for 19 months I am thinking system resources of some kind. Performance, memory, size of dB something like that. I have ompletely re-installed everything from the OS on up. I do not really know where to go from here.

Any help would be appreciated.

Tracy

# December 7, 2006 12:21 PM

rajiv said:

Error: the request failed or the service did not respod in a timely fashion. Consult the event log or other applicable or other applicable error logs for details.

# December 18, 2006 9:40 AM

vin said:

Cannot connect to PNES014\SQLEXPRESS.

------------------------------

ADDITIONAL INFORMATION:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

# December 21, 2006 7:05 AM

SQL Protocols

Recent Posts

Tags

Archives

SQL Server 2005 connectivity error messages

Comments

Joe SQL said:

SQL Protocols said:

Lunchbox027 said:

SQL Protocols said:

Bob Anderson said:

SQL Protocols said:

SqlRick said:

SQL Protocols said:

Junifer said:

SQL Protocols said:

Nitin said:

Vitgar said:

Aji Jose said:

angi said:

Gokul said:

SQL Protocols said:

Shahid said:

SQL Protocols said:

Saeid said:

SQL Protocols said:

SQL Protocols said:

Aaron Gregory said:

SQL Protocols said:

SQL Protocols said:

Christopher Cheng said:

Daryl said:

Matt Neerincx [MSFT] said:

Arthur Berlin said:

Paul Nguyen said:

JInn said:

Srinivas said:

SQL Protocols said:

SQL Protocols said:

Andrew said:

Koterpillar said:

Lil Turtle said:

SQL Protocols said:

Matt Neerincx [MSFT] said:

Harold Bright said:

Varghese Cherian said:

Nam said:

Sonu said:

Anita said:

SQL Protocols said:

SQL Protocols said:

Thomas said:

Nam said:

Nam said:

SQL Protocols said:

SQL Protocols said:

Thomas said:

Nam said:

Ritu said:

SQL Protocols said:

SQL Protocols said:

rape movies said:

Nam said:

Brian said:

SQL Protocols said:

rape videos said:

jmcnamera said:

Kamate said:

Kamate said:

SQL Protocols said:

Ramesh Babu V said:

Charlie B said:

masaki said:

SQL Protocols said:

SQL Protocols said:

Sergey said:

SQL Protocols said:

Sergey said:

SQL Protocols said:

M.Seenuvasan said: