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. 

Simple concept: How to use SharePoint cmdlets in PowerShell ISE

[Question]:
I heard that I can manage everything in SharePoint 2010 with PowerShell. But when I run Windows PowerShell ISE , it doesn't recognize SharePoint PowerShell commands (ex: Get-SPContentDatabase)

How can I use PowerShell ISE for SharePoint 2010?

[Answer]:
 SharePoint 2010 Management Shell is a just a PowerShell instance that loads sharepoint.ps1 script. So, you are free to use your own favorite IDE to build scripts, just load sharepoint.ps1 (SharePoint 2010: PowerShell and Stsadm)

To get sharepoint.ps1 loaded in PowerShell ISE:

1. Start PowerShell ISE

2. (in case you haven't created a profile yet)
run the following command to create a profile which loads every time you start PowerShell ISE
new-item -path $profile -itemtype file -force 

3. (in case your profile is empty)
the following command writes to profile "." source loading of SharePoint.ps1"

sc  $profile  “.'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\CONFIG\POWERSHELL\Registration\SharePoint.ps1'”

OR use more universal approach which doesn't depend on the SP version (whether it's 14 or 15 (2010 or 2013))

sc  $profile "Add-PSSnapin 'Microsoft.SharePoint.PowerShell'"

4. Restart PowerShell ISE

P.S. In case you don't know where to get PowerShell ISE: PowerShell and SharePoint: What, Why and How

Simple concept: Database is up to date, but some sites are not completely upgraded.

[Question]:
After Mount-SpcontentDatabase, I m having Database is up to date, but some sites are not completely upgraded. on /_admin/DatabaseStatus.aspx. When I click on the /_admin/UpgradeStatus.aspx it shows me the status - Succeeded


How can I know what site collection is not upgraded and how can I upgrade it?


[Answer]:
First of all, to identify what site collection doesn't got upgraded, run:


stsadm.exe -o localupgradestatus > upgradestatus.txt


Open upgradestatus.txt and scroll down to the end of the file, you will see similar section:

In this picture as you see, there no site collection that need upgrade.
If you have any, you can identify them by searching by word "Needs upgrade" in this file.

Secondly, run the command in the PowerShell:


$id=(Get-SPContentDatabase -Identity "{Your content db name}").Id
Upgrade-SPContentDatabase -id $id

Finally, check the upgrade status by running stsadm again:
stsadm.exe -o localupgradestatus > upgradestatus.txt


In my case, it helped me get rid of the site collection "Needs update" warning in the report generated by stsadm.exe -o localupgradestatus


P.S. But still, I am having Status "Database is up to date, but some sites are not completely upgraded." which doesn't bother me since localupgradestatus shows me "0" objects need upgrade.

"SharePoint 2007 to 2010 Upgrade" online project (part 9) : SharePoint service accounts

Here is a bare minimum of the service accounts that I have used for the SharePoint 2010 farm:


SQL Server service account
Setup user account
Application pool account
Service managed account
Default content search access account
SSRS




Automating SharePoint 2010 with Windows PowerShell 2.0 sheds the light on what service accounts are necessary to build a 2010 farm:

"Farm account" - (also reffered to by the SharePoint Configuration Wizard as the Database Connection Account). will be used as the central administration site Application Pool identity as well as the SharePoint Timer Service (SPTimerV4) identity.

Gary Lapointe strongly suggests to user another "Setup SharePoint" account which should be in the Web server local group "Administrators" and should have "dbcreator" and "securityadmin" roles on the SQL server.

You may need to modify the group membership for the farm account in order to start User Profile Synchronization Service


UPDATE: Todd Klindt has an excellent article where he presents his view on what accounts are needed for SP Farm 2010: Service Account Suggestions for SharePoint 2010

Simple concept: How to add SSL certificate

[Question]:
How to add ssl certifate to machine? And how to use it?

[Answer]:
1.

2.Add\Remove Snap-in

Add ->“Certificates”->Computer account->Local computer

3.

4. Select you SSL certificate and  import it.

How to use it:

1. in IIS, you configure https, the imported certificate will appear in the SSL certificate drop box:

Note: SSL certificate is valid for specific host name.

2. in ISA, you can configure to fetch https request even though the balanced web fronts are not configured for https:

SharePoint Load balancing:local settings for a web server

I have configured load balancing for SharePoint through ISA.


My second thought was "How can I check what's wrong with specific SharePoint IIS site if I can't trace where I have been redirect?"


Solution is to set the specific setting on the web server box itself.


Every time you log into a web server and navigate to central administration or web application using the same url as all users do, you will be redirected to specific local IIS site.


In order to achieve:


1. Change the host file:


C:\Windows\System32\drivers\etc\host
Add the following:
{ip  the web server} {host name for CA}
{ip (second) for the web server } {host name for a web app}


2. Required if you setup https with certificates on ISA and want to set HTTPS between ISA and WFE
(Refer to the post Simple concept: How to add SSL certificate)
In case AAM has public url as https, you need to implement https on the local IIS (since you local request will not reach ISA with SSL certificates installed there),
Put the address that you have specified in the host file on the previous step, select the corresponding SSL certificate (the host name for web app should be exact the same as it is in SSL certificate)
ATTENTION: You can skip this step if you configure ISA HTTPS between a user and ISA  and HTTP between ISA and WFE (ISA: How to redirect HTTP to HTTPS, How to set SSL Certificate)

3. The last but not least.
When you try to navigate using the hostname that you put in host file you likely to get the error:401 (Logon Failure: Reason: An error occurred during logon)
To overcome this issue:
You need to register your host names for local IIS Site (BackConnectionHostNames):

KEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0

Restart the IISAdmin service

Integrated Windows authentication: NTLM or Kerberos?

Recently, I have been studied the topic "Kerberos: Why do I need it?"


Here is a quick explanation (from Configuring Kerberos Authentication for Microsoft SharePoint 2010 Products)what is Integrated Windows Authentication and relation to NTLM and Kerberos:

Integrated Windows authentication enables Windows clients to seamlessly authenticate with SharePoint Server without having to manually provide credentials (user name/password). Users accessing SharePoint Server from Internet Explorer will authenticate by using the credentials that the Internet Explorer process is running under — by default the credentials that the user used to log on to the desktop. Services or applications that access SharePoint Server in Windows integrated mode attempt to authenticate by using the credentials of the running thread, which, by default, is the identity of the process.

NTLM

NT LAN Manager (NTLM) is the default protocol type when Integrated Windows authentication is selected. This protocol takes advantage of a three-part challenge-response sequence to authenticate clients. For more information about NTLM, see Microsoft NTLM (http://go.microsoft.com/fwlink/?LinkId=196643).

Pros:

·         It is easy to configure and typically requires no additional infrastructure/environment configuration to function

·         It works when the client is not part of the domain, or is not in a domain trusted by the domain that SharePoint Server resides in

Cons:

·         It requires SharePoint Server to contact the domain controller every time that a client authentication response needs validation, increasing traffic to the domain controllers.

·         It does not allow delegation of client credentials to back-end systems, otherwise known as the double-hop rule. It is a proprietary protocol.

·         It is a proprietary protocol.

·         It does not support server authentication.

·         It is considered less secure than Kerberos authentication

Kerberos protocol

The Kerberos protocol is a more secure protocol that supports ticketing authentication. A Kerberos authentication server grants a ticket in response to a client computer authentication request, if the request contains valid user credentials and a valid Service Principal Name (SPN). The client computer then uses the ticket to access network resources. To enable Kerberos authentication, the client and server computers must have a trusted connection to the domain Key Distribution Center (KDC). The KDC distributes shared secret keys to enable encryption. The client and server computers must also be able to access Active Directory directory services. For Active Directory, the forest root domain is the center of Kerberos authentication referrals. For more information about the Kerberos protocol, see How the Kerberos Version 5 Authentication Protocol Works (http://go.microsoft.com/fwlink/?LinkId=196644) and Microsoft Kerberos. (http://go.microsoft.com/fwlink/?LinkId=196645)

Pros:

·         Most secure Integrated Windows authentication protocol

·         Allows delegation of client credentials

·         Supports mutual authentication of clients and servers

·         Produces less traffic to domain controllers

·         Open protocol supported by many platforms and vendors

Cons:

·         Requires additional configuration of infrastructure and environment to function correctly

·         Requires clients have connectivity to the KDC (Active Directory domain controller in Windows environments) over TCP/UDP port 88 (Kerberos), and TCP/UDP port 464 (Kerberos Change Password – Windows)

Long story short, I have decided that I am willing to spare time and configure Kerberos if I see the farm desperately needs following:

 1. Boost performance

 2. SSRS reports needs to pass a user credentials to external source like this one:

3. BDC uses Web services that needs to pass a user token to the external source

4. Any other type delegation (which is not present in my specific scenario but it might be in your case)

Logon Failure: Reason: An error occurred during logon

This issue occurs if you install Microsoft Windows XP Service Pack 2 (SP2) or Microsoft 


Windows Server 2003 Service Pack 1 (SP1). Windows XP SP2 and Windows Server 2003 SP1 


include a loopback check security feature that is designed to help prevent reflection attacks 


on your computer. 


Therefore, authentication fails if the FQDN or the custom host header that you use does not 
match the local computer name.

Method 1: Specify host names (Preferred method if NTLM authentication is desired)

To specify the host names that are mapped to the loopback address and can connect to Web sites on your computer, follow these steps:

1. Set the
   DisableStrictNameChecking
   registry entry to 1. For more information about how to do this, click the following article number to view the article in the Microsoft Knowledge Base:
   281308  Connecting to SMB share on a Windows 2000-based computer or a Windows Server 2003-based computer may not work with an alias name
2. Click Start, click Run, type regedit, and then click OK.
3. In Registry Editor, locate and then click the following registry key:
   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0
4. Right-click MSV1_0, point to New, and then click Multi-String Value.
5. Type BackConnectionHostNames, and then press ENTER.
6. Right-click BackConnectionHostNames, and then click Modify.
7. In the Value data box, type the host name or the host names for the sites that are on the local computer, and then click OK.
8. Quit Registry Editor, and then restart the IISAdmin service.

Back to the top

Method 2: Disable the loopback check (less-recommended method)

The second method is to disable the loopback check by setting the DisableLoopbackCheck registry key.

To set the DisableLoopbackCheck registry key, follow these steps:

1. Set the
   DisableStrictNameChecking
   registry entry to 1. For more information about how to do this, click the following article number to view the article in the Microsoft Knowledge Base:
   281308  Connecting to SMB share on a Windows 2000-based computer or a Windows Server 2003-based computer may not work with an alias name
2. Click Start, click Run, type regedit, and then click OK.
3. In Registry Editor, locate and then click the following registry key:
   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
4. Right-click Lsa, point to New, and then click DWORD Value.
5. Type DisableLoopbackCheck, and then press ENTER.
6. Right-click DisableLoopbackCheck, and then click Modify.
7. In the Value data box, type 1, and then click OK.
8. Quit Registry Editor, and then restart your computer.

(http://support.microsoft.com/kb/896861)

Simple concept: SSRS authentication

[Question]: How to figure out what authentication does SSRS use? Is Kerberos enabled ?


[Answer]:


Go to the box where SSRS service is running;
"{System drive}:\Program Files\Microsoft SQL Server\MSRS{some numbers}.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config"


Check the section: "Authentication"


For example, the value for SSRS authentication without Kerberos which SSRS service runs under not NetworkService or LocalSystem account:



<Authentication>
<AuthenticationTypes>
<RSWindowsNTLM/>
</AuthenticationTypes>
<RSWindowsExtendedProtectionLevel>Off</RSWindowsExtendedProtectionLevel>
<RSWindowsExtendedProtectionScenario>Proxy</RSWindowsExtendedProtectionScenario>
<EnableAuthPersistence>true</EnableAuthPersistence>
</Authentication>

The explanation of the value is here - How to: Configure Windows Authentication in Reporting Services


More on that: Authentication Types in Reporting Services 

SharePoint 2010 gentelman's tool set (freeware)

I have compiled a tool set for SharePoint 2007 a time go which can help developers to handle SharePoint 2007 issues easily. All mentioned tools are freeware.

I am starting compiling tools for SharePoint 2010:

SharePoint Feature Administration and Clean Up Tool - http://featureadmin.codeplex.com/ - helps to investigate missing features and remove them. The most common task after database attach upgrade is to get rid of 367b94a9-4a15-42ba-b4a2-32420363e018 ReportServer SharePoint 2007 feature. Feature Admin Tool v2.2. makes is fast and easy!

As before, a tremendous help with reading SharePoint logs -ULS Viewer - http://code.msdn.microsoft.com/ULSViewer - ULSViewer allows users to open a ULS log file and display its contents in a user friendly format.

A descendant of The SharePoint Manager 2007 -  http://spm.codeplex.com/  SharePoint manager 2010- still powerful  and "must have" for everybody who touches the SharePoint from administrative side.

A great tool to diagnose farm performance by MS - SPDiag 3.0

An interesting PowerShell solution to automate installation - http://autospinstaller.codeplex.com/

Simple concept:SharePoint document is locked for editing by "another user"

[Question]:
I am trying to edit the document from SharePoint library , getting an error:
The document is locked for editing by "another user".
How I can resolve this issue?

[Answer]:
Annoying...
Need some knowledge on that - The document is locked for editing by another user" error message when you try to open a document in Word
Need an immediate help? -
1. Kill the process WINDWORD.exe
2. Check out the file to the draft folder from SharePoint library

 Edit the document and safe it with checking in.
Next time you will not have problem with editing the document

Search Service Application: Architecture in one page

Search service application consists of the following pieces:

1. Administration interface (_admin/ServiceApplications.aspx)

2. Crawl component

3. Databases (Administration, crawl, property)

4. Index partition (Query component) 

Crawler - a component that browses the contents automatically on a regular basis. The goal of the crawling process is to provide up-to-date data from the data store to the indexer(s).

Indexer -a component that collects and stores crawled data. The goal is to make data available for fast and precise information retrieval during the queries.

Query -  a component that provides UI for entering the user queries, presents the result set to the end users. It communicates with the Indexer component directly to get the result.

(Real World SharePoint 2010: Indispensable Experiences from 22 MVPs)

SharePoint 2007 defined 2 search roles: Query and Index.

With modularity of SharePoint 2010, these functions are defined as components.

The Index role(2007) has been renamed as the crawl component (2010).

In 2010 you have 2 components:

 - Crawl component;

 - Query component.

In 2010 you can make Search fault tolerance by having several crawl and query components (with option "Failover-only Query Component").

To run a Query component on a server, the service "Search Query and Site Settings Service" should be started. (refer to Service Application : Architecture in one picture)

Simple concept: Profile synchronization log file

[Question]:
The profile synchronization failed. I found an error in the Event log:



The management agent  failed on run profile "DS_FULLIMPORT" because of connectivity issues.

 Additional Information
 Discovery Errors       : "0"
 Synchronization Errors : "0"
 Metaverse Retry Errors : "0"
 Export Errors          : "0"
 Warnings               : "0"

 User Action
 View the management agent run history for details.



Where is the management agent?


[Answer]:


C:\Program Files\Microsoft Office Servers\14.0\Synchronization Service\UIShell\miisclient.exe

Service Application : Architecture in one picture

Recently, I have been studying the inner work of Service Applications in SharePoint 2010.
I have discovered several terms that can be confusing if you haven't dived yet into the theory.


Service application -  in the general case, are just a logical concept made up of one or more components, one of which may be an actual Service Application component that defines the configurations for a particular implementation of a specific Service Instance." (Gary Lapointe's book)


Service application (second meaning - most used) - a management interface with some SQL back end (_admin/ServiceApplications.aspx)


Service application Endpoint - IIS Virtual application - a WCF service (a WCF service per a service application)


Service instance  - a dll, register keys, binary files, timer job (_admin/Server.aspx)


(Got Access denied error on _admin/Server.aspx? - Read on Simple concept: Manage services on server link is missing)


Here is a simple picture to see the relationships between the components of the Service Application:

"SharePoint 2007 to 2010 Upgrade" online project (part 8) : How to start User Profile Synchronization service

[What you have]:
You have a 2010 farm.

[What you want]:
You want to pull users from AD into Profiles.

[What you want to know]: 
Here is a beautiful represenation of User Profile Architecture from UPA 2010 : Intro – Part1

In order to get AD connection configured, you want to configure User Profile Synchronization.
Before you get your hands dirty with that, the essential understanding of the User Profile architecture is required.

Terminology:

User Profile Application -  a logical set of functionality that allows to have profiles, and if needed social tagging, my site functionality (note: you can configure whether users will be able to create "my site" -Central Administration  Manage Profile Service: User Profile Service -> Manage User Permissions)

"A key thing to understand is that Service Applications, in the general case, are just a logical concept made up of one or more components, one of which may be an actual Service Application component that defines the configurations for a particular implementation of a specific Service Instance." (Gary Lapointe's book)

( you want more of that? - Service Application : Architecture in one picture)

What is the User Profile Synchronization service anyway? - just a wrapper for the ForeFront Identity Manager (FIM) services.

[What you want to do]:

Make sure that:
1. Start the “User Profile Service” first.
2. Create the “User Profile Service Application”
"You must create the User Profile Service Application while logged on as the Farm Account 
This is generally contrary to well-understood best practices that stipulate that you should never log as the Farm Account, but unfortunately, it is a neccessary eveil due to an issue with how the Service Application is created."
(Gary Lapointe's book)

Now you are ready to kick the User Profile Synchronization server off:

1. Make sure that A farm account is in the Farm Administrator group (/_layouts/people.aspx?MembershipGroupId=3).
2. Add temporarily a farm account into Local Administrators group on the application server where you want to run the User Profile Synchronization service.
3. You logged as a farm account into the application server where you want to start the User Profile Synchronization service.
4. Network service account is a member of WSP_WPG group on that application server
5. Don't forget to reset the application server if you have just added a member to the local group. I even would recommend to restart IIS and SharePoint 2010 Timer.

In case NetBIOS Name and FQDN mismatch:

Before Start User Profile Synchronization Service:

First, enable netbios name 

$UserProfileServiceApp = Get-SPServiceApplication | where {$_.TypeName -eq "User Profile Service Application"}                                                                                           

   $UserProfileServiceApp.NetBIOSDomainNamesEnabled = 1                                                                                                                                             

   $UserProfileServiceApp.Update()    

6. Run User Profile Synchronization service on the same application server where the User Profile Service is running.
7. IIS reset after the provisioning of User Profile Synchronization service
8. The permanent assigment "log on locally" must be granted to the Farm Account (Local Policies->User Rights Assignments - Allow Log on locally).
"Though adding the farm Account into the Local Administrator group does achieve this, do not be tempted to leave the Farm Account in the group as doing so is considered a security risk". (Gary Lapointe's book)

9.Check whether User Profile Synchronization works. If it does, remove the farm account from Local Administrator group, you don't need it anymore.

* Because User Profile Sync Service doesn't work with the managed account, if you ever change the farm account password, it will break the user profile synchronization

**Farm backup will stop the User Profile and after the backup job is done, it will try to start the User Profile Service. If at that time all required conditions (That mention above) don't meet, you will end up with broken USP after the farm backup. (Why SharePoint backups break the User Profile Sync Service and other mysteries solved (Todd Klindt))

[What you want to consider]:

To get more details on the topic - Rational Guide to implementing SharePoint Server 2010 User Profile Synchronization

To know exactly what's going on under the hood - refer to UPA 2010 : Setting up the User Profile Service Application

My personal respect to the  author of the post Forefront Identity Manager & User Profile Synchronization Service. This post is responsible for that I got the User Profile Synchronization running.

To encourage me to write you  helpful posts, you can acquire knowledge from a great book by Gary Lapoint through Amazon associate program. (just click this link and buy Automating SharePoint 2010 with Windows PowerShell 2.0)