Connecting to DB Engine

I am running a mirrored database using SQL2005 and whether or not mirroring has anything to do with this has not been determined. Often when a failover is necessary we are in a state where the active server responds just enough to show existence but not enough to determine database state. The SQL Management Studio can not connect to the server and the exception report from our application has the following message:

<exception type="System.Data.SqlClient.SqlException">

<message>A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)</message>
<source>.Net SqlClient Data Provider</source>
<target>System.Data.ProviderBase.DbConnectionPool::GetConnection</target>
<stack>

<trace index="0">at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)</trace>
<trace index="1">at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)</trace>
<trace index="2">at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)</trace>
<trace index="3">at System.Data.SqlClient.SqlConnection.Open()</trace>
<... application stack/>
</stack>

</exception>

When this happens, the the SqlClient does not fail over to the mirror until we stop the service on the failed SQL server. Sometimes, the mirror server doesn't full pick up until we stop the service on the failed server.

Today, out of curiosity I attemped to connect to port 1433 using TelNet to see if the server was even listening at the standard endpoint anymore (before I received the Exception Report above). It was. So how do I determine what is happening that causes the connections to close, and if at all possible - prevent it?

Thanks in advance for any advice you may have.

-Dave

Server Version:

Microsoft SQL Server 2005 - 9.00.3042.00 (X64)

Feb 10 2007 00:59:02

Copyright (c) 1988-2005 Microsoft Corporation

Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

HI Dave,

Check for the following,

1.) Check the login whether it has necessary privileges on the database(default db for the login) its going to connect. Since the error shows its connected to the server and not to the default db.

2.) Check on which port Sqlserver is listening then connect using the port as servername,portno.

If possible post Errorlog & Eventlog info....
|||

Vidhya,

Yes. The login does have the rights and it is connecting to the correct port. When this occurs we (the DB admins) can not even connect via SQL Management Studio. We have to shut the service down via the Service Control Manager in Windows.

-Dave