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.

Steps:
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:
http://blogs.msdn.com/b/sambetts/archive/2013/08/22/sharepoint-farm-patching-explained.aspx

***************

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.

Resolution:
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
http://onpointwithsharepoint.blogspot.co.uk/2013/06/configuring-service-connection-points.html
http://sharepointfreshman.wordpress.com/2012/03/02/1-failed-to-add-the-service-connection-point-for-this-farm/
http://gallery.technet.microsoft.com/ScriptCenter/af31bded-f33f-4c38-a4e8-eaa2fab1c459/
http://blogs.technet.com/b/balasm/archive/2012/05/18/configuration-wizard-failed-microsoft-sharepoint-spexception-the-object-ldap-cn-microsoft-sharepoint-products-cn-system-dc-contoso-dc-com-dc-au-doesn-t-exist-in-the-directory.aspx

Thursday, 20 March 2014

Data Protection and Regulation using SharePoint

Overview:  Data protection in relation to SharePoint is a large body of information.  This post outlines my notes on holding data within SharePoint and generally applicable to various regulations I have come across.  Also, see my post on Compliance for O365 and SharePointLast updated: 18 July 2019


Records Management:  Data needs to be disposed of depending on the applicable rules, the rules depend on the industry, country, the 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.  

http://www.computerweekly.com/news/2240114326/EC-proposes-a-comprehensive-reform-of-data-protection-rules 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.  DPA 2018 alignes with GDPR.

Purpose of GDPR (started 25 May 2018):
  • Protect personal data
  • Consistency legislation in the EU
  • Encourage competition between EU countries
GDPR is concerned with EU citizens personal data and protecting it.

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 strategy.  Still need the old school basic defences: 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 highly confidential data, IP or client lists  
Security Centre helps with:
  • Investigation
  • Forensic collection
European Union (including the UK):
  • 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 to 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
United States
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 - Greatly affects companies as the US can request access to data.  This leads to multinationals choosing to host data outside of the US.

Internal State Laws - Each federal state may have localized laws that your business needs to adhere too. For example California Data Privacy Protection Act (CDPA)

Brazil 
LGPD - General Data Protection Law - Basically GDPR for protecting personal data and peoples user privacy.

China has the PRC Cybersecurity Law relating to protecting personal data.
Hong Kong has the Personal Data (Privacy) Ordinance (Ap.486)
Middle East: Bahrain - Data Protection Law (Law No. 30 of 2018), Qatar - Data Protection Law (Law No. 13 of 2016), UAE has Digital payment Regulation and Data protection laws specific to each emirate
Turkey - Law on the Protection of Persoanl Data 6698 (LPPD)
South America, all the major countries have Data protection laws including Argentina (Law 25.326)
Canada - Various state laws pretty much PIPA.
Australia has a host of Data privacy laws including the "Australian Privacy Principles"
Japan has APPI.
South Korea has PIPA

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

Pharma and Medical:
  1. HIPAA - "Health Insurance Portability and Accountability Act of 1996) is United States legislation that provides data privacy and security provisions for safeguarding medical information"
  2. DSP is broadly similar to HIPAA but from the UK, it is a toolkit for compliance for the NHS.
  3. HL7 - "Health Level-7 refers to a set of international standards for the transfer of clinical and administrative data between software applications used by various healthcare providers"
  4. FHIR V4 - replaces HL7 for exchanging data exchange and information modelling standards for over 20 years. FHIR is a new specification like HL7
  5. GxP - Good x Practice e.g. GCP, GMP (manufacturing), CLP, 
  6. GCP - Good Clinical Practice - Guidelines and regs used in the pharma industry
  7. GMP - Good Manufacturing Practice
  8. GAMP - Good Automated Manufacturing Practices - applies to software development.  GAMP has 4 categories.  Part 5 is the most hardcode for companies.  Cat 1 is infrastructure Software, Part 4, for instance, is Configurable software, PArt 5 is for custom software.  So depending on the software you are providing to a customer, you need to be audited by external parties and clients to be compliant.  If you have written your own Software, you need to be GAMP5 compliant.  GAMP is GxP but for IT systems.  Build-in quality, this requires following procedures and principles when building software products.  ISPE - Run GAMP and check qualifications.
  9. Eudralex - Pharma industry in EU guidelines for dev, manufacture and control of medicinal products.  Rules governing medical products in the EU.
  10. EMA - European Medicines Agency, same as FDA but covers Europe.
  11. FDA - Food and Drug Agency out of the US.
  12. MHRA (Medical and Healthcare Products Regulation Agency), same as Eudralex but for the UK.
  13. Title 21 CFR part 11 - FDA reg so that electronic records and signatures equivalent to paper hand signed reconds and consent.  About storing e-records including securing and signatures.  Code of Federal Regulations (CFR) Title 21 Part 11.  Ensure the system is secure, audit logs of all transactions with timestamps maintain the integrity of the open or closed system.  Signatures must ensure non-repudiation (the signer can't claim it wasn't him).  E-signatures can be biometric-based, this is hard for web-based systems without specific hardware.  E-signature that are not biometric-based requires that on the first sign-in all components (general means sign in with username and password) on the first signature assuming the user is already logged into the system.  See Section 11.200 Electronic Signature Components and controls go to point a) (1)(i).  Subsequent e-signatures can 1 component meaning the username. If using biometric signatures each signature uses the biometric method again.
  14. ISO 27001 - ISO is best practices guidelines, not regulations.  27001 is concerned with info security and info assets.  Asses and treatment of security risks.  Also see ISO27002 & ISO27017.
  15. ISO 9001 - Quality Management, checklist / process orientated.
  16. FDA (Food and Drug Agency) is equivalent to EMA (European Medicines Agency) in Europe.  

ISO 27001 for SaaS

It is pretty easy to get ISO27001 certified for SaaS companies and it brings huge benefits if implemented correctly.  Azure provide fantastic documentation and if your product is based on Azure.  It is really easy as the technology infrastructure is validated.  

Azure have a fantastic set of documentation for certifications called Blueprints

https://docs.microsoft.com/en-us/microsoft-365/compliance/offering-iso-27001?view=o365-worldwide

https://docs.microsoft.com/en-us/azure/governance/blueprints/samples/iso27001/











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:
http://go.limeleap.com/community/bid/286331/Forms-in-SharePoint-Seven-Ways-to-Create-a-Form-in-SharePoint
http://blogs.office.com/2013/03/04/options-to-create-forms-in-sharepoint-2013/#comments

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:
http://www.brainlitter.com/2010/06/08/how-to-resolve-event-id-1511windows-cannot-find-the-local-profile-on-windows-server-2008/
http://todd-carter.com/post/2010/05/03/give-your-application-pool-accounts-a-profile/

*************************

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:
127.0.0.1  crl.microsoft.com
2.> Trust the SP root cert
http://support.microsoft.com/kb/2625048

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: http://stevesps.blogspot.co.uk/2013/01/sharepoint-foundation-event-id-8321.html

 *************************

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.

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




http://khalidstech.blogspot.co.uk/2012/11/certificate-validation-errors-in.html

Automation to add the SharePoint Root Certificate is done very nicely in this post: http://lennytech.wordpress.com/2013/06/18/powershell-install-sp-root-cert-to-trusted-root/

 **************

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.

***************