SQL Server 2008 R2 – User Mapping Dropped After Switching from Principal to Mirror Server

I maintain an Access 2003 application that connects to a pair of SQL Server 2005 servers setup with mirroring. I use the following connection string which allows the application to connect to the mirror after an application restart:

Dim ws As Workspace
Dim db As Database
Dim a As Long

Set ws = DBEngine.CreateWorkspace("MyODBCWorkspace", "admin", "", dbUseJet)
Set db = ws.OpenDatabase("C:\[pathtodatabase]", True, False, "MS Access;")
For a = 0 To db.TableDefs.Count - 1
    Me.ProgressBar1.Value = a
    DoEvents
    If db.TableDefs(a).Connect  "" Then
        db.TableDefs(a).Attributes = TableDefAttributeEnum.dbAttachSavePWD
        db.TableDefs(a).Connect = "ODBC;DRIVER=SQL Native Client;SERVER=[PrimaryServerName];Trusted_Connection=Yes;APP=[MyAppName];WSID=[MyWorkstationName];DATABASE=[SQLServerDatabase];Failover_Partner=[MirrorSQLServerName];"
        db.TableDefs(a).RefreshLink
    End If
Next a
db.Close
ws.Close
Set db = Nothing
Set ws = Nothing

I use this code in a VB launcher app I created that the user double-clicks to launch the Access MDB/MDE.

Alternatively, this is the content of a DSN file we sometimes use to accomplish the same thing:

[ODBC]
DRIVER=SQL Native Client
APP=Microsoft Data Access Components
Trusted_Connection=Yes
SERVER=[ServerFullyQualifiedDomainName]
FAILOVER_PARTNER=[MirrorFullyQualifiedDomainName]
DATABASE=[DatabaseName]

The problem is that the principal became disconnected from the witness server before it went offline. In this case, the witness cannot agree that the principal is offline because it was disconnected while the principal was still connected. As far as the witness knows, the principal may still be up.

In this scenario, it would require that you force service on the mirror to bring it online using ALTER DATABASE ... SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS.