Troubleshoot Connectivity Issue in SQL Server 2005 – Part II


Please visit http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx for troubleshooting connection problems when SNAC(SQL Native Client) connect to SQL Server 2005.

Part II – Connection Fail when MDAC connects to SQL Server 2005

 

Use osql.exe to simulate the connection string in your application and quick troubleshoot if your application uses ODBC provider and use Query Analyzer (isqlw.exe) in SQL Server 2000 for OLEDB provider. Usually, they are located under %SYSTEMDRIVE%Program FilesMicrosoft SQL Server80toolsbinn.

 

How to distinguish your application is using MDAC client or SNAC client:

1) From connection string, especially follow blue part:

 

– MDAC ODBC –

DRIVER= {SQL Server}; SERVER=xx; Trusted_connection=yes; Connect Timeout=30

 

– MDAC OLEDB –

Provider= SQLOLEDB; Data Source=xx; Integrated Security=SSPI;Connect Timeout=30

 

– SNAC ODBC –

DRIVER= {SQL Native Client}; SERVER=xx; Trusted_connection=yes; Connect Timeout=30

 

– SNAC OLEDB –

Provider=SQLNCLI; Data Source=xx; Integrated Security=SSPI; Connect Timeout=30

 

2) From error message:

Osql /Sxxx /E ß “xxx” is an unknown server

 

Result of using MDAC client:

 

[DBNETLIB]SQL Server does not exist or access denied.

[DBNETLIB]ConnectionOpen (Connect()).

Or

[DBNETLIB]Specified SQL server not found.

 

Result of using SNAC client:

 

Named Pipes Provider: Could not open a connection to SQL Server [53]

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.

 

Basic connection string:          osql(sqlcmd) /S[prefix]<servername> /E

                                                osql(sqlcmd) /S[prefix]<servername><Instance> /E

 

Before start troubleshooting, another important thing is to identify MDAC version on your client box.

 

Step 1: Browse to the following Microsoft Web Site to know about MDAC release version.

http://support.microsoft.com/default.aspx?scid=kb;en-us;231943

Step 2: Browse to the following Microsoft Web Site to download the tool that help you check the MDAC version on your client box.

http://support.microsoft.com/default.aspx?scid=kb;en-us;301202

 

However, MDAC does not provide so many visible error messages like SNAC, which makes it more difficult to troubleshoot connection problems when using MDAC connect against Yukon. Most likely, any of the follow three errors you would see in any case. Therefore, I will give tips for troubleshooting not based on the messages, but, on various Protocols Setting in SQL Server 2005.

 

Message 1:

[DBNETLIB]SQL Server does not exist or access denied. 

 

Message 2:

[DBNETLIB]Specified SQL server not found.

 

Message 3:

[DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentation.

 

Before further troubleshooting, you need to know what is the current protocol setting on the server box, and make sure server is running and sqlbrowser service is running, two ways to check.

 

1) Go to SSCM( SQL Server Configuration Manager ), click “protocols for  <Instance>”, you can see the status of each protocol that server support.

2) Look at server ERRORLOG file.

 

– Key words indicates server shared memory is enabled –

“Server local connection provider is ready to accept connection on [\.pipeSQLLocalMSSQLSERVER]” or [ \.pipeSQLLocal<InstanceName>]

 

– Key words indicates server named pipe is enabled –

Server local connection provider is ready to accept connection on [ \.pipeMSSQL$<InstanceName>sqlquery ] or [ \.pipesqlquery ]

Note: you may also see these key words even when only shared memory is enabled. To make sure server remote named pipe is enabled, you need to do one more thing, test connection through named pipe. “osql /S\<machinename>pipesqlquery /E” or

“osql /S\<machinename>pipeMSSQL$<InstanceName>sqlquery /E” only when the connection succeeds that means server is listening on name pipe.

 

– Key words indicates server TCP/IP is enabled –

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

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

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

 

 

Thereby, the following troubleshooting tips would be based on which protocol was enabled. When you came across connection failure, please first identify what is the current protocol configuration on your server box.

 

Note: only when I explicitly point out named instance, otherwise, all cases apply to both default instance and named instance. And, normally, there are three parts in “Server” or “DataSource” field in the connection string. [<Prefix>]<Server>[<InstanceName>], in the server part, it could be any of those:

  1. “.”
  2. “(local)”
  3. “localhost”
  4. <machinename>
  5. “127.0.0.1”
  6. FQDN(Server Fully Qualified Domain Name)
  7. <IpAddress>

 

Assumption: Follow situation are mainly cover MDAC 2.8/2.81/2.82 client connects against Yukon RTM on Windows 2000/Windows 2003/Windwos XP.

 

Case 1 – Only shared memory was enabled:

 

Go through follow reason list:

 

1) You might specify “tcp:” in your connection string, however, TCP was disabled, to resolve this, remove the prefix.

 

2) You might specify ”np:” in your connection string, plus, specify localhost or FQDN or “127.0.0.1” or <ServerIP> in the server part in Server or DataSource field in your connection string, to resolve this, either enable name pipe or specify <machinename> as Server part.

 

3) You might specify localhost or FQDN or “127.0.0.1” or <ServerIP>, to resolve this specify <machinename> as Server part.

 

4) You might specify “lpc:” in your connection string, plus, specify localhost or FQDN or “127.0.0.1” or <ServerIP> in the server part in Server or DataSource field in your connection string, to resolve this, specify <machinename>  instead as Server part.

 

5) You might first, use OLEDB provider; secondly, connect to local default instance through “.” or “(local)”, to resolve this, replace the server part as <machinename>.

 

Case 2 – Shared Memory and TCP/IP was enabled, but Named Pipe was disabled.

 

Go through follow reason list:

 

1) You might specify ”np:” in your connection string, plus, specify  localhost or FQDN or “127.0.0.1” or <ServerIP> in the server part in Server or DataSource field in your connection string, to resolve this, either enable name pipe or specify <machinename> as Server part.

2) You might specify “lpc:” in your connection string, plus, specify in the server part in Server or DataSource field in your connection string. To resolve this, replace to <machinename>.

 

Case 3 – Shared Memory and Named Pipe was enabled, but TCP/IP was disabled.

 

Go through follow reason list:

 

1) You might specify “tcp:” in your connection string, however, TCP was disabled, to resolve this, remove the prefix.

2) You might first, use OLEDB provider; secondly, connect to local default instance through “.” or “(local)”, to resolve this, replace the server part as <machinename>.

 

Case 4 – Only Named Pipe was enabled.

 

Go through follow reason list:

 

1) You might specify “tcp:” in your connection string, however, TCP was disabled, to resolve this, remove the prefix.

2) You might first, use OLEDB provider; secondly, connect to local default instance through “.” or “(local)”, to resolve this, replace the server part as <machinename>.

 

Case 5 – Only TCP was enabled.

 

Go through follow reason list:

1) You might specify “np:” prefix in your connection string, Named Pipe was disabled. To resolve this, remove the prefix.

 

Case 6 – Only Shared Memory was disabled.

Case 7 – All protocols are enabled.

 

All connection in Case 6 and Case 7 should succeeds unless some special cases.

 

Special Cases:

 

Case 1: localhost

 

Windows 2000 and Windows XP do not recognize “localhost” as a representative of local machine in pipe name. In another word, you can not connect  to default instance through explicit pipe \localhostpipesqlquery( eg, you connection string like “Data Source = \localhostpipesqlquery; Integrated Security = SSPI” or “osql /S\localhostpipesqlquery /E”) on Win2k and WinXP when remote name pipe was enabled.

 

Case 2: Connect to local named instance

 

If you can not identify cause for connection fail when use MDAC connecting to local  named instance, there are two other possible reasons:

1) Your application was running under an account that has no permission to the registry entry where MDAC client reads (HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server<InstanceName>MSSQLServer).

2) SQL Browser service was not enabled or running.

 

Case 3: Remote Connection

 

Whenever remote connection fail and you saw any of above error messages, you should first check whether remote named pipe or tcp was enabled or sqlbrowser service was enabled and running. Please see “SQL Server 2005 Connectivity Issue TroubleShoot –Part I” for more detail info.

 

Case 4: Blank

 

If you use ODBC driver and leave the server part as blank when connecting to local default instance, connection would fail. Please explicitly specify server name whenever make connection. The error message when connection fail usually is “[Microsoft][ODBC SQL Server Driver]Neither DSN nor SERVER keyword supplied ”.

 

Case 5: “.” or “(local)”

 

To emphasize this, if you use MDAC OLEDB, you can not connect to local default instance through “.” or “(local)” whenever TCP was disabled on the Server. To resolve this, please use <machinename> instead.

 

Summary:

 

When you discover any connection fail through MDAC client to SQL Server 2005, please first make sure server is accessible and SQL instance is running; secondly, identify which protocol is enabled; finally, go through above scenarios to track the root cause.

 

Ming Lu SQL Server Protocols

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

 


Comments (104)

  1. Eric says:

    I am using MDAC OLEDB connection where shared memory is the only enabled protocol.

    I am just wondering why "(local)" and "localhost" do not work but <machinename> does? Is there any more information on this? This seems very counter intuitive to me. Is this a problem in Windows 2000 and XP?

  2. Hi, Eric

    MDAC OLEDB uses TCP protocol when you connect through (local) and localhost and in your case, your TCP/IP was disabled, you can verify this by enabling TCP/IP. However, if you specify <machinename>, it connects through shared memory.

    Appreciate your comments.

  3. Allen Modine says:

    I am trying to connect to a remote SQL Server 2005 Standard edition server with TCP from a Virtual Machine running XP/VS2005. When I try to add a connection in VS, I get the server name in the database server dropdown list but I get the following error when testing the connection:

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

    On the Sql Server server:

    I have the SQLBrowser service running and it is active.

    I have the remote connection enabled using TCP/IP only

    Connection string I am using from VS2005:

    Data Source=W-MODINEDEV;Integrated Security=True;Network Library=dbmssocn

    I am at a loss, any help would be appreciated. When I am on the server, making connections works fine, it is just remotely that I have issue. –Allen

  4. Hi, Allen:

    In my another post: http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx

    I point out the exact case you faced.

    Please search Message 11 in the post and follow the resolution.

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

    1)Enable SqlBrowser, see the info in Message 4. Plus, add sqlbrowser.exe into Firewall exception list: HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesSharedAccessParametersFirewallPolicyDomainProfileAuthorizedApplicationsList

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

    If you have further question, please let me know.

    Thanks for your comments.

    Ming.

  5. Allen Modine says:

    Ming:

    Thanks for the reply. I would not think this is the problem because I should not be going through the firewall to make a connection to the SQL Server machine, we are both on the same domain and both behind the firewall. Why would I need to do anything with the firewall? Thanks, Allen

  6. Hi, Allen

    Please follow the below step to identify your problem:

    1) Make sure SQL Server is listening on the right TCP port. You can check errorlog, and search key words:Server is listening on [‘any’ <ipv4> <Port Number>] or

    [ <ipaddress> <ipv4> <Port Number>]. Then "netstat -ano |findstr <Port>" to see which process is listening on this port.

    2)From your client machine, "telnet <ServerIP> <PortNum>" see whether succeeds.

    3)Please check whether the windows firewall is on your server. Eg, if your server is XP, then Go to Control Panel -> select "Network and Internet Connection" -> "Windows Firewall", please turn off it if it is on and then try your connection again, if works that means your original connection failure is because the windows firewall on your server blocks any out packet if the application was not added into the exceptionlist since you were making remote connection. I believe the *firewall* that you mentioned is not the same as the windows firewall.

    Thanks!

    Ming.

  7. Allen Modine says:

    The Ming Dynasty Rules, you the man, thanks much, and yes, I was thinking "real firewall" versus "windows fiewall" and thought you were crazy, but off course it was me that was foolish.

  8. Vinay says:

    Hi,

    Where I can find part III of this series?

  9. Shawn says:

    Out of all my forum/net searching on this topic, yours by far is the most indepth and well-presented. Good job!

    After going through your steps, my problem hasn’t gone away. On my development machine, I’m using SQL Server 2005 and the sqloledb provider via C++. This is my connection string:

    string ConnectionString2 = "Provider=’sqloledb’;Data Source=’localhost’;Initial Catalog=’master’;Integrated Security=’SSPI’;";

    and it works just fine on that machine.

    I’ve installed SQL Server 2005 Express on two separate machines and it fails in the connnection->Open() call with the standard error: "[DBNETLIB][ConnectionOpen(Connect()).]SQL Swerver does not exist or access denied."

    I can connect to the sqlcmd just fine. TCP, Shared, NP are all enabled. The browser is started. The OLEDB provider is correctly pointing to MS’s file. The port (1314) is reporting in the App event log and Netstat as listening.

    I tried changing <localhost> to machine name, but it didn’t help. I did notice in the TCP/IP properties of the Server config mgr that even though TCPIP was enabled, looking at the specific IP address showed that they were active, but disabled. After enabling port there it still didn’t help.

    Any clues?

  10. Shawn says:

    On a hunch, I changed my provider from MDAC to Native Client with the string:

    "Provider=’SQLNCLI’;Data Source=’localhost’;Initial Catalog=’master’;Integrated Security=’SSPI’;"

    This gave me a more helpful error:

    "[SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2]."

    I didn’t even know I was using Named Pipes, I thought it would’ve been defaulting to TCP/IP. Going back to your other article as reference and stepping through the debugging process for named pipes, I couldn’t connect with \.pipesqlquery, but I could connect with \.pipeMSSQL$SQLEXPRESSsqlquery.

    That seems to have done it, my app works both on the development and test machines.

    Thanks for the tips!

  11. Hi, Shawn

    Glad to hear you solve the problem. Basically, SQLEXPRESS is installed as a named instance and with TCP and Named Pipe are disabled by default.

    For local connection: You need to change your connection string to:

    Provider=’SQLNCLI’;Data Source=localhostSQLEXPRESS;Initial Catalog=’master’;Integrated Security=’SSPI’

    For remote connection, remember enable tcp or named pipe.

    Here is useful info for SqlExpress

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

    Thanks for your comments!

    Ming.

  12. rudy 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.

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

  14. GeorgeC says:

    Ming,

    I’m getting the error message: "SQL Server does not exist or access denied."  I’ve followed the instructions here as closely as possible.  I’m trying to access from a client with MDAC 2.8 SP1 on Windows XP SP2.  I checked with netstat -ano on the server, and it’s listening on the right IP address and right port.  I can connect to an SQL 2000 server just fine.

    One possibility is that I installed Windows Server 2003 and SQL 2005 on a new machine and then changed the name of the server.

    Any advice would be greatly appreciated.

    Thanks,

    George C

  15. Hi, George

      Were you trying to connect remotely to SQL Server 2005? Is it default or named instance? Is it SQL Express 2005? How do you make connection?

      The MDAC error is very general, if you can provide the above info, we can help you identify the problem quickly.

    Thanks for your comments!

    Ming.

  16. Dan says:

    Hi,

    I’m having a problem with a .NET 2 command-line app which accesses a SQL 2000 cluster.

    If I run it on a Windows 2003 Server SP1 box, it runs fine.

    If I run it on a Windows 2000 Server SP4 box, it reports that it can’t access the SQL database despite the fact that I can create a DSN on that same box pointing to the db which runs fine and run the .NET 1.1 version of the same app from that box without a glitch. I’ve run cliconfg.exe and enabled TCP and named pipes but with no avail.

    The connection string looks like this. Server=xx;Integrated Security=SSPI;Initial Catalog=name

    Any ideas? Does Win2k work differently for .NEt 2.0 than .NET 1.1 somehow?

    Cheers, Dan

  17. Nan Tu says:

    Is the SQL Server named instance or default instance? If it is named instance, you need to enable sql browser service eventthough the client is on the same box of the server. Otherwise, please post the entire exception stack trace of your .Net2.0 app of connection failure.

  18. Dan says:

    It’s the name referring to the virtual IP address for a SQL cluster. But its just the default instance on that cluster: CLUSTERDB rather than CLUSTERDBINSTANCE. That said, do you mean turn on the sqlbrowser on the client or the cluster? Apologies for being dumb, but how do i do either?  The Win2k machine has no client tools installed – is part of cliconfg.exe? – and how do ou switch sqlbrowser on for a cluster? – do ou need to switch it on on both machines?

    The full error messages I get are as follows – the first is if I try and use named pipes, the second if i use tcp.

    —–

    Named pipes error message

    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)

    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException

    exception, Boolean breakConnection)

    at

    System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject

    stateObj)

    at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner,

    Boolean& failoverDemandDone, String host, String failoverPartner, String

    protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean

    encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection

    owningObject, Boolean aliasLookup)

    at

    System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection

    owningObject, SqlConnectionString connectionOptions, String newPassword,

    Boolean redirectedUserInstance)

    at

    System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity

    identity, SqlConnectionString connectionOptions, Object providerInfo, String

    newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

    at

    System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions

    options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection

    owningConnection)

    at

    System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection

    owningConnection, DbConnectionPool pool, DbConnectionOptions options)

    at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection

    owningObject)

    at

    System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection

    owningObject)

    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.FillInternal(DataSet dataset,

    DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String

    srcTable, IDbCommand command, CommandBehavior behavior)

    at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32

    startRecord, Int32 maxRecords, String srcTable, IDbCommand command,

    CommandBehavior behavior)

    at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

    —–

    TCP error message

    System.Data.SqlClient.SqlException: Timeout expired. The timeout period

    elapsed prior to completion of the operation or the server is not

    responding.

    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException

    exception, Boolean breakConnection)

    at

    System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject

    stateObj)

    at

    System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject

    stateObj, UInt32 error)

    at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult

    asyncResult, TdsParserStateObject stateObj)

    at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32

    bytesExpected)

    at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean

    encrypt, Boolean trustServerCert, Boolean& marsCapable)

    at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner,

    Boolean& failoverDemandDone, String host, String failoverPartner, String

    protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean

    encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection

    owningObject, Boolean aliasLookup)

    at

    System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection

    owningObject, SqlConnectionString connectionOptions, String newPassword,

    Boolean redirectedUserInstance)

    at

    System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity

    identity, SqlConnectionString connectionOptions, Object providerInfo, String

    newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

    at

    System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions

    options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection

    owningConnection)

    at

    System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection

    owningConnection, DbConnectionPool pool, DbConnectionOptions options)

    at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection

    owningObject)

    at

    System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection

    owningObject)

    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.FillInternal(DataSet dataset,

    DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String

    srcTable, IDbCommand command, CommandBehavior behavior)

    at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32

    startRecord, Int32 maxRecords, String srcTable, IDbCommand command,

    CommandBehavior behavior)

    at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

  19. Dave W. says:

    I have a client with 64 bit 2003 server running SQL 2005.

    Also has 32 bit XP sp2 client machines that connect fine to the server with OLEDB.  When attempting to connect with DMO from the machines with the same credentials and IP, I get

    error number: 55555

    error source: Microsoft SQL-DMO (ODBC SQLState: 42000)

    error description: [Microsoft][ODBC SQL Server Driver][SQL Server]To connect to this server you must use SQL Server Management Studio or SQL Server Management Objects (SMO).

    If I try the same code locally on the server it connects fine.

    The client has installed the DMO backwards compatibility patch on both client and server.

    I am at wit’s end.  All protocals on the server are enabled.  SQL Browser is enabled on the server.  Windows firewall is not enabled on the server.

  20. Hi, Dave

       This is not connectivity issue, please post your question to SQL DMO/SMO Forum:

    http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=88&SiteID=1

    Thanks!

    Ming.

  21. Bobikus says:

    Ouyaaaaah!

    I solved it! I spent two day by reading many many forums and blogs. Finally I found this stupid error…it must be two backslashes between MachineName and InstanceName in connection string in my application – "<MachineName>\<InstanceName>".

    Everything was enabled, every service ran well but symptoms were identical

    So if someone has the same little problem…..

  22. bow says:

    i just test for adding an exception in firewall. Only add sqlbrowser.exe in exception. it’s fine. No need to add port 1433 anymore. thank you.

  23. try to register your sql server on the active directory.

    go to the sql server instance name and then right click go to properties and an active directory tab is there click on add and then ok.

    try to connect now.

    I hope it connects

  24. Hi,

     Can you provide your connection string? The error you saw could caused by various reason:

    1) Is your Yukon default instance?

    2) What protocol enabled in your Yukon instance?

    3) How you make connection? "localhost/127.0.0.1/<FQDN>/<IP>" which one you specified as server?

    4) Could you run cliconfg.exe and see what protocol order you specify?

    5) Which OS were you running your client APP? WIN2K3?

    Thanks!

    Ming.

  25. su says:

    Hi,

    I can’t create a DSN using SQL Server to connect to SQL Server 2005, but I can connect successfully using SQL Native Client. My Shared Memory, Named Pipes, & TCP/IP are enabled. My firewall is disabled.

    I upgraded SQL Server 2K to 2005 so I have some users who have an DSN using FileMaker SQL Server Driver. I am trying to re-establish that connection but I get:

    [ODBC SQL Server Driver] Client unable to establish connection.

    [ODBC SQL Server Driver] Unable to load communication module. Driver has not been correct.

    I was thinking if I can establish a connection using SQL Server drivers then I can figure out my FileMaker SQL Server Driver issue. The error I’m getting using SQL Server is:

    [TCP/IP Sockets]ConnectionOpen(Invalid Instance())

    [TCP/IP Sockets]Invalid connection.

    Please help.

    Thanks

    SU

  26. Hi, Su

       From the error message, it looks like that your connection string is not correct, could you provide it to us? Double check following things:

    1) Is your SQL 2k5 a default instance or named instance? if named instance, you need add <instancename> in your connection string, like <servername><instancename>

    2) What is the SKU of your SQL 2k5? Express? Were you making local or remote connection? What OS are you using?

    Thanks!

    Ming.

  27. Su says:

    Hi Ming,

    I figured out the SQL Server DSN issue. My "Dynamically determined port" was checked with port number 1433 on "Client Configuration", but my TCP Dynamic Ports on SQL 2K5 was 1157.

    Now I still have that Filemaker SQL Server Driver issue. I tried connecting using a named instance "DPMMSSQLMSSQL" and I’m getting [Named Pipes]Specifed SQL server not found. When I use the static IP, I get [ODBC SQL Server Driver] Unable to load communication module. Driver has not been correct.

    Could it be that Filemaker SQL server Driver does not support SQL 2K5?

    My OS is 64 bit Win2K3 server. All clients are using XP Pro. SQL server 2k5 is 64 bit version 9.00.2047.00.

    Thank you so much Ming!

    Su

  28. Hi, su

       Glad to hear you address the issue.

       I am not sure whether your SQL2k5 is a named instance? And you connect through" DPMMSSQLMSSQL", is the "DPMMSSQL" the remote machine name? Is the "MSSQL" the instance name of SQL2K5? Can you provide more specifically about your connection string? Means how you make connection?

       Can you telnet to your remote server? eg: if your sql server was listening on tcp port 1157, can you run "telnet <remoteservernae> 1157" on your client machine?

       Can you try "osql /S<remoteserver><instancename> /E" from client machine? what was the error?

       What sql protocols enabled on your server?

       To identify your problem quickly, could you help to answer following question and provide it to us?

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

    Good Luck!

    Ming.

  29. Su says:

    Hi Ming,

    I’ve called the vendor of that ODBC drive and they don’t support 2K5.

    My new question is can I backup a DB from 2K5 and restore it on SQL server 2000?

    Thanks

    Su

  30. Hi, Su

       You can post your question to sql database engine forum, there are a lot of experts who can quickly help you:

    http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1

    Good Luck!

    Ming.

  31. Gabby says:

    hi,

    i am having connectivity issues with sql 2000. i have a cgi script that has been accessing this database for production operations and suddenly, it started to return this error

    Cannot connect: [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check your network documentation. (SQL-08001)

    [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (PreLoginHandshake()). (SQL-01000)(DBD: db_login/SQLConnect err=-1)

    i have tried everything but restarting the sql server as this server is constanly in use. is there a way to solve this problem? i hav tried using sql ping and i can greac the server, but sqlping also gives the followinh error

    Connection error: [DBNETLIB][ConnectionOpen (PreLoginHandshake())

    timely response will be grately appreciated

  32. Hi,Gabby

       

      The error you saw is quit common and it can be mapped to many cases. In most cases, the error happens because the server closes the connection for some reason when client is expecting data from the server.

    1) Can you described more about you app? Does the error happen during a long running query? Is the error consistent or intermittent?

    2) Which OS your sql server installed? WIN2K3 SP1? Here is a blog that bound to OS specific issue:

    http://blogs.msdn.com/sql_protocols/archive/2006/04/12/574608.aspx

    Also, could you check your Server ERRORLOG, what protocls enabled on your server? and whether there is any error info that may give clue? If you were running long query, please open server trace file and watch which operation caused server close connection.

    Good Luck!

    Ming.

  33. Gabby says:

    thanks a lot Ming,

    i actually found out that it was one of the network admins that turned on firewall filtering on the Gateway router. this was actually filtering out MSSQL connections

    Thanks a lot for your response

  34. Greg says:

    Hi Ming,

    I have SQL Server 2005 Express installed on a windows server 2003 standard endition. I have configured the firewall according to specs above where I added program exceptions for sqlservr.exe and sqlbrowser.exe. I have enabled remote and local connections using tcp/ip & Named Pipes through the Sql Server Surface Area Connection utility.

    Here is the strange part: I am able to connect to the Sql Server remotely via Sql Server Management Studio Express, but I am unable to connect to the Sql Server by registering it in Sql Server Enterprise Edition…Is it possible to register the Instance through Enterprise Manager?

    Thanks,

    Greg

  35. pkj says:

    hi,

    I am trying to deploy an analysis server cube on a remote server on my LAN. The remote server has firewall off. From the Business development studio I can connect to the remote machine and create data source and data source view. However, when I try to deploy the cube I get the folowing error:

    A connection cannot be made to redirector. Ensure that ‘SQL Browser’ service is running.  

    I have ensures that the SQL Browser is running. What could be the problem and its solution?

    thanks in advance,

    PKJ

  36. PKJ,

    Please take a look at the Windows event log on the machine where you have Analysis Server and SQL Browser running to see if there is any error and warning events generated by SQL Browser.  If there are, please post a sample of them to help us troubleshoot what the problem may be.

    BTW, I would recommend that you continue to have the Windows Firewall on, but put the SQL Server process (sqlservr.exe) and SQL Browser process (sqlbrowser.exe) in the exception list.

    Jimmy

  37. Hello, very interesting site, nice work.

    <a href=" http://phenterminecheap.gjjgt.info/cheap-online-phentermine.html ">cheap online phentermine</a>
    <a href=" http://xanaxbuy.bht0a.info/buy-xanax-cheap.html ">buy xanax cheap</a>

    Thanks!

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

  39. fhfjop says:

    [url=http://cruise-123.cafe150.com]cruise[/url]

    <a target=_top href="http://cruise-123.cafe150.com">cruise</a>

    <a target=_top href="http://cruise-123.cafe150.com">cruise</a>

    [url=http://cruise-123.cafe150.com]cruise[/url]

  40. Sandy says:

    Hi,

    I am trying to deploy a DTSX package to 32-bit SQL Server 2005 in a 64-bit machine from my installer.

    I am getting the following error in the SaveToSQLServer method:

    The SaveToSQLServer method has encountered OLE DB Error code 0x80004005 (Communication Link Failure). The SQL statement that was issued has failed.

    However I am not facing this problem when I try to deploy the DTSX package to a 32-bit SQL Server 2005 in a 32-bit machine.

    Can anyone tell me why this happens and how this could be solved?

    Thanks,

    Sandhya

  41. deena says:

    Hi,

    I have been running backup on a database(with size more than 20 GB), here I was not able to connect mssql server 2005 thro’ management studio.

    Event log says the following error

    Use the "max worker threads" configuration option to increase number of allowable threads, or optimize current running queries. SQL Process Utilization: 4%. System Idle: 16%.

    However I icreased the worker thread value from 128 to 300, but still problem exists.

    After the backup was completed every thing was fine.

    Any suggestions would be appreciated.

    Thanks

    deena

  42. Deepak sethi says:

    I have just installed SQL SErver 2005,

    when i start server management studio, it asks for server registration.

    i have downloaded adventureworks x86 from the website. but now i don;t know how to register.

    please provide some feedback as i am new toit.

  43. Hi, Deepak

       Find the answer from Books Online installed w/ SQL Server 2k5.

    Good Luck!

    Ming

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

  45. ALZDBA says:

    Is there a space for Service Broker connectivity issues ? (… connection forcibly closed …)

  46. Sanjay says:

    I am have setup SQL Server 2005 Express on Windows XP Pro SP2 machine. I enabled SQL Server and SQL Server Browser services, configured the firewall. I migrated Access 2002 databases (in linked tables mode) to this SQL server from workstations running XP Pro SP2. All went perfectly well, as planned. The problem is that, we have two W2k Pro machines, and I can not connect these machines to the SQL at all. I can ping the machine running SQL, but can’t connect to the SQLEXPRESS.

  47. Hi, Sanjay

       Try this blog and follow the best practice:

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

    Good Luck!

    MIng.

  48. Vijay Anand says:

    I unable to connect to sql from windows 2003 server were as I can able to connect from windows xp

    Connection failed:

    SQLState: ‘01000’

    SQL Server Error: 53

    [Microsoft][odbc sql server driver][dbnetlib]connectionopen(connect())

    connection failed:

    SQLState: ‘08001’

    SQL Server Error: 17

  49. praveen says:

    Hi Guys,

    I have a unique problem, i am having SQL Server 2000 client (English Version)running in my machine and i have a SQL Server 2005 (Korean Version). When i try to establish a connection in my enterprise manager i am not able to connect, but the same is getting connected when i try in SQL Query Analyzer.

    Can someone help me in resolving the issue.

    Thanks

    Praveen

  50. Hi, Praveen

       Please provide your connection string and the error message you came accross.

       If you face further problem, please provide more detail info by following:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1 

    Thanks!

    Ming.

  51. Hi, Vijay

       Can you provide more detail info by follow up below questions?

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1

    Thanks!

    Ming.

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

  53. Aliyah says:

    Help am doing C# and whenever i call store procedure a message come up that i can’t open or login.

    string connstring =  "Data Source =.\SQLEXPRESS; Initial Catalog=testDatabase; User Instance=True; Integrated Security = True";

               SqlConnection connection = new SqlConnection(connstring);

              // SqlConnection Connection = new SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|testDatabase.mdf;Integrated Security=True;User Instance=True");

               SqlCommand cmd = new SqlCommand();

               SqlDataReader reader;

               cmd.CommandText = "StoredProcedure1";

               cmd.CommandType = CommandType.StoredProcedure;

               cmd.Connection = connection;

             //  cmd.ExecuteNonQuery();

           //  connection.Open();

                connection.Open();

               reader = cmd.ExecuteReader();

               // Data is accessible through the DataReader object here.

            connection.Close();

    where am i going wrong?  HELP!!!!!!!!!

  54. Priya says:

    I am not able to figure out how to use sql express 2005 in client server environment.

    I have installed the completed SQLEXpress and Advnaced Sql Express on both machines, enabled all protocols but still not able to connect from client to server.

    I also tried SQLEXpress and Advnaced Sql Express on one machine and only Sql NAtive Client on client machine but no success.

    What settings needed on server machine and what on client machine?

    Can you please help?

  55. Shane says:

    Ming,

    First of all, thank you for this great resource.

    We have a strange case with some customers.  We are connecting to a local SQL 2005 Express named instance.  We are supplying the local server and instance name using "127.0.0.1TOCTTARGPPC05".  Also note we are using Windows Authentication.  When we try to connect to the instance using the IP for the local machine we get the error:

    [DBNETLIB][ConnectionOpen (Connect()).]Specified SQL Server not found. (6)

    We have shared memory, named pipes and tcp protocols enabled.  In our troubleshooting we tried using sqlcmd to connect to the database.  When we used "127.0.0.1TOCTTARGPPC05" the connection failed, but when using ".TOCTTARGPPC05" the connection was established immediately.  From our research we noticed that this can occur if the SQL Browser service is not running and in fact we could reproduce this issue on our own machines.  However, on the customers machine the SQL Browser service is running and they are still not able to connect using "127.0.0.1TOCTTARGPPC05".  We are currently stumped.  Do you have any suggestions?

    Thanks,

    Shane

  56. Hi, Shane

    Assume your local sqlexpress has np and tcp enabled, and it is listening on a tcp port and named pipe from ERRORLOG.

       Can you try:

       1) "sqlcmd /Snp:127.0.0.1TOCTTARGPPC05", what is the error message you got?

       2) "sqlcmd /Stcp:127.0.0.1TOCTTARGPPC05", what is the error message you got?

       3) Repeat step 1) and 2) by replace server to

    a. "<localmachinename>TOCTTARGPPCO5"

    b. "localhostTOCTTARGPPCO5"

    c. "<ipaddress>TOCTTARGPPCO5"

    Would all of these work?

    Is this problem only occur when you were trying "127.0.0.1"?

    What OS when repro? WIN2K3? Do you have ipv6 enabled on the box?

    Thanks!

    Ming.

  57. Peter says:

    I want to know why in Case 2 that SQL Browser service is required when it is dealing with local instance:

    Case 2: Connect to local named instance

    If you can not identify cause for connection fail when use MDAC connecting to local  named instance, there are two other possible reasons:

    1) Your application was running under an account that has no permission to the registry entry where MDAC client reads (HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server<InstanceName>MSSQLServer).

    2) SQL Browser service was not enabled or running.

  58. khalid says:

    i am using the following  connection string in my desktop application.it works fine with SQL Enterprise edition but when i use it with SQLExpress edition it does not work.

    kindly help me how to fix this problem

    Provider=sqloledb;Data Source=;Initial

    Catalog=HTMS;User Id=HC;Password=123;

  59. Hi, khalid

       Please take a look the blog for best practice of connecting to sqlexpress.

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

    Good Luck!

    Ming

  60. sethu says:

    i need to differentiate MS SQL SERVER 2005 named instance and default instance

  61. sethu says:

    i need to differentiate MS SQL SERVER 2005 named instance and default instance through c# code..

    This is needed to attach the data base with server.  that is i have to know  whether i have to give (local) or (local)SQLEXPRESS at the time attaching the data base.

  62. Chandra says:

    Hello,

    I have some problem here…

    When I use Server Explorer on Microsoft Visual Studio .NET 2005, I can’t connect the SQL Server 2005 with Data Source : Microsoft SQL Server (SqlClient) but when I use Microsoft SQL Server (OLE DB) its work, its also happening when I make a connection test using udl file.

    why this problem happening?

    Thank for your help. Thank You

  63. Hi, Chandra

       Please check out the blog according to the error message that you saw from sql client.

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

    Good Luck!

    Ming.

  64. We had a major issue on one of the SQL Server instance with the memory and getting following error message

  65. can anyone help me in this says:

    Database access error. Reason [Microsoft][SQL Native Client]Communication link failure.

    Webi report server log

    (.infostore.cpp:265): trace message: CInfoStore::Query: SELECT SI_SERVER_NAME,SI_CLUSTER_NAME,SI_SERVER_IS_ALIVE,SI_SERVER_IOR,SI_SYSTEM_INFO FROM CI_SYSTEMOBJS WHERE (SI_PARENTID = 16 AND SI_SERVER_KIND = ‘aps’) OR SI_ID = 4

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: UNV_Manager:originalReportFilePath:frs://Input/a_068/001/000/324/test_universe.unw

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: UNV_Manager:tempFileName:

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: FSFactoryListener_impl::GetNewServiceHelper: Asking for filestore from server Input:Input.alg1101rh2boa01..

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: [EXIT]  UNV_Manager:attachHelper: 0.015

    [Wed Jun 20 08:04:08 2007]      4920    6528    assert failure: (.TraceLog.cpp:1644). (false : TraceLog: **ASSERT:UNV_CacheManager:copy Universe file failed… [UNV_CacheManager.cpp;210]).

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: [EXIT]  UNV_CacheManager:UNWCacheManager::DownloadFile: 0.031

    [Wed Jun 20 08:04:08 2007]      4920    6528    assert failure: (.TraceLog.cpp:1646). (false : TraceLog: **ERROR:UNV_Manager:The universe cache manager failed to download file.. [UNV_Manager.cpp;1758]).

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: [ENTER] UNV_CacheManager:UNWCacheManager::DownloadFile

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: [ENTER] UNV_Manager:attachHelper

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: UNV_Manager:originalReportFilePath:frs://Input/a_068/001/000/324/test_universe.unw

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: UNV_Manager:tempFileName:

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: [EXIT]  UNV_Manager:attachHelper: 0

    [Wed Jun 20 08:04:08 2007]      4920    6528    assert failure: (.TraceLog.cpp:1644). (false : TraceLog: **ASSERT:UNV_CacheManager:copy Universe file failed… [UNV_CacheManager.cpp;210]).

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: [EXIT]  UNV_CacheManager:UNWCacheManager::DownloadFile: 0.016

    [Wed Jun 20 08:04:08 2007]      4920    6528    assert failure: (.TraceLog.cpp:1646). (false : TraceLog: **ERROR:UNV_Manager:The universe cache manager failed to download file.. [UNV_Manager.cpp;1758]).

    [Wed Jun 20 08:04:08 2007]      4920    6528    assert failure: (.TraceLog.cpp:1646). (false : TraceLog: **ERROR:UNV_Manager:Failed at : -2147206823 [UNV_Manager.cpp;1986]).

    [Wed Jun 20 08:04:08 2007]      4920    6528    assert failure: (.TraceLog.cpp:1646). (false : TraceLog: **ERROR:UNV_Manager:HRESULT : -2147206823 [UNV_Manager.cpp;1986]).

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: [EXIT]  UNV_Manager:ImportUniverseFromCE: 0.062

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: [EXIT]  UNV_Manager:GetUniverseFromCache: 0.062

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: [EXIT]  UNV_Manager:LoadUniverseHeader: 0.062

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: [ENTER] QT_LocalServer:SetErrorMessage

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: QT_LocalServer:An error occurred, the corresponding message will be set in the TLS

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: QT_LocalServer:Error Code:506

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: ctResourceBundle:Message bundle loaded::qtserver_error_en_

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: ct_cdz_error_context:[Cdztools Error Trace:BEGIN]

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: ct_cdz_error_context:PREFIX:ERR

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: ct_cdz_error_context:COMPONENT:WIS

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: ct_cdz_error_context:ERRORCODE:506

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: ct_cdz_error_context:ERRORTYPE:USER

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: ct_cdz_error_context:MESSAGE:Could not download the universe test_universe from the CMS for the following reason: . See your Business Objects administrator for further information. (Error: WIS 00506)

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: ct_cdz_error_context:BORESULT:89

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: ct_cdz_error_context:MODULENAME:QTServer

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: ct_cdz_error_context:FILENAME:QTLocalServer.cpp

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: ct_cdz_error_context:LINEPOSITION:1770

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: ct_cdz_error_context:DPNAME:

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: ct_cdz_error_context:DPLONGNAME:

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: ct_cdz_error_context:DPID:

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: ct_cdz_error_context:COMMANDNAME:

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: ct_cdz_error_context:COMMANDID:

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: ct_cdz_error_context:[Cdztools Error Trace:END]

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: ctResourceBundle:Message bundle released::qtserver_error_en_

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: ctResourceBundle:Message bundle released::qtserver_error_en_

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: [EXIT]  QT_LocalServer:SetErrorMessage: 0

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: [EXIT]  QT_LocalServer:GetUniverseParameters: 0.062

    [Wed Jun 20 08:04:08 2007]      4920    6528    assert failure: (.TraceLog.cpp:1646). (false : TraceLog: **ERROR:C3_cdbQTServer:m_pQT->GetUniverseParameters : -2147206823 [kc3qtds.cpp;3911]).

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: [EXIT]  C3_cdbQTServer:LoadUniverseConnection: 0.062

    [Wed Jun 20 08:04:08 2007]      4920    6528    assert failure: (.TraceLog.cpp:1646). (false : TraceLog: **ERROR:C3_cdbQTServer:LoadUniverseConnection failed [kc3qtds.cpp;5389]).

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: [EXIT]  C3_cdbQTServer:_LoadDictionary: 0.062

    [Wed Jun 20 08:04:08 2007]      4920    6528    assert failure: (.TraceLog.cpp:1646). (false : TraceLog: **ERROR:C3_cdbQTServer:m_pServer->_LoadDictionary(*m_pDict); failed : -2147467259 [kc3qtds.cpp;2801]).

    [Wed Jun 20 08:04:08 2007]      4920    6528    trace message: TraceLog: [EXIT]  C3_cdbQTServer:tbQTDS_CreateObjects: 0.062

    Input FRS log

    Timestamp       ProcessID       ThreadID        Message

    [Wed Jun 20 07:58:06 2007]      7072    7896    (.nsclient.cpp:2037): trace message: Name server communication failed.  Reconnect…

    [Wed Jun 20 07:58:06 2007]      7072    7896    (.nsclient.cpp:224): trace message: NSCLIENT: Do connection to alg1101rh2boa01.connectprod.connect.bt.com.  Last successful connection to ALG1101RH2BOA01.CONNECTPROD.CONNECT.BT.COM

    [Wed Jun 20 07:58:06 2007]      7072    7896    (.oscafactorymgr.cpp:1318): trace message: DONT CALL DONT CALL DONT CALL DONT CALL

    [Wed Jun 20 07:58:07 2007]      7072    7896    trace message: SIWarning: ResolveSocket failed!

    [Wed Jun 20 07:58:07 2007]      7072    7896    trace message: CNSClientManager::DoConnectToAps(): failed to retrieve aps factory handle from aps alg1101rh2boa01.connectprod.connect.bt.com.

    [Wed Jun 20 07:58:07 2007]      7072    7896    (.oscafactorymgr.cpp:1318): trace message: DONT CALL DONT CALL DONT CALL DONT CALL

    [Wed Jun 20 07:58:08 2007]      7072    7896    trace message: SIWarning: ResolveSocket failed!

    [Wed Jun 20 07:58:08 2007]      7072    7896    trace message: CNSClientManager::DoConnectToAps(): failed to retrieve aps factory handle from aps alg1101rh2boa01.connectprod.connect.bt.com:6401.

    [Wed Jun 20 07:58:08 2007]      7072    7896    trace message: CNSClientManager::ClusterInfo::InvalidateAPS: Removing APS alg1101rh2boa01.connectprod.connect.bt.com from m_ActiveMembers and adding to m_InactiveMembers

    [Wed Jun 20 07:58:08 2007]      7072    7896    trace message: CNSClientManager::ClusterInfo::InvalidateAPS: Removing APS alg1101rh2boa01.connectprod.connect.bt.com:6401 from m_ActiveMembers and adding to m_InactiveMembers

    [Wed Jun 20 07:58:08 2007]      7072    7896    trace message: CNSClientManager::ClusterInfo::InvalidateAPS: Removing APS alg1101rh2boa01.connectprod.connect.bt.com from m_ActiveMembers and adding to m_InactiveMembers

    [Wed Jun 20 07:58:08 2007]      7072    7896    trace message: CNSClientManager::ClusterInfo::InvalidateAPS: Removing APS alg1101rh2boa01.connectprod.connect.bt.com:6401 from m_ActiveMembers and adding to m_InactiveMembers

    [Wed Jun 20 07:58:08 2007]      7072    7896    (.nsclient.cpp:237): trace message: NSCLIENT: no valid APS available to connect to

    [Wed Jun 20 07:58:08 2007]      7072    7896    (.nsclient.cpp:2069): trace message: Failed to connect to name server: Transport error: unable to retrieve the CMS factory. (44)

    [Wed Jun 20 07:58:52 2007]      7072    5016    trace message: FileResourceMgr::cleanup: Going thru maps of readers and writers

  66. Addi says:

    My application is losing connectivity while being installed / running on same machine as SQL Server.

    SQL Error! SQL State: 01000, ErrorCode 10054, ErrorMessage: [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionRead (recv()).

    Any ideas.

    Addi

  67. neklace says:

    Respected Sir,

    I face a problem .please read it and email me its solution as soon as possible.

    This is problem.

    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 connection to SQL Server))

    I try to serach from google its solution ,but i can not find its solution.

    Try this link.i do every thing which tell in this link but my problem still remain.

    http://www.datamasker.com/SSE2005_SAC.htm

    2) Problem

    SQL Server(SQLEXPRESS) is in stop state .when i try to change its state from stop to start it generate error & do not change its state stop state to start state.kindly please sole my these problems.

    I will be thankful to you.

    Best Regards

  68. Lucas Rodrigues Gonçalves says:

    Congratulations!! Excellent article!  Very helpfull!!  

  69. Dale says:

    I’m using classic ASP to connect using the following connection string:

    Conn.open "Provider=sqloledb;Server=server;Initial Catalog=db;UID=;PWD=;

    But I’m getting the following error:

    [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

    Note: This *was* working at one point, but something changed on the database machine and I can’t figure out what changed.

  70. Ashish Gupta says:

    Unique Issue :

    I can connect to SQL Server 2005 using 32 bit processor but when i tries the same application from 64 bit processor machine i recived remote connections not allowed by sql server error.

    Please help

  71. Bharath says:

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

    what this means

  72. Tom says:

    I installed SQL Server 2005 Standard on Windows Vista Business. I am using Sql Server Management Studio and have no problem connecting to my local database. However, I keep getting the following error when trying to connect to a remote SQL Server. I can connect fine under XP with SQL 2000 Enterprise Manager. Here’s the error message I get:

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

    I have tried the following, based on reading your blog and suggestions by others:

    1) Using Surface Area Configuration, enabled remote connections through both TCP/IP and named pipes

    2) Using Surface Area Configuration, granted sysadmin priveleges to my admin user account

    3) Using SQL Server Configuration Manager, created an alias specifying the tcp protocol, server name, and port number required by the SQL server

    4) Created exceptions for sqlservr.exe and sqlbrowser.exe in the Windows Firewall

    5) Using SQL Server Configuration Manager, enabled shared memory, TCP/IP, and named pipes in both SQL Server 2005 Network Configuration and SQL Native Client Configuration

    6) Temporarily disabled the Windows Firewall completely

    Despite all of this I am still getting the same error message. Did I leave out something important?

    Any help would be appreciated…I’ve been at this for several days.  Thanks, Tom

  73. Sadat says:

    I have XP/SP2 laptop. I downloaded the trial edition. When I click on the Management Studio, the dialog box asks for server name. The computer name is shinaspc. I installed XP as a standalone workgroup. I get the 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: 53)"

    I cannot create any new database or start learning from samples. Please help.

  74. fliben says:

    Setup is web farm accessing SQL 2055 through a firewall with port 1433 open to the sql 2005 machine and all ports from SQL network open. Windows firewalls are turned off. SQL accepts remote connections and is set for port 1433.

    I am seeing the below error randomly on connections to our SQL 2005 Std server.

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

  75. Eric says:

    Very helpful, I was able to resolve the connectivity issue.

    Thank you!

  76. hi says:

    i have installed visual studio 2008 and i can’t find sql enterprise manager in it and i have installed query analyzer seperately but when i try to connect it with the database it is throwing error that odbc driver missing or sql server not found……………… kindly suggest me how come i get rid of this problem

  77. Leo says:

    I have searched many places and finally got it solved by trying a suggestion here about registration.

    I was able to add a SQL Express to a gourp in SQL 2005 Server Management Studio using Windows Authentication. I tried to register the same database using a SQL server user. Testing of registration was successful. But whenever I tried to create an ODBC connection to SQLEXPRESS, I got an error saying that the SQL server does not exist or authentication failed.

    What I finally did was to right click the SQL database group or the database, then cilck the option "Update Local Server Registration".

    The reason that I tested ODBC connection was the failure of connection when I used connection string in the code.

  78. Leo says:

    The exact error of mine was "SQL Server does not exist or access denied" after connection is open.

    I had connection errors before this error. I cannot remember the exact error messages. I tried suggestions at this site:

    http://74.125.95.132/search?q=cache:VWFIV3SxCH4J:blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx+sqlexpress+odbc&cd=3&hl=en&ct=clnk&gl=us

    Here is how to enable connections:

    Start->All Programs->Microsoft Sql Server 2005

    ->Configuration Tools-> SQL Server Surface Area Configuration

    In the left area:

    SQLEXPRESS

     Database Engine

       ->Remote Connections

     Enable "Using both TCP/IP and named pipes"

    After the change, you can stop/start the service

    SQLEXPRESS

     Database Engine

      ->Service

    or using command

      net stop mssql$sqlexpress

      net start mssql$sqlexpress

  79. Michael Gibson says:

    Just installed SQL Express 2008 – amazing all the tools they include for free now!  Your page helped me tremendously. Was troubleshooting connection strings on old .asp code trying to connect to the new DB.  Thanks a ton!

    Just FYI, I switched from

    MyConn.Open("driver={SQL Server};server=ABC

    to

    MyConn.Open("driver={SQL Native Client};Data Source=XYZ

    (With an appropriately configured DSN)

  80. laertejunior says:

    Hi…We have a VM with Sharepoint connect to another server (SQLSERVER) and sometimes we have this error in

    VM

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

    But it is not normal and in some hours of day this happend. We have to restart the VM to connect again in sql server. Could be a sql server problem ?

    Thanks

  81. Carol says:

    I already test HTTP Proxy is success connect to MSN messenger services. Test TCP connection unable to connect 8007274c.

  82. ramesh says:

    A network-related or instance-specified errer occurred while establishing a connection to sql server. The server was not found or wasnot accessible. Verfy that the instance name is correct and that SQL server is configured to allow remote connections.(provider: TCP Provider, error: 0- no connection could be made because the target machine actively refuded it)

  83. Zamai Babu says:

    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: TCP Provider, error: 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.)

    Please help, urget….

  84. Saravana says:

    i am using MDAC libraries to connect to the SQL Server 2005 and SQL Server 2008 instances and i connect the same instances from local machine and also remotely. So i enabled both named pipe and TCP/IP protocols for the instance. I want the application to try named pipe protocol first for the connection. So i changed the protocol order in the HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server<InstanceName>MSSQLServerSuperSocketNetLibProtocolList to  ‘np tcp’. But when i restart the instance, then the value gets reset to ‘tcp np’.

    Any clue why is this happening?

    Thanks,

    Saravana

  85. Fernando says:

    The post from Bobikus is THE ONE for those who are facing problems due to NAMED INSTANCES.

    NO docs from Microsoft state you need a double-backslash for a named instance…

    Thanks Bobikus !!!

  86. Phil says:

    I read the article with great interest, but it doesn't seem to address intermittent connection problems.  We have an application that copies changed info from a production DB to an "Archive" one every night.  We are having intermittent errors that cause the process to die, and then we have to rerun it for it to catch up and do the rest of the copying.

    The most frequent intermittent error is:

    "DestCN errors are :  -2147467259 [DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentation. ;"

    The next-most mysterious errors indicate problems in the cooperation of Windows, AD, and SQL Server 2005 to provide NT authentication for the archiving process to log into SQL Server.  These errors include:

    "DestCN errors are :  -2147467259 [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. ;",

    "DestCN errors are :  -2147467259 Login failed for user ''. The user is not associated with a trusted SQL Server connection. ;", and

    "DestCN errors are : -2147467259 Cannot generate SSPI context ;".

    How can we stop these errors from happening?