I'm having an intermittent problem with one of my old SQL 2000 servers. Every once in a while it stops accepting logins. I fix the problem by bouncing sqlservr.exe and then it starts working properly for a few days. For example, running the PowerShell:
$connectionstring = "Server=.;Integrated Security=SSPI;"
$sqlconnection = new-object 'System.Data.SqlClient.SqlConnection'
$sqlconnection.connectionstring = $connectionstring
$sqlconnection.open()
Produces the error:
Exception calling "Open" with "0" argument(s): "A connection was successfully established with the server, but then an
error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The specified network name is no lon
ger available.)"
Similarly, if I change the connection string to:
$connectionstring = "Server=\\.\pipe\MSSQL`$SMS3000\sql\query;Integrated Security=SSPI;"
I get a similar error, except the end of the error says: (provider: Named Pipes Provider, error: 0 - The pipe has been ended.)
Checking C:\Program Files\Microsoft SQL Server\80\Tools\Binn\SVRNETCN.exe. Both Named Pipes and TCP/IP are enabled and "Force protocol encryption" is unchecked. And as I mentioned, if I bounce sqlservr.exe then the above commands create a successful connection.
I've checked the certificate store and there are no expired certificates in there. Checking the Windows event logs and SQL Server logs I'm not finding anything remotely useful there.
Lastly, I've run some network traces. For a failed connection I show:
TLS:TLS Rec Layer-1 HandShake: Client Hello.
TCP:Flags=...A.R.., ScrPort=1433, DstPort=18721, PayloadLen=0, Seq=888695317, Ack=3640041213, Win=0 ...
A successful connection looks like:
TLS:TLS Rec Layer-1 HandShake: Client Hello.
TLS:TLS Rec Layer-1 HandShake: Server Hello. Certificate.
I've hunted around the registry to try to figure out which certificate SQL is using, but I haven't had any luck there yet. Any ideas on what to look for next?
