Connecting to Failover Partner using ODBC and OLE DB

I need to connect to mirrored SQL servers (Developer Edition) using OLE DB, I tried both OLE DB and ODBC, but it doesn't work
I used connection ODBC string:
Driver={SQL Native Client};Server=10.0.1.161;Failover Partner=10.0.1.162;Uid=test;Pwd=test;Database=TestDB
if server 161 is principal and server 162 mirror, it connects ok, but when I exchange server roles, connect fails (the error message is: Cannot open database "TestDB" requested by the login. The login failed. in LOGIN)
the connect string using OLE DB is:
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=test;Password=test;Failover Partner=10.0.1.162;Initial Catalog=TestDB;Data Source=10.0.1.161;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False
error message is the same
when I try to connect using VS 2005 using connection string Database=TestDB;User Id=test;Password=test;Server=10.0.1.161;Failover Partner=10.0.1.162, it works OK
i have installed SQL server 2005 (on local - client machine) with SQL Native Client and also
SQL Server service pack 1
Is there any way how to connect from OLE DB?
Thanks

With OLE DB the connection string keyword is 'FailoverPartner' (no space) in the provider string for IDBInitialize::Initialize and 'Failover Partner' (one space) for IDataInitialize::GetDataSource.

With ADO it's 'Failover Partner' (one space) and ODBC it's 'Failover_Partner'

Depending on the method you use to connect, you may be using the wrong keyword.

Mirroring happens per database rather than per server, and covers data (tables and indexes) but not user accounts and access rights. These have to be set up in both databases for failover to work, otherwise you can have a login fail after the servers switch roles if the login does not have access rights in the mirror database.

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

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