Pages

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

2 comments:

  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.
    http://www.itwalkthru.com/2010/06/find-unmirrored-sql-2008-sharepoint.html

    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.

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

    ReplyDelete