SharePoint 2010 – ConnectionTimeout issues from WFE to Profile DB

Often (at least when working with SharePoint) you can come across something that just stumps your style and leave you wishing the bad man will go away. I’ve just had such a problem and of course figured I’d project my issues onto you.

Problem – getting this evil error screen in SharePoint 2010.

Coupled with a “ConnectionTimeout” error in the ULS and Event log…

PowerShell_ISE.exe | Database | EventID 880i
– System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding

– at Microsoft.Office.Server.Data.SqlSession.ExecuteReader(SqlCommand command, CommandBehavior behavior, SqlQueryData monitoringData, Boolean retryForDeadLock) 

– SqlError: ‘Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.’    Source: ‘.Net SqlClient Data Provider’ Number: -2 State: 0 Class: 11 Procedure: ” LineNumber: 0 Server: ‘xxx.xxx.xx.xx’

– ConnectionString: ‘Data Source=xxx.xxx.xx.xx;Initial Catalog=xxx_xxx_profileDb_xxx;Integrated Security=True;Enlist=False;Asynchronous Processing=False;Connect Timeout=15’    ConnectionState: Closed ConnectionTimeout: 15

– ProfileEnumerator.PopulateQueue() Exception: System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.   

Scenario:

Multi-server environment (WFE, App/CA, SQL)…
Multi-domain (Claims) environment, using ADFS and local ADDS….

The DMZ is split into (currently, and yes, we are moving SQL into a third zone) 2 zones…

The problem we were seeing was “ConnectionTimeout” errors from the WFE in Zone 1, to the Profile DB (User Profile Service/sync) in Zone 2 each time we were attempting to retrieve a list of profiles via the UserProfileManager.

In order to see what was causing this (obviously we were seeing the error in the ULS and in the Event logs, but we couldn’t see what exactly was causing it) we had to manually retrieve the profiles..the choice was powershell of course.

[void][System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.Office.Server”)
[void][System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.Office.Server.UserProfiles”)
[void][System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SharePoint”)

$site = new-object Microsoft.SharePoint.SPSite(“https://xxx.xxxx.com“);
$ServiceContext = [Microsoft.SharePoint.SPServiceContext]::GetContext($site);
$ProfileManager = new-object Microsoft.Office.Server.UserProfiles.UserProfileManager($ServiceContext)
try {
$AllProfiles = $ProfileManager.GetEnumerator()

If (!($?)) {Throw ” – Facepalm!”}

foreach($profile in $AllProfiles)
{
    $DisplayName = $profile.DisplayName
    $AccountName = $profile[[Microsoft.Office.Server.UserProfiles.PropertyConstants]::AccountName].Value
    $workEmail = $profile[[Microsoft.Office.Server.UserProfiles.PropertyConstants]::workEmail].Value
    write-host $DisplayName, “;”, $AccountName, “;” , $workEmail, “;”
}

write-host “Finished.”
}
catch {
    Write-Warning ” – $($_.Exception.Message)”
    throw “more facepalm!”

}
$site.Dispose()

Ran this from the WFE and voila – we got the ConnectionTimeout error here as well.

Next step was for us to capture the network packages going across the network, because a simple powershell script which just so happen query the same database that the UserProfileManager was connecting to, resulted in a successful query, bringing back the profiles that we were looking for. Ok, so it’s not a port blocked causing this.

After talking to a bright network engineer/architect, we were asked to run WireShark on the WFE and capture the traffic. We did, and the engineer was nice enough to read through the massive lump of data that this had generated. Breaking down the traffic made the engineer see that we had a lot of reverse DNS lookup requests simply not being responded to. Ok, that made sense, because the DNS first off all doesn’t have it configured (internet facing DMZ w/ADDS + DNS) and the “broadcast” from the WFE in order to find the SQL Server (that was the ConnectionTimeout btw) would have been blocked by firewall so the SQL Server would never have seen the request…so, hence…ConnectionTimeout.

A simple way to fix this (it’s not ideal of course, but without getting the reverse DNS lookup setup there really isn’t much choice. Reverse DNS Lookup has it’s own security implications that for this scenario really isn’t ideal) is to add the SQL Server details into the hosts file on the WFE server.

Naturally, once the 3rd zone is created and the SQL Server moved into that zone, this “fix” has to be done for both the WFE and the CA/App server.

SharePoint 2010 FBA Powershell Warmup Script [quick and dirty]

I was browsing around for for a quick and dirty SharePoint 2010 warmup script for Powershell the other day, and found quite a few – however, all would fail for me.

See, the environment i’m running is a FBA (or rather AD + ADFS, multiple domains, no trust) environment and the scripts i found would all throw a 403 Forbidden error.

Figured quickly that this was of course caused by the WebClient call that most scripts utilised, so to overcome this obstacle i changed the WebClient call out with a WebRequest call and added the all-important UserAgent (this is the reason you’d get the 403 Forbidden error btw).

So, quick and dirty solution…still some stuff to do on it, such as ensure that the timeout error can be handled – but that’s for another day.

if (( Get-PSSnapin -Name “Microsoft.SharePoint.Powershell” -ErrorAction SilentlyContinue) -eq $null) {
Add-PSSnapin Microsoft.SharePoint.Powershell
}

function WarmupSite() {
param([System.Net.NetworkCredential]$NetworkCred,[Microsoft.SharePoint.SPWeb]$Web)

start-sleep 1

$request = [System.Net.WebRequest]::Create($Web.Url)
$request.AllowAutoRedirect = $false
$request.proxy = [System.Net.WebRequest]::DefaultWebProxy

$request.Credentials = $NetworkCred
$request.ContentType = “application/x-www-form-urlencoded”

$request.Method=”GET”
$request.UserAgent = “Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322)”

return $request.GetResponse().StatusCode

}

function RecurseSite() {
param([Microsoft.SharePoint.SPWeb]$SiteIdentity)
write-output (“Site: $($SiteIdentity.Url) – $($SiteIdentity.Title)” | format-list)
$cred = [System.Net.CredentialCache]::DefaultNetworkCredentials

if ($SiteIdentity.Webs.Count -gt 0) {
foreach  ($subweb in $SiteIdentity.Webs) {
$output = WarmupSite -NetworkCred $cred -Web $subweb
$output | Out-file -FilePath “C:\isd\output\$($subweb.Title).txt”
RecurseSite -SiteIdenitity $subweb
}
$SiteIdentity.Dispose()
}
else {
$output = WarmupSite -NetworkCred $cred -Web $SiteIdentity
$output | Out-file -FilePath “path to local network folder\$($SiteIdentity.Title).txt”
$SiteIdentity.Dispose()
}
}

$webappservices = (Get-SPFarm).services | ? {$_.typename -eq “Microsoft SharePoint Foundation Web Application” }

foreach ($webapp in $webappservices.WebApplications) {
foreach ($sitecollection in $webapp.Sites) {
write-output “Site collection: $($sitecollection.Url)”
RecurseSite -SiteIdentity $($sitecollection.RootWeb)
}
}

I also added a “start-sleep 1” as SharePoint 2010 would go crazy if i just let it run without it (too many SP objects).

Disclaimer – this code is provided “AS IS”..should be no danger in running it, however, clean it up as it’s a subset of my own code here (doing other things too) and is a cut n’ paste n’ edit.