locked
Connecting to Failover Partner using ODBC and OLE DB RRS feed

  • Question


  • 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
    Wednesday, May 24, 2006 11:06 AM

Answers

  • 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.

    Wednesday, May 24, 2006 7:03 PM

All replies

  • 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.

    Wednesday, May 24, 2006 7:03 PM


  • 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


    Is there any way how to connect from OLE DB?
    Thanks


    Yes there is a way to connect but you need to change your provider. So you connection string needs to look something like this:

    Provider=SQLNCLI10;Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial Catalog=myDataBase;Integrated Security=True;
    Thursday, February 3, 2011 4:34 PM