Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.