Troubleshoot Connectivity Issue in SQL Server 2005 – Part III


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 http://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 (218)

  1. Randy Martin says:

    This is a great list of troubleshooting tips. I would like to add one for your inspection and (if possible) resolution. This has been a stumper for me. Any help would be appreciated.

    Randy Martin

    randy.martin@ds-iq.com

    OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

    Msg 782, Level 16, State 1, Line 0

    SQL Network Interfaces: No credentials are available in the security package

    OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message "Client unable to establish connection".

    Msg 782, Level 16, State 1, Line 0

    SQL Network Interfaces: No credentials are available in the security package

  2. Julian says:

    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

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

  4. Hi, randy

    Here is our suggestion:

    This might caused by the kerberos ticket in the session cache expires in the middle tier server.The situation could be first, you configure a linked server C on your machine B, and you connect to B from machine A by using SSMS and executing query of C; Secondly, you connected from A to B as your domain credential, and B connect to C using same credential based on your configuration. But if you kept the connection from A to B for a long time, your domain credential catche on B probably expires after a period of continuous use.

    Possible resolution:

    1) If your OS is WIN2K and XP, please refer follow KB artical

    818173 Authentication May Intermittently Fail

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;818173

    2) If not 1), on your machine B, please configure a new linked server by specifying the DSN equals C, and explicitly specify a sql login name and pwd in security instead of using current login credential.

    If you still face the problem, please let us know your configuration and how you make connection, if possible, attach any info in the server log.

    Thanks for your comments!

    Ming.

  5. Steve says:

    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

  6. Hi, Steve

    First, thanks for posting detail info to help us identify possible cause. Secondly, your problem might be either corruption of encryption setting or schannel corruption on your client side box.

    1)Your client side setting probably was messed up. Please go to SQL Server Configuration Manager and click properties for "SQL Native Client Configuration", if correct, you should be able to see two flags, one is "force client encryption", the other one is "trust server certificate" and value were set "yes" or "no". If you see "No properties are available", that means your client registry key was messed up and leads to the connection error . To fix this, suggest reinstall SNAC(SQL Native Client).

    2)If you are sure 1) was not the case, that might be schannel library somehow corrupt on your client box or no encryption support in you OS, and we need to collect more info, eg, see whether there is warning or error in the eventlog ‘security’.

    3)Please double check whether there is any error which indicates certificate or encryption in the server error log, and if there is, please let us know what kind of error displayed.

    Most likely, your case is due to 1), to fix the messed up setting on your client, you’d better reinstall SNAC which is in tools part of Yukon installation.

    Wish this help you!

    Ming.

  7. Steve says:

    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

  8. Rajesh says:

    Hi,

    I am having a SQLNCLI problem similar to  Randy’s issue above, but slightly different.

    I have configured a linked server C (SQL2000) on machine B (SQL2005), and I connect to B from machine A by using terminal Server and executing a simple query

    INSERT INTO mydb..mytable — this is on Machine B

    Select * from C.mydb..mytable where ….

    This is dealing with a data set of about 13 Million and it fails with a timeout error (details at the end of this message) and it fails exactly after 10 minutes.

    Summary – Scenario 1:

    A ->termServ->B->Run distributed query involving C (in management studio) or execute SSIS package. — Failure either way after exactly 10 minutes.

    I have this same operation going on on a different machine D, where this is part of an "Exec SQL task" inside a SSIS package.

    And the package is schedule to run nightly and it does this part in about 30 minutes.

    Last week this package started failing after 10 minutes of trying – before last week it was always successful and finished in roughly 30 minutes.

    Summary – Scenario 2:

    D -> Scheduled task involving a pkg doing a distributed query involving C — Suddenly Started Failing after exactly 10 minutes and continued failing consistently.

    So, I looked around and found a Remote Query timeout setting of 10 minutes on ServerC. I set this to 0 (unlimited), and this fixed scenario 2.

    Scenario 1 continues to fail.

    ======Error===========================

    OLE DB provider "SQLNCLI" for linked server "ServerC" returned message "Query timeout expired".

    Msg 7399, Level 16, State 1, Line 1

    The OLE DB provider "SQLNCLI" for linked server "ServerC" reported an error. Execution terminated by the provider because a resource limit was reached.

    Msg 7421, Level 16, State 2, Line 1

    Cannot fetch the rowset from OLE DB provider "SQLNCLI" for linked server "ServerC". .

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

    Any thoughts or help is greatly appreciated.

    Thanks,

    Rajesh

  9. Rajesh says:

    Forgot to include my email.

    rajesh_vee@donotspam.hotmail.com

    – email modified to prevent automated spam engines. Please cut out the donotspam part. Thanks.

  10. Try setting the Remote Query timeout on server/machine B, or possibly on all servers involved, and let us know whether it makes any difference.  

    FYI, setting the value to 0 (infinity) is not recommended.  Prfeerably set to to a value that is high enough for your purpose.  

    Thanks,

    Peter Gvozdjak

  11. skiblue says:

    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.

  12. Nan Tu says:

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

  13. Francois says:

    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?

  14. Agron Bauta says:

    Hi,

    the problem that is driving me nuts is this:

    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.

    =====

    I have enabbled and activated sqlbrowser but  still get the error: 26 as above.

    When I am running the >sqlcmd utility, connecting and quering the DB work fine.

    Is there any other cause and solution to the error: 26 other than the above explanation. All the resources I found online say the same thing, which obviously is not good enough for me.

    Regards,

      Agron

     

  15. Agron Bauta says:

    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

  16. Hi, Agron

      It seems you installed SQLEXPRESS. Normally, it is installed as a named instance and with TCP and Named Pipe are disabled by default.

    To resolve this:

    1)If you have shared momery disabled, either enable it or enable tcp,named pipe and restart SQL Server.

    2)If 1) is not the case, and you still fail to connect even with TCP and Named Pipe enabled, please check which port or pipe name server is listening on( go to SQL Server Configuration Manager, click properties for *Protocols for MSSQLSERVER*), then specify exact port in your connection string:

    data source=AGRONSQLEXPRESS,<PortNumber> ..; if that works, that might be sqlbrowser issue. But remember double check whether server is listening on the configured port.

    Here is useful info for SqlExpress

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

    Let me know if you have further  questions.

    Ming.

  17. Agron Bauta says:

    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

  18. Joe THomas says:

    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?

  19. Hi, Joe

       To identify your problem, we need further information:

       1) Does your SQL 2000 run on the same box as SQL 2005?

       2) What OS are they installed? Was SQL 2000 installed on WIN2K or WIN2K3?

       3) How do you make connection to SQL 2000 and SQL 2005? Could you provide the connection string, which protocol you use to connect different server, TCP or Named Pipe?

       4) Could you attach any info inside the Server Log file? The error you saw was returned by server response, so we believe helpful info should be logged.

       5) Since the domain aren’t trusted, the SQL Server 2005 behavior seems right. Could you provide more detail info about your domain credential configuration?

    Thanks!

    Ming.

  20. Joe THomas says:

    1) Does your SQL 2000 run on the same box as SQL 2005?  

    No – separate servers.

      2) What OS are they installed? Was SQL 2000 installed on WIN2K or WIN2K3?

    SQL 2000 – Windows 2000

    SQL 2005 – Windows 2003

      3) How do you make connection to SQL 2000 and SQL 2005? Could you provide the connection string, which protocol you use to connect different server, TCP or Named Pipe?

    TCP — I am using the System DSN (under Admin tools, Data Sources).

      4) Could you attach any info inside the Server Log file? The error you saw was returned by server response, so we believe helpful info should be logged.

    Date 3/13/2006 5:23:47 PM

    Log SQL Server (Current – 3/14/2006 2:00:00 AM)

    Source Logon

    Message

    Login failed for user ”. The user is not associated with a trusted SQL Server connection. [CLIENT: 10.3.1.73]

    —————–

    Date 3/13/2006 5:23:47 PM

    Log SQL Server (Current – 3/14/2006 2:00:00 AM)

    Source Logon

    Message

    Error: 18452, Severity: 14, State: 1.

    ———————

    Date 3/13/2006 5:23:47 PM

    Log SQL Server (Current – 3/14/2006 2:00:00 AM)

    Source Logon

    Message

    SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 10.3.1.73]

      5) Since the domain aren’t trusted, the SQL Server 2005 behavior seems right. Could you provide more detail info about your domain credential configuration?

    Sorry – I don’t understand the question.

  21. Nan Tu says:

    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.

  22. Joe,

     In both cases, ss2k and ss2k5, you can check the login use name using "select suser_name()"

  23. Jeremy Holt says:

    Hi, I’m in a bit of a panic.

    I have successfully been running SQL Server 2005 (Integrated Security) on Windows Server 2003 SP1 for the last few months.

    Ever since I installed the latest Critical Updates from Windows Updates this afternoon, all remote connections to SQL Server are being refused. The updates installed were:

    Update for Windows Server 2003 (KB904942)

    Update for Windows Server 2003 (KB912945)

    Windows Malicious Software removal Tools – March 2006 (KB890830).

    I am unable to connect to my remote server using SQL Server Management Studio on my local machine. None of my applications using the same connection string: "Data Source=my.server.com;Initial Catalog=myCatalog;Integrated Security=True" are working – each attempt to connect to the server returns this error message:

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

    If I access the server through an internet application (i.e. one running on the same machine as SQL Server) – I have no problems, in that case the connection string is: "Data Source=(local);Initial Catalog=myCatalog;Integrated Security=True".

    The server was under a sustained attack from some prick this morning trying the usual username = root, admin, sa, sql etc.

    Please let me know if there is any more information I can provide to help in working out what’s happened.

    I am absolutely desperate ……

    Regards

    Jeremy Holt

    jholt@nospam.amberwoodtrading.com (please remove the nospam bit).

    Tel: +5585 3273 3444

    The error log is

    2006-03-15 16:52:58.84 Server      Microsoft SQL Server 2005 – 9.00.1399.06 (Intel X86)

    Oct 14 2005 00:33:37

    Copyright (c) 1988-2005 Microsoft Corporation

    Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

    2006-03-15 16:52:58.84 Server      (c) 2005 Microsoft Corporation.

    2006-03-15 16:52:58.84 Server      All rights reserved.

    2006-03-15 16:52:58.84 Server      Server process ID is 236.

    2006-03-15 16:52:58.84 Server      Logging SQL Server messages in file ‘C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG’.

    2006-03-15 16:52:58.84 Server      This instance of SQL Server last reported using a process ID of 3948 at 3/15/2006 4:52:20 PM (local) 3/16/2006 12:52:20 AM (UTC). This is an informational message only; no user action is required.

    2006-03-15 16:52:58.84 Server      Registry startup parameters:

    2006-03-15 16:52:58.84 Server       -d C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmaster.mdf

    2006-03-15 16:52:58.84 Server       -e C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG

    2006-03-15 16:52:58.84 Server       -l C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmastlog.ldf

    2006-03-15 16:52:58.85 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

    2006-03-15 16:52:58.85 Server      Detected 1 CPUs. This is an informational message; no user action is required.

    2006-03-15 16:52:58.95 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-03-15 16:52:59.01 Server      Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.

    2006-03-15 16:52:59.04 Server      The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted.  If you would like distributed transaction functionality, please start this service.

    2006-03-15 16:52:59.04 Server      Database Mirroring Transport is disabled in the endpoint configuration.

    2006-03-15 16:52:59.07 spid5s      Starting up database ‘master’.

    2006-03-15 16:52:59.22 spid5s      Recovery is writing a checkpoint in database ‘master’ (1). This is an informational message only. No user action is required.

    2006-03-15 16:52:59.50 spid5s      SQL Trace ID 1 was started by login "sa".

    2006-03-15 16:52:59.57 spid5s      Starting up database ‘mssqlsystemresource’.

    2006-03-15 16:53:00.33 spid9s      Starting up database ‘model’.

    2006-03-15 16:53:00.39 spid5s      Server name is ‘STAR’. This is an informational message only. No user action is required.

    2006-03-15 16:53:00.69 Server      A self-generated certificate was successfully loaded for encryption.

    2006-03-15 16:53:00.70 Server      Server is listening on [ ‘any’ <ipv4> 1433].

    2006-03-15 16:53:00.70 Server      Server local connection provider is ready to accept connection on [ \.pipeSQLLocalMSSQLSERVER ].

    2006-03-15 16:53:00.71 Server      Server local connection provider is ready to accept connection on [ \.pipesqlquery ].

    2006-03-15 16:53:00.71 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].

    2006-03-15 16:53:00.71 Server      Dedicated admin connection support was established for listening locally on port 1434.

    2006-03-15 16:53:00.72 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-03-15 16:53:00.72 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.

    2006-03-15 16:53:00.79 spid12s     Starting up database ‘msdb’.

    2006-03-15 16:53:00.79 spid13s     Starting up database ‘ReportServer’.

    2006-03-15 16:53:00.79 spid14s     Starting up database ‘ReportServerTempDB’.

    2006-03-15 16:53:00.81 spid15s     Starting up database ‘AdventureWorksDW’.

    2006-03-15 16:53:03.95 spid9s      Clearing tempdb database.

    2006-03-15 16:53:04.22 spid12s     Starting up database ‘AdventureWorks’.

    2006-03-15 16:53:04.31 spid15s     Starting up database ‘ATRBE’.

    2006-03-15 16:53:04.40 spid14s     Starting up database ‘Florenzano’.

    2006-03-15 16:53:04.45 spid13s     Starting up database ‘Iracema’.

    2006-03-15 16:53:08.98 spid14s     Starting up database ‘StockControlSQL2001’.

    2006-03-15 16:53:09.21 spid12s     Starting up database ‘StockControlSQL2002’.

    2006-03-15 16:53:13.26 spid13s     Starting up database ‘aspnetdb’.

    2006-03-15 16:53:16.75 spid9s      Starting up database ‘tempdb’.

    2006-03-15 16:53:19.16 spid13s     The Service Broker protocol transport is disabled or not configured.

    2006-03-15 16:53:19.16 spid13s     The Database Mirroring protocol transport is disabled or not configured.

    2006-03-15 16:53:20.07 spid13s     Service Broker manager has started.

    2006-03-15 16:53:20.25 spid52      Using ‘xpstar90.dll’ version ‘2005.90.1399’ to execute extended stored procedure ‘xp_instance_regread’. This is an informational message only; no user action is required.

    2006-03-15 16:53:20.68 spid5s      Recovery is complete. This is an informational message only. No user action is required.

    2006-03-15 16:53:22.14 spid52      Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.

    2006-03-15 16:53:22.36 spid52      Configuration option ‘Agent XPs’ changed from 0 to 1. Run the RECONFIGURE statement to install.

    2006-03-15 16:53:22.56 spid52      Configuration option ‘show advanced options’ changed from 1 to 0. Run the RECONFIGURE statement to install.

    2006-03-15 16:53:24.33 spid52      Using ‘xpsqlbot.dll’ version ‘2005.90.1399’ to execute extended stored procedure ‘xp_qv’. This is an informational message only; no user action is required.

    2006-03-15 16:53:25.07 spid52      Using ‘xplog70.dll’ version ‘2005.90.1399’ to execute extended stored procedure ‘xp_msver’. This is an informational message only; no user action is required.

  24. Matt Neerincx [MSFT] says:

    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.

  25. Jeremy Holt says:

    Yes – you’re absolutely correct. Our ISP had put a complete block on incoming packets to 1433 after I reported the attacks (but forgot to mention this to me).

    By the time I’d sent this message to you, I had started smoking again – and I only gave up a week ago!!

    By the way,  what is the recomended way to protect SQL Server from attacks? The database is really an intranet – limited access to a very small group of users – however the users are located in various parts of the world, and for the most part use dynamic ip addresses provided by their local ISP.

    Regards

    Jeremy

  26. Joe THomas says:

    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.

  27. Julius P. says:

    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?

  28. Nan Tu says:

    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,

  29. Matt Flynn says:

    Hi,

    We recently upgraded from SQL 2K to SQL 2005 on WINDOWS 2003 Server. The entire upgrade process has run pretty smoothly aside from encryption.

    We have ASP.NET 1.x apps that connect to an internal server but fail when encrypted. We have tried all of the steps available on the MS KB and still have not resolved the issue.

    On http://msdn2.microsoft.com/en-us/library/ms191192.aspx, we find the following:

    To configure the server to accept encrypted connections

      1. In SQL Server Configuration Manager, expand SQL Server 2005 Network Configuration, right-click Protocols for <server instance>, and then selectProperties.

      2. In the Protocols for<instance name> Properties dialog box, on the Certificate tab, select the desired certificate from the drop down for the Certificate box, and then click OK.

      3. On the Flags tab, in the ForceEncryption box, select Yes, and then click OK to close the dialog box.

      4. Restart the SQL Server service.

    The problem is when we get to step 2, there are NO certificates available in the dropdown box.

    This problem is solely the result of installing SQL Server 2005. The apps were successfully encrypting data connections prior to the install. All internal and non-encrypted connections work correctly and no other changes were made to the box aside from the SQL 2005 upgrade.

    What can I do to get certificates to appear in the Certificate tab of the Properties dialog box?

    Many thanks.

    -Matt

  30. Matt Neerincx [MSFT] says:

    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.

  31. Matt Flynn says:

    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.

  32. Matt Neerincx [MSFT] says:

    Open MMC snapin and locate your certificate and double click on it to open the "Certificate" dialog box.

    On the General tab you should see something like:

    Issued to: xyz

    Issued by: xyz

    Valid from <start date> to <end date>

    Then below the Valid from… you should see:

    "You have a private key that corresponds to this certificate"

    If you don’t see this, then the cert does not contain the private key.

    For Enhanced Key Usage go to the Details tab and scroll down until you see "Enhanced Key Usage" section.  If you select this you should see:

    Server Authentication (1.3.6.1.5.5.7.3.1)

    You may see more things in here, this is fine, but Server Authentication is required.

    I know I ran into difficulty getting #2 to work with some methods of obtaining the certificate.  Let me know how you are obtaining the certificate.

  33. Nan Tu says:

    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.

  34. Matt Flynn says:

    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

  35. Matt Flynn says:

    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.

  36. Matt Neerincx [MSFT] says:

    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)

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

  38. MING LU [MSFT] says:

    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.

  39. Matt Flynn says:

    Ming,

    The link you provided under #1 is clearly incorrect. Goes to a celebrities page of some sort.

    Anyway, as soon as we upgraded, all of our ASP.NET apps that were using Encrypt=True in their connection strings simply stopped working. Nothing else on either box, the server or the client, was changed. We changed the connection string to Encrypt=False and the apps work like they should, aside from not being encrypted. So my question is, what could cause this?

    We have tried just about every MS KB article and recommendation we can find. We created new certificates using a CA server. We made sure they have had private keys and allowed server authentication. Force protocol encryption is set to YES on the server.

    W still have never been able to get the certificate we create to appear in the list. We are logging on the server as the domain admin and starting the service under the same account.

    Any ideas why we can’t see the cert in the list or why the encryption failed after a SQL upgrade?

  40. MING LU [MSFT] says:

    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.

  41. Matt Flynn says:

    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.

  42. Matt Flynn says:

    Here is the error we recieve from asp.net 2.0 when Encrypt=True…

    "System.Data.SqlClient.SqlException: 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.)

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

      at System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState)

      at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

      at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)

      at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

      at Security.GetSystemData() in C:InetpubscreenApp_CodeSecurity.vb:line 109

      at Security.SecurityCheck() in C:InetpubscreenApp_CodeSecurity.vb:line 94"

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

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

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

  46. SQL Express Guy says:

    Help!

    I have recently installed SQL Express on my Web Server. I am running .Net 1.1. I am using the ADO.NET SqlClient. The SQL Server and the Web Server are on the same machine.  Only local connections and shared memory are enabled.  I have no trouble connecting to the server via Management Studio or sqlcmd.  When I attempt to connect via ADO.NET, I receive a SQLException which says simply "Invalid Connection".  This is the connection string:

    Server=.SQLEXPRESS;Database=SomeDb;trusted_connection=sspi;

    What am I doing wrong?  I would prefer not to enable remote connections or other protocols if possible.

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

  48. Also, please try "odbcad32.exe" through command line, and type ".SQLExpress" as server name, if connection works, that means your .NET app has problem.

    Thanks!

    Ming.

  49. Alistair says:

    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. …???

  50. bdotjones says:

    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.

  51. MING LU [MSFT] says:

    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!

  52. Ricky says:

    Hi, I am getting the following error when ever I am trying to connect to sql server 2005

    "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. (Provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (.Net SqlClient Data Provider)"

    I tried to ping the server "ping xx.xxx.xx.x" and it works fine, but however when I try to "telnet xx.xxx.xx.x 1433" this however fails.  When I log on into the physical box where SQL server is installed then I can connect, however if I try to connect from another machine, this error happen, Can any one please shed some light on this error, I am sure its some setting being incorrectly set, Arrrrrrrrrrrrrrrrhh

  53. Hi, Ricky

       1) Did you install SQL Express 2005? if so, please how to enable remote connection according to below blog:

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

       2) You can not telnet indicates that either you did not enalbe TCP or server did not listen on port 1433, it depends on whether you install sqlserver as a default instance and named instance, if it is named instance, please first, enable sqlbrowser services; secondly, enalbe named pipe and tcp and restart sql server make sure it started, check which port sql server is listening on, then try telnet by specify the port.

    3) double check your connection string, see whether it specify the correct instance, if it is default instance, you can specify the <machinename>, if it is named instance, you need to specify <machinename><instancename>.

    Good Luck!

    Ming.

  54. Ranga says:

    Hi…!

    I need to log remotely to the SQL EXPRESS 2005. lets say i dont have any tools like SQL SERVER MANAGMENT STUDIO in da machine which is SQL EXPRESS installed. how can i access it from another machine which has SQL SERVER MANAGMENT STUDIO ?

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

  56. JBurgess says:

    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

  57. Madrona says:

    This is helpful but I still can’t get it working. Here’s the scenario:

    Server A runnign SQL 2005 Standard (Two instances – 1 and 2).

    Instance 1 hosts many customer databases

    Instance 2 hosts an application db

    I am writing sp’s and functions to eventually be called from the application.

    In order for most of them to work I need to have the application pass me the name of the customer database among other parameters.

    THen I want to run a sp (or funtion) on the specified client Db. Example:

    Declare @ExString varchar

    Declare @ClientDb nvarchar(50)

    Declare @Id int

    Declare @TimeZoneOffset varchar

    set @ClientDb = ‘CLIENT_DB1’

    set @Id = 123

    set @ExString = ‘SELECT Time_Zone_offset FROM ‘ + @ClientDb + ‘.dbo.TimeZone_tb WHERE Id =’ + @Id

    Execute (@ExString, @TimeZoneOffset OUTPUT)

    AT [Instance1];

    I am trying to execute this from SSMS either on my workstation or on the server itself.

    I have setup Instance1 as a linked server and security is set to "Being Made using this security context:" with a valid SQL user account for the database on Instance1.

    The message returned is:

    Msg 7411, Level 16, State 1, Line 12

    Server ‘INSTANCE1’ is not configured for RPC.

    My questions are:

    A. Am I approaching this the best way (using execute to pass the client database name? Almost everything I have to write needs to be database non-specific because of the number of client databases. Is there another, better way?

    B. How do I configure RPC so that this will work?

    Thanks for the prompt reply!

  58. Madrona says:

    Got the RPC thing working. It also looks like sp_executesql will solve this issue.

    But one question remains, is this the best way?

  59. Migue says:

    Hello,

    I have a problem that is disturbing to me.

    I have installed SQL Server 2005 in a W2K3 joined to a workgroup. I’m using a local user account  to execute the service, the Windows Authentication mode and local accounts for users.

    Everything is working, unless I cannot connect from the same machine using FQDN or IP address. ie: from the server I can connect, without problems, using any of this:

    tcp:localhost,1433

    tcp:machine,1433

    but I cannot be connected like

    tcp:machine.domain.com,1433

    tcp:x.y.z.t,1433

    From other computers (using the same user + password) it works using anyone of the options (IP address, short name and FQDN).

    Although I do not understand which is the problem, did not worry to me until I have seen that I cannot setup the Database mirroring, since it gives that error to me.

    Any help would be appreciated

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

    1) [Logon] Error: 17806, Severity: 20, State: 2

    2) [Logon] SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 158.42.250.74]

    3) [Logon] Error: 18452, Severity: 14, State: 1

    4) [Logon] Login failed for user ”. The user is not associated with a trusted SQL Server connection. [CLIENT: x.y.z.t]

  60. Hi, Migue

       The two quick workaround here is:

       1) Enable Named Pipe, and connect through "np:machine.domain.com/

    np:x.y.z.t"

       2) Use SQL Authentication.

       To identify the root cause, I have question for you:

       a. Did your machine join domain before?

       b. Is your machine connected to a network?

       c. Can you also try "tcp:127.0.0.1,1433", see what happens?

    Thanks!

    Ming.

  61. Migue says:

    Hi, Ming

    thanks for your quick answer.

    Answering your questions:

      a. Did your machine join domain before?

         No, from the beginning it is in a workgroup and it has never joined a domain

      b. Is your machine connected to a network?

         Yes. It’s in a network with connectivity to the other computers and servers (DHCP, DNS, …).

      c. Can you also try "tcp:127.0.0.1,1433", see what happens?

         It works well. When I noticed the problem I checked the connectivity and everything seems correct (telnet x.y.z.t 1433 also works)

    And … do you mean that Database mirroring works with named pipes? I had understood that the protocol was necessary tcp.

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

  63. suyog says:

    OLE DB provider "SQLNCLI" for linked server "LinkerServerName" returned message "Communication link failure".

    Msg 233, Level 16, State 1, Line 0

    Named Pipes Provider: No process is on the other end of the pipe.

    OLE DB provider "SQLNCLI" for linked server "LinkerServerName" returned message "Invalid connection string attribute".

  64. Hi, suyog

       The error indicated that either you might have not name pipe protocol enabled on your server or your connection string is not correct.

       To resolve the issue, could you

    1) provide your connection string? how do you make connection? Is your server a default or named instance? did you add the instance name to your connection string? Did you make local or remote connection?

    2) Can you go to SQL Server Configuration Manager to find your sql instance, and enable Named Pipe and then restart sever, and retry?

    Good Luck!

    Ming.

  65. Faisal says:

    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.

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

     

     

  67. Van-Loc Doran says:

    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>

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

  69. Dexter says:

    Hello Ming –

    Thanks for the great t-shooting list.

    Despite this, I’m still having trouble getting a new linked server to work.

    Server Mfr is 2005 sp1 and is the ‘local’ server.

    Server Krypton is 2000 sp3 and is the target server I need to link to.

    I have the Windows login on both systems and I’m in the sysadmin group on both servers.

    After linking the servers, I ran this:

    EXEC sp_addlinkedsrvlogin @rmtsrvname = ‘KRYPTON’, @useself = ‘true’

    When I try running this query:

    SELECT * FROM OPENQUERY(KRYPTON, ‘SELECT * FROM CRICUST WHERE CustName = ”Acme”’)

    I get these errors:

    OLE DB provider "SQLNCLI" for linked server "KRYPTON" returned message "Communication link failure".

    Msg 10054, Level 16, State 1, Line 0

    TCP Provider: An existing connection was forcibly closed by the remote host.

    Msg 18452, Level 14, State 1, Line 0

    Login failed for user ‘(null)’. Reason: Not associated with a trusted SQL Server connection.

    Can you see what I’m missing?

    Thanks!

    dj

  70. 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?

  71. Very many thanks for a good work. Nice and useful. Like it!

  72. kihh says:

    Hi,

    I’m getting the same error as suyog (233 Named Pipes Provider: No process is on the other end of the pipe). I’m sure that named pipes is enabled, however.

    I get this error when trying to log in with the SQL Management Studio.

    Thanks for any help you can provide.

  73. MING LU [MSFT] says:

    Hi, kihh

       Assume you have named pipe enabled on your server:

       1) Can you check the server errorlog the pipe name that server is listening on, try your connectiong string by specifying the pipe name, eg: "osql /Snp:\<machinename>pipesqlquery" or "osql /Snp:\<machinename>pipemssql$<instancename>sqlquery" see whether you can connect?

     2)Are you making remote connection? if so, could you check whether you turn on "Files and printer sharing" on your remote server firewall?

     3)Can you check following blog, whether it is a potential cause?

    http://blogs.msdn.com/sql_protocols/archive/2006/07/26/678596.aspx

     4)The error you saw is intermitentely or consistently?

     5)Is your sql server a default or named instance? If named instance, please check your connection string, whether you specify the correct server pipe?

    Finally, when you were saying you have named pipe enabled, is it client side or server side, if you do not have named pipe enabled on server side, the cause might be

    a. go to Sql server configuration manager,-> client protocols -> enabled protocol, put tcp on the top of np.

    b. or turn off shared memory on your server side and restart sql server.

    Good Luck!

    Ming.

  74. kihh says:

    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?

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

  76. riaan says:

    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.

  77. page says:

    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)

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

  79. With shipping SQL Server 2005, we heard from customer feedback about suffering make successful remote…

  80. With shipping SQL Server 2005, we heard from customer feedback about suffering make successful remote

  81. Vidya says:

    Hi

    Can you please help me with this problem

    .Following is the problem I am facing when trying to remotely log into  SQL Server 2005

    ·         Got an error when logging in with SA::St0pLand0 – There was no process at the end of the pipe (or some such)

    o   Followed KB info to activate named pipe and TCP/IP access, restarted, getting same error

    ·         After a couple attempts, SA account gets locked out

    o   Run script: Alter Login SA With Password = ‘St0pLand0’ unlock

    o   Indicates success

    o   Access sa login properties under SecurityLogins – status area, shows “Login is locked out” checkbox checked.

    ·         Also server seems VERY slow to respond at some points

  82. Vidya says:

    Hi

    Can you please help me with this problem

    .Following is the problem I am facing when trying to remotely log into  SQL Server 2005

    ·         Got an error when logging in with SA::St0pLand0 – There was no process at the end of the pipe (or some such)

    o   Followed KB info to activate named pipe and TCP/IP access, restarted, getting same error

    ·         After a couple attempts, SA account gets locked out

    o   Run script: Alter Login SA With Password = ‘St0pLand0’ unlock

    o   Indicates success

    o   Access sa login properties under SecurityLogins – status area, shows “Login is locked out” checkbox checked.

    ·         Also server seems VERY slow to respond at some points

  83. Jay Larbes says:

    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.

  84. Goose says:

    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

  85. Ajith says:

    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

  86. gsmani says:

    Hi Ming,

    I am having a peculier problem when using Site Manager for MCMS 2002 and i am using SQL Server 2005(standard) sitting on other side of firewall.  I am able to access data sizes of 30 MB and below.  When it exceeds, i am getting the following error message:

    Import Preview

    ——————————————————————————–

    Report Information

    Package file: C:PROGRA~1MIB84C~1ServerTempsdupload1.sdo  

    Generated on: 11/8/2006 6:33:21 PM  

    Report generated by: WinNT://NYCEDC-BAT-WEB1/MCMSAdmin  

    Client Machine: NYCEDC-BAT-WEB1  

    Client Version: 5.0.5054.0  

    Server URL: http://192.168.71.100:80/NR/System/Marshalling/  

    Server Version: 5.0.5054.0  

    ——————————————————————————–

    Error Information

    An error has occurred that has interfered with the generation of this report. Please try the operation again, and contact your system administrator should this error persist.

    Error ID: -2147352567  

    Source File:  

    Source Method: .Net SqlClient Data Provider  

    Source Line: 0  

    Description: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 – The specified network name is no longer available.)  

    Recommended Action:  

    ——————————-

    Can you help in resolving this error?

    Thanks

    Mani

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

    1) You can check event log SQL Server to see whether transaction log full or server ran out of disk place.

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

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

  88. gsmani says:

    Thanks Ming for your suggestion.  Do you think the firewall plays a part in this?  Is it invalidating the connection?

    Thanks

    Mani

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

  90. I am trying to connect to a internet based webserver [sql14.aquesthosting.com] using SqlServer 2005 on a Windows XP 64 bit version.

    I have activated remote connections, configured TCP protocol, opened the windows firewall {even turned it off for some tests].  I have spent the better part of a day reading Google and the archives of this [most helpful] blog.

    I originally had the 64 bit version of Sql Server 2005 on this machine, but got the impression that Report Server does not work properly on this version.  I uninstalled SS 2k5 64 bit version, and installed the 32 bit version of SS2k5

    I have a 32 bit tablet computer connected to the same DSL line, with the {nearly as I can tell) same configuration and it connects perfectly — and quickly.

    Are there any special considerations for connecting with a 64 bit operating system?

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

  92. Sundar says:

    We are getting the below error in a production environment. What would be the main cause of this issue? The message “Not enough storage is available to complete this operation” does mean the disk space on the server?

    Exception Message: 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.) (type SqlException)

    SQL Exception Class: 20

    SQL Exception Number: 14

    SQL Exception Procedure:

    SQL Exception Line Number: 0

    SQL Exception Server:

    SQL Exception State: 0

    SQL Error(s):

    Thanks

    -Sundar

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

  94. Marnee says:

    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.

  95. Marnee says:

    One more thing:  I cannot connect to the 2000 instance using Enterprise Manager.

  96. Marnee says:

    No, actually I can register the 2000 instance in Enterprise Manager if I specify the ip address, not the name.

  97. Jeff says:

    I think your problem is the same one I’m having. Using SQL 2005 Mgmt Studio & trying to connect to a 2k DB using the .Net SqlClient Data Provider. Ent. Manager uses a different data provider & I was hunting for a way to set which data provider to use when connecting to various SQL servers. If the host allows the .net data provider you could probably connect to your db, just fyi

  98. Phil says:

    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)

  99. Jeff says:

    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?

  100. Jeff,

    It would be helpful to know if the Windows Event Log has any error events for this problem and what the error message is.

    Were the SQL services configured to run under some user account or a service account (like "Local System", "Local Service", or "Network Service")?  If they were configured to run under a user account, did that user account password expire?

    If you would like to view/modify the account in which these services run under, I recommend using the "Configuration Manager" tool and look under the properties for each of these services.

    HTH,

    Jimmy

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

  102. Johan says:

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

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

  104. Johan says:

    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?

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

  106. Regan says:

    I seem to have come across a NEW error message not described in the 8 listed in the article.

    Using SQLServer 2005, Enterprise Edition, SP 1 (9.0.2047).

    I have a remote connection to a box (our DialBox), where we can remotely access our servers for that domain. Up until 2 days ago, I had no issues connection from DialBox, using SSMS, to a server. Since yesterday, I’ve been seeing this error message when I try and connection to the Registered Server:

    ————————————–

    TITLE: Microsoft SQL Server Management Studio

    ——————————

    A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 – The Local Security Authority cannot be contacted) (Microsoft SQL Server, Error: -2146893052)

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

    ————————————–

    I can connect to other servers, in that domain, from the DialBox.

    If I remote through to the actual DB server from the DialBox, I can connect successfully.

    Both connections are made using the same domain user (domainnameuser).

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

  108. Regan says:

    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?

  109. Regan says:

    … 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 …

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

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

  112. Named Pipes Provider, error 40 Could not open a connection to SQL says:

    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

  113. This post provides some tips to troubleshoot Sql Server connection problems based on various displayed

  114. Sankar says:

    I am running a query from Server A (Sql Server 2005) which fetches 15 Million rows from a remote server B (SQL server 2000) This fails runs sometimes and fails some times

    the error stmt as below

    The OLE DB provider "SQLNCLI" for linked server  reported an error. Execution terminated by the provider because a resource limit was reached.

    Msg 7421, Level 16, State 2, Line 1

    Any Suggestions what will be the potential cause

    thanks

    sankar

  115. Jim Colley says:

    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

  116. Matt says:

    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.

  117. Jim Colley says:

    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?

  118. Matt Neerincx (MSFT) says:

    The limit is related to resources on the machine, there is not a hard limit to all handles.  So if you have more RAM for example or are running the machine in /3GB mode it could affect the resource limits.  /3GB mode means OS has 1GB and rest of apps has 3GB (versus normal 2GB each).  In /3GB mode you can run out of system resources faster in most cases because you are taking away resources from kernel.

    You could use process explorer to look at what specific handle types are leaking this might give you a clue as to the root cause (for example if you see 10000 file handles to c:fooapp.dat file you know foo application is leaking handles).

    You can get process explorer here:

    http://www.microsoft.com/technet/sysinternals/ProcessesAndThreads/ProcessExplorer.mspx

  119. Sheetal says:

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 – The specified network name is no longer available.)

  120. Sheetal says:

    Hi,

    I am getting the below error while my ASPX page trying to connect with SQL Server 2005. Web application is running under the IIS.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 – The specified network name is no longer available.)

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

  122. Shum says:

    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

  123. rob says:

    hey, im getting this 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)

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: 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)

    my connection string is

    <add name="siteDBConnectionString" connectionString="Server=EVOLUTION99SQLEXPRESS;Persist Security Info=False;Initial Catalog=siteDB;Data Source=siteDB" providerName="System.Data.SqlClient" />

    ive enabled the sql browser on the server and tc/ip is open, but i cant get rid of the error. maybe its a connection string problem?

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

  125. JeffT says:

    Hi all.

    While closely related, I am not using linked servers.  Instead I am trying to use an open rowset command:

    SELECT * FROM OPENROWSET(

     ‘SQLOLEDB’,

    ‘Server=server-alias;Trusted_Connection=YES;Database=MyDb;Integrated Security=SSPI’,

     ‘Select * From SomeTable’)

    This returns the following message:

    OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Communication link failure".

    Msg 10054, Level 16, State 1, Line 0

    TCP Provider: An existing connection was forcibly closed by the remote host.

    Msg 18452, Level 14, State 1, Line 0

    Login failed for user ‘(null)’. Reason: Not associated with a trusted SQL Server connection.

    OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Invalid connection string attribute".

    There are some caveats to this.

    1) I am going from 64 bit 2005 to 32 bit 2000

    2) We are using groups and delegation via AD.  There are no SQL logins and no direct access to the DB – only access via a group.

    3) This occurs when people try to run the statement above from thier desktop.

    4) I can fix the problem – for my login only – if I go to the server then run the query from there.  After that, when I go back to my desktop it will run fine for that server only.

    5) We are using TCP – unfortunately we can not switch from that.

    6) We are using server aliases.

    I think this is related to the ‘double hop’ problem, but I am unsure just how to fix it.

    Any help would be GREATLY appreciated!

    Thanks

  126. Josh Coady says:

    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

  127. Tanu says:

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

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

  129. John F - jbflowersATcomcast.net says:

    Hi Ming, this blog is great info, thanks for all your hard work!

    I am getting a very weird error and it is not two machines talking to each other, its a SSIS package trying to load data on the same box, the SSIS pkg uses Win auth and we are TCP – has anyone found a fix for this?  its very sporadic but seems to occur when we are trying to load larger amounts of data – any help is greatly appreciated – thanks – Win2K3 64 bit, SQL2005 dev 64 bit SP2,

    here is my error –

    TCP Provider: The specified network name is no longer available.  

    Executing the query "execute [usp_NPRInsertFromNPRStagePrev]" failed with the following error:

    "Communication link failure". Possible failure reasons: Problems with the query,

    "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  

  130. Kev says:

    There’s a lot of great information here so I thought I’d try my luck.

    I have a SQL Server 2005 db on machine ‘B’ which has a linked server to machine ‘C’.  I have credentials to access ‘C’ directly and I am able to successfully run queries to the linked server from B.  Under the security settings for the linked server I have it set up to impersonate my local login.

    Here’s my problem.  I have created an ASP application on ‘A’ that will connect to B and includes a query involving the linked server.

    My connection string is built as follows:

    Server=servername;Initial Catalog=database;Trusted_Connection = Yes";

    I get the following error message:

    Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’. OLE DB provider "SQLNCLI" for linked server "ldrollup" returned message "Invalid connection string attribute".

    Since I have mapped my login for the linked server to my login name, why is it using NT AUTHORITYANONYMOUS and how do I fix that?  It might be worth noting that I have had similar issues using SQL Agent which I have only temporarily resolved by hard coding my user name and password as the owner of SQL Agent.

    Any help would be greatly appreciated!

  131. Tushar Patel says:

    Go to the SQL Server Network Configuration and make sure the protocols are enabled.

  132. Kev says:

    The only one that is not enable is ‘VIA’. Should that be enabled? What exactly does that protocol do?

  133. Gianfranco says:

    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

  134. Aftab says:

    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/

  135. Aftab says:

    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

  136. Garick says:

    Thanks to this article, I was able to fix a service broker problem.

  137. Vedran says:

    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.

  138. nicks says:

    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

  139. Mary Jo says:

    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.

  140. seif says:

    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?

  141. Matt Neerincx (MSFT) says:

    Run netstat -aon on the machine to see what IP is bound to port 1433, you could have multiple IPs on the box.  Also, the SQL Server ERRORLOG file should list exactly what IPs the server is bound to.

    I recall from vague memories of debugging this code in the past that the server will just bind to all ips available on the machine.

  142. Shreyas says:

    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

  143. Tintin says:

    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:

    1. use the database on internet, in debug mode from Visual Studio 2008, it works fine.

    2. deploy the website, change the database to use an intranet database server, the website works fine.

    3. deploy the website, use the database on internet, it get the error message.

    How can i figure out the error and resolve it?

  144. shawn says:

    Changing the service "SQL Server (SQLEXPRESS)" from local service to network service fixed the problem for me.

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

  146. Mugunthan says:

    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

  147. Vivek says:

    [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

  148. Sanjay says:

    hello,

    i have this connection string in web.config file

    <add name="mycon" connectionString="Server=localost; Database=dbtest.mdf;uid=&quot;sanjay&quot;; pwd=&quot;rpg007in&quot;"

    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

  149. Somnath Dey says:

    Objective: My source database is internet database server which is located by a Ip address.  My

    objective is to insert some data from internet database to local machine. Internet database has Windows

    2003 standard Os, and sqlserver 2005 database server and local machine has Windows 2000 standard OS and

    sqlserver 2000 database server. So I write a store procedure in internet database as follows

    —————————————————–

    exec sp_configure ‘remote admin connections’,1

    reconfigure

    INSERT INTO [dbasomnath].Webpatentfiling.dbo.patentInventor1

                 (INV_NAM,INV_ADD1,INV_ADD2,INV_ADD3,NATIONALITY )

           select INVENTOR_NAME,INVENTOR_ADDRESS,

                  INVENTOR_STATE,INVENTOR_COUNTRY,INVENTOR_NATIONALITY

           from OPENDATASOURCE(

            ‘SQLOLEDB’,

            ‘Data Source=123.456.78.555;User ID=sa;Password=mfklj4Y’

            ).Efiling.dbo.TBL_APP_INVENTOR

            where APP_ID=’7′

    At the time of execution the following error msg come

    OLE DB provider "SQLNCLI" for linked server "dbasomnath" returned message "Login timeout expired".

    OLE DB provider "SQLNCLI" for linked server "dbasomnath" returned message

    "An error has occurred while establishing a connection to the server.

    When connecting to SQL Server 2005, this failure may be caused by the fact

    that under the default settings SQL Server does not allow remote connections.".

    Msg 65535, Level 16, State 1, Line 0

    SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

    So give me a solution

    – Somnath Dey

  150. alexwcy@hotmail.com says:

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

  151. cosmosvert says:

    Hello,guys,

    I create a package SSIS locally, and there’s a SQL task that executes a stocked procedure in ServerA, whoes domain name is AA, and the procedure selects items from a table in ServerB, whoes domain name is BB. The package SSIS failed to execute, and the error message goes like this:

    [Execute SQL Task] Error: Executing the query "exec Procedure_Test" failed with the following error: "Login failed for user ‘(null)’. Reason: Not associated with a trusted SQL Server connection.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Can anyone tell me how to solve it, please? By the way, I used integrated authentication for the oledb connection.

    Thank you in advance.

  152. feliks says:

    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

  153. The problem seems to be with the SSL certificate being used by the server.  My guess is:

    1) Your server is loading a self-signed certificate

    2) Your server is not enforcing encryption on inbound connections.

    3) Your remote client machine is not enforcing encryption on outbound connections.

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

  154. Ray says:

    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

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

  156. Prasad says:

    I am developing an application in VB 2008 which uses SQL Server 2005 database. The application is running perfectly well on the development server and I can execute my options and update tables like Product Master, etc. When I build the project and install it on another machine, I get the following error:

    "SQL Server does not allow reote connections. (provider SQL Network Interfaces. error 26 – Error locating server / instance specified)"

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

  158. Prasad says:

    I am a newbie. How do I update the connection string.

    I am well and truly stuck.

    Thanks.

  159. Thanks, it solved my "error 26"

  160. Mahesh K says:

    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.

  161. NB says:

    I have problem in connecting to remote SQL Express instance, when Windows firewall is enabled:

    1. It’s a Workgroup environment

    2. I can connect through remote desktop when firewall is enabled.

    3. I can connect when I disable the Windows firewall

    4. Port 1433 is added in exceptions

    5. SQlserver.exe and SQLbroswers are added in execeptions

    What am I missing?

    Thank you

  162. NB says:

    Correction:

    2. I can connect through remote desktop when firewall is DISabled.

    Therefore its only a forewall problem.

  163. Chris says:

    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)

  164. Gary Tsiang says:

    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.

  165. Matt Neerincx (MSFT) says:

    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.

  166. Gary Tsiang says:

    Thanks — I just checked the Windows Event Log and found that, right after SQL Server shuts down, MSInstaller kicks off and tries to install/update SQL Server automatically (I think). Here are the log file messages at this point:

    * Product: Microsoft SQL Server 2005 — Install started.

    * Product: Microsoft SQL Server 2005 — Error 29528. The setup has encountered an unexpected error while Setting Internal Properties. The error is: Fatal error during installation.

    * Product: Microsoft SQL Server 2005 – Update ‘GDR 3068 for SQL Server Database Services 2005 ENU (KB948109)’ could not be installed. Error code 1603. Additional information is available in the log file C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB948109_sqlrun_sql.msp.log.

    * Product: Microsoft SQL Server 2005 — Configuration failed.

    * Product: Microsoft SQL Server 2005 Analysis Services — Install started.

    * Product: Microsoft SQL Server 2005 Analysis Services — Error 29528. The setup has encountered an unexpected error while Setting Internal Properties. The error is: Fatal error during installation.

    * Product: Microsoft SQL Server 2005 Analysis Services – Update ‘GDR 3068 for SQL Server Analysis Services 2005 ENU (KB948109)’ could not be installed. Error code 1603. Additional information is available in the log file C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixOLAP9_Hotfix_KB948109_sqlrun_as.msp.log.

    * Product: Microsoft SQL Server 2005 Analysis Services — Configuration failed.

    * .NET Runtime Optimization Service (clr_optimization_v2.0.50727_32) – Completed all work. Shutting down.

    Does this mean an auto-update flag for SQL Server is set somewhere? If so, why would it be failing every night? Again, I don’t remember setting anything to do this but I guess I could have.

  167. Gary Tsiang says:

    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!

  168. MP says:

    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.

  169. bolivar1985 says:

    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

  170. MP,

    Do you see any issue with this behavior? It looks like a client made a NP connection to the server after the Pipe is created, but before it’s ready to accept connection. When we try to setup things to accept connection, we could re-active the existing client connection and thing would be OK. However, looks like in your case, client closed the connection before everything is ready and we logged this error. The server is robust to create another pipe to accept new connections after this. You can verify if this is the case by using Netmon to see how the NP connection is opened/closed.

    Thanks,

    Xinwei

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

  172. bolivar1985 says:

    Xinwei,

    Thanks, i will try your suggestion…

    Thanks,

    Bolivar1985

  173. Bolivar1985 says:

    Xinwei,

    It works, thank you, i just reinstalled MSSQL2005 Standard edition, then chose the mixed mode, and it run, using mssql_connect(),

    Thanks,

    Bolivar1985

  174. Tasleem says:

    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?

  175. Enzo says:

    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.

  176. Karthik Vijayakumar says:

    Out of nowhere, we got this error and are trying to figure out what the issue is.

    OLE DB provider "SQLNCLI" for linked server "<server name>" returned message "Communication link failure".

    Msg 64, Level 16, State 1, Line 0

    TCP Provider: The specified network name is no longer available.

    Any help would be greatly appreciated.

    Thanks,

    KV

  177. Anand says:

    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

  178. Paul says:

    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?

  179. Barbara Johnson says:

    Help!!!  We are getting the following message on a SQL Server 2005 box.  I could find anyone else who has had this problem.

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

  180. Barbara Johnson says:

    Our problem has been resolved.  Someone had made some SSL registry changes which affected SQL Server and other applications.

  181. Andrew Fiebert says:

    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!!

  182. shahrbano says:

    MMC cannot open the file c:program filesmicrosoft sql server 80toolsebinnsql srver enterprise manager.msc

  183. Monichan says:

    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

  184. Rui Silva - Portugal says:

    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"/>

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

    ——————————

  186. Helen says:

    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

  187. Reader_ManZZ2@yahoo.co.uk says:

    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.

  188. agnainx@msn.com says:

    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.

  189. Benjamin says:

    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.

    ——————————

    ADDITIONAL INFORMATION:

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

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

    ——————————

    BUTTONS:

    OK

    ——————————

  190. RANJITH says:

    hi ming.. plz help me i am getting this error.. i am using sql server management express studio.. i was occupied to clear this error for 2 days.. plz help me..

    Cannot open database "cms" requested by the login. The login failed.

    Login failed for user ‘SYSTEM2IUSR_SYSTEM2’.

      thanks

             ranjith

  191. BAO says:

    TITLE: Connect to Server

    ——————————

    Cannot connect to BAOSQLEXPRESS.

    ——————————

    ADDITIONAL INFORMATION:

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 28 – Server doesn’t support requested protocol) (Microsoft SQL Server, Error: -1)

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

    ——————————

    BUTTONS:

    OK

    ——————————

  192. SeismoDude says:

    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?

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

  194. Todd says:

    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?

  195. Abnier says:

    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

  196. DAScanni says:

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

  197. Mahes says:

    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.

  198. Guru says:

    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

  199. xr280xr says:

    I don’t know if anyone’s answering questions on here or if everyone’s just asking but I’ve apparently got a new one:

    System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.)

    Here’s the important portion of the Stack Trace:

      at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

      at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

      at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

      at System.Data.SqlClient.TdsParserStateObject.WriteSni()

      at System.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode)

      at System.Data.SqlClient.TdsParserStateObject.ExecuteFlush()

      at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc)

      at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

      at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

      at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

      at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

      at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

      at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

    This error seems to appear and disappear for a .NET windows service making frequent connections to a SQL Express 2005 database. Anyone know why?

  200. Aarthi says:

    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?

  201. Aarthi says:

    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?

    1. restarted the SQL browser

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

  202. Albert says:

    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?

  203. Gigi says:

    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!!!

  204. Abe says:

    I just encontered this problem and the way I resolved it was just restarting the PC after creating the new user.

  205. bonny says:

    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?

  206. sathyanarayanan.av says:

    thanks. This worked for me.

    cheers

    Sathya

  207. resuram says:

    I have Server X (SQL2008 R2 RTM) and Server Y (SQL2008 R2 RTM) connected via a VPN.

    Server X has a linked server set up to Server Y and pulls data accross from Server Y on a regular basis. This works fine but sometime the following error is recieved on Server X:

    select top 1 * from ServerY.[DBName].dbo.Test

    OLE DB provider "SQLNCLI10" for linked server returned message "Communication link failure".

    Msg 64, Level 16, State 1, Line 0 TCP Provider: The specified network name is no longer available.

    We have created Linked server we don't find any issue in it we can browse the linked server node and can see all the objects in the database. Also i have create DSN and check it was working fine and no connection issue. Able to ping also network guys says there was no packet drops as well.But still when we query using linked server it gives error.

    Everytime i need to reboot the server and problem gets resolved Simply stopping and starting SQL does not fix it.

    Please help urgently

  208. I'm back says:

    Just wanted you to get new version!  

Skip to main content