Wednesday, November 30, 2011

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"}   

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

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


  1. Hi, thanks for your note on my article on 2010 failover.

    That particular article was on a specific failover case involving pausing the underlying storage for a SQL VM hosting the database primary, in which SQL eventually fails over, but SharePoint doesn't until the old primary is taken offline. The closest I got to an answer from Microsoft is that it might be a problem w/ the .Net provider and not SharePoint in particular.

    I have a bunch of other notes on PowerShell scripts for configuring SharePoint 2010 failover if they're helpful.

    My understanding is that the login error on failover is usually that SharePoint knows about the failover partner, but doesn't have permissions to the new primary. I like your solution to re-add w/PowerShell.

    Nice PowerShell scripts! I'm out of the SharePoint game now, but i wish I'd seen your blog a year ago when we were first migrating to 2010. We couldn't find anyone who had actually started a relatively large 2010 migration, although we did get a lot of opinions from people who hadn't actually touched it.

  2. Yes its true that Because I have also face the this type of problem.but here i get the solution for solving this problem.