TCP listening ports and ENDPOINT ports behavior

Endpoint configuration seems to be creating some confusion.  I hope that this will clarify some of the behavior as it applies to TCP endpoints. With a default configuration of SQL Server, try the following: > select name, port, is_dynamic_port, state from sys.tcp_endpoints> goname                        port is_dynamic_port state————————— —- ————— —–Dedicated Admin Connection     0               1     0TSQL Default…


Understanding “Data Source=(local)” in SQL Server Connection Strings

Lately we have noticed many misunderstandings surrounding the usage of the Data Source keyword in connection strings, caused by people generalizing from an example demonstrating a connection string for local connectivity and creating their own connection string for a remote connection.  Here is one such example connection string for local connectivity as it would be…


Configuring SQL Protocols through Windows PowerShell

                Sometimes we are asked about the possibility of configuring SQL Server protocols through PowerShell.  In SQL Server 2008, the sqlps tool incorporates WMI and SMO into this powerful Windows administrator tool, making it easy to manage SQL Server protocols through PowerShell.                 To get started, run (elevated, if on Windows Vista or Windows Server…


Connection failure because of mismatched TDS version

Some customers have reported connection failure because of wrong TDS version.   The client application gets the following error message: (or similar depends on the protocol used) A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 – No process is on…


Troubleshooting SQL Server Cluster Installations

On a Windows Cluster it is possible to install a SQL Server Cluster, which will be a separate entity with disctinct Virtual Server Name, IP address, and pipe name. The nodes of the SQL Cluster must be able to communicate with one another. This communication may fail if the TCP port on which SQL Server is listening is not…


Connectivity troubleshooting in SQL Server 2008 with the Connectivity Ring Buffer

SQL Server 2008 contains a new feature designed to help troubleshoot particularly tricky connectivity problems. This new feature, the Connectivity Ring Buffer, captures a record for each server-initiated connection closure, including each kill of a session or Login Failed event. To facilitate efficient troubleshooting, the ring buffer attempts to provide information to correlate between the client…


Understanding the error message: “Login failed for user ”. The user is not associated with a trusted SQL Server connection.”

Understanding the error message: “Login failed for user ”. The user is not associated with a trusted SQL Server connection.” This exact Login Failed error, with the empty string for the user name, has two unrelated classes of causes, one of which has already been blogged about here: http://blogs.msdn.com/sql_protocols/archive/2005/09/28/474698.aspx.  In addition to an extra space…


Steps to troubleshoot SQL connectivity issues

We have been seeing and trying to resolve SQL connectivity issue all the time. I guess it would be helpful if we can put some guidance on how to resolve connectivity issues. Here comes a proposal based on my experience.   Basically, when you failed to connect to your SQL Server, the issue could be:…


Could not connect to X64 named instance

We see a couple of cases where customer could not connect to named instance on X64. They all have the following symptons. 1) Customer has no problem to connect X86 instance on the same server machine even if it’s a named instance. Customer can even connect to some of the X64 named instance, but always see…


Understanding “Connection forcibly closed by remote host” Errors Caused by TOE/Chimney

Sporadic “Connection forcibly closed by remote host” errors with SQL Server connections can be very difficult to troubleshoot and resolve. This blog post is targeted at diagnosing TOE/Chimney issues that may lead to this client error message. Chimney is a feature introduced in the Windows Server 2003 Scalable Networking Pack, which was included in Windows Server…