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 5 and 1 and type the answer here:
  • Post
  • 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?

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




  • 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_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\DomainProfile\AuthorizedApplications\List
    2)Add Tcp port to Firewall exception list. (eg, Name-1433:TCP, Value-1433:TCP:*:Enabled:Tcp 1433).

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\DomainProfile\GloballyOpenPorts\List

    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.




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



  • 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.
  • Hi,

    Where I can find part III of this series?
  • Here it is
    http://blogs.msdn.com/sql_protocols/archive/2005/12/22/506607.aspx

    Thanks for your comments!
    Ming.
  • 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?
  • 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 \\.\pipe\sql\query, but I could connect with \\.\pipe\MSSQL$SQLEXPRESS\sql\query.

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

    Thanks for the tips!
  • 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=localhost\SQLEXPRESS;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.
  • 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.
  • 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”.
  • 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
Page 1 of 7 (98 items) 12345»