This post provides some tips to troubleshoot Sql Server connection
problems based on various displayed error messages. And, I will describe
connection problems according to different client stack:
SNAC/MDAC/SQLClient. Thus, there are 3 parts for this topic.
First Part – Troubleshoot SNAC connect to SQL Server 2005
Second Part - Troubleshoot MDAC connect to SQL Server 2005
Third Part – Troubleshoot SqlClient connect to SQL Server 2005
Before
start, it is very important to identify which client connected to
Server and failed. Here, I list out follow key terminology in Sql Server
Connection.
SNAC - A new data
access technology that is new in SQL Server 2005, and is a stand alone
data access application programming interface that is used for both ODBC
and OLEDB.
MDAC - Microsoft Data Access Component contains core data access components, such as OLEDB provider and ODBC provider.
SqlClient - Access data from within a CLR database object by using the .NET Framework Data Provider for SQL Server.
Protocol
Prefix: explicitly specify which protocol you want to use to make
connection. Supported protocol prefix in Sql Server 2005 includes:
“lpc:”, “np:”, “tcp:”, “via:”
Last
connect cache – contains the fully resolved/specified connection
strings for the instances that were successfully connected to.
Part I – Connection Fail when SNAC 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
sqlcmd.exe for OLEDB provider. They are located in %SYSTEMDRIVE%\Program
Files\Microsoft Sql Server\90\tools\binn.
Basic connection string: osql(sqlcmd) /S[prefix]<servername> /E
Osql(sqlcmd) /S[prefix]<servername>\<Instance> /E
In each follow Message, there are two, one is from ODBC, and the other one is from OLEDB.
Message 1:
[SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [53].
[SQL Native Client]Login timeout expired
[SQL
Native Client]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.
-OR-
HResult 0x35, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [53].
Error:
Microsoft SQL Native Client : 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..
Error: Microsoft SQL Native Client : Login timeout expired.
This
indicates that your target server can not be accessed or does not
exist. Try to use "ping <servername>" , " ping <ipofserver>"
, "ping -a <ip>", If either of the pings time out, fail, or do
not return the correct values, then either the DNS lookup is not working
properly or there is some other networking or routing issue that you
will need to resolve.
Message 2:
[SQL Native Client]Named Pipes Provider: Could not open a connection to SQL
Server [2].
[SQL Native Client]Login timeout expired
[SQL Native Client]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.
-OR-
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Error:
Microsoft SQL Native Client : 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..
Error: Microsoft SQL Native Client : Login timeout expired.
This
error message means that the server was not found or not running or can
not make Named Pipe connection through the pipe name that client
specified. To verify this:
1)
From the command line, do "sc query mssqlserver" or "sc query
mssql$<instancename>" to check whether sql instance present. Then
open sql server configuration manager -> check the state of the sql
service, if it is not running, start it. If server started fail, check
event log or server error log, see what happened there.
2)
if you are sure the service is running and shared memory/Named Pipe
enabled, please try connection if it is local default instance "osql
/Snp:\\.\pipe\sql\query"; or try connection "osql
/Snp:\\.\pipe\mssql$<InstanceName>\sql\query" if it is local named
instance. if you still get error 2, then go to step 3).
3)
Double check the server is started and listening on named pipe if you
enabled Named Pipe. One way is that see the ERRORLOG of the server,
search follow keywords:
Server named pipe provider is
ready to accept connection on [ \\.\pipe\sql\query ] or
[\\.\pipe\mssql$<InstanceName>\sql\query]
Notice
that "sql\query" is the default pipe name, so you need to know server
is listening on which pipe name. eg: if you specify server pipe name is
"sql\query1", then you would see in the errorlog that server listening
on [ \\.\pipe\sql\query1 ].
4) See your connection
string, whether you explicitly specify the pipe name and does it match
the pipe that server is listening on? Or whether you just specify server
name( like ".","(local)", etc), but you specify the wrong pipe name on
client side Named Pipe configuration.eg, go to SQL Server Configuration
Manager, click client Named Pipe properties, see whether the pipe name
is same with the one server listening on.
Note: For remote connection, you need to verify step 2) and 3).
Message 3:
[SQL Native Client]Named Pipes Provider: Could not open a connection to SQL
Server [233].
[SQL Native Client]Login timeout expired
[SQL
Native Client]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.
-OR-
HResult 0xE9, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [233].
Error:
Microsoft SQL Native Client : 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..
Error: Microsoft SQL Native Client : Login timeout expired.
Note: the difference between Message 2 and Message 1 is “Could not open a connection to SQL Server [233].” – error state.
You
might specify the server name as FQDN/127.0.0.1/ IP Address and NP was
disabled on the server. To resolve this, first way is to replace server
name as the machine name or “.” or”(local)” or “<machinename>” and
you should be able to connect as long as server listening on Shared
Memory; second way is to enable named pipe from sql configuration
manager and restart server.
Message 4:
[SQL Native Client]Named Pipes Provider: No process is on the other end of the pipe.
[SQL Native Client]Communication link failure
[SQL
Native Client]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.
-OR-
HResult 0xE9, Level 16, State 1
Named Pipes Provider: No process is on the other end of the pipe.
Error: Microsoft SQL Native Client : Communication link failure.
Error:
Microsoft SQL Native Client : 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..
1)
Local connection: You might specify the server name as FQDN/127.0.0.1/
IP Address in the connection string and speculate connection through
Named Pipe provider. To resolve this, either change server name to
<machinename> as long as the server is listening on Shared Memory
or enabled NP.
2) Remote connection: the server is not
listening on Name Pipe. To resolve this, enable name pipe on the remote
server and restart the server.
Message 5:
[SQL Native Client]SQL Network Interfaces: Server doesn't support requested protocol [xFFFFFFFF].
[SQL Native Client]Login timeout expired
[SQL
Native Client]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.
-OR-
HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: Server doesn't support requested protocol [xFFFFFFFF].
Error:
Microsoft SQL Native Client : 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..
Error: Microsoft SQL Native Client : Login timeout expired.
1) Local connection: You might explicitly specify protocol prefix “np:” and connect to a named instance and NP was disabled.
2) Remote connection: You might connect to remote named instance and remote server is not listening on name pipe.
To
resolve this, enable NP if you only want to use name pipe protocol or
you can remove “np:” prefix to let connection over shared memory
locally.
Message 6:
[SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [0xFFFFFFFF].
[SQL Native Client]Login timeout expired
[SQL
Native Client]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 remot the connections.
-OR-
HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
Error:
Microsoft SQL Native Client : 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..
Error: Microsoft SQL Native Client : Login timeout expired.
You
might connect to local named instance and explicitly specify protocol
prefix “tcp:” or “np:” in the connection string, however, SqlBrowser
service was not running.
To resolve this, you should enable Sqlbrowser service on the server
1) Use net start or go to sql configuration manager(SSCM), check whether sqlbrowser service is running, if not, start it.
2)
You still need to make sure SqlBrowser is active. Go to SSCM, click
properties of sqlbrowser service -> Advanced-> Active “Yes” or
“No”, if sqlbrowser is running but is not active, the service would not
serve you correct pipe name and Tcp port info on which your connection
depends.
Message 7: Shared Memory provider error
HResult 0x2, Level 16, State 1
Shared Memory Provider: Could not open a connection to SQL Server [2].
Error:
Microsoft SQL Native Client : 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..
Error: Microsoft SQL Native Client : Login timeout expired.
-OR-
[SQL Native Client]Shared Memory Provider: Could not open a connection to SQLServer [2].
[SQL Native Client]Login timeout expired
[SQL
Native Client]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.
The error indicate you
speculated shared memory as connection protocol and server is not
listening on shared memory, plus you probably specified
“.”/”(local)”/<machinename> /localhost as the server name in the
connection string. To resolve this, enable shared memory protocol and
restart the server.
Message 8:
[SQL Native Client]SQL Network Interfaces: Cannot open a Shared Memory connection to a remote SQL server [87].
[SQL Native Client]Login timeout expired
[SQL
Native Client]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.
-OR-
HResult 0x57, Level 16, State 1
SQL Network Interfaces: Cannot open a Shared Memory connection to a remote SQL server [87].
Error:
Microsoft SQL Native Client : 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..
Error: Microsoft SQL Native Client : Login timeout expired.
The
reason is same as the one of Message 6, just you might specify
FQDN/127.0.0.1/IP Address as server name in the connection string.
Message 9: TCP specific
[SQL Native Client]TCP Provider: No connection could be made because the target machine actively refused it.
[SQL Native Client]Login timeout expired
[SQL
Native Client]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.
-OR-
HResult 0x274D, Level 16, State 1
TCP Provider: No connection could be made because the target machine actively refused it.
Error:
Microsoft SQL Native Client : 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..
Error: Microsoft SQL Native Client : Login timeout expired.
The reason is server is not listening on TCP, probably TCP protocol was not enabled.
-Follow messages are some special case -
Message 10:
[SQL Native Client]Unable to complete login process due to delay in opening server connection.
Reason:
1)
There are spaces after Instance name in the connection string eg. osql
/S”<machinename>\Instance “ /E, to resolve this, you need to
remove the trailing space.
2) Connect through 127.0.01.
3)
Remote connection and WINS was disabled on the client machine and you
connect using FQDN as server name. To resolve this, One way, turn on
“File and Printer Sharing” and explicitly use name pipe protocol.
Another is enlarge the connect timeout to around 30 secondes.
Message 11: - Firewall specific
HResult 0x274C, Level 16, State 1
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.)
-OR-
Error:
Microsoft SQL Native Client : 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..
Error: Microsoft SQL Native Client : Login timeout expired.
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 [url]http://support.microsoft.com/default.aspx?scid=kb[/url];en-us;287932
Message 12:
“[SQL Native Client] Can not Generate SSPI Message”
[url]http://blogs.msdn.com/sql_protocols/archive/2005/10/19/482782.aspx[/url]
Part II - Troubleshoot Connectivity Issue in SQL Server 2005
[url]http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx[/url]
Part III - Troubleshoot Connectivity Issue in SQL Server 2005
[url]http://blogs.msdn.com/sql_protocols/archive/2005/12/22/506607.aspx[/url]
No comments:
Post a Comment