A connection was successfully established with the server, but then an error occurred during the login process.

Recently I came across an issue when connecting to SQL Server instance using SQL Authentication.
The error says,

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 the other end of the pipe.) (Microsoft SQL Server, Error:233)

To fix the issue, Disable "Shared Memory" protocol in the SQL Server Configuration Manager.

To find the status of SQL Server Protocols using T-SQL, refer to the below link
http://www.sanssql.com/2011/04/t-sql-query-to-find-sql-server.html

Hide an Instance of SQL Server Database Engine

In one of my previous post "List of SQL Server instances currently installed in your network", I explained how to get the list of SQL server instances installed in your network.
Sometimes you might require an SQL Server instance to be not exposed to others due to some security reason. This can be done by using the "Hide" option available for an SQL Server instance.
Before Hiding an Instance:

Steps to Hide an Instance of SQL Server Database Engine

1. Go to "SQL Server Configuration Manager"
2. In the Left Pane Expand "SQL Server Network Configuration"
3. Now Right Click on "Protocols for <ServerName>" and go to Properties for the server you need to hide.
4. In the "Flags" Tab, change the option to "Yes" for "Hide Instance"
   
5. Click on "Apply" and "OK"
6. Now re-start the SQL Server Service for this Instance and you are done. This instance from now will be hidden.

After Hiding an Instance:

T-SQL Query to find the SQL Server Protocols Status

Here is a T-SQL Query that I have developed to get the status of the SQL Server Protocols.
This query reads data from the registry.

DECLARE @InstanceName nvarchar(50) DECLARE @value VARCHAR(100) DECLARE @value_Out VARCHAR(100) DECLARE @RegKey_InstanceName nvarchar(500) DECLARE @RegKey nvarchar(500) SET @InstanceName=CONVERT(nVARCHAR,isnull(SERVERPROPERTY('INSTANCENAME'), 'MSSQLSERVER')) CREATE TABLE #SQLServerProtocols (ProtocolName nvarchar(25), Value nvarchar(10), Data bit) if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<>8 BEGIN SET @RegKey_InstanceName='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL' EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = @RegKey_InstanceName,   @value_name = @InstanceName,   @value = @value OUTPUT SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\Sm' Insert into #SQLServerProtocols (Value,Data) EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = @RegKey,   @value_name = 'Enabled' EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = @RegKey,   @value_name = 'DisplayName',   @value = @value_Out OUTPUT UPDATE #SQLServerProtocols set ProtocolName=@value_Out  where ProtocolName is null SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\Np' Insert into #SQLServerProtocols (Value,Data) EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = @RegKey,   @value_name = 'Enabled' EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = @RegKey,   @value_name = 'DisplayName',   @value = @value_Out OUTPUT UPDATE #SQLServerProtocols set ProtocolName=@value_Out where ProtocolName is null SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\TCP' Insert into #SQLServerProtocols (Value,Data) EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = @RegKey,   @value_name = 'Enabled' EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = @RegKey,   @value_name = 'DisplayName',   @value = @value_Out OUTPUT UPDATE #SQLServerProtocols set ProtocolName=@value_Out  where ProtocolName is null SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\Via' Insert into #SQLServerProtocols (Value,Data) EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = @RegKey,   @value_name = 'Enabled' EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = @RegKey,   @value_name = 'DisplayName',   @value = @value_Out OUTPUT UPDATE #SQLServerProtocols set ProtocolName=@value_Out  where ProtocolName is null END SELECT ProtocolName, IsEnabled=CASE WHEN Data=1 THEN 'Enabled'  ELSE 'Disabled' END FROM #SQLServerProtocols DROP TABLE #SQLServerProtocols

Download this script from here.