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 2 and 4 and type the answer here:
  • Post
  • 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.

  • 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

  • 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

  • Thank you!

    http://symy.jp/?Ct_220745,Thank">http://symy.jp/?Ct_220745,Thank you!

    http://symy.jp/?Ct_220745

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

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

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

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

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

  • 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.1\TOCTTARGPPC05".  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.1\TOCTTARGPPC05" 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.1\TOCTTARGPPC05".  We are currently stumped.  Do you have any suggestions?

    Thanks,

    Shane

  • 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.1\TOCTTARGPPC05", what is the error message you got?

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

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

    a. "<localmachinename>\TOCTTARGPPCO5"

    b. "localhost\TOCTTARGPPCO5"

    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.

  • 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_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\MSSQLServer).

    2) SQL Browser service was not enabled or running.

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

  • 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

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

Page 4 of 7 (98 items) «23456»