Monday, 24 March 2014

Installing CU1 for SharePoint 2013

Overview: I need to upgrade from SP2013 CU June 2013 to SP2013 SP1. 

Tip: SP1 does not require the March 2013 PU to be installed.  In my situation it was already installed.

1.> Check there are no upgrades pending.
2.> Run the SP1 upgrade on each machine in the farm containing the SP binaries.
3.> Ensure the Upgrade is required PS>get-spserver $env:computername).NeedsUpgrade
if True on all SP machines (can also verify on a large farm using CA as shown below) then
4.> PS> psconfig.exe -cmd upgrade -inplace b2b -force  (This will upgrade the SharePoint databases and update the binaries on the 1st machine).
5.> Run psconfig on all the remaining SharePoint servers in the farm.

Result:  The farm should upgrade, my dev farms upgrade however my UAT and Prodcution farms did not complete the upgrade, the fix is shown below.

More Info:


Problem:  The Usage and Health database cannot be in an AOAG when upgrading.
 ERR          Failed to upgrade SharePoint Products.
An exception of type System.Data.SqlClient.SqlException was thrown.  Additional exception information: The operation cannot be performed on database "SP_UsageAndHealth" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
ALTER DATABASE statement failed.
System.Data.SqlClient.SqlException (0x80131904): The operation cannot be performed on database "SP_UsageAndHealth" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
ALTER DATABASE statement failed.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler,

Tip: Any CU, PU or SP will not perform the upgrade if the Usage and Health SharePoint database is a AOAG database.  You need to remove the db and perform the upgrade.

Initial Hypothesis:  The error message is pretty clear that the problem is the UsageandHealth database can't be modified in the upgrade process if it is part of the availability group.  I use an aliase so I could repoint the aliase to the primary database do the upgrade and then update the SQL aliase back to point to the listerner or the approach I use is to remove the AOAG listener for the usage database, perform the upgrade to SP and readd the AOAG for the HealthandUsage database.

1.> "Remove the UsageAndHealth database from the Availability Group",

2.> Perform the SP1 upgrade
3.> Change the Recovery model to "FULL" and perform a Full backup.
4.> Add the database back in as part of the availability group.


Problem: When running PSConfig to upgrade my SP2013 farm to include SP1, the upgrade fails and the PSConfigDiagnostic log informs me of the problem:
WRN Unable to create a Service Connection Point in the current Active Directory domain. Verify that the SharePoint container exists in the current domain and that you have rights to write to it.
Microsoft.SharePoint.SPException: The object LDAP://CN=Microsoft SharePoint Products,CN=System,DC=demo,DC=dev doesn't exist in the directory.
at Microsoft.SharePoint.Administration.SPServiceConnectionPoint.Ensure(String serviceBindingInformation)
at Microsoft.SharePoint.PostSetupConfiguration.UpgradeTask.Run()

More Info

Thursday, 20 March 2014

Data Protection using SharePoint

Overview:  Data protection in relationship to SharePoint is a large body of information.  This post outlines my notes on holding data within SharePoint.  For me holding data is more a process issue, once this is clarified SharePoint can be used in multiple ways to help comply with legal requirements.  Also see my post on Compliance for O365 and SharePoint.

Records Management:  Data needs to be disposed of depending on the applicable rules, the rules depend on the industry, country, category of data.  AvePoint has good records management and governance tools to help with the disposal/cleanup of data.

Search: Request for Information (Freedom of Information (FOI)).  SharePoint can be used to traverse over multiple systems/LOB to determine where information is held about individuals.  Configure to generate reports or as a starting point in trawling data in the enterprise.


United Kingdom:
Updated 24 May 2016 - The European union (EU) General Data Protection Regulation (GDPR) "intends to strengthen and unify data protection for individuals within the European Union (EU). It also addresses export of personal data outside the EU" Wikipedia 
The EU GDPR applies to EU member states such as the UK, Germany et al. and covers personal data held by companies in the EU and extends to companies holding EU citizens data.  Of interest for the GDPR is that companies can be fined up to 4% of turnover.  SharePoint and Office 365 holds a lot of company assets and data and appropriate protection needs to be in place.  Part of any companies active Defense needs to include SharePoint.  Of note here is Office 365 have fantastic capabilities in defense and I believe will increase the speed enterprises move to the cloud. New EU Data Protection Directive not yet legally binding.  Companies in the UK are bound by the Data Protection ActFreedom of Information Act 2000  also plays a part with personal data. DPA 1998 explained.

DLP has a module for Health Records that adheres to the U.K. Access to Medical Reports Act 

G-Cloud allows public sector organizations to buy cloud services, from a range of suppliers on a validated secure network.  In effect it is cloud services for local and central government.  G-Cloud in effect offers the cloud (think AWS & Azure type services) to government bodies.  Updated: 16 March 2016, the G-Cloud has been abandoned.

Dealing with Breaches:
SharePoint holds a ton of company data and needs to be part of any companies Active Defence staregy.  Still need the old school basic defenses: Firewall, Intrusion detection, and anti-virus. Do you have a list of critical applications and data within SharePoint?  Do we know who we do business with (client or HR could compromise our data)?  Who is likely to attach?  Employee, organised crime, ... and what happens when we are compromised?  (Do we shut down or restrict,  how do we identify, legal and forensics, communication plan). DLP can help with breaches:
PII data
Theft - are employees mining SP data looking for high confidential data, IP or client lists  
Security Centre helps with:

  • Investigation
  • Forensic collection

European Union (including the UK):
Not yet binding in the UK, but it will be
  • Companies will be required to appoint data protection officers if more than 250 employees.
  • Organisations will have to notify citizens in plain language what information is collected and how it is used as well as explicitly get consent before using any personal information.
  • Users of online services must also have the right to be forgotten, which means they must be able to remove or delete personal information from an online service.
  • Clear rules for data transfer across borders within multinational corporations with a streamlined process that once approved by one data authority, will be accepted by all others.
  • Requiring organisations to notify the national data protection authority and all individuals affected by a data breach within 24 hours.
  • Businesses operating in more than one EU country will, however, welcome the fact that they will be subject to oversight from one supervisory authority rather than multiple authorities
  • Once the directive is accepted companies will have 2 years to comply.
  • Organisations will only have to deal with a single national data protection authority in the EU country where they have their main establishment. Likewise, people can refer to the data protection authority in their country, even when their data is processed by a company based outside the EU. Wherever consent is required for data to be processed, it is clarified that it has to be given explicitly, rather than assumed.
  • People will have easier access to their own data and be able to transfer personal data from one service provider to another more easily
  • A ‘right to be forgotten’ will help people better manage data protection risks online: people will be able to delete their data if there are no legitimate grounds for retaining it.
  • EU rules must apply if personal data is handled abroad by companies that are active in the EU market and offer their services to EU citizens.
  • Penalties of up to €1 million or up to 2% of the global annual turnover of a company.
South Africa:

What is POPI?
Protection of Personal Information (POPI) is the legal requirement in South Africa for holding, collecting, distribution, amending and destruction of information involving people and companies. POPI controls how your personal information is used by organizations, businesses or the government.
With so much personal data held by an increasing number of companies, there needs to be some benchmark for companies to follow if they are to ensure that data is handled legitimately. POPI provides the laws/framework to guide how companies must store personal data relating to people and companies that it holds in either electronic or paper form.
In a nutshell, when holding parties personal data POPI attempts to enforce:
  • transparency
  • only collect information that you need
  • ensure the data is protected/secure
  • ensure the personal data help is correct, required and up to date
  • discard data when it is no longer needed
  • ensure the end person/subject has given his/her explicit consent to keep and use their personal data
  • allow the end person/subject to see their own data that you hold if they request it

Why is should you adhere to POPI?

  • Customer confidence is improved
  • No superfluous data is stored
  • Data is more secure, accurate and old data is expired
  • Avoid criminal and civil actions

What you need to do?

POPI applies to all IT and paper based data that your company holds.  Your company will take steps to ensure the security of personal data which are held in electronic and paper form.  You must prevent the unauthorized disclosure of data to third parties, and loss or damage to data that may affect the interests of end person/subjects.  You will also ensure that data processors your organization uses provide an appropriate level of security for the personal data which they are processing on your behalf.  Any data must be restricted to the appropriate person and your company needs to take steps to ensure it is not allowing unauthorized access to data and information.

What happens if you Violate POPI - EY South Africa
FATCA requires a financial institution to identify and report US customers. 

Safe Harbour  - US companies storing EU customer data would self-certify that they adhere to 7 principles to comply with the EU Data Protection Directive and with Swiss requirements. Overturned in 2015.  The EU-US Privacy Shield is an agreement between the European Union and the United States to enable US businesses to store EU citizens personal data that complies with EU privacy laws.  EU-US Privacy Shield in effect the replacement to safe harbour agreement.  

Patriot Act - 

Common Reporting Standard (CRS), same idea as FACTA but not just US customers, heavier and most of Europe and others.  "CRS is a globally co-ordinated approach to the disclosure of income earned by individuals and organizations outside their country of tax residence",

Tuesday, 11 March 2014

Capturing data for SharePoint

I got an email from an old school friend that heard I may do some SharePoint stuff. 

"I need your advice on a Sharepoint question.  We have a client that need users to capture forms and the ablity to create new forms on the fly, does this sound possible?"

My dashed off reply is below  - comments are welcome

On the SharePoint thing, this is the deal with forms. InfoPath was the standard for creating web forms for SharePoint, saying that about 3 weeks ago, MS announce it is no longer the product of choice and it will not be support after 10 years. It really comes down to how hectic the requirement is where you want you data stored.

SharePoint out of the box allows for users to create lists, this are not too complex and the logic is generally pretty simple. It works really well if your requirement is simple web forms, lots of them and not relational data. All native, very little training but customizing the default look and functionality gets expensive real quick (inject custom JS), there is also a tool SharePoint designer that can be used to customers the forms. When you create a list the CRUD forms are all created for the list.

InfoPath - tool to draw forms custom logic, lots of issues when it get complicated but if you need a lot of forms fast and need some logic this is still a good option.

K2 and Ninetex have forms engines, I have used smartforms from K2, For forms for workflows and building complex forms this is a good option but more if you have a dedicated forms team/guy. If you told me you need 1000 forms with complex logic and more forms need to be added in time, there is workflow and you will have dedicate form requirements this is a good option but be careful it is not as easy as folks may make out.

Pdf share forms work with SP, so if your client has pdf forms - make sure you look at this.  I've never used this approach but it seems plausible.

Custom options, such as SharePoint Designer aspx, you can build and deploy aspx pages, slow but good for customization. Good option if you have a unique complex requirements (think a drawing tool) as basically you have full C# control. You can also create web apps and consume them in SharePoint.

With what I think your skill sets is at .., it is probably also worth looking at using MVC or creating the forms in .NET code (webforms), then display using iFrame or the new app model in SP2013. You secure the app using claims based auth/OpenId/OAuth.
Those are your basic options. Send me some more detail and I can try give you a clearer match.

Also see:

With SharePoint 2013, look at CSR.

Ultimate Forms from InfoWise offers a good option for form capture and output.
Stratus Forms looks like a great tool.

Update 09 March 2016:
·         SharePoint 2016 on-prem. shall support InfoPath Forms Services until 2026 (extended support only from 2021).
·         InfoPath Forms on Office 365  supported until further notice.
·         No InfoPath 2016 as part of Office 2016, use the InfoPath 2013 desktop application to build forms.

Friday, 7 March 2014

EventLog Error Fix

Overview: After building my farms I trawl through the ULS and event logs to look for logs messages to identify any issues.  This post contains errors from my event logs that hopefully will help me in future.

Problem: My event log shows a Windows/IIS error whereby the IIS sites application pool uses a service account that does not have a user profile on the machine.  The error message reads "Event Id: 1511 Windows cannot find the local profile and is logging you on with a temporary profile. Changes you make to this profile will be lost when you log off."

Verify the issue:

Resolution: (IEDaddy's post gave me the resolution)
1.> Stop the processes that use the account (I stopped the web sites that used the application pool account "demo\OD_Srv")
2.> cmd prompt> net localgroup administrators demo\OD_Srv /add
3.> cmd prompt> runas /u:demo\OD_Srv /profile cmd
4.> in the new cmd prompt run > echo %userprofile%
5.> Check the user profiles and verify the profile store for the account (demo\OD_Srv) has a status of "Local"
6.> Remove the account from the local administrators group ie cmd> net localgroup administrators demo\OD_Srv /delete

More Info:


Problem: EVENT ID: 8321 - Task Category: Topology
A certificate validation operation took 120053.1569 milliseconds and has exceeded the execution time threshold. 

Resolution:  I performed various steps:
1.> Host entry add the host entry:
2.> Trust the SP root cert

Import the Trusted certificate

3.>  Reduce the time when the crl check is done (not a fix but it will fail quicker and carry on)

This post may also help - but it wasn't my issue:


Problem: Event Id: 8313 - Task Category: Topology
A failure was reported when trying to invoke a service application: EndpointFailure
Process Name: w3wp
Process ID: 5640
AppDomain Name: /LM/W3SVC/1647355528/ROOT-1-13036555135555957
AppDomain ID: 2
Service Application Uri: urn:schemas-microsoft-com:sharepoint:service:649a3e7c090555059555c7a101555576#authority=urn:uuid:55b29cf855594c76555658fca66dac65&authority=https://sv-sp-web1:32844/Topology/topology.svc
Active Endpoints: 2
Failed Endpoints:1
Affected Endpoint: http://sv-sp-app2:32843/649a3e7c0904495552e4c7a555d64555/MetadataWebService.svc

Initial Hypothesis: It looks like the Web front ends cannot coomunicate with the MetadataWebService.svc, run mmc > file > add/remove snapin > snap-in "certificates" > Add > Computer Account > Local Computer > OK.
Expand "Certificates" > SharePoint > Certificates.  Open the certs and check if they are verified.  In my case my wfe's are good but my app servers do not have a valid certificate as shown below.

PS> $rootCert = (Get-SPCertificateAuthority).RootCertificate
PS> $rootCert.Export(“Cer”) | Set-Content C:\SharEPointRootAutority.cer –Encoding Byte

Automation to add the SharePoint Root Certificate is done very nicely in this post:


Disable CRL check (I believe this is from AutoSPInstaller)
Set-ItemProperty -path "HKCU:\Software\Microsoft\Windows\CurrentVersion\WinTrust\Trust Providers\Software Publishing" -name State -value 146944
set-ItemProperty -path "REGISTRY::\HKEY_USERS\.Default\Software\Microsoft\Windows\CurrentVersion\WinTrust\Trust Providers\Software Publishing" -name State -value 146944
get-ChildItem REGISTRY::HKEY_USERS | foreach-object {set-ItemProperty -ErrorAction silentlycontinue -path ($_.Name + "\Software\Microsoft\Windows\CurrentVersion\WinTrust\Trust Providers\Software Publishing") -name State -value 146944 


Problem: Event Log is capturing EventId: 2159 Source: SharePoint Foundation Error message refers to Event 8306 within the ULS logs.

Resolution: Edit the web.config allowing the ULS to capture additional information relating to the error.  The resulting error show the common SharePoint COM class factory error.  In this scenario changing the "SecurityTokenService" app pool "Load User Profile" property to true correct the underlysing issue.