Wednesday, December 13, 2017

SharePoint web services always use forms authentication when forms-based authentication with an LDAP provider configured

After we configure forms-based authentication with an LDAP provider for a new SharePoint 2013 web application, users could select either window or form login to SharePoint. However, we have issue to use any SharePoint web services through window accounts. The problem is SharePoint is always try to authenticate the user as form based authentication after debugging with Microsoft. Here is one simple Powershell to illustrate the issue.

#########################################
if ( (Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null )
{
            Add-PSSnapin Microsoft.SharePoint.PowerShell
}

$spserver = "mysharepoint.com";
$sharePointAuthUri = "https://$(spserver)/_vti_bin/authentication.asmx?wsdl";

$sharePointAuth = New-WebServiceProxy -uri $sharePointAuthUri
$sharePointAuth.CookieContainer = New-Object System.Net.CookieContainer
$authResult = $sharePointAuth.Login("username","password")
$mode = $sharePointAuth.Mode()  # Always Forms
 ########################################


The login in the dump also indicates SharePoint try to authenticate the user as LDAP users.

  0x0000006866232a38 System.IdentityModel.Tokens.UserNameSecurityToken
  0000  id            : 00000068662329e8  "LdapMember:LdapRole:True" [24] (System.String)
  0008  password      : 00000068662322c0  "********" [8] (System.String)
  0010  userName      : 0000006866232298  "username" [6] (System.String)
  0018  effectiveTime : 0000006866232a58 11/29/2017 6:13:54 PM (System.DateTime)

There are some other discussions also confirmed this behavior. Microsoft support from SSRS team confirmed this is the behaviors for SSRS web service.

We are working with Microsoft try to see if there is way to authenticate window user on SharePoint forms-based authentication web application. The workaround for any web service against SharePoint forms-based authentication web application is to use the from-based account.


Tuesday, November 28, 2017

The "disaster" of SharePoint Server 2016 MinRole

MinRole is a new farm topology based on a set of predefined server roles introduced in SharePoint Server 2016. When configuring your SharePoint farm, you now select the role of a server when you create a new farm or join a server to an existing farm. SharePoint will automatically configure the services on each server based on the server's role. SharePoint Server 2016 has been optimized for the MinRole farm topology.

After we followed the Microsoft instructions to configure the SharePoint 2016 servers as Front-end with Distributed Cache, Application, Application with Search, we found one issue. All wsp solutions deployed to ALL the servers including WFE, search and app servers!



After looking at the server role in details, we found “Microsoft SharePoint Foundation Web Application” service enabled as default for “Application” role and “Application with Search” role. This will cause few major issues but not limited as listed below.


1. Third party license issues. Most of the third party solutions are licensed by WFEs that is defined that server is running “Microsoft SharePoint Foundation Web Application” service. You may run into license issues if all servers have the service running.

2. Deployment performance issue. The custom solution will take significant time since it will deploy to all servers. We had this issue for one small solution deployment.

3. Potential security issues. The application servers or the search servers can also accept the call event they may not intended to server the end users request. This may cause security concerns since most WFE are behind load balancer.

4. Maintainability issues. The application server and search server will have all files related to custom solution deployed. It’s difficult to manage the configurations.

We have seen people to create custom role to avoid this issue. 

It looks like a "disaster" to include the “Microsoft SharePoint Foundation Web Application” service in the “Application” MinRole.

We have discussed with Microsoft to move the following two services to WFE so we have two custom MinRoles for WFE and App servers. If you need to migrate the default MinRole to custom MinRole, you could refer following two articles.


Friday, November 10, 2017

How to resolve "EPERM: operation not permitted" error when installing Yeoman SharePoint generator?

The SharePointFramework (SPFx) is a page and web part model that provides full support for client-side SharePoint development, easy integration with SharePoint data, and support for open source tooling. With the SharePoint Framework, you can use modern web technologies and tools in your preferred development environment to build productive experiences and apps that are responsive and mobile-ready from day one. The SharePoint Framework works for SharePoint Online and soon also for on-premises (SharePoint 2016 Feature Pack 2).

One step to Set up your SharePoint client-side web part development environment is to Install Yeoman SharePoint generator. We run into the issue the installation failed with the following error.

 131672 verbose stack Error: EPERM: operation not permitted, rename 'C:\Users\harryc\AppData\Roaming\npm\node_modules\@microsoft\generator-sharepoint' -> 'C:\Users\harryc\AppData\Roaming\npm\node_modules\@microsoft\.generator-sharepoint.DELETE'


Here are some steps to help you to resolve this or workaround this issue.

1. First, verify to run the script as administrator.

2. Second verify the npm and node version are in supported version.  You can use the following command to verify the versions. Install the support versions, clean up the npm cache, and try again.

npm -v
3.10.10

node -v
v6.12.0

 npm cache clean --force

3. Disable anti-virus. If you have anti-virus (Avast) software, disable it before installation as in other blog.

4. Stop Windows Defender. If you have Windows Defender running, you could as administrator to stop it during the installation.

5. Copy the Yeoman SharePoint generator files from another machine. If none of the above options are available for you, the workaround I used to work around this issue is to copy the Yeoman SharePoint generator files. Here is the details and tricks.

Install the Yeoman SharePoint generator on different machine (VM 2012 R2 for example) that has no issue. Copy all the folders and files under to the machine that failed to install.

C:\tools\node-v6.12.0-win-x64\node_modules\@microsoft\*.*

If you try to copy the everything in one copy, you might run into copy error. I would recommend to copy few folders and files under the ..\@microsoft\generator-sharepoint\node_modules folder at a time.


After you have all folders and files copied, you could use this machine to develop The SharePoint Framework (SPFx) now.

Thursday, November 2, 2017

How to workaround "Sorry, we are having trouble connecting to the server" error from Chrome and Edge for SharePoint 2016

After configured SharePoint 2016 with FP2, we have seen  "Sorry, we are having trouble connecting to the server" error in many places using Chrome and Edge. This error does not happened when you are using Firefox or IE.  Here are the findings and workaround.

1. Here are some functions but not limited to you will receive this error. 
  • Add users to any permission groups like administrator, the error displayed when you typing the user but could not resolve the name. (See picture #1)
  • Unable to add any list item to the list (See picture #2)
  • Unable to delete any list item from list view
  • Unable to share documents with anyone
  • Unable to add list template to list template gallery
  • Unable to delete list template from list template gallery
  • Constantly getting error "Sorry, we are having trouble connecting to the server".



2. Debugging error from fiddler. 

If you debug the issue through Fiddler, you will find the “403” error and the “Origin” under Security of the request Header is dropping the port number like this below.
“Origin: myprojectsite.mycompany.com”

3. Steps to reproduce the issues.

We did further debugging with Microsoft and identified this is new security “feature” implemented in SharePoint 2016 that is causing this issue. This is impacting any web applications with SSL that is not in default 443 port number. Here is the step to reproduce the issue.

In SharePoint 2016 create two web applications with root site collections and then enable for SSL
One web applications use default port number and another is something like 51000.
In central admin go to  alternate access mapping settings and add internal URL mapping to the second new web application.  Use the same name and append a port number.
Example map:

Internal URL
Zone
Public URL
https:\\myprojectsite.mycompany.com
Default
https:\\ myprojectsite.mycompany.com
https:\\myprojectsite.mycompany.com:51000
Default
https:\\ myprojectsite.mycompany.com

Go to IIS on the WFE and edit binding, change 443 to port 51000 and apply SSL cert to binding.
On load balance device configure for port redirection and SSL offload
Configure device to listen for https:\\myprojectsite.mycompany.com
Configure device to send traffic to WFE node as https:\\myprojectsite.mycompany.com:51000
Browse to the site as https:\\myprojectsite.mycompany.com
Site settings -> People and Groups ->New ->Add user, people picker should be present now. Type a user name and press to activate name resolution.
Error message "Sorry, we are having trouble connecting to the server" will be displayed

We have tied to add a Custom Rule in the fiddler like below and the issue can be resolved.
    static function OnBeforeRequest(oSession: Session) {
        if ( oSession.HostnameIs("myprojectsite.mycompany.com") && oSession.uriContains("/ProcessQuery")) {
            oSession["ui-bold"]="true";
            oSession.oRequest["Origin"]="https://myprojectsite.qualcomm.com:51000";
        }
       // …
 }

4. Multiple options to work around this issue.

Now we have few options to work around this issue. Here are the options confirmed with Microsoft.
  • Option 1 - Create a rule in the Load balancer
  • Option 2 - Use the same SSL certificate on all the web applications in the farm using a SAN configuration and configure all the web applications to use port 443 and a host header
  • Option 3 - Configure all the VIPs in the LB to forward to the SharePoint servers on port 443 instead of the port the web applications is actually listening on
  • Option 4 - Configure the SharePoint servers to have multiple IP addresses for each web applications so they all can use port 443.


We have implemented the option one by adding the following rule to the Load balancer.
IF the hostname = "myprojectsite.mycompany.com " && the URI contains "/ProcessQuery”

You might try other options that should also resolve the issue.

Thursday, August 17, 2017

Ultimate debugging guide for SharePoint 2013 on-premises Office Web Apps (OWA)

When used with SharePoint 2013 on-premises, Office Web Apps (OWA) provides updated versions of Word Web App, Excel Web App, PowerPoint Web App, and OneNote Web App. Users can view and optionally edit Office documents by using a supported web browser on computers and on different mobile devices, such as Windows Phones, iPhones, and iPads. In addition to Office documents OWA can also support PDF view in browser.


After you follow Microsoft instructions to install OWA to facilitate users to view different documents in browser, users might run into issues quite often. Here is the ultimate debugging guide for SharePoint 2013 on-premises Office Web Apps when you have issues.

1. Whenever you have issues on OWA, the first thing to check is if OWA is available. You could use the following URL to verify if OWA is available. If the OWA is available, the result should be similar as the screenshot.

http(s)://servername/hosting/discovery


2. Next you will need to verify if SharePoint still connect to OWA servers. You could run the following command from SharePoint WFEs.

Get-SPWOPIBinding -Server "OWASERVER.MYCOMPANY.COM"  (If you know the OWA server URL)

Get-SPWOPIZone | Get-SPWOPIBinding (If you do not know the OWA server URL)

The result should be list of file type configured for OWA to support.

3. The third step is to check the  OWA zone by running the following Powershell.

Get-SPWOPIZone

If the zone is not correct, please set to the correct like this in our environment.

Set-SPWOPIZone -zone "external-https"


4. The third step is to very OWA version. We found different OWA versions may support different type of the files. The easiest way to verify OWA version is to run the following Powershell. You can find the OWA version from the result as highlighted.

(Invoke-WebRequest https://owatst.qualcomm.com/op/servicebusy.htm).Headers

Key                                                                                                Value
---                                                                                                -----
X-CorrelationId                                                                                    dfed84e9-ca53-4be6-bec8-399d5e61c9fc
X-OfficeFE                                                                                         5632742190694a8bba2353f6ddea79d8
X-OfficeVersion                                                                                    15.0.4937.1000
Accept-Ranges                                                                                      bytes
Content-Length                                                                                     6241
Cache-Control                                                                                      public,max-age=31536000
Content-Type                                                                                       text/html
Date                                                                                               Thu, 20 Jul 2017 17:22:54 GMT
ETag                                                                                               "01bbdc714e9ce1:0"
Last-Modified                                                                                      Sun, 24 Nov 2013 12:57:50 GMT
Server                                                                                             Microsoft-IIS/8.0
X-Powered-By                                                                                       ASP.NET


There are two other way to verify OWA build number you might use. 

A. If you are end user and do not know how to use Powersell, you can use browser  to hit the URL like below in the browser.

https://owaserver /op/servicebusy.htm

Then you can use Fiddler or F12 to check the request header header as described in the article.

B. You can also use server Powershell to verify the OWA version as described here.

5. The forth step is to verify if any file type are suppressed in the OWA. You can run the following Powerhsell. You should find the suppressed file type as the screenshot.

Get-SPWOPISuppressionSetting 


When OWA Server view mode is used to view workbooks, the following BI features will not be available.


  • Excel Web Access Web Part
  • Refresh OData connections
  • View and interact with Power View reports
  • View and interact with PowerPivot data models
  • Refresh PowerPivot data models
  • Refresh data by using the Excel Services unattended service account
  • Refresh data by using Effective User Name connections
  • Kerberos delegation
You will following the instruction provided by Tom to suppress the excel from OWA to avoid these issues. The Powershell is described in Tom's Microsoft TechNet site.
x


Get-SPWOPISuppressionSetting
New-SPWOPISuppressionSetting -Extension "XLS" -Action "view"
New-SPWOPISuppressionSetting -Extension "XLSM" -Action "view"
New-SPWOPISuppressionSetting -Extension "XLSX" -Action "view"
Get-SPWOPISuppressionSetting 

6. The fifth step is to verify the web application "Permissive mode in browser file handling" setting. You could refer this article to verify.

7. The sixth step is to verify if site collection feature "Open Documents in Client Applications by Default" enabled. You could refer to Microsoft instruction to enable it from Powershell or UI.

8. The seventh step is to verify the library setting if  you have correct setting for "Opening Documents in the Browser".

One note to point out is OWA upgrade or patch process. You have to pull the OWA server and upgrade or patch. After that you need to bind the SharePoint server again! Remember to do this after the upgrade or patch. You could use the steps listed above to verify the OWA after each upgrade or patch.

OWA provides document view/edit inside the browser. After you know the tips and tricks, users can enjoy this function.

Monday, June 19, 2017

Procedure to restore SharePoint 2013 access apps in the same farm from database without impacting other apps

In previous blog, I’ve provided the procedure to restore SharePoint 2013 access apps after deleted in the same farm from database backups. The approach is to overwrite all the four databases including site content, access app, app management service, and secure store services. The issue for that process is it will overwrite all the whole app management and secure store services database. As a result, any new or updated app might be impacted. For example, the newly added access will not be recover after this process.

In this article, I’ll provide a different way to restore SharePoint 2013 site with access apps. However, sicne this involves database insertion, this might not be supported by Microsoft SharePoint support contract. Please contact your Microsoft support before using the approach!!!


Before we explain the procedure to restore SharePoint 2013 site with access apps in the same farm, let’s create a team site with an access app. The site has the following url “http://spsbx15/sites/APP-DR” and the Access App is named “Harry-APP-Testing” as out of box Customers table. Before we delete the access app “Harry-APP-Testing” and start the restore, let's complete the preparation steps. We will need all this information during the restore.

1. Preparation step #1, we will need to get the access apps App Ids from the site collection. You could use the following Powershell.

         $appInstances = Get-SPappinstance -web $siteUrl

        if($appInstances -ne $null)
        { 
                foreach ($eachAppInstance in $appInstances)
                {        
                    $title = $eachAppInstance.Title
                    $version = $eachAppInstance.App.VersionString
                    $AppPrincipalId = $eachAppInstance.AppPrincipalId
                    $Id = $eachAppInstance.Id
                    $Status = $eachAppInstance.Status
                    $AppWebFullUrl = $eachAppInstance.AppWebFullUrl

                    Write-Output "$title; $version; $Id; $AppPrincipalId; $Status; $AppWebFullUrl"| Out-File $reportName -append 
                    Write-Output $eachAppInstance
    
                }
        } 

The result is listed below and please record the App Id.
  • Site Url                 http://spsbx15/sites/APP-DR
  • App Name            Harry-APP-Testing
  • App Id                  ddbe8814-5cdf-48a4-9322-a53cad392c17
  • App Type             Access App

2. Preparation step #2, we will need to get the access apps ReferenceIds, AppInstallationId from the site collection. You could use the following SQL query on the site content DB.

SELECT TOP 1000 [AppInstallationId]
      ,[DatabaseName]
      ,[ReferenceId]
      ,[TargetAppId]
      ,[AppSource]
      ,[SiteId]
  FROM [WSS_Content_APP-DR].[dbo].[AppDatabaseMetadata]

The result is listed below and please record the App ReferenceId. The AppInstallationId,  DatabaseName, and TargetAppId will also be used in other restore steps.
  • AppInstallationId  DDBE8814-5CDF-48A4-9322-A53CAD392C17
  • DatabaseName      db_ddbe8814_5cdf_48a4_9322_a53cad392c17
  • ReferenceId           0C904934-D29E-48A2-893E-C37472CB07AD
  • TargetAppId          AppDBCredentials_ddbe8814_5cdf_48a4_9322_a53cad392c17
  • AppSource            4
  • SiteId                        A44B0A1A-0FF0-4470-9D43-40DB0C6891CE

3. Preparation step #3, we will need to get the access apps secure store entries from the secure store service DB. You could use the following SQL query on the secure store service DB.

SELECT TOP 1000 [ApplicationId]
      ,[PartitionId]
      ,[ApplicationName]
      ,[FriendlyName]
      ,[ApplicationType]
      ,[TicketTimeout]
      ,[ContactEmail]
      ,[CredentialManagementUrl]
FROM [SVC_SecureStore].[dbo].[SSSApplication]
Where PartitionId = '0C904934-D29E-48A2-893E-C37472CB07AD'  

Please note the PartitionId is the ReferenceId form step #2. The result is listed below.

ApplicationId                                 C5FD28F7-41A9-4980-8F94-4EC1D4E478BF
PartitionId                                      0C37852B-34D0-418E-91C6-2AC25AF4BE5B
ApplicationName                           AppDBCredentials_ddbe8814_5cdf_48a4_9322_a53cad392c17
FriendlyName                                Credentials for App instance 'ddbe8814-5cdf-48a4-9322-a53cad392c17'
ApplicationType                             5
TicketTimeout                                NULL
ContactEmail                                  admin@admin.com
CredentialManagementUrl             NULL

4. Preparation step #4, we will need to get the access apps different entries from the app management store service DB. You could use the following SQL query on the secure store service DB.

4.1 Get AM_AppPrincipalAppInstances entries using the following SQL query.

SELECT TOP 1000 [CompositePartitionKey]
      ,[AppInstanceId]
      ,[ProductId]
      ,[AssetId]
      ,[ContentMarket]
      ,[AppSource]
 FROM [SVC_APPS].[dbo].[AM_AppPrincipalAppInstances]
 WHERE AppInstanceId ='DDBE8814-5CDF-48A4-9322-A53CAD392C17'

The AppInstanceId is from the step#2. The result is listed below.

CompositePartitionKey 0x2B85370CD0348E4191C62AC25AF4BE5B0049003A00300049002E0054007C004D0053002E00530050002E0049004E0054007C00370032003700370041003900410032002D0034003000420041002D0034003000450038002D0042003500420033002D004300450038004500360046003900350039003700320042004000390038004500390042004100380039002D0045003100410031002D0034004500330038002D0039003000300037002D00380042004400410042004300320035004400450031004400
AppInstanceId               DDBE8814-5CDF-48A4-9322-A53CAD392C17
ProductId                       C181CBB8-0474-41C3-8C3C-6C86962E497D
AssetId                          NULL
ContentMarket              NULL
AppSource                    4

4.2 Get AM_AppPrincipals entries using the following SQL query.

SELECT TOP 1000 [CompositePartitionKey]
      ,[Title]
      ,[RedirectUrl]
      ,[Realm]
      ,[Flag]
      ,[AppInstanceId]
      ,[DeploymentData]
      ,[DelegateAppPartitionKey]
  FROM [SVC_APPS].[dbo].[AM_AppPrincipals]
 WHERE Title = 'Harry-APP-Testing'

Please note the Title is the "App Name" from step #1. Be aware the same app names in the farm. In this case, you need to compare the CompositePartitionKey with the value from step 4.1. The result is listed below.

CompositePartitionKey 0x2B85370CD0348E4191C62AC25AF4BE5B0049003A00300049002E0054007C004D0053002E00530050002E0049004E0054007C00370032003700370041003900410032002D0034003000420041002D0034003000450038002D0042003500420033002D004300450038004500360046003900350039003700320042004000390038004500390042004100380039002D0045003100410031002D0034004500330038002D0039003000300037002D00380042004400410042004300320035004400450031004400
Title                                             Harry-APP-Testing
RedirectUrl                                 access:AuthRequestComplete
Realm                                          NULL
Flag                                              0
AppInstanceId                           NULL
DeploymentData                      NULL
DelegateAppPartitionKey       NULL


4.3 Get AM_ProductOAuthRegistration entries using the following SQL query.

SELECT TOP 1000 [CompositePartitionKey]
      ,[AppId]
      ,[AppInstanceId]
FROM [SVC_APPS].[dbo].[AM_ProductOAuthRegistration]
WHERE AppInstanceId = 'DDBE8814-5CDF-48A4-9322-A53CAD392C17'

Please note the AppInstanceId is the value from step #2. The result is listed below.

CompositePartitionKey  0x2B85370CD0348E4191C62AC25AF4BE5B01B8CB81C17404C3418C3C6C86962E497D
AppId                      i:0i.t|ms.sp.int|7277a9a2-40ba-40e8-b5b3-ce8e6f95972b@98e9ba89-e1a1-4e38-9007-8bdabc25de1d
AppInstanceId         DDBE8814-5CDF-48A4-9322-A53CAD392C17

Now, we can delete the access app and start to restore the access app along with the site collection. Some steps are similar to we discussed in previous article.

1. Restore the access app logins - see previous blog.
2. Restore the access database
3. Restore the site collection content DB
4. Insert the entry from step #3 to secure store service database
5. Insert multiple entries from step #4 to three app management service database

This way, you should have the access app restored along with site collection in the same farm. At meantime, since we are not overwriting the whole secure store or app management services, instead  inserting entries related to the specific access app, this approach will restore only the access app deleted without impacting other apps!

Since we are query SharePoint databases and insert data into both secure store and app management databases, this might violate Microsoft SharePoint support contract. Be aware NOT to sue approach before consulting with your Microsoft support team.



Thursday, June 15, 2017

Procedure to restore SharePoint 2013 site with access apps in the same farm from database backups

In March 2017, Microsoft announced that they will no longer recommend Access Services for new apps and web databases. When Access Services in SharePoint is first introduced in SharePoint 2013, the mission was to enable both information workers and developers to quickly create data eccentric web applications with little or no programming. Over the last several years it has become clear that the needs of our customers have grown beyond the scope of what Access Services can offer. One of the major issue we are facing is one of our  Access Service App is bigger that could not be packaged as app package. We are not be able to make any changes that could be saved or packaged. The worst case is there is no disaster recover procedure we could  recover the Access Service App along with the site collection.


Although Microsoft is recommending Microsoft PowerApps as alternative that offers a comprehensive set of application building tools, connection to custom web APIs, and a wide array of database options including SharePoint lists, SQL Azure databases, Common Data Service and third-party data sources, there is no clear way to convert existing Access Service App. While we are migrating existing Access Service Apps, we still need to support the current Access Service Apps in production. In this article, I’ll describe the way how you could restore the Access Service Apps from database backups along with the site collections using Microsoft way. You could use app package as access app backup and restore if the app is small enough to be saved as .app package. In different article, I’ll also provide a way that might not be supported by Microsoft but for your reference.

Before we explain the procedure to restore SharePoint 2013 site with access apps in the same farm, let’s create a team site with an access app. The site has the following url “http://spsbx15/sites/APP-DR” and the Access App is named “Harry-APP-Testing” as out of box Customers table. The disaster recover methods are different different scenarios of the site with access app restore.

Here is the summery of the site collection and farm information needed for restore. I'll explain the details how to get this information in the following sessions.

  • Site collection  URL                  http://spsbx15/sites/APP-DR
  • Site collection DB                  WSS_Content_APP-DR
  • Access App                            Harry-APP-Testing
  • Access App DB                      db_ddbe8814_5cdf_48a4_9322_a53cad392c17
  • App manager service DB        SVC_APPS
  • Security Store service DB       SVC_SecureStore
  • Access App logins                    db_ddbe8814_5cdf_48a4_9322_a53cad392c17_dbo 
                                                         db_ddbe8814_5cdf_48a4_9322_a53cad392c17_custom
                                                         db_ddbe8814_5cdf_48a4_9322_a53cad392c17_ExternalWriter

All the SharePoint components related to access app for the site collection and the relationships are illustrated in the below diagram.



1. The first scenario is the site collection content corrupted or deleted, we want to recover the site along with Access App. 

In order to test this scenario, we delete the site collection “http://spsbx15/sites/APP-DR”. Here is the way to restore.
  • Get the site content database WSS_Content_APP-DR from backup
  • Restore the content database to SharePoint SQL database
  • Mount the database using the Powershell

         Mount-SPContentDatabase WSS_Content_APP-DR -DatabaseServer spsbxsql15 -WebApplication        
         http://spsbx15

If the site collection had been just deleted within few hours and the SharePoint daily graduate site deletion timer job has not started (~6AM), you can quickly restore the site using the following steps.

        # Get the site GUID using Powershell. Record the site GUID
        Get-spdeletedsite -webapplication http://spsbx15

        # Restore the site using Powershell by passing the site GUID
        Restore-SPDeletedSite -Identity 610857cb-8414-4a89-8bf3-ad3628f6c86c


2. The second scenario Access App content corrupted, we want to restore the Access App to the same site. Here are the steps.

         Get the Access App database from backup. Please note the naming conversion to the Access App DB
         Restore the Access App database like “db_ddbe8814_5cdf_48a4_9322_a53cad392c17”


3. The third scenario is Access App deleted, we want to restore the Access App to the same site. Here are the steps before and after the Access App deletion.

As daily SharePoint back up process, please back up the following databases and logins.
  • Back up the Access App logins daily using the script provided by Microsoft.
  • Back up the site collection databases
  • Back up the access app databases
  • Back up the app manager service database
  • Back up security store service database
Whence we need to restore the site collection with access app, here is the procedure.
  • Restore the access logins using the script provided by Microsoft. The logins will be recreated with same SID and passwords. The logins are prefixed as "db_" and then access app ID. db_ddbe8814_5cdf_48a4_9322_a53cad392c17_dbo, db_ddbe8814_5cdf_48a4_9322_a53cad392c17_custom, db_ddbe8814_5cdf_48a4_9322_a53cad392c17_ExternalWrite
  • Restore the access app database db_ddbe8814_5cdf_48a4_9322_a53cad392c17
  • Restore the app manager service database SVC_APPS. This can be done when SharePoint is running.
  • Restore the security store database SVC_SecureStore. You need to stop IIS  by IISRESET /stop before the database restore.
  • Restore the site collection database WSS_Content_APP-DR and mount the site again
The last step to restore the site collection is tricky since the database cannot be restored if the current database is still be used. Here is the way to do that.

  • Delete the site collection from CA
  • Remove the deleted site from pool using Powershell
           get-spdeletedsite -webapplication http://spsbx15 | Remove-SPDeletedSite 

  • Run “Gradual Site Deletion” immediately to delete the sites that were marked for deletion 
  • Close any browser hitting the site, close all SQL query using the database
  • Conduct a IISRESET
  • Delete the site collection content database from SQL - You may still need to try few time until all connections dropped to the database
  • Restore the previous site collection content database WSS_Content_APP-DR 
  • Mount the database to SharePoint using Powershell
       Mount-SPContentDatabase WSS_Content_APP-DR -DatabaseServer spsbxsql15 
       -WebApplication http://spsbx15

Now you have the site collection along with working access app from previous back up!

Please note there are potential issues for the third scenario. The restore will overwrite the entire app manager service and security store service!  What will be impacted? Here are the potential risks you need to manage.

  1. If new access apps have been added to some site during the time, these will NOT be recovered. These access app will be orphan on the sites!
  2. If new security store entry has been added or existing security entry has been modified, these will NOT be recovered. You will have previous values!

Since we will disable access app creation, we can eliminate the risk #1. We control the security store entry update and have Powershell script to retrieve the entries, we can eliminate the risk #2. However, if you like my access restore procedure, please be aware the risks.

At this time, you might ask, is there anyway we could recover the site along with access without overwrite the entire app management service and security store service?  The answer is YES! I'll provide the details in another blog. However, that approach will involve SQL database insert to both app management service and security store service databases. This seems to be unsupported by Microsoft.

I'm working with Microsoft to see if there is anyway we can restore app and site without overwrite the app management service and security store service.