Troubleshoot Connectivity Issue in SQL Server 2005 - Part II

Troubleshoot Connectivity Issue in SQL Server 2005 - Part II

  • Comments 98

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 Files\Microsoft SQL Server\80\tools\binn.

 

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 [\\.\pipe\SQLLocal\MSSQLSERVER]” or [ \\.\pipe\SQLLocal\<InstanceName>]

 

- Key words indicates server named pipe is enabled -

Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$<InstanceName>\sql\query ] or [ \\.\pipe\sql\query ]

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>\pipe\sql\query /E” or

“osql /S\\<machinename>\pipe\MSSQL$<InstanceName>\sql\query /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 \\localhost\pipe\sql\query( eg, you connection string like "Data Source = \\localhost\pipe\sql\query; Integrated Security = SSPI" or "osql /S\\localhost\pipe\sql\query /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_MACHINE\SOFTWARE\Microsoft\Microsoft 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

 

Leave a Comment
  • Please add 4 and 3 and type the answer here:
  • Post
  • 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.
  • 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
  • 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.
  • 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 CLUSTERDB\INSTANCE. 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)
  • 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.
  • 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.
  • 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.....
  • 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.
  • 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
  • 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.
  • 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




  • 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.
  • 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 "DPMMSSQL\MSSQL" 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
  • Hi, su

       Glad to hear you address the issue.

       I am not sure whether your SQL2k5 is a named instance? And you connect through" DPMMSSQL\MSSQL", 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.
  • 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
Page 2 of 7 (98 items) 12345»