Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Article
12/21/2005

Part III – Connection Fail when SqlClient connects to Sql Server 2005

When you connect to SQL Server 2005 either using "SQL Server Managment Studio" or any application compiled with .NET Framework 2.0, you are using SqlClient provider(Access data from within a CLR database object by using the .NET Framework Data Provider for SQL Server.)

Error Message 1:

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)

Troubleshoot:
1) Make sure your sql service is running, use either "net start" or "sc query <InstanceName>" or run services.msc, check status of the server; If server start fail, go to ERRORLOG to see what happened there, fix the problem and restart server.

2) You might explicitly use "np:"prefix which ask for connect through named pipe. However, client can not connect to server through the pipe name that specified.Double check the server is started and listening on named pipe if you enabled Named Pipe. One way is that see the ERRORLOG of the server, search follow keywords:

Server named pipe provider is ready to accept connection on [ \.pipesqlquery ] or [\.pipemssql$<InstanceName>sqlquery]

Notice that "sqlquery" is the default pipe name, so you need to know server is listening on which pipe name. eg: if you specify server pipe name is "sqlquery1", then you would see in the errorlog that server listening on [ \.pipesqlquery1 ], and go to SQL Server Configuration Manager, click client Named Pipe properties, see whether the pipe name is same with the one server listening on.

3) You might specify named pipe protocol in connection string, but did not enable named pipe on the server, check ERRORLOG.

4) You might use FQDN/IPAddress/LoopbackIP to connect to the server when only shared memory was enabled, you can change to <machinename> to resolve this.

5) You might explictly specify "lpc:" prefix in your connection string, but shared memory was not enabled. To resolve this, either remove the prefix as long as named pipe or tcp was enabled or enable shared memory.

Error Message 2:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. 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: 0 - No process is on the other end of the pipe.)

1) You might explicitly use "np:"prefix which ask for connect through named pipe and specify FQDN/LoopbackIP/IPAddress as server name in the connection string.
2) You might use FQDN/IPAddress/LoopbackIP to connect to the server.

To resolve 1) and 2), you can specify <machinename> instead of FQDN/IPADress/LoopbackIP.

Error Message 3:

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)

Troubleshoot:
1) You might explicitly use "lpc:"prefix which ask for connect through shared memory. Either server instance was not started successfully or shared memory was not enabled on the server.To resolve this, you remove "lpc:"prefix in your connection string as long as Server is listening on other protocols or enable shared memory and restart server.

2) You explicitly use "lpc:"prefix and connect to a local named instance through form [./(local)/localhost/<machinename>]<InstanceName>, but Sqlbrowser service was not started. To resolve this, you need to enable sqlbrowser.

3) You might connect through "lpc:" which not includes any server name, to fix this, add correct server name in your connection string.

Error Message 4:

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.)

Troubleshoot:
1) You might explicitly use "tcp:"prefix which ask for connect through TCP/IP, however either server was not listening on TCP/IP, to resolve this, either remove "tcp:"prefix in your connection string or enable tcp protocol.

2) You might not connect through the exact port that server is listening on, to verify this, go to SQL Configuration Managner
choose "Protocols for <InstanceName>" and click properties for TCP/IP, see which port is configured for server listening and then try connect through the port, like in connection string "tcp:<machinename>,<portnumber>".

3) The instance that you want to connect through TCP was not started, check server ERRORLOG and restart server.

Error Message 5:

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)

1) You might have named pipe or tcp enabled and connect to a named instance, but SQL Browser service was not started or enabled. To enable browser, First, Use net start or go to sql configuration manager(SSCM), check whether sqlbrowser service is running, if not, start it; Secondly,You still need to make sure SqlBrowser is active. Go to SSCM, click properties of sqlbrowser service -> Advanced-> Active “Yes” or “No”, if sqlbrowser is running but is not active, the service would not serve you correct pipe name and Tcp port info on which your connection depends.

Error Message 6:

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: 41 - Cannot open a Shared Memory connection to a remote SQL server)

1) You might explicitly use "lpc:"prefix and connect to a named instance but specify FQDN/LoopbackIP/IP as <servername>, eg, your connection string looks like "lpc:<FQDN><InstnaceName>" or "lpc:127.0.0.1<InstanceName>"..

2) You might explicitly use "lpc:"prefix and give the wrong server name in your connectionstring, eg: "lpc:xx" <xx> is not the hostname of your machine.

Error Message 7:

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)

1) You might explicitly use "np:" prefix in your connection string and try to connec to a named instance, but named pipe was not enabled on the remote server, to resolve this, enable the remote named pipe and restart instance or remove "np:"prefix if remote server is listening on TCP/IP.

2) You might explicitly use "tcp:" prefix in your connection string and try to connec to a named instance, but TCP/IP was not enabled on the remote server, to resolve this, enable the remote TCP/IP and restart instance or remove "tcp:"prefix if remote server is listening on Named Pipe.

Error Message 8:

An error has occurred while establishing a connection to the server. When connectiong 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, errror:0-A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)

1) This is because connection blocked by Windows firewall. To resolve this, take follow steps:

a. Enable SqlBrowser, see the info in Message 4. Plus, add sqlbrowser.exe into Firewall exception list: HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesSharedAccessParametersFirewallPolicyDomainProfileAuthorizedApplicationsList
b. Add Tcp port to Firewall exception list. (eg, Name-1433:TCP, Value-1433:TCP:*:Enabled:Tcp 1433).
HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesSharedAccessParametersFirewallPolicyDomainProfileGloballyOpenPortsList.For more detailed operation, see https://support.microsoft.com/default.aspx?scid=kb;en-us;287932

Summary:

1) In any case, SqlClient should be able to connect to SQL Instance through any of the protocols(Shared Memory/Named Pipe/TCP) as long as SQL Instance was started successfully.

2) And if you speculate any protocol in connection string ("lpc:"/"np:"/"tcp:"), the error message would display "<Protocol> Provider, error <Num1> -....<Num2>." <Protocol> stands for "Shared Memory" or "Named Pipes" or "TCP"; If you do not speculate
any protocol, the error message indicates that connection fails when connecting through specific <Protocol>.

3) In the error message format for SqlClient, please notice two different error number. <Num1> stands for internal error thrown out by SQL Protocols, <Num2> is the OS error(eg: 233 - No process is on the other end of pipe). When you see <Num1>=0, that means the connection fails due to OS error not caused by SQL Protocols, under this situation, you can use "net helpmsg" to check specific OS info.

Finally, if you were developing .NET framework application and came across above issues in your client app, the best way is first try SQL Server Management Studio to connect to SQL Server using the exact same connection string in your app, and watch the error message, normally, there is additional error info at the end of error string, eg ( Microsoft SQL Server, Error:87) which gives you clue(net helpmsg 87) that problem inside your connection string.

MING LU

SQL Server Protocols

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Comments

Anonymous
January 23, 2006
The comment has been removed
Anonymous
January 23, 2006
i need help with this.

I try to connect my project in visual studio with my database sql server express 2005 and...

An error has occurred while established a connection to 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

please any help to:
juliangrijalba@gmail.com
Anonymous
January 24, 2006
Julian: If you’re trying to connect from a remote machine you will have to enable network protocols to enable remote connectivity.

This may help.
http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx
Anonymous
January 25, 2006
The comment has been removed
Anonymous
January 26, 2006
Hi, my SQL Agent 2005 service won't start. I cannot connect to the Profiler (2005) either. But both the SQl Server 2005 and sqlbrowser services are running and active. I have Sql Server 2000 on the same machine as the default instance and the SQL Server 2005 as the named instance wiht name "SQL2005".
The error log from starting sqlagent is the following:
[298] SQLServer Error: 21, Encryption not supported on the client. [SQLSTATE 08001]
[298] SQLServer Error: 21, Client unable to establish connection [SQLSTATE 08001]
[165] ODBC Error: 0, 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. [SQLSTATE 08001]
[000] Unable to connect to server '<servername>SQL2005'; SQLServerAgent cannot start
[298] SQLServer Error: 21, Encryption not supported on the client. [SQLSTATE 08001]
[298] SQLServer Error: 21, Client unable to establish connection [SQLSTATE 08001]
[165] ODBC Error: 0, 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. [SQLSTATE 08001]
[382] Logon to server '<servername>SQL2005' failed (DisableAgentXPs)
[098] SQLServerAgent terminated (normally)

If I run sqlcmd -S instancename, I got the following error:
HResult 0x15, Level 16, State 1
Encryption not supported on the client.
Sqlcmd: Error: Microsoft SQL Native Client : Client unable to establish connecti
on.
Sqlcmd: Error: Microsoft SQL Native Client : 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.

I have both remote connection and tcp/ip enabled.
Any help is greatly appreciated!

Steve
Anonymous
January 27, 2006
The comment has been removed
Anonymous
February 03, 2006
Hi, Ming,
Sorry it took a little while. I wanted to make sure I tried your solutions before responding. Suggestion 1) helped. I did not see those two flags so went right ahead uninstalling and reinstalling the SQL Native Client.
Both the agent and profiler are now working.
Thanks very much for the help.
Steve
Anonymous
February 09, 2006
The comment has been removed
Anonymous
February 09, 2006
Forgot to include my email.
rajesh_vee@donotspam.hotmail.com

- email modified to prevent automated spam engines. Please cut out the donotspam part. Thanks.
Anonymous
February 09, 2006
The comment has been removed
Anonymous
February 19, 2006
I try to connect to SQL Server 2005 using ADO:

(1)connect to a named instance(mynamedinstance),using SQL Native Client as the driver.

CString strConn =_T("Provider=SQLNCLI;Data Source=mypcname\mynamedinstance;Initial Catalog=mydb;User Id=myuser;Password=mypw;");
_ConnectionPtr conn("ADODB.Connection");
_bstr_t bstrConn(strConn);
if( FAILED(conn->Open(bstrConn, _T(""), _T(""), 0)) )
{...}

Connect successfully!

(2)connect to the default instance, using SQL Native Client as the driver, but use DSN in the connection string.

CString strConn =_T("Provider=SQLNCLI;DSN=myDSN;Uid=myuser;Pwd=mypw;");
...
...

Connect successfuly!

(3)Conncet to a named instance(mynamedinstance), using SQL Native Client as the driver, using DSN in the connection string.

CString strConn =_T("Provider=SQLNCLI;DSN=myDSN;Uid=myuser;Pwd=mypw;");
...
...

Connect error!!!

Why (3) failed? Please help me.
Anonymous
February 20, 2006
Provider SQLNCLI does not recognize DSN. So, in the second case, the DSN=myDSN is ignored and, possibly, connection successfully connect to local default instance. In the third case, the DSN=myDSN is ignored as well and connection cannot be established for named instance.

So the short answer is “Do not use DSN when using SQLNCLI provider”.
Anonymous
March 01, 2006
Hi guys, Im getting this connection 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)]

, but only when i move the application from my local machine (which connects fine) to a IIS server. What can the error be?
Anonymous
March 02, 2006
The comment has been removed
Anonymous
March 03, 2006
Sorry, I forgot to mention that my Application is not trying to access the DB remotely. The app and the DB are located in the same computer.

The connection string is the following:

<add key="connStr" value="data source=AGRONSQLEXPRESS;initial catalog=INVENTORY;integrated security=SSPI;persist security info=False;workstation id=AGRON;packet size=4096;" />

Regards
Anonymous
March 03, 2006
The comment has been removed
Anonymous
March 04, 2006
Thank you Ming,

I just uninstalled and reinstalled SQL Server Express, and now everything seems to be working fine.

Thank you for your help,

Agron
Anonymous
March 13, 2006
A connection I used for SQL 2000 doesn't work for 2005. App servers are in DOMAIN1 and SQL servers are in DOMAIN2. There is no trust between the domains.

Create DOMAIN1User1 and DOMAIN2User1 with identical passwords.

From App servers (DOMAIN1) I can create a DSN that uses Windows NT Authentication to SQL 2000 (DOMAIN2). Connection works fine.

From App servers (DOMAIN1) if I create the same DSN that uses Windows NT Authentication to SQL 2005 (DOMAIN2) - it fails.

[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ''. The user is not associated with a trusted SQL Server connection.

The domains aren't trusted because a firewall separates them. The app I am using requires NT Authentication -- I can't do SQL Server Authentication to get around this.

Any suggestions?
Anonymous
March 14, 2006
The comment has been removed
Anonymous
March 14, 2006
The comment has been removed
Anonymous
March 15, 2006
Joe,

The solution to hop over untrusted domain are. Basically the authentication uses NTLM.

1) create local account with identical password on both machine. Say, create "testuser" on both machineA and machineB with password "testuserpass".

2) grant testuser permission to access SS2k5 on machine A.

3) run your client app as testuser on machine B.

In your case, eventhough the username are same, but the difference between domains make the access token SID actually different. By using machine local account, NTLM do not check domain credentials.

Hope this helps.
Anonymous
March 15, 2006
Joe,
In both cases, ss2k and ss2k5, you can check the login use name using "select suser_name()"
Anonymous
March 15, 2006
The comment has been removed
Anonymous
March 15, 2006
Hi Jeremy,

Sounds like someone perhaps tweaked the settings on Windows Firewall on your SQL Server machine (perhaps in response to someone trying to bust in as sa? Not sure).

Check Windows Firewall on the machine and make sure that there is a way for external applications to use tcp port 1433 to your SQL Server.

Take a look at this article for details on setting this up properly:

841249 How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;841249

Note that this article applies equally well to Windows 2003 server as far as the Firewall settings go.
Anonymous
March 15, 2006
The comment has been removed
Anonymous
March 16, 2006
Thanks for the help. I broke down and setup the domain trust. It is working. I just didn't want to poke those holes through the firewall, but I guess I have to.
Anonymous
March 16, 2006
Hi,

I am having a problem in regards to "Cannot generate SSPI context". Initially if the firewall was disabled (using windows2k3 and sql server 2005).. I am able to connect to SQL Server without having problem at all. Setting the suggested parameters in the firewall and enabling it, I am now stuck with this problem. I have tried following the troubleshooting procedures set to diagnose this but to no avail. Before trying to do anything else is there any other way to tweak the firewall settings? In addition I noticed if I initially disabled the firewall and connected to SQL server and disconnected the firewall again. Connecting to SQL server will not result with the same error? why is that?
Anonymous
March 28, 2006
Julius,

"Cannot generate SSPI context" in your case indicates that your client machine can't access domain controller. Might be blocked by enabled firewall. Your second scenario is because Windows caches server credential when connect succussfully with domain controller and use that credential in subsequent connections.

To further understand your problem,
(1) What OS is on your client/server machine?
(2) Does your client/server machine part of a domain?
(3) Is your client login a domain account?
(4) Where the firewall seat at, network topologically?

Thanks,
Anonymous
April 03, 2006
The comment has been removed
Anonymous
April 03, 2006
Hi Matt,

The certificates dialog in SQL Server 2005 is actually trying to help you. It will only display certificates that are valid for usage with SQL 2005 encryption. So it verifies the following things:

1. Enhanced Key Usage section of cert contains:

Server Authentication (1.3.6.1.5.5.7.3.1)

2. Certificate has private key.

3. Certificate is in correct store (should be in Local Computer store under Personal Certificates for SQL running under localsystem or network service). If your SQL is running under domain account, then it will look in the store for the domain account.

So take a look at your cert and verify the above 3 items and it should show up in the dialog.
Anonymous
April 03, 2006
Matt,

Thank you for your response. I know we have #3 set up as you mention, and we're looking into #1 and #2. Unfortunately my knowledge of certificates is limited so I don't know how to set or even check on these properties without doing some research first.

We were able to open mmc and add the certificates snap-in, but we couldn't find any EKU or private key properties on the cert itself.

Thanks again.

-Matt F.
Anonymous
April 03, 2006
The comment has been removed
Anonymous
April 03, 2006
Also pay attention to
(1) whether the issue to: xyz match the FQDN of your machine name,

(2) the expriation date of your certificate.

These two checks are added for sql servr 2005 among others.
Anonymous
April 04, 2006
Speaking with my sys admins, they do see "You have a private key that corresponds to this certificate" as you mentioned and the cert does have server authentication checked in the EKU section.

Interestingly, when the cert is imported onto my machine I do not see "You have a private key that corresponds to this certificate" listed on the same cert. Also, I'm importing a cert with a p7b file extension and a FQDN, but it installs not with the FQDN. I know I'm missing s step here.

They are creating the cert using CA. We are currently trying different combinations of where the cert lives to try to resolve.

-Matt
Anonymous
April 04, 2006
One other note.... Just tried to install a .cer with the correct FQDN and it tells me it installed correctly, but it does not appear in the cert list on ie.
Anonymous
April 04, 2006
This sounds like when your admins are generating the certs, they are not enabling the following options when generating the cert. These options are under the "Key Generation Options" section ->

#1. You must check "Use local machine store".
#2. You must check "Mark keys as exportable".

Sorry this is so confusing, the cert folks seem to never make life easy for us mere mortals. (G)
Anonymous
April 04, 2006
Matt,
You can try to post more info on
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1
the SQL Server Data Access Forum.
Anonymous
April 04, 2006
Hi,Matt

Beside the suggestions from Matt Neerincx, I would add following:

1) Your sql service should run under admin account, otherwise it has no permissing to the private key and thus the cert would not show up in the drop down list of SSCM, visit KB article for more detail:
http://support.microsoft.com/?kbid=900495.
2) SQL Server 2005 has a new kind cert called self-signed certificate, which means when server can not find a good cert to load in the cert store, it will load such a self-signed cert. In another word, you always can make encryption connection if you force server encryption, but, if you force client encryption, you will get error"certificate was not trusted" unless you specify trusting server cert through configuration,search more information in Books online.

3)Before upgrad to 2005, if you set any cert in the registry key, you should be careful whether it already expired or match condition of valid cert for 2005, otherwise, after installing 2005, you might not be able to see them in the cert list and server would load a self-signed cert.

Hope this helps!
Ming.
Anonymous
April 07, 2006
The comment has been removed
Anonymous
April 07, 2006
Hi, Matt

First, Sorry about the wrong link, I meant to point to our KB article, it is http://support.microsoft.com/?kbid=900495.

Secondly, the encryption failure you saw is due to the reason that I desicribed in #2 of my last answer. Your server might load a self-signed cert(To verify this, you can go to server errorlog and see there is keyword "self-signed certificate"), and you forced client encryption through setting "Encrypt=True". If you already forced server encryption, the connection should be encrypted. And if you require forcing client encryption, you need to have server loading a certificate issued by a trusted CA.

Thirdly, please run "certutil.exe -v -store my", the tool would verify whether the certs you installed are valid, and send us info, that would help us to identify why cert not appear in the list; and try change sql service running under LocalSystem account, see whether any cert appear in the list.

Finally, we have questions about your scenario, whether you installed a cert in SQL 2000 before you upgrading and specifying server using such a cert? what is the reason you trying to install a new cert?

Let us know how things going.
Anonymous
April 07, 2006
Thank you for your quick responses Ming, I really appreciate it!

Looking at the errorlog, I see the following:
A self-generated certificate was successfully loaded for encryption.

We need to force client encryption. How do we force the server to load a specific certificate for sql 2005? We have valid certificate installed.

We ran certutil.exe -v -store my and got the output. Do you want that displayed as well? I'm concerned that might pose a security risk, only because I don't know the details.

In terms of our scenario, we had a certificate installed for SQL 2K but as soon as we upgraded and the encrypted connections didn't work, we thought it was a certificate issue so we recreated hoping that would solve the problem.
Anonymous
April 18, 2006
The comment has been removed
Anonymous
April 18, 2006
Check Server ERRORLOG and see if server load a self-signed cert? If you need force client encryption, you really need install a good certificate that valid for SQL Server 2005, otherwise, you can force server encryption to make encrypt connection if you are fine with the self-signed cert.

Thanks!
Ming.
Anonymous
April 18, 2006
Since you are using ASP.NET 2.0, if you force client encryption and server load a self-signed cert, you can add one more connection string property "TrustServerCertificate=True" to make connection through. See detail in http://msdn2.microsoft.com/en-US/library/system.data.sqlclient.sqlconnection.connectionstring(VS.80).aspx

Thanks!
Ming.
Anonymous
April 18, 2006
One more thing might be related to the error is that your server is not running under an admin account if you think you installed a valid certificate but server still load a self-signed cert. To fix that,change the service account to localsystem or an admin account, but this might not you want, recommend keep sql service running under low priviliage accoun and use the suggestion that I posted in last comments.

Thanks!
Ming.
Anonymous
April 24, 2006
The comment has been removed
Anonymous
April 25, 2006
Hi,

The error you saw indicates problem inside your client app,since you can connect through Whidbey and SNAC.
If possible, could you provide your application? Or could you try follow script and see what happens?
***********************************
using System;
using System.Net;
using System.Data.SqlClient;

namespace Sqlclient
{
/// <summary>
/// Summary description for Class1.
/// </summary>
class LPCPrefixFallback
{

/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main(string[] args)
{
string strConn = @"Server=.SQLEXPRESS;Trusted_Connection=TRUE;";

SqlConnection myConn = null;
//
// TODO: Add code to start application here
//

Console.WriteLine("Conn WIHOUT PRIFIX...");

try
{
Console.WriteLine("connection {0}",strConn);
myConn = new SqlConnection(strConn);
myConn.Open();
SqlCommand curComm = new SqlCommand("select net_library from sysprocesses where spid=@@spid", myConn);
string netLib = (string) curComm.ExecuteScalar();
Console.WriteLine("Net library: " + netLib);
}
catch (Exception ex)
{
myConn.Close();
Console.WriteLine("ex->" + ex.Message );
return;
}
}
}
}
*************************************

Thanks!
Ming.
Anonymous
April 25, 2006
The comment has been removed
Anonymous
April 27, 2006
Hi,
I've got a connectivity issue, on a server that is running both SS2000 and SS2005.

Each has a named instance.

* The client application can not connect to the SS2000 named instance. This is the problem.
* I can access the instance remotely, but only if I create an alias on the client (in IP, Ispecifying the port of the instance)
* The client application can not connect reliably using an alias. (sometimes it works...)

The problem seems to be SS2000's SQL Server Browser service, which keeps "terminating unexpectedly" (messages in the System Log) and gets re-started every 60 seconds.

I have no idea why this service is crashing. I guess it is needed to ensure visibility of the named instances, in this co-existence scenario.

So, I guess I need some clues on how to troubleshoot the browser service, or else a workaround for the instance visibility. ...???
Anonymous
May 12, 2006
wewe
Anonymous
May 12, 2006
Hi,

I've installed SQL Server 2005 Exprees on my PC and I keep getting this error when trying to connect to a database:

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 went through the steps to try and correct it, including others, and I still am unable to connect
.
I am able to connect to the sever using the Management studio express

Any help would be greatly appreciated

Here's the error log:

2006-05-13 02:22:40.69 Server Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
Apr 14 2006 01:12:25
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition with Advanced Services on Windows NT 5.1 (Build 2600: Service Pack 2)

2006-05-13 02:22:40.69 Server Error: 17054, Severity: 16, State: 1.
2006-05-13 02:22:40.69 Server The current event was not reported to the Windows Events log. Operating system error = 1502(The event log file is full.). You may need to clear the Windows Events log if it is full.
2006-05-13 02:22:40.69 Server (c) 2005 Microsoft Corporation.
2006-05-13 02:22:40.69 Server All rights reserved.
2006-05-13 02:22:40.69 Server Server process ID is 3780.
2006-05-13 02:22:40.69 Server Logging SQL Server messages in file 'c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG'.
2006-05-13 02:22:40.69 Server This instance of SQL Server last reported using a process ID of 2928 at 5/13/2006 2:22:33 AM (local) 5/13/2006 6:22:33 AM (UTC). This is an informational message only; no user action is required.
2006-05-13 02:22:40.69 Server Registry startup parameters:
2006-05-13 02:22:40.69 Server -d c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmaster.mdf
2006-05-13 02:22:40.69 Server -e c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG
2006-05-13 02:22:40.69 Server -l c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmastlog.ldf
2006-05-13 02:22:40.69 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2006-05-13 02:22:40.69 Server Detected 2 CPUs. This is an informational message; no user action is required.
2006-05-13 02:22:40.88 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2006-05-13 02:22:40.90 Server Database mirroring has been enabled on this instance of SQL Server.
2006-05-13 02:22:40.90 spid5s Starting up database 'master'.
2006-05-13 02:22:41.02 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2006-05-13 02:22:41.15 spid5s SQL Trace ID 1 was started by login "sa".
2006-05-13 02:22:41.16 spid5s Starting up database 'mssqlsystemresource'.
2006-05-13 02:22:41.19 spid5s The resource database build version is 9.00.2047. This is an informational message only. No user action is required.
2006-05-13 02:22:41.38 spid5s Server name is 'BLEU-13SQLEXPRESS'. This is an informational message only. No user action is required.
2006-05-13 02:22:41.38 Server Virtual Interface Architecture protocol is not supported for this particular edition of SQL Server.
2006-05-13 02:22:41.38 spid8s Starting up database 'model'.
2006-05-13 02:22:41.52 Server A self-generated certificate was successfully loaded for encryption.
2006-05-13 02:22:41.54 Server Server is listening on [ 'any' <ipv6> 1027].
2006-05-13 02:22:41.60 spid8s Clearing tempdb database.
2006-05-13 02:22:41.82 spid8s Starting up database 'tempdb'.
2006-05-13 02:22:41.88 spid11s The Service Broker protocol transport is disabled or not configured.
2006-05-13 02:22:41.88 spid11s The Database Mirroring protocol transport is disabled or not configured.
2006-05-13 02:22:41.88 spid11s Service Broker manager has started.
2006-05-13 02:22:42.32 Server Server is listening on [ 'any' <ipv4> 1027].
2006-05-13 02:22:42.32 Server Server local connection provider is ready to accept connection on [ .pipeSQLLocalSQLEXPRESS ].
2006-05-13 02:22:42.32 Server Server local connection provider is ready to accept connection on [ .pipeMSSQL$SQLEXPRESSsqlquery ].
2006-05-13 02:22:42.32 Server Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.
2006-05-13 02:22:42.32 Server The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
2006-05-13 02:22:42.32 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2006-05-13 02:22:42.33 spid5s Starting up database 'msdb'.
2006-05-13 02:22:42.57 spid5s Recovery is complete. This is an informational message only. No user action is required.
2006-05-13 02:24:02.45 spid51 Starting up database 'ReportServer$SQLExpress'.
2006-05-13 02:25:02.40 spid51 Starting up database 'ReportServer$SQLExpress'.
2006-05-13 02:26:02.40 spid51 Starting up database 'ReportServer$SQLExpress'.
2006-05-13 02:26:02.72 spid52 Starting up database 'ReportServer$SQLExpressTempDB'.
2006-05-13 02:30:22.82 spid11s Service Broker manager has shut down.
2006-05-13 02:30:22.83 spid5s SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required.
2006-05-13 02:30:22.83 spid5s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
Anonymous
May 15, 2006
Hi,

1) You were making local connection to SQLExpress, noticed it is a named instance, hence you should specify instance name in your connection string, eg:
"Provider=SQLNCLI;Server=<MachineName>SQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI "; What does your connection string look like?

2) You were connecting through SqlClient provider,please do:
"sc query sqlbrowser", make sure sqlbrowser is running.

3) go to "..programe filesmicrosoft sql server90toolsbinn", run "osql /S .pipeSQLLocalSQLEXPRESS /E", can you connect?

If you still face problem, please follow the guidline and provide more detail info, especially how you make connection in your case:

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

Thanks!
Anonymous
May 24, 2006
The comment has been removed
Anonymous
May 25, 2006
The comment has been removed
Anonymous
June 12, 2006
The comment has been removed
Anonymous
June 12, 2006
Hi, Ranga

Please check following blog

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

There is a section called "Configure Express if you want to mak remote connection."

Good Luck!
Ming.
Anonymous
June 16, 2006
Okay, we have a similar problem that I haven't seen specifically mentioned here, so if there's another blog or resource I should be using, please let me know. Our problem:

Customers and DB Admin staff can connect to SOME 2005 instances, but not others. The problem instance will be different for each person having a problem, but others can connect fine to that same instance. In other words, it appears to be a random thing. Everybody but X can connect to Server1, everybody but person Y can connect to Server2. X can connect to all other servers but 1, and Y can connect to all others but 2. All our SQL instances are set for TCP/IP over a specified port, not the default. Named Pipes is disabled.

Any help would be appreciated!

Jack Burgess
State of Ohio
jack.burgess@ohio.gov
Anonymous
June 24, 2006
The comment has been removed
Anonymous
June 24, 2006
Got the RPC thing working. It also looks like sp_executesql will solve this issue.

But one question remains, is this the best way?
Anonymous
July 02, 2006
The comment has been removed
Anonymous
July 03, 2006
The comment has been removed
Anonymous
July 03, 2006
The comment has been removed
Anonymous
July 03, 2006
Hi, Migue

1)Thanks a lot for your response. Your scenario seems very interesting, it sounds like you try "tcp:127.0.0.1,1433" works, only "tcp:<FQDN>,1433" and "tcp:<IP>,1433" not work, right? We saw such issue on WINXP and WIN2K due to OS design, but not on WIN2K3.
So, sounds for you the current workaround is either use "tcp:127.0.0.1,1433" or SQL Authentication.

2)Yes. In DB-Mirroring scenario, you are required using TCP.

Basically, I will do some investigation and send you reply about the possible cause.

Thanks!
Ming.
Anonymous
July 10, 2006
The comment has been removed
Anonymous
July 13, 2006
The comment has been removed
Anonymous
July 24, 2006
Hi,

I get following the errors, these are rare and random. The application is written C++ uses MS ODBC on SQL 7 with Windows NT4:

SQLError Info
SqlState 01000fNativeError 233
Error Msg [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionTransact (TransactNamedPipe()).
SqlState 08S01f NativeError 0
Error Msg [Microsoft][ODBC SQL Server Driver]Communication link failure

Thanks.
Anonymous
July 25, 2006
Hi,Faisal

We need more info to identify your problem, could you provide:

1) What is your connection string?, namely how do you make connection? Connect through server name or ipaddress?
2) Were you making local or remote connection? If remote, do you have firewall on your server? Did you have "file and printer sharing" added in the exception list? Or can you try whether you can access a share folder of your remote server?
3) Can you check your server errorlog to see whether there is any error info, the error indicates that server might close connection or you can open sql trace file to see which client operation cause server closing the connection.

Good Luck!
Ming.
Anonymous
August 04, 2006
Hello Ming,
I'm unable to run my webapp (written in NET 2.0 and C#) from home. Could you please help?
Thank you.

The error is:
[SqlException (0x80131904): 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)]

Here is my connection setup:
<appSettings>
<add key="ConnectionStringXML" value="Server=sof2;Provider=SQLOLEDB;Database=myDB;UID=myUID;PWD=myPWD" />
<add key="ConnectionString" value="Server=sof2; Database=myDB;UID=myUID;PWD=myPWD" " />
</appSettings>
Anonymous
August 05, 2006
Hi, Van
Can you answer following question:

1) Were you making local or remote connection?
2) Is your sql server a default or named instance?
3) Do you have sqlbrowser on?

Here is checklist of troubleshooting tips:

1) If remote connection, double sheck whether you have firewall on your server, if so, please add "File and Printer Sharing" to exception list; plus add sqlservr.exe to exception list; and add sqlbrowser.exe to exception list.

2) If you tried to connect to a named instance, change your connection string to set "<servername><instancename>" as the value of Server field.

3) If you were connecting to a named instance and it is remote connection, on your remote server, please do "net start sqlbrowser".

Also, see the section "Error Message 1:" in this blog, there are some other potential cause and resolution described.

Good Luck!
Ming.
Anonymous
August 09, 2006
The comment has been removed
Anonymous
August 09, 2006
DJ,
I believe your case is single hop in that the client is collocated with the middle server.
Are you be able to login as the windows login on the local server and make successful direct SQL connection to the target linked server?
If you can, could you describe more about your setting?
(1) The version of Windows OS.
(2) The version of SQL.
(3) The user account is local account or domain account?
(4) The SQL service running account is local account or a domain account?
Anonymous
August 26, 2006
Very many thanks for a good work. Nice and useful. Like it!
Anonymous
September 08, 2006
The comment has been removed
Anonymous
September 09, 2006
The comment has been removed
Anonymous
September 15, 2006
Hi,

Currently this is local.

Most advice I've read deals with point 5b. However, I can't figure out to sort them--I'm only using SQL Express if that has an effect--it seems like they just sort by name or status. Right clicking should have a move up or down option, but I can't find it.

It appears to be caused whenever more than one connection exists, though Max connections is set to 0. I could be wrong though. However, now, after a lot of trouble shooting, it seems to have subsided a bit. Sorry for the vague answer, but I don't know.

Is this something that will go away when I move to a host and SQL Server Standard?
Anonymous
September 19, 2006
Hi, kihh

Could you describe more specific about your problem? What if you set max connections to non 0? Are you able to connect to Express successfully? Or you faced some data operation issue? You can try to use same client app against SQL Server Standard or Enterprise or Dev sku, if the problem disappear, that may be caused by Express.

Following Forum can help you w/ that:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=385&SiteID=1

Good Luck!
Ming
Anonymous
September 20, 2006
Hi,
I get the above mentioned: "Login failed for user ''. The user is not associated with a trusted SQL Server connection." error.

But I get this error when connecting through a website on an IIS webserver. When connecting through a normal app the connection works fine.
So the problem seems to be with IIS.

I have a local account on both machines with the same password. The SQL server is set to mixed mode authentication. I am sure that the connection string is correct.

The SQL server 2000 is running on a windows 2003 server. I am running win xp and IIS 5.1.

Any help would be greatly appreciated.

thanx
riaan.
Anonymous
September 21, 2006
We testig SQL server 2005 with 20 GB database for check limit of users who can login to SQL server. But we recive an error:
"A connection was successfully established with the server, but then an error occurred during the pre-login handshake. 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 - An existing connection was forcibly closed by the remote host.)
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)"

We change SQL settings (max worker threads from 0 to 2048 (default was 0 - for 64 bit platform and 8 processors is value=576)

Is this action ok (we use long query)
Anonymous
September 25, 2006
Hi,

1) Is the error you saw consistently or intermittently? If not consistently, could you reduce the CPU assumption when the problem occured? and see whether that helps?

2) Can you open SQL Server Profile and trace which client operation trigger the connection closed, did you see any error info in the server error log or system eventlog when problem occured?

3) Did the problem occure after your adjusting the "max worker thread"? or not? From books online, your configuration of that seems OK based on your system configuration, the problem probably during data operation, client or server close the connection. To open sql trace file and monitor SQL Server error log will help you get clue.

Good Luck!
Ming.
Anonymous
September 30, 2006
With shipping SQL Server 2005, we heard from customer feedback about suffering make successful remote...
Anonymous
October 03, 2006
With shipping SQL Server 2005, we heard from customer feedback about suffering make successful remote
Anonymous
October 04, 2006
The comment has been removed
Anonymous
October 04, 2006
The comment has been removed
Anonymous
October 18, 2006
Hi, on a web application that I develop, I just upgraded to a new database server running SQL Server 2005 and Windows Server 2003 Enterprise Edition. The previous database server ran SQL Server 2000 and Windows Server 2003 Standard Edition. Since this upgrade, the application has been causing "timeout" errors all over the application. I've been stumped. Here's the error mssage: Microsoft OLE DB Provider for ODBC Drivers error '80040e31' [Microsoft][ODBC SQL Server Driver]Timeout expired I'm hoping there is some sort of a setting that can be modified to increase the maximum number of connections between the web server and database server. This is a fresh version of Windows Server 2003 and SQL Server 2005. Any support that can be provided will be greatly appreciated.
Anonymous
October 20, 2006
I have the same problem that many of the others where i'm getting the error about connecting remotely not allow. I tried all the solutions, but still have the problem. My issue is just a little different than the others. The program runs fine if I were calling it directly, however, i added a login page and change authentication to "forms". That's when i get the error. Any ideas? Thanks
Anonymous
October 27, 2006
App: Classic ASP Migrated from SQL2K -> SQL2K5 Legacy ConnString conn.open "Provider=sqloledb;Data Source=CLUSTERSQL,1433;Network Library=DBMSSOCN;Initial Catalog=AjithsDB", "uid", "pswd" Still works for SQL2K5. DataShape gives me the following error MSDataShape error '80040e14' Provider command for child rowset does not produce a rowset. So I tried ConnString as follows but it fails conn.open "Provider=SQLNCLI;Server=CLUSTERSQL;Database=AjithsDB;", "uid", "pswd" Also tried conn.open "Provider=SQLNCLI;Server=CLUSTERSQL,1433;Database=AjithsDB;", "uid", "pswd" I am not finding any examples of how to specify a port for SQLNCLI provider. Please let me know if there is a workaround. Thanks Ajith
Anonymous
November 08, 2006
The comment has been removed
Anonymous
November 09, 2006
Hi, Mani Does this happen consistently or intermittently? The problem looks like either you lost network access to the server temporarily or sql server reject new connection. You need first identify whether it is SQL Server issue or pure network issue.

You can check event log SQL Server to see whether transaction log full or server ran out of disk place.
Open SQL Server Profile, start a new trace, redo your client operation, watch what were server doing? Or if you think this problem can only occured when you access data size larger than 30MB, please post your question to this forum, provide your detail data operation info. http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1
If the error just happen intermittently, it might be due to bad connection, and you can see following KB article to troubleshoot: http://support.microsoft.com/kb/325487 Good Luck! Ming.

Anonymous
November 10, 2006
Thanks Ming for your suggestion. Do you think the firewall plays a part in this? Is it invalidating the connection? Thanks Mani
Anonymous
November 12, 2006
Hi, Mani Did you enable your firewall during running your client application? What if you turn off firewall, see whether the problem repro? BTW, for remote connection troubleshooting, you can see the following blog: http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx Good Luck! Ming.
Anonymous
November 16, 2006
The comment has been removed
Anonymous
November 18, 2006
Hi, Mark Could you give more specific error when you connect from 64bit client? It should not make difference compared to 32bit if you were making remote connection. Good Luck! Ming.
Anonymous
November 22, 2006
The comment has been removed
Anonymous
November 23, 2006
Hi, Sundar 1) Can you check any info in server errorlog when the problem repros? 2) Can you open sql profile to see whether large or incorrect data operation caused the problem? As far as I know, the error does not necessarily mean run out of memeory. 3) Can you collect ETW trace and send to us to further investigate? Here is a guide to collect ETW trace. http://blogs.msdn.com/sql_protocols/archive/2006/08/04/688396.aspx Thanks! Ming.
Anonymous
November 29, 2006
Hello, I am having a somewhat odd problem. I was unable to create a connection to a SQL 2005 instance on a win2K3 server using an ASP.Net Framework 1.1 app running on IIS/Visual Studio 2003 on XP machine. There are both a SQL 2005 and 2000 instance on the server. The SQL 2005 instance is a named instance. I decided to try configuring the two instances to listen on different ip addresses and ports as follows. SQL 2005 Server is listening on [ 'any' <ipv4> 2602]. Server is listening on [ 192.168.2.99 <ipv4> 1312]. SQL 2000 SQL server listening on 192.168.2.9: 1433. SQL server listening on 192.168.2.99: 1433. SQL server listening on 127.0.0.1: 1433. By specifying the ip address and port for the server instance in my connection string I can connect to the 2005 instance but not the 2000 instance. Connection String: Data Source=192.168.2.9,1433;Initial Catalog=DB;Persist Security Info=True;User ID=USER;Password=PASSWORD;Network Library=dbmssocn However, I can connect (same username and password) to either instance without error using the Management Studio or the Visual Studio 2005 data connection browser but only if I specify the ip address and port of the desired instance. If I use the instance enumerated in the drop down, the connection is active refused. I am imagining that it must be a kind of tcp/ip conflict. But I do not know how to resolve this. Please advise.
Anonymous
November 29, 2006
One more thing: I cannot connect to the 2000 instance using Enterprise Manager.
Anonymous
November 29, 2006
No, actually I can register the 2000 instance in Enterprise Manager if I specify the ip address, not the name.
Anonymous
December 14, 2006
The comment has been removed
Anonymous
December 15, 2006
I am having the same problem as Mani above. I can connect to my remote SQL Server 2005 just fine, but when I try to add a new table in Management Studio I get a (TCP Provider, error: 0 - The specified network name is no longer available.) (Microsoft SQL Server, Error: 64). It doesn't seem like it could really be a connectivity issue as I can access all other parts of the DB both before and after attempting this. (It does it constantly, not intermittently)
Anonymous
December 21, 2006
I recently upgraded sql express 05 to developer edition (win xp pro laptop). After struggling to get the upgrade to work, i finally unistalled all 2005 stuff and reinstalled developer edition from scratch using mixed authentication. Everything was great and i could connect to my databases no problem. When i started up my pc this morning I cannt start any of the services in the surface area configuration manager (MSSQLServer, SQL Agent, SQL Browser etc). I have been stuck on this for 4 hours, do you have any advice? Thanks! Do I need to change the login info for those services? How do I do that?
Anonymous
December 21, 2006
The comment has been removed
Anonymous
December 21, 2006
Hi, Phil To resolve your problem, please take a look at following forum post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=984492&SiteID=1 Good Luck! Ming.
Anonymous
December 27, 2006
Hi all, I've got a little problem. I'm working on a ASP.NET project. The applications has 4-layers. When I look in the Data Access Layer and test the typed dataset, then the "Preview Data" works fine. I see the right records from SQL Server 2005. But when I run the app (default.aspx) in debug-mode, then the system gives the following 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)" Please help.....
Anonymous
December 27, 2006
Hi, Johan Did this happen intermittently or consistently? 1) If consistently, have you tried the tips in "Error Message 1" section of this blog? Plus, did you enable sql port in firewall if you were making remote connection? 2) If intermittently, can you open sql profile and see when the connection failed, is it during data operation? then you need check server errorlog to trace more detail. Good Luck! Ming.
Anonymous
December 31, 2006
ASP.NET application, Visual Studio 2005 and SQL Server 2005 are on the same development machine (WinXP Pro SP2). Does the application use "remote connections" in this configuration?
Anonymous
January 02, 2007
Hi, Johan I am not sure about your question. You can check following blog to know about configuration in remote connections to SQL 2k5. http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx Good Luck! Ming.
Anonymous
January 11, 2007
The comment has been removed
Anonymous
January 13, 2007
Hi, Regan This is OS Error, which indicates that unreachable Domain Controller. It might due to the DC temparily shutdown. Try "nslookup" to find the DC, then troubleshoot the network between your computer and DC, such as a. ping <DCname> b. telnet <ipofDC> 445 c. telnet <ipofDC> 135 If you fail to do above steps, try to disjoin your computer and rejoin your computer to the domain. Also, there are several articles talking about this problem and potential cuase, hope them helps. http://support.microsoft.com/kb/813550 http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=129237&SiteID=1 http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=578673&SiteID=1 Good Luck! Ming.
Anonymous
January 14, 2007
Ming, thanks for the repsonse - I'll have a look! If it is a DC issue, it's a bit surprising that other users can use the DialBox, and connect successfully. Might this be related to a specific profile (i.e. mine) and an expired kerebos ticket, or some such?
Anonymous
January 15, 2007
... and, given a weekend, the problem went away, so I may never know what the issue was. At least I can work on the DialBox again ...
Anonymous
January 18, 2007
Ming, thank you for your helpful instruction. I followed everything you mentioned but, for some reason, I still can't connect to SQL Server Express. I have two computers on the office network, the Host with SQL Express (DELLDESKTOP1) and the Remote computer I'm trying to connect from. On the Remote, if I go to Windows Explorer and enter "\DELLDESKTOP1" in the Address, I'm able to 'see' the Host computer. Just the printers and shared docs. On the Host, I've made sure that Shared Memory, Named Pipes and TCP/IP are all enabled. I've Enabled IP1/IP2 and added TCP Port 1434 for IP1/IP2/IPALL. the TCP Dynamic Ports under IPALL is blank. The error I'm getting is the server timed out when I enter, "SQLCMD -E -S DELLDESKTOP1SQLEXPRESS, 1434" The error message I get is "Login timeout expired." I have disabled the Windows Firewall on the Host just to take that out of the equation. Again, I have followed all of you suggestions above and have run out of options. Thank you, thank you for any help you can provide. (if you can't tell already, I'm not a dba or a developer for that matter, just a newbie) Thanks, Shane shane.eckel@seattlesoftware.com
Anonymous
January 21, 2007
Hi, shane When you were using "sqlcmd -E -S DELLDESKTOP1SQLEXPRESS, 1434", are you sure sqlexpress was listening on this tcp port? Please check: 1) telnet DELLDESTOP1 1434, see whether it succeeds? 2) go to SQL express ERRORLOG, check server has TCP enabled and listening on port 1434? 3) If 1) & 2) has not any problem, try enlarge the connection timeout, such as "SQLCMD -E -S DELLDESKTOP1SQLEXPRESS, 1434 -l 120" Note:Go to SQL server configuration manager, click properties of "Protocols for SQLExpress", check tcp was enabled and click properties, see the configuration of ip1, ip2, ipall, normally, you do not need enable ip1 or ipv2, suggest you disable ipv1 and ip2, let server listen on ipall, leave the dynamic port of ipall blank, restart the sqlserver and retry your connection. Good Luck! Ming
Anonymous
January 22, 2007
Hi, I have a big problem, I have a website in a Windows 2003 and SQL Server 2005, and the website usually goes weel, but some times and error appears, the error is "Named Pipes Provider, error: 40 - Could not open a connection to SQL Server". There is not installed a firewall and TCP and Pipes connection are enabled. Any ideas? Thanks in advance, Miguel
Anonymous
January 28, 2007
This post provides some tips to troubleshoot Sql Server connection problems based on various displayed
Anonymous
January 29, 2007
The comment has been removed
Anonymous
February 04, 2007
I have a really strange problem on our new Windows 2003 SQL 2005 active/active cluster where after approximately 48 hours the server is unable to create or accept socket connections. Windows authentication fails, clustered file shares fail, some applications recieve WSAENO_BUF errors, Windows is unable to create a secure connection to a domain controller, and many other socket related errors. Stopping and restarting the SQL instance resovles the issue for another 48 hours. The server has lots of memory available and MS Server Performance Advisor reports that the server is healthy. I am stumped...The other instance running on the second node does not have this problem. The server ran without issues until we moved production databases to it from SQL 2000
Anonymous
February 05, 2007
Sounds like SQL is leaking resources, most likely socket handles. In the past the few times I've seen this was with 3rd-party items running inside SQL, for example 3rd-party extended stored procedures (xprocs). You can verify this by looking at Task Manager and selecting View | Select Columns... check Handle Count, then monitor the Handles column for sqlservr.exe process. If you see the handles keep climbing over time then you have a handle leak inside SQL. Check if you have any 3rd-party items running inside SQL (for example SQL Lightspeed, etc...) and contact these vendors to ensure you have the latest patches. You may also have your own extended stored procedure dlls verify that these are not doing things with socket handles or review the code to ensure you are not leaking handles. Debugging what is leaking handles inside a process is not an easy to do unfortunately if you are not familiar with debugging tools if the above does not work I would open a support incident with Microsoft for assistance.
Anonymous
February 06, 2007
I think you be right, if I watch the handle count for the sqlservr.exe it climbs constantly at a rate of around 240 hanles per hour. It seems to climb faster when a particular database and related XProc is being accessed. I also notice the system process hanldes climb relative to the handle count for the sqlservr.exe process, is that normal? I will investigate some more..Is there an actual limit to the number of handles a Windows 2003 server can manage before it runs into problems?
Anonymous
February 06, 2007
The comment has been removed
Anonymous
February 14, 2007
The comment has been removed
Anonymous
February 14, 2007
The comment has been removed
Anonymous
February 14, 2007
Hi, Sheetal Did you make local connection or remote connection? If it is remote, you need to identify whether it is because of network issue or sql server close the connection. To identify network issue, please check out the following blog "On client side" section: http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx After making sure it is not network issue, please check out server ERRORLOG or open sQL profile to see which client operation caused the server closing connection. Finally, whether this error occured intermittently or consistently from the beginning. LMK if you have further questions. Good Luck! Ming.
Anonymous
February 20, 2007
Hi everybody!! I'm trying to connect sql server 2005 on two remote computers and the error that i get is: = = = = = = = = = = = = = = = = == = == = = A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Microsoft SQL Server, Error: 10054) = = = = = = = = = = = = = = = = = = = = = = = Any idea how it could be resolved. Thanks
Anonymous
February 23, 2007
The comment has been removed
Anonymous
February 26, 2007
Hi, rob Please check out the following blog regarding connection to sqlexpress, you probably did not enable remote connection for sqlexpress. http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx Good Luck! Ming
Anonymous
February 28, 2007
The comment has been removed
Anonymous
April 23, 2007
I have an ASP.Net 2.0 app that is using sql express. Every couple days I get a "Timeout Expired" error message when creating a connection (not when running a query). This never happens on dev machine, just on production server. I am using a named instance of sqlexpress, sample connection string: "Data Source=.sqlexpress;Database=MyDBName;Trusted_Connection=true;". This is all running on Win2K3 server under NETWORK SERVICE account. Any ideas? Stack trace follows: [SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734867 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) +556 System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj) +164 System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected) +34 System.Data.SqlClient.TdsParserStateObject.ReadBuffer() +30 System.Data.SqlClient.TdsParserStateObject.ReadByte() +17 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +59 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111
Anonymous
May 10, 2007
<a href=http://idisk.mac.com/tolsto/Public/weight-loss-cure.html>weight loss cure</a> <a href=http://idisk.mac.com/tolsto/Public/weight-loss-tips.html>weight loss tips</a>
Anonymous
May 25, 2007
Windows updates are scheduled for the server every 15 days. After windows update the machine reboots on its own but sql server does not start. If I reboot the mchine manually sql server starts without any problem. SQL services set to start automatically. Can somone tell me what do I need to do to fix this problem. Appication Log Set AWE Enabled to 1 in the configuration parameters to allow use of more memory. FallBack certificate initialization failed with error code: 1. Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate. TDSSNIClient initialization failed with error 0x80092004, status code 0x80. TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log. SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems. But when I reboot it manually I got this messages: Set AWE Enabled to 1 in the configuration parameters to allow use of more memory. A self-generated certificate was successfully loaded for encryption. Server local connection provider is ready to accept connection on [ .pipeSQLLocalMSSQLSERVER ].
Anonymous
June 11, 2007
Hi, Tanu This is interesting scenario. Which service account your SQL Server was running under? I suggest you post your question to our security forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=92&SiteID=1 Good Luck! Ming.
Anonymous
June 26, 2007
The comment has been removed
Anonymous
August 14, 2007
The comment has been removed
Anonymous
August 15, 2007
Go to the SQL Server Network Configuration and make sure the protocols are enabled.
Anonymous
August 15, 2007
The only one that is not enable is 'VIA'. Should that be enabled? What exactly does that protocol do?
Anonymous
August 19, 2007
Hi all, here it is another connection issue. I am trying to connect a SQL Server 2005 server from an XP machine. From OSQL and a ASPX application, everything goes well; but when I try to connect with SQL Server Management Studio, the program stops responding and seems waiting indefinitely for the connection: no error message is displayed. The same happens trying to connect the SQL Server on the local machine. I am using Windows authentication, all protocols are enabled, both the server and the local machine allow remote connections, Windows Firewall is stopped and other collegues have no problem. Anyone can help me? Thanks in advance, Gianfranco
Anonymous
August 27, 2007
Hi there, I didn't have much problem connecting to the server and database but I did however have problem with starting up the Sql Server Agent Service. If you have the same problem you can try getting SQL Server SSIS and install it. Hopefully it will help you out! Cheers! Aftab http://www.ComputerVideos.110mb.com/
Anonymous
August 27, 2007
Hi there, I didn't have much problem connecting to the server and database but I did however have problem with starting up the Sql Server Agent Service. If you have the same problem you can try getting SQL Server SSIS and install it. Hopefully it will help you out! Cheers! Aftab
Anonymous
August 31, 2007
Thanks to this article, I was able to fix a service broker problem.
Anonymous
August 31, 2007
PingBack from http://www.nootz.net/index.php/troubleshoot-connectivity-issue-in-sql-server-2005-part-iii.html
Anonymous
September 27, 2007
Hi there, I've have a strange connection problem. The application is ASP.NET (1.1) and it is connection with no problem on SQL 2005 server (SSPI). Recently we have implemented threading and the problem is that when thread tries to access SQL it fails. It doesn't use correct credentials - the error in SQL is following "SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed". Do you have any ideas ? We now that it's works when we use SQL user and password is used which has a credentials on database.
Anonymous
September 27, 2007
Hi, I am getting the following error can you suggest? SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 208.38.82.114 Nicks
Anonymous
October 12, 2007
I am trying to connect to SQL Server 2005 Express through a command prompt. I have gone through the sites and followed the directions to change my settings to connect remotely but still having issues. Below is my error message: SQLState = 08001, NativeError = 2 Error = [Microsoft][SQL Native ClientNamed Pipes Provider: Could not open a connection to SQL Server [2]. SQlState = HYT00, NativeError = 0 Error = [Microsoft][SQL Native Client]Login timed expired SQLState = 08001, NativeError = 2 Error = [Microsoft][SQL Native Client]An error has occured 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.
Anonymous
December 08, 2007
I'm trying to connect to a local database on the same machine where i run my website. when i specify (local) in the datasource i get the target machine actively refused error. When i use my ip address in the datasource i get the "connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond" error. By the way, i enabled tcp and named pipes, i had tcp listenning to 1433 port., basically i did everything that could be done here. Any help please?
Anonymous
December 08, 2007
The comment has been removed
Anonymous
January 04, 2008
Hi, I have hosted a website on a hosting provider. The SQL server data fetching works fine on a page when the page is in the root directory. But when I add the page to a subdirectory and access the database, it gives error 26. I am using the same connection string as defined in the web.config.. Please advise. Thanks Shreyas
Anonymous
January 31, 2008
Hi SQL Team: I am developping a website, I can connect to the production database when I debug the website from Visual Studio 2008, But after I deploy the website to my IIS server, I get the error message 1 above(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)); I try as below:

use the database on internet, in debug mode from Visual Studio 2008, it works fine.
deploy the website, change the database to use an intranet database server, the website works fine.
deploy the website, use the database on internet, it get the error message. How can i figure out the error and resolve it?

Anonymous
February 05, 2008
Changing the service "SQL Server (SQLEXPRESS)" from local service to network service fixed the problem for me.
Anonymous
March 03, 2008
I installed SQL client connectivity tools, and now I got the more traditional (and php team supported 'official') extension called mssql_connect to work, and select/print from tables works ok now with EITHER the odbc_connect or mssql_connect. But I'm still not able to get sqlsrv_connect to work. Here is the code I'm trying to use: ===========//BEGIN QUOTE//========== <?php /* Specify the server and connection string attributes. / $serverName = "(subname.subname2.parent.topname)"; / Get UID and PWD from application-specific files. / $uid = ("myuserid"); $pwd = ("mypw"); $connectionInfo = array( "UID"=>$uid, "PWD"=>$pwd, "Database"=>"testdb"); / Connect using SQL Server Authentication. / if( !( $conn = sqlsrv_connect( $serverName, $connectionInfo))) { echo "Unable to connect.</br>"; die( print_r( sqlsrv_errors(), true)); } / Free statement and connection resources. */ sqlsrv_free_stmt( $stmt); sqlsrv_close( $conn); ?> ===========//END QUOTE//========== Here is the error I get: ===========//BEGIN QUOTE//========== Unable to connect. Array ( [0] => Array ( [0] => 08001 [SQLSTATE] => 08001 [1] => 53 [code] => 53 [2] => [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [53]. [message] => [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [53]. ) [1] => Array ( [0] => HYT00 [SQLSTATE] => HYT00 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Native Client]Login timeout expired [message] => [Microsoft][SQL Native Client]Login timeout expired ) [2] => Array ( [0] => 08001 [SQLSTATE] => 08001 [1] => 53 [code] => 53 [2] => [Microsoft][SQL Native Client]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. [message] => [Microsoft][SQL Native Client]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. ) ) ===========//END QUOTE//========== I have version 8.00.194 of ntwdblib.dll in sys32 dir and in c:php and IUSR has full control over it. At this point, I'm not worried about security, so much as getting this thing to work. I can tighten down later if need be. So I also gave IUSR read/write over the sql server subkey in the registry odbc hive
Anonymous
March 03, 2008
This does not look like an issue particular to php. This looks like a db connectivity issue. See this post for troubleshooting connectivity issues: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1
Anonymous
April 21, 2008
[MICROSOFT][ODBC SQL SERVER DRIVER][TCP/IP SOCKET]CONNECTIONOPEN (CONNECT()).CONNECTION FAILED I'm facing this problem when I configure ODBC in Windows Vista for SQL Server from Windows Sever 2003, where the SQL Server 2000 is installed. Please resolve this problem. Thanks ViveK vivek@bsquareinternational.com vivek_l2k@yahoo.com
Anonymous
April 28, 2008
hello, i have this connection string in web.config file <add name="mycon" connectionString="Server=localost; Database=dbtest.mdf;uid="sanjay"; pwd="rpg007in"" providerName="System.Data.SqlClient" /> but in the above connection string i get error Cannot open database "dbtest.mdf" requested by the login. The login failed. Login failed for user 'snajay'. everything in my code is same and its working properly on y local machine but on shard hosting service i am getting this error. only change is above conenxcion string. my databse was created on my local machine i used ftp to copy it on srver created a db folder under wwwroot while my code behind file is in subdomain i am desperately looking for help after two days of search i came across ur blog and i finally feel that this is where i will get solution. email candychip [at ]gmail.com
Anonymous
June 18, 2008
The comment has been removed
Anonymous
July 06, 2008
PingBack from http://nayeli.linkmediavideo.info/error17054severity16state1sqlserver2005.html
Anonymous
July 23, 2008
Can anyone tell me what is the caused of below. I am facing this error, not always....sometime only... My production is running Windows Server 2003 and SQL Server 2005. Source = .Net SqlClient Data Provider Error = A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - Not enough storage is available to complete this operation.) | at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open()
Anonymous
August 21, 2008
The comment has been removed
Anonymous
August 21, 2008
i have problems connection to my SQL on the server, but not from my remote PC. The error is: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Microsoft SQL Server, Error: -2146893019) I can pay $100 if you can fix it trakhtenberg@stanford.edu
Anonymous
August 25, 2008
The problem seems to be with the SSL certificate being used by the server. My guess is:

Your server is loading a self-signed certificate
Your server is not enforcing encryption on inbound connections.
Your remote client machine is not enforcing encryption on outbound connections.
Your server IS enforcing encryption on outbound connections. (this is the source of your problem) Since neither side enforces encryption with the remote client, it doesn't need the server to have a trustworthy SSL certificate. But, since the server, when opening an outbound connecting to itself, demands encryption, the server needs to have a trustworthy certificate - it tries to use the self-signed cert, and you get this error. If that is the problem, here is a simple solution: disable client-side Force Encryption on the server. On the machine that runs the SQL Server instance, open up the SQL Server Configuration Manager, right-click SQL Native Client Configuration, and set Force Protocol Encryption to No. Then try connecting locally. Hope that helps, Dan

Anonymous
August 28, 2008
on attempt to login to SQL Server 2005 sp2, standard edition in SSMS OR start in services, resulted in the following: "TITLE: Connect to Server

Cannot connect to IBMSERVER.

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: 2) I'm fairly new at this and have spent over 12 hours wading through technet articles that don't apply or don't resolve the issue. SS is configured to listen at port 1433, but if the service won't start, it can't listen, right? More details: System was working until a couple of days ago, when my external app that access SQL couldn't connect to the db. Attemted to login to SSMS under user sa (SQL authentication), but got the above error message. Attempts to login w/ Windows authentication also failed. SQL Server 2005 is running under Windows Server 2008 on Virtual PC 2007 with all latest updates. My busines depends on this working, & I don't know where to go from here. Can some bright guru provide some direction on what to check next? raymillr[AT(change to @)]gate.net

Anonymous
August 29, 2008
Hi Ray, From the error message and the scenario you describe, I expect that your SQL Server service isn't started (you mentioned something about it not being started as well). You can start the service from services.msc. It will be named something like "SQL Server (MSSQLSERVER)" (if it is a named instance, replace MSSQLSERVER with the name of your instance). If your service won't start, post the error message you receive when you try to start it, and if there is a new ERRORLOG file generated at %ProgramFiles%Microsoft SQL ServerINSTANCEMSSQLLog, get that and post the errors from the ERRORLOG file. Also, just FYI, you'll generally get a better/faster response on the SQL Server Data Access forum (http://forums.microsoft.com/msdn/ShowForum.aspx?siteid=1&ForumID=87) than on our blog. Hope this helps, Dan
Anonymous
September 09, 2008
The comment has been removed
Anonymous
September 09, 2008
Prasad, Can you make sure your connection string is updated correctly on the new machine? Please refer to http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx for the specific error. Thanks, Xinwei
Anonymous
September 10, 2008
I am a newbie. How do I update the connection string. I am well and truly stuck. Thanks.
Anonymous
September 16, 2008
Thanks, it solved my "error 26"
Anonymous
September 18, 2008
Hi, I have installed SQL Server 2005 on a Vista Business. I am able to connect to SQL Server instance on Vista machine from Management Studio on other machine (Running on Xp). But I am not able to connect to Analysis Services default instance on Vista Business machine from Management Studio on other machine (Running on Xp). I did all settings with Vista firewall exceptions, SQL Surface area configuration, Security, everything. Please tell me do I need to modify something else on 'Vista Business'. Thank you.
Anonymous
September 28, 2008
I have problem in connecting to remote SQL Express instance, when Windows firewall is enabled:

It's a Workgroup environment
I can connect through remote desktop when firewall is enabled.
I can connect when I disable the Windows firewall
Port 1433 is added in exceptions
SQlserver.exe and SQLbroswers are added in execeptions What am I missing? Thank you

Anonymous
September 28, 2008
Correction:

I can connect through remote desktop when firewall is DISabled. Therefore its only a forewall problem.

Anonymous
October 08, 2008
its win 2003 64bit cluster with sql 2005 sp2 std ed 64 bit. SQL is running fine on one node but failing on another.. while checking agent seems to have problems.. i have disabled shared memory now but still problem is there. I did enable AgentXps but no luck.. 2008-10-09 00:23:40 - ? [393] Waiting for SQL Server to recover databases... 2008-10-09 00:23:40 - ! [298] SQLServer Error: 50, Shared Memory Provider: Shared Memory is not supported for clustered server connectivity [50]. [SQLSTATE 08001] 2008-10-09 00:23:40 - ! [165] ODBC Error: 0, Protocol error in TDS stream [SQLSTATE HY000] 2008-10-09 00:23:40 - ! [298] SQLServer Error: 50, Client unable to establish connection due to prelogin failure [SQLSTATE 08001] 2008-10-09 00:23:40 - ! [000] Unable to connect to server '(local)'; SQLServerAgent cannot start 2008-10-09 00:23:40 - ! [298] SQLServer Error: 50, Shared Memory Provider: Shared Memory is not supported for clustered server connectivity [50]. [SQLSTATE 08001] 2008-10-09 00:23:40 - ! [165] ODBC Error: 0, Protocol error in TDS stream [SQLSTATE HY000] 2008-10-09 00:23:40 - ! [298] SQLServer Error: 50, Client unable to establish connection due to prelogin failure [SQLSTATE 08001] 2008-10-09 00:23:40 - ! [382] Logon to server '(local)' failed (DisableAgentXPs) 2008-10-09 00:23:41 - ? [098] SQLServerAgent terminated (normally)
Anonymous
December 05, 2008
My new SQL Server 2005 instance shuts down every day at 3:00am but I don't recall ever setting anything to do so. I would prefer not having to manually restart this every morning. How do I correct this? All I see in the error log is this: 2006-05-13 02:30:22.82 spid11s Service Broker manager has shut down. 2006-05-13 02:30:22.83 spid5s SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required. 2006-05-13 02:30:22.83 spid5s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
Anonymous
December 05, 2008
SQL does not have an "auto shutdown" feature. I suspect you have automatic Windows Updates turned on any Windows is rebooting. You can verify by scanning event log (Start| Run -> eventvwr) If you see a Windows Update event prior to your shutdown that's probably the cause. Recommendation is turn off automatic Windows Updates and use download automatically feature (but do not apply updates). This way you can precisely schedule when to apply updates and reboot.
Anonymous
December 05, 2008
The comment has been removed
Anonymous
December 05, 2008
Ok, I did find the Windows Update section and it was indeed set to download and install every night at 3:00am. I reset it to just download and prompt me. I'm thinking that this still doesn't explain why the downloads fail though. I'll let this run through first and see what happens when I manually intervene going forward. Thanks again!
Anonymous
December 29, 2008
I see the following error in the event viewer of the SQL 2005 server. This is happening every day at between certain time interval. Please help. Thanks in advance Server local connection provider has stopped listening on [ .pipeSQLLocalMSSQLSERVER ] due to a failure. Error: 0xe8, state: 4. The server will automatically attempt to re-establish listening.
Anonymous
December 30, 2008
It might be the firewall: http://dotnetnotforcompletedummies.blogspot.com/2008/12/its-firewall.html
Anonymous
January 04, 2009
Good Day to all, I really need your advice guys, anyway my problem is that i installed PHP4, MSSQL2005, and using IIS6 in Windows 2003 Server I have no problems in the installation, but when i tried to connect to MSSQL an error would appear, error below: mssql_connect(): message: Login failed for user 'sa'. The user is not associated with a trusted SQL Server Connection. and also this mssql_connect(): message: unable to connect to server: KPSWEB1 KPSWEB1 is the name of the MSSQL Server. Please help... Thanks
Anonymous
January 05, 2009
The comment has been removed
Anonymous
January 05, 2009
bolivar1985, Looks like you have only Windows Auth. Can make sure SQL Authentication is enabled? http://msdn.microsoft.com/en-us/library/ms188670.aspx Thanks, Xinwei
Anonymous
January 05, 2009
Xinwei, Thanks, i will try your suggestion... Thanks, Bolivar1985
Anonymous
January 08, 2009
Xinwei, It works, thank you, i just reinstalled MSSQL2005 Standard edition, then chose the mixed mode, and it run, using mssql_connect(), Thanks, Bolivar1985
Anonymous
January 09, 2009
I have installed SQL server 2005 on my system (Window Vista Home Premium). However, I am getting error messgae when I try to connect. The message is "Cannot connect to ... Login failed for user ... error:18456". I have selected Windows Authentication mode during installation. Can any body help?
Anonymous
January 15, 2009
Hello, I'm experiencing an issue trying to setup an ODBC connection on a Windowns 2003 Server(64 bit)using SQL server 2005(64 bit). It is a 32 bit application that will be using the ODBC datasource, so I am using the ODBC administrator located in C:WindowsSysWOW64odbcad32.exe. However, I am receiving the Microsoft SQL Server Login errors indicating: Connection failed: SQLState: '08001' SQL Server Error: 64 [Microsoft][SQL Native Client]TCP Provider: The specified network name is no longer available. Connection failed: SQLState: '08001' SQL Server Error: 64 [Microsoft][SQL Native Client]Client unable to establish connection Any assistance you can provide would be greatly appreciated.
Anonymous
January 20, 2009
PingBack from http://www.hilpers-esp.com/404764-error-al-querer-conectar-una
Anonymous
January 21, 2009
PingBack from http://www.keyongtech.com/2079472-error-40-a
Anonymous
January 30, 2009
The comment has been removed
Anonymous
February 01, 2009
Bad public user name or password. Database Server Error: Named Pipes Provider: Could not open a connection to SQL Server [53]. I am getting this message on installing Primavera Can anybody help me...? anandc1@gmail.com
Anonymous
February 25, 2009
Hello, I am building a SQL 2008 cluster. On the initial node that I ran the install on, SQL is running fine. However, whenever I try to fail it over, I am receiving these messages in the event viewer: FallBack certificate initialization failed with error code: 1. I do not want to use certificates. Is there any way I can bypass this?
Anonymous
March 17, 2009
The comment has been removed
Anonymous
March 17, 2009
Our problem has been resolved. Someone had made some SSL registry changes which affected SQL Server and other applications.
Anonymous
March 19, 2009
I have a very unique issue related to Error: 26 - Error Locating Server/Instance Specified, I am hoping that your expertise may help. I have a cluster running SQL Server with two instances, listening on ports 2300 and 2305. Connecting to the instances within the network works fine by either using VSPHOST1APHOST1A or VSPHOST1A,2300. I can also from within the network use VSPHOST1A.name1.name2.name3.comPHOST1A or VSPHOST1A.name1.name2.name3.com,2300. The problem is I can not access the SQL Server from another domain/network (within the same company) using the fully qualified namePHOST1A, it only works with the port. I am however able to get both methods under the fully qualified name to work with non clustered instances. It is definitely cluster-related, any thoughts? Thanks!!
Anonymous
March 23, 2009
Hi Andrew, It sounds like you very likely have the problem discussed on this blog entry: http://blogs.msdn.com/sql_protocols/archive/2006/02/27/unable-to-connect-to-a-sql-server-named-instance-on-a-cluster.aspx
Anonymous
May 10, 2009
MMC cannot open the file c:program filesmicrosoft sql server 80toolsebinnsql srver enterprise manager.msc
Anonymous
May 26, 2009
PingBack from http://backyardshed.info/story.php?title=sql-protocols-troubleshoot-connectivity-issue-in-sql-server-2005
Anonymous
June 07, 2009
PingBack from http://greenteafatburner.info/story.php?id=1584
Anonymous
July 20, 2009
Hi If you wants to know how to enable remote connections in sql server 2005 pls visit this http://aspnetmembershipprovider.blogspot.com/2009/02/eanble-remote-connections-on-instance.html Regards
Anonymous
July 22, 2009
Solution: After many tries in diferent ways we found the solution in simply change the connection string in the web config file. We take out: "Persisty security Info=True; User ID=theuser; Password=thepass" and simply keep: <add name="TheLocalNet" connectionString="Data Source=theserver;Initial Catalog=theDataBase;Integrated Security=True" providerName="System.Data.SqlClient"/>
Anonymous
September 01, 2009
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: Failed to connect to server 192.168.0.1. (Microsoft.SqlServer.ConnectionInfo)

A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (Microsoft SQL Server, Error: 64) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476

BUTTONS: OK

Anonymous
September 03, 2009
Hi Ming, I am getting this error when trying to connect to SQL server 2008 server OLEDB Provider for ODBC Drivers: [Microsoft][ODBC SQL Server Driver]Timeoutexpired Any suggestions/ideas, please? Thanks Helen
Anonymous
September 29, 2009
Guss what, i tried everything i saw for errors 26, 28, 40 when they come to me but as if magically they get solved until i suspected in my ADSL modem that i use for DSL internet and that has in it a switch of 4 ports, so i connected my development computer to a new switch and connected to it also the SQL Server workstation, and voila everything worked perfect from the start, the strange thing that when i was on the old ADSL modem, i can make file sharing and copy files but i can't connect to SQL Server. So i suggest that the last step after the above is to check if changing the switch make the connection work or not.
Anonymous
September 30, 2009
Hi, I have the Error Message 2. SQL Server was working since about 2 years and this morning it just stopped to work. I tried everything, even to uninstall then reinstall but now it can't be reinstalled because I have the same error when I tries to connect to configure the server. I really really need help.
Anonymous
November 12, 2009
Hi, I can access my sql server from my laptop, but can not do it from desktop. Following is the error message. I turned off firewall on sqlserver and sqlbrower is running. Thanks, -Benjamin TITLE: Connect to Server

Cannot connect to XYCOMVMEPCSQLEXPRESS.

BUTTONS: OK

Anonymous
November 23, 2009
The comment has been removed
Anonymous
November 30, 2009
TITLE: Connect to Server

Cannot connect to BAOSQLEXPRESS.

BUTTONS: OK

Anonymous
January 08, 2010
We have been using SQL 2005 Express for a long time without issue. Today we lost connectivity with the database on our server. After uninstalling SQL and in trying to reinstall SQL I get the following error during database services: "SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]SSL Provider: The client and server cannot communicate, because they do not possess a common algorithm." I cannot look at surface configuration because there is no connection. What could possibly cause this?
Anonymous
January 12, 2010
Hi SeismoDude, The root cause of the problem is in this message: "The client and server cannot communicate, because they do not possess a common algorithm". This is a message from the Windows Schannel security provider, corresponding to error code SEC_E_ALGORITHM_MISMATCH. I would recommend following up on the Windows Security MSDN forum, and describing the error code: http://social.msdn.microsoft.com/Forums/en-US/windowssecurity/threads If I had to guess, I suspect that some security policy or update made on your database server changed the list of enabled Schannel algorithms, but I do not know how to configure that list (it is a Windows object, not a SQL Server list); people on the Windows Security forum should know how to make those changes to Windows.
Anonymous
January 17, 2010
I have two SQL 2005 servers on Windows 2008 Exterprise. I am trying to establish a mirror between the two servers. I am able to successfully ping both servers by name and IP address. I have turned the Windows firewall off and they are on the same subnet with no firewall between the two devices. However, when I try to establish the mirror, I can connect to both servers, but when the mirror replication starts, it gives an error that it could not communicate with the server. Would this be a shared memory or SQL browser issue?
Anonymous
January 26, 2010
Hi there? I have one question. Is there a problem using server name like server-dc? Becuse I have HP Prolient server gl360 and sbs2008, and sql server 2005 enterprise. But when i tried to connect using a conneciton stirng server name server-dc it keeps rejecting me. Any solution Regards Abnier
Anonymous
January 27, 2010
Getting the following error and FIPS is NOT Enabled on eiher the server or the client... A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The client and server cannot communicate, because they do not possess a common algorithm.)
Anonymous
February 14, 2010
I have installed my system the combination of Windows'2008 , Sql Server 2008 and Classic ASP3.0. I Can connect Asp With sqlserver 2008 Successfully, and I can connect Sqlserver 2008 with Crystal Reports 8.5. But I am not able to Migrate my Reports from Crystal Reports to Sql Server Report services in 2008. So Please help me to resolve the problem.
Anonymous
April 12, 2010
Hi, We are about to change the Password for SQL SERVER SERVICE ACCOUNT and we have encrpytion implemented my question is > Will this change of password would effect our encrytion keys. If yes what should be done to overcome. we have SQL server set on clustered (ActivePassive) Environment. Thanks
Anonymous
May 03, 2010
The comment has been removed
Anonymous
May 13, 2010
I have a customer that is doing a client/server installation using existing SQL express installation. On the server we have migrated the database and on the workstation we have tried to point to the server DB and get the error “server doesn't exist or access denied”. I have tried to disable firewall on the server machine, same results. I even did a full install on the client and tried to use the change database option. It will see the remote server but when you hit “fetch DB from Server” it gives the same error. The server is running windows server 2003 and the client machine is windows XP. Do you have any suggestions on what I can try next?
Anonymous
May 13, 2010
I have a customer that is doing a client/server installation using existing SQL express installation. On the server we have migrated the database and on the workstation we have tried to point to the server DB and get the error “server doesn't exist or access denied”. I have tried to disable firewall on the server machine, same results. I even did a full install on the client and tried to use the change database option. It will see the remote server but when you hit “fetch DB from Server” it gives the same error. The server is running windows server 2003 and the client machine is windows XP. Do you have any suggestions on what I can try next?

restarted the SQL browser
made sure Listen all is enabled 3.Client server in same network and domain 4.Firewall is off, did it on both server and client to be sure 5.Settings on SQL surface configuration is enabled except VIA Please help me in resolving this issue And log says: Opening of connection failed.Error no:--2147467259.Error description:-[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

Anonymous
September 05, 2010
We're sometimes getting this error, which doesn't seem to be covered by anything in this article: [SQL Native Client]TCP Provider: The semaphore timeout period has expired. [SQL Native Client]Communication link failure Any ideas?
Anonymous
January 31, 2011
Bad public user name or password. Database Server Error: SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. ; i have this error when trying to configur primavera database.Please help me!!!
Anonymous
March 14, 2011
I just encontered this problem and the way I resolved it was just restarting the PC after creating the new user.
Anonymous
October 17, 2011
I have a problem creating a communication between sqlserver and sqlclient,when running both sqls the sqlclient cannot see or recognise sqlserver.what might be the problem?
Anonymous
March 04, 2013
thanks. This worked for me. cheers Sathya
Anonymous
March 15, 2013
The comment has been removed
Anonymous
December 02, 2014
Just wanted you to get new version!

Share via

Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Comments

Cannot connect to IBMSERVER.

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: Failed to connect to server 192.168.0.1. (Microsoft.SqlServer.ConnectionInfo)

BUTTONS: OK

Cannot connect to XYCOMVMEPCSQLEXPRESS.

BUTTONS: OK

Cannot connect to BAOSQLEXPRESS.

BUTTONS: OK

Additional resources