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


  • 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.
  • 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
  • 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
  • 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
  • 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
  • Very many thanks for a good work. Nice and useful. Like it!
  • 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
  • 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

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

  • Hi, Deepak

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

    Good Luck!

    Ming

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

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

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

Page 3 of 7 (98 items) 12345»