Deleting SharePoint AuditData

As most we have a auditing requirements for when we need to do forensic excercises.  Unfortunatly we turned auditing on with no real thought.  We are now in the situation that we have hundreds of millions of rows of AuditData.  We have attempted to delete this data using the standard powershell methods (SPSite.Audit.DeleteEntries(SomeDate), however we have found this method causes outages as is causing locks at the database.  We have attempted deleting a tiny amount of data (last hour) this does not cause database locks but it would take a year to delete all data.

We have been in contact with Microsoft Premier support and they have given us approval to Truncate the AuditData table, which will save our problems.  You must contact premier support to get specific approval your self or your SharePoint environment will be un-supported.  Below is a script that will backup the database and Truncate the AuditData table for you. 

It goes with out saying but make sure you test this in a non-production enviornment!

DECLARE @DatabaseName VARCHAR(254)
DECLARE @Path VARCHAR(1000)
DECLARE @Truncate VARCHAR(1000)

SET @DatabaseName = 'SP_PP'
SET @Path = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SP2010\MSSQL\Backup\'
SET @Truncate = 'TRUNCATE TABLE AuditData'

--Configure Path
IF (SUBSTRING(@Path, LEN(@Path), 1) != '\')
BEGIN
 SET @Path += '\'
END
SET @Path += @DatabaseName + '-Truncate.bak'

--Backup Database
BEGIN TRY
 BACKUP DATABASE @DatabaseName TO  DISK = @Path WITH NOFORMAT,
 NOINIT, 
 NAME = N'TODEL-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
 PRINT 'Backup has Completed'
END TRY
BEGIN CATCH
 PRINT 'Backup Failed Stopping.................'
 RETURN            
END CATCH
BEGIN TRY
 EXEC ('USE [' + @DatabaseName + '] ; EXEC sp_executesql N''' + @Truncate +'''')
 PRINT 'Truncate Success'               
END TRY
BEGIN CATCH
 PRINT 'Truncate Failed........................'               
END CATCH

SharePoint Email Tester (SPUtility.sendmail)

Formatting email using SPUtility.SendMail is a bit of a nightmare, most development VM's do not have exchange installed on them so I have written a little console application that you can run in an integration environment or production environment (not recommended) and test your email formatting.   You could also use this for testing email to exchange integration is working it is a lot quicker than telneting to port 25 and manually constructing an email to exchange.  How it works:

1. Unzip the files
2. Open up the EmailContent.xml
3. Update the to / from and body etc.
4. Run the exe following the instructions on the screen (1 to send email and 2 to create a new xml file in case you delete it)

To put line feeds in the body of your mail message do the following in the xml file <![CDATA[<br/>]]>

You can download the exe from here and the source code from here

Let me know if you have any issues happy to help out.

User Profile Sync Issues

I just came across an issue in one of our development environments with User Profile Sync. UPS can be the bane of a SharePoint guy's existence if you run into issue with it.  I have spent quite a bit of time with Microsoft Premier Support working on UPS issues they are very well versed in resolving the issues with UPS.  I highly recommend reading this article on the UPS infrastructure it has completely changed since the MOSS 2007 days http://technet.microsoft.com/en-us/library/gg188041.aspx.  Here are my top tips for resolving UPS issues:

Patch your SharePoint Farm
UPS RTM had some "interesting features" which have most likely caused the issue your are currently having the June 2011 Cumulative Update resolved a substantial amount of the issues.  Patch to a minimum of June 2011!

Re-Provision the FIM configuration
This will fix 95% of User Profile Sync issues something will get corrupt with the connection between SharePoint and FIM.  Stopping the User Profile Synchronization Service de-provisions the FIM configuration and starting it re-provisions it.  It does a substantial amount of configuration that is why it takes so long.

Generally the following procedure will resolve the issue:

1. Stop the User Profile Synchronization Service (Central Administration --> Manage Services on server)
2. Wait until the service has completely stopped!!
3. iisreset
4. Start the User Profile Synchronization Service (Central Administration --> Manage Services on server)
5. iisreset

The iisresets appear to be needed due to the ability to reference the Microsoft.ResourceManagement dlls in the GAC.

If this does not resolve your issue you may need an extended outage:

1. Stop the User Profile Synchronization Service (Central Administration --> Manage Services on server)
2. Stop the User Profile Service 
3. Wait for an hour (I have seen configuration changes occur after the services stop)
4. Restart the server 
5. Start the User Profile Service 
6. Start the User Profile Synchronization Service and the User Profile Service
7. Wait for an hour (I have seen configuration changes occur after the services start as well)
8. Restart the server

Some of the error messages I have seen that this will resolve are:
System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.ResourceManagement, Version=4.0.2450.34, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.  

File Not Found

Disable All Content Deployment Jobs

Just a quick script drop we have around 20 content deployment jobs that require disabling and enabling when we are service packing / upgrading.  This number is increasing constantly and will eventually be quite large.  Here is a one liner to disable and re-enable your content deployment jobs, I have two ps1 files on the server (Turn Off Content Deployment Jobs and Turn On Content Deployment Jobs):

Enable
Get-SPContentDeploymentJob | ForEach-Object {$_.IsEnabled = $true; $_.Update()}


Disable
Get-SPContentDeploymentJob | ForEach-Object {$_.IsEnabled = $false; $_.Update()}

Automated SSL Certificate Import (Certutil)

Just a quick script drop.  You can use this script to import SSL certificates.  It will import all the the PFX files in the directory to your COMPUTER\Personal\ Store.  Which is the required store for your SSL Certificates.  It does not import intermediate or CA certificates.  I might add that functionality one day.  Any questions post a comment below:

1. Create a Directory called "SSL" (or something like that)
2. Drop all your PFX files into the SSL folder
3. Create a Batch File "InstallCert.bat" 
4. Run a command prompt as administrator
5. Change Directory to your batch file and run it

Batch File Contents


set CURDIR=%CD%
for /f "usebackq delims=|" %%f in (`dir /b "%CURDIR%" ^| findstr /i pfx`) do certutil.exe -f -p <PFX File Password> -importpfx "%CURDIR%\%%f"
pause

SharePoint 2010 December 2011 Cumulative Update Install Fails

I have found an issue with the installation of SharePoint 2010 December 2011 Cumulative Update.  If you are seeing the following error in you Application Log:

Product: Microsoft Shared Components - Update 'Hotfix for Microsoft Office Server (KB2597014)' could not be installed. Error code 1603. Additional information is available in the log file C:\Users\??\AppData\Local\Temp\osrv-x-none_MSPLOG.LOG.

Open the file and if you search for return value 3 you will find the actual error message just above the return code.  In my situation the error was:


MSI (s) (04:18) [12:33:42:190]: Executing op: End(Checksum=0,ProgressTotalHDWord=0,ProgressTotalLDWord=9722923)
MSI (s) (04:18) [12:33:42:425]: Assembly Error:The process cannot access the file because it is being used by another process.

MSI (s) (04:18) [12:33:42:425]: Note: 1: 1935 2: {E3DD2806-A5AB-43D8-AE84-DFAF878F579C} 3: 0x80070020 4: IAssemblyCacheItem 5: Commit 6: Microsoft.Office.Server.FilterControls,fileVersion="14.0.6108.5000",version="14.0.0.0000000",culture="neutral",publicKeyToken="71E9BCE111E9429C",processorArchitecture="MSIL"
MSI (s) (04:18) [12:33:42:425]: Note: 1: 2205 2:  3: Error
MSI (s) (04:18) [12:33:42:425]: Note: 1: 2228 2:  3: Error 4: SELECT `Message` FROM `Error` WHERE `Error` = 1935
MSI (s) (04:18) [12:33:42:428]: Note: 1: 2205 2:  3: Error
MSI (s) (04:18) [12:33:42:428]: Note: 1: 2228 2:  3: Error 4: SELECT `Message` FROM `Error` WHERE `Error` = 1709
MSI (s) (04:18) [12:33:42:428]: Product: Microsoft Shared Components -- Error 1935. An error occurred during the installation of assembly 'Microsoft.Office.Server.FilterControls,fileVersion="14.0.6108.5000",version="14.0.0.0000000",culture="neutral",publicKeyToken="71E9BCE111E9429C",processorArchitecture="MSIL"'. Please refer to Help and Support for more information. HRESULT: 0x80070020. assembly interface: IAssemblyCacheItem, function: Commit, component: {E3DD2806-A5AB-43D8-AE84-DFAF878F579C}

A simple reboot and re-run of the install resolved the issue.

I am going to do some additional testing to see if I can establish why this requires a restart / re-run.

Kerberos Nuance

N:B Real IP’s and DNS names have been changed to protect the innocent


We are setting up a new Business Intelligence farm 2 WFE’s 1 APP Server and Physical behemoth of a DB server to replace the old farm that is a bit lack lustre in performance .  Luke Welch and I sat down to perform the Kerberos configuration, I have done this a million times normally it goes off without a hitch but run into issues.  Luke came up the suggestion so all credit to Luke for this one.

I am an ex-developer so things on one line are cool still in my head, so I like to do host entries one line for the one IP.  The old URL is bob.compay and we are configuring the new farm on bob2.company for testing purposes.  So the HOST entry looks like this.

192.168.1.140 bob.company bob2.company

After I turned Kerberos Logging on we noticed For some reason it did not work looking at the error log we spotted the following error

The Kerberos client received a KRB_AP_ERR_MODIFIED error from the server svc_bob. The target name used was HTTP/bob.company. This indicates that the target server failed to decrypt the ticket provided by the client. This can occur when the target server principal name (SPN) is registered on an account other than the account the target service is using. Please ensure that the target SPN is registered on, and only registered on, the account used by the server. This error can also happen when the target service is using a different password for the target service account than what the Kerberos Key Distribution Center (KDC) has for the target service account. Please ensure that the service on the server and the KDC are both updated to use the current password. If the server name is not fully qualified, and the target domain (COMPANY) is different from the client domain (COMPANY), check if there are identically named server accounts in these two domains, or use the fully-qualified name to identify the server.

That should read HTTP/bob2.company not bob.company.  Luke thought that the fancy one line HOST entry was causing the issue.  I pinged bob2.company

C:\Windows\system32>ping bob2.company

Pinging bob.company [192.168.1.140] with 32 bytes of data:

Reply from 192.168.1.140: bytes=32 time<1ms TTL=128

Notice the bob resolution in the ping!  So IE was requesting a Kerberos ticket for bob.company instead of bob2.company which the service account svc_bob is not allowed to do.  All we had to do was spit the HOST entry onto two lines and all was fine.

Update-SPSolution Automated Install

Just a quick script drop, I am going to polish this up a bit over the coming weeks.  This script will Update all of the WSP’s in the install folder. It will not add new solutions only update old ones.  How it works:

1. Create the following folder structure (where OutofBand is the name of your release):





2. Drop your wsps you want to release in the install folder
3. Drop your current production wsps in the rollback folder (if you do not have them check this out)
4. Create setup.ps1 in the OutofBand folder with the following code
5. Do not run this from PowerShell ISE, the Get-Location will fail.  This will cause an IISReset


#Release Script for SharePoint 2010 wsps (assumes wsps have already been released)
asnp Microsoft.SharePoint.PowerShell –erroraction SilentlyContinue


[string]$strDeploymentFiles = Get-Location -PSProvider FileSystem;
[bool]$Rollback = $false;


if ($Rollback)
{$strDeploymentFiles += "\rollback\";}
else
{$strDeploymentFiles += "\install\";}


$strd = dir -Recurse $strDeploymentFiles *.wsp;
foreach($wsp in $strd)
{
    [string]$strWSPName = $wsp  
    Update-SPSolution -Identity $strWSPName -LiteralPath "$strDeploymentFiles$wsp" -Force -GAC
}




6. To deploy your solution files simply run setup.ps1 from SharePoint 2010 Management Shell
7. If you need to roll back update the $Rollback variable to $true

Error opening every Document Library in a Web Application

Credit:
My esteemed colleague Andrew Haughan and I worked on this issue together I cannot take full credit for the resolution to this issue.

We have a newly created Web Application that has been configured, trying to open any document library on the site was causing the standard and very helpful "An unexpected error has occurred" SharePoint Correlation ID error message.

A visit to the ULS logs (using ULSViewer of course) uncovered the following unexpected error for that Correlation ID:

System.NullReferenceException: Object reference not set to an instance of an object.   

 at Microsoft.Office.Server.Administration.UserProfileApplicationProxy.get_ApplicationProperties()    

 at Microsoft.Office.Server.Administration.UserProfileApplicationProxy.get_PartitionIDs()    

 at Microsoft.Office.Server.Administration.UserProfileApplicationProxy.IsAvailable(SPServiceContext serviceContext)    

 at Microsoft.Office.Server.WebControls.MyLinksRibbon.get_PortalAvailable()    

 at Microsoft.Office.Server.WebControls.MyLinksRibbon.EnsureMySiteUrls()    

 at Microsoft.Office.Server.WebControls.MyLinksRibbon.get_PortalMySiteUrlAvailable()    

 at Microsoft.Office.Server.WebControls.MyLinksRibbon.OnLoad(EventArgs e)    

 at System.Web.UI.Control.LoadRecursive()    

 at System.Web.UI.Control.LoadRecursive()    

 at System.Web.UI.Control.LoadRecursive()    

 at System.Web.UI.Control.LoadRecursive()    

 at System.Web.UI.Control.LoadRecursive()    

 at System.Web.UI.Control.LoadRecursive()    

 at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

From this error message we can determine that we have an issue consuming the User Profile Service.  A simple overview of our environment is an Enterprise Services Farm running a central User Profile Service Application.  We have several farms that consume this service, to protect the innocent this Farm will be called "Farm A" and the Web Application throwing the Correlation error will be called "Web Application B":

There are many ways to start diagnosing this issue 95% of the time I have found that this issue arises due to lack of permissions of the Web Application's Application Pool identity.  The identity that runs the web application must have permissions to read from the User Profile Service Application.  This was the case in this scenario.  To check the permissions of the user profile service application:

1. Load Central Administration on the Farm that hosts the User Profile Service Application (Enterprise Services Farm)
   
   
   
2. Select Manage Service Applications under Application Management
   
   
   
3. Select just to the right of the User Profile Application (do not click the actual text or it will take you to the management page)
   
4. Click Permissions in the ribbon
   
   
   
   
   
5. Check to ensure the Web Application Identity is not in the list
6. Type the Web Application Identity in the people box and click Add (not OK)
7. Select Full Control
8. Click OK

Hopefully this will resolve your issue.  I have seen the following issues:

• If you are publishing with Threat Management Gateway ensure that you are pointing directly to the Enterprise Services Server and not going into the TMG NIC in the DMZ.  Update your HOST file on the Web Server in Farm A to point directly to the Enterprise Services Server