SharePoint 2010 Failover doesn't work: Login failed at System.Data.SqlClient.SqlInternalConnectionTds.LoginWithFailover

We did the SQL mirroring for a content database.Then I went on /_admin/CNTDBADM.aspx select the content database and set the failover server for it.


We have 2 servers. Initially, db01 - is a principal, db02- failover.


The connection strings looks like this one:Data Source=db01;Failover Partner=db02;Initial Catalog={Content_db_name};Integrated Security=True;Enlist=False;Asynchronous Processing=False;Connect Timeout=15'  


Then for test purposes we switch the principal to  the previous failover server(db02). db01 became a mirror.


SharePoint worked great first couple hours.


But then when we sunset db01, the SharePoint collapsed with error that he can't connect to db01:



SqlError: 'Login failed for user '{failover instance service account}.'    Source: '.Net SqlClient Data Provider' Number: 18456 State: 1 Class: 14 Procedure: '' LineNumber: 65536 Server: '{db01 server netbios name}'


SQL database login for '{Content_db_name}' on instance 'db01' failed. Additional error information from SQL Server is included below.  Login failed for user '{failover instance service account}



ConnectionString: 'Data Source=db01;Failover Partner=db02;Initial Catalog={Content_db_name};Integrated Security=True;Enlist=False;Asynchronous Processing=False;Connect Timeout=15'    ConnectionState: Closed ConnectionTimeout: 15


An error occured while attempting to verify that the database is read only for database {Content_db_name}. The error contained the following message: Login failed for user {failover instance service account}

System.Data.SqlClient.SqlException: Login failed for user '{failover instance service account}'.    
 at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)    
 at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)    
 at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)    
 at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)    
 at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)    
 at System.Data.SqlClient.SqlInternalConnectionTds.LoginWithFailover(B

Even though I can see the connection string with Failover Partner value in the log, it hasn't been recognized by SharePoint.


This strange fix worked for me:


I re-applied the failover setting on the content database through PowerShell:


***Note you must be logged into PS as the Farm Account to set the failovers, a farm admin does not have sufficient rights to do this.***
(Setting Failover Partners for Sharepoint 2010)


 $db = get-spdatabase | where {$_.Name -eq "Content_db_name"}   
 $db.AddFailoverServiceInstance("db02");
 $db.Update() 




How do you know if SharePoint uses the failover succesfully? Here is an indicator in the log file:


SQL Server failover connection detected. SQL Server db01 failing over to SQL server db02


P.S. The failover server should have a partner on SQL side. My DBA set the content db without mirroring on db02 . That immediately brought the error on SharePoint side. Somehow it checks that the failover server contains the content database that should be mirrored.


System.InvalidOperationException: Server db02, database {Content_db_name} is not configured for database mirroring


P.S.S.
There is an excellent article that covers all SharePoint 2010 databases and their failover ability.