ShareGate : Create user mapping file

 User mapping file is essential for migrating user permissions. OneDrive and SharePoint sites can migrate the user permissions, as long as the users are present in both tenants. Below is the script which can be used to create the user mapping file (.sgum). 

• Create a CVS file with two columns, SourceValue and DestinationValue
• Add the user UPNs and save the file
• Change the $csvFile variable to point to your CSV file
• Run the script and get the out file from "C:\Temp\"

import-Module Sharegate

$csvFile = "C:\Temp\usrprmmapping.csv"

$table = Import-CSV $csvFile -Delimiter ","

$mappingSettings = New-MappingSettings

foreach ($row in $table) {

    $results = Set-UserAndGroupMapping -MappingSettings $mappingSettings -Source $row.SourceValue -Destination $row.DestinationValue

    $row.sourcevalue

}

Export-UserAndGroupMapping -MappingSettings $mappingSettings -Path "C:\Temp\usermappingfile"

Restore OneDrive Site

Once a user leaves the organization and the user Id is deactivated, the user OneDrive site is deleted after the retention days are over. However, after the OneDrive site is deleted, the site remains in the recycle bin for 93 days. This recycle bin isn't accessible (as of June 1st 2022) from the UI. So, Microsoft has provided PowerShell capability to restore the OneDrive site.

1. Connect-SPOService - Connect to the SharePoint Online service using your admin center URL
2. Get-SPODeletedSite -Identity {OneDrive Site URL} - This should list the number of days left before the site is permanently deleted. If this doesn't return anything, that means the site isn't deleted.
3. Restore-SPODeletedSite -Identity {OneDrive Site URL} - The cmdlet will restore the Deleted OneDrive site
4.  Set-SPOUser -Site {OneDrive Site URL} -LoginName {UPN of the user needs access} -IsSiteCollectionAdmin $True - This is optional. In case you/someone needs admin access to the OneDrive site
5. Disconnect-SPOService - to close the open connection

Restrict creation of New SharePoint 2013 Workflows in your tenant

Microsoft is planning to deprecate SharePoint 2013 Workflows. The dates aren't finalized yet, however, Microsoft has introduced new parameter for SharePoint Online to disable creation of new SharePoint 2013 workflows. The existing workflows can continue to run and be modified even after this tenant setting is enabled. This will help organizations to reduce/limit their scope of creating workflows using SP Designer 2013.

Spoiler :) - You must be on 16.0.21610.12000 for Microsoft.Online.SharePoint.PowerShell module.

Step1 : Connect-SPOService 

Provide your SharePoint admin URL and SP admin ID/Password to connect to the tenant

Step2: Get-SPOTenant

You should see StopNew2013Workflows as FALSE

Step3: Set-SPOTenant -StopNew2013Workflows $TRUE

This final step will change the status of StopNew2013Workflows to TRUE. Now on your existing SP 2013 workflows will work, can be edited but new workflows cannot be created.

Test Case:

If you try to create a new SP 2013 workflow, while publishing the workflow you should see an error like below.

Azure Storage Container - Get blob/file names using PowerShell

We often create Azure storage containers to store files. These files could be for importing to your Office 365 blob or anything else. Below is a script that will help you generate the names of all files in your container. What you need to provide is the storage account name, container name and sas token of the container.

Connect-AzAccount
$StorageAccountName = 'o365'
$ContainerName = 'dev'
# Creates a storage context
$sasToken = 'Paste SAS token URL here'
$StorageContext = New-AzStorageContext $StorageAccountName -SasToken $sasToken
#listing the file names
$fileNames = Get-AzStorageBlob -Container $ContainerName -Context $StorageContext | select Name
$fileNames | out-file "D:\Raj\Log\FileNames.txt"
Disconnect-AzAccount

OneDrive for Business - Issues and Fix

I have recently completed OneDrive to OneDrive migration and during the migration, there were several issues which I have encountered. I am trying to put together some of them below.

1. Error Code: 0x8004deb4 : There was a problem signing you in. Login was either interrupted or unsuccessful. Please try logging in again. (Error Code: 0x8004deb4)

      

          Solution :

1. Click on run > regedit
2. Navigate to HKEY_CURRENT_USER\SOFTWARE\Microsoft\OneDrive
3. Search for EnableADAL key. If the key is available and the value isn’t 00000002, change it to 00000002.
4. In the case that it is unavailable, create a new DWORD(32-bit value) and set the value to 00000002
5. Close the registry and restart the laptop.

2. Error Code: 0x8004deea : 

1. Click on start Menu > Run
2. Type Regedit > Enter 
3. Navigate to Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\OneDrive|AllowTenantList and Search for your tenant ID. 
4. If the tenant ID isn't available, create a new DWORD key and add your O365 tenant id.
5. The change requires admin rights in the system and post addition, run the below steps.
6. Go to command prompt and run Gpupdate /force
7. Restart the machine.

3. Error Code: 0x8004deef

     The error code appears in the OneDrive client while logging in when OneDrive site isn't provisioned for the user. Please navigate to SharePoint user profile and check if the user has OneDrive site. If there isn't, provision OneDrive for the user.

4. Restrict OneDrive Personal Sync in your organization 

    Many organization do not realize that Microsoft offers OneDrive personal with Microsoft accounts and it is free up to 5 Gb. Users using OneDrive personal can sync their OneDrive personal data in organization devices and legal/security doesn't like that. To prevent OneDrive personal from syncing, we have to add a registry key. Navigate to the below and add a new key DisablePersonalSync and add value 1. 

To add the key in all machines, you need to take help from your SCCM team to update the registry using GPO.

Computer\HKEY_CURRENT_USER\Software\Policies\Microsoft\OneDrive

5. Error code : 0x8004de69

     - Pause syncing the OneDrive

     - Close the OneDrive

     - Navigate to Control Panel -> Credential Manager -> Windows Credentials and delete all Generic Credentials containing OneDrive

     - Navigate to OneDrive website

     - Click Sync - this will open OneDrive in Windows and should resolve the issue

eDiscovery - Put mailboxes On Litigation Hold using PowerShell Script

O365 administrators can use GUI to put SharePoint sites, ODB, mailbox on litigation hold. Microsoft eDiscovery UI helps admins to create a Case and apply Hold to mailboxes. However, there are PowerShell scripts available to put mailboxes On Litigation Hold and you do not need to create Cases for it. 

Below is how we do that  -

1. Connect to Exchange Online

$UserCredential = Get-Credential
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection
Import-PSSession $Session –DisableNameChecking

2. Check Litigation Status of the mailbox

Get-Mailbox -identity rajdeepc@onmicrosoft.com |fl Identity, LitigationHold*

3. Put the mailbox on hold

Set-Mailbox -identity rajdeepc@onmicrosoft.com -LitigationHoldEnabled $true

4. (Optional) Use the content search to get collection for your legal team

5. Release the mailbox if it is not needed to be On Litigation Hold

Set-Mailbox -identity rajdeepc@onmicrosoft.com -LitigationHoldEnabled $false

6. Disconnect the session (Must do to ensure the session is not occupied)

Remove-PSSession $Session

Enable SPO Site Collection App Catalog

By now everyone knows how to enable Site Collection App Catalog in SharePoint Online. It is simply to run the commands like below -

Connect-SPOService -Url {SharePointAdminSiteURL} -Credential rajdeep@domain.net
$site = Get-SPOSite {SiteCollectionURL}
Add-SPOSiteCollectionAppCatalog -Site $site

You may encounter an error like "Must have Manage Web Site permissions or be a tenant admin in order to add or remove sites from the site collection app catalog allow list". This is because if the person who's executing the command is not admin in the App Catalog site.

Add rajdeep@domain.net as admin in the site collection app catalog and run it again.

Backup and Restore Project Server Sites 2013

The below article outlines how to take backup and restore Project Server sites from one tier to another. The steps are tested successful in SharePoint/Project Server 2013 farm. The process assumes that you already have the Project Server database and content database backup files available from the backup/restore team.
First we will start by dismounting the existing running instances. Please have communication out for the downtime your application/site will have during the process.

• Dismount-SPProjectWebInstance -SiteCollection  <URL of PWA site that we want to refresh>
• The Project Server service application in CA the project site should have been removed
• Next we will dismount the content database - Dismount-SPContentDatabase “<Content Database name here>”
• Launch SQL server and restore the SQL database backups you have received from the B/R team.
• Mount the restored content database in the web application - Mount-SPContentDatabase “<restored content database in previous step>” -DatabaseServer “<SQL Server>” –WebApplication <web application URL>
• Mount the restored project server database - Mount-SPProjectDatabase -Name “Name of the restored Project database” –WebApplication “Web Application URL that the Project Web App database will mount to” –DatabaseServer “SQL Server where the database was restored”
• Provision the Project Web App site collection - Mount-SPProjectWebInstance –DatabaseName “Restored Project Web App database” –SiteCollection “site collection URL including the PWA path” –DatabaseServer “SQL Server where the database was restored”
• We can check the provisioning status using - Get-SPProjectWebInstance -URL "https://...." | Select ProvisioningStatus

If your farm is managed by different administrative accounts, you may have to consider adding the correct account permission in the newly provisioned project site. For that navigate to - CA > Manage Service Apps > Project Server Service App and choose to Edit the Project Server Instance, modify the Administrator account to be the correct account.

If Excel service reports are in use, will need to update to use the ODC files from correct environment. 

START or STOP App Fabric Caching service using PowerShell

START or STOP App Fabric Caching service using Power Shell

App Fabric or Distributed Caching service can be started using services console or from the central admin UI. However, I find it easy to start it using power shell. Copy and paste the below code in a notepad and save it as .ps1 file. The script file can be executed from the distributed cache server/s.

START App Fabric Caching Service

$instanceName =”SPDistributedCacheService Name=AppFabricCachingService”
$serviceInstance = Get-SPServiceInstance | ? {($_.service.tostring()) -eq $instanceName -and ($_.server.name) -eq $env:computername}
$serviceInstance.Provision()

STOP App Fabric Caching Service
Stop-SPDistributedCacheServiceInstance –Graceful

After the service is started, from the DC server, type the below two commands to ensure that your service is UP.

Use-CacheCluster

Get-CacheHost

Now about the verification. First verification should be in the services.

1. Click on Start > Run > type services.msc > Enter

2. Ensure the App Fabric Caching service is running.

The next verification is from the Central Administration.

1. Navigate to Central Administration > Click on services on server

2. Select the server from the dropdown where your DC is configured

3. Ensure the Distributed Cache status is Started.

Hope this helps !

Remove access to the third party app store for SharePoint on prem farms

Remove access to the third party app store for SharePoint on prem farms

If you don't want your users to access the SharePoint App store in your on prem farm and try to add or request apps for your farm, use the below command from any of your frond-end/app server to disable the app store.

Set-SPAppStoreConfiguration -Enable $false

SQL account permission sync issue on Always On

SQL account creation process is slightly different for SQL servers which are on always on availability group. The SQL account permissions won't sync properly if the SID of the accounts are not same in primary and secondary SQL server. 

Since the SQL account created in the primary SQL server cannot sync the permission properly in secondary server, after the fail over (when the secondary sever will become primary and vice versa) the account will have permission issue and cannot be used anymore to login. To avoid this issue, either 1) we should create the accounts in primary and secondary server with same SID, or 2) if the accounts are already created, delete/drop the account in the secondary SQL server and have the account recreated using same SID (this is not recommended if the account has many database mappings, it will complicate things).

Create a SQL account in primary and secondary SQL server

- Login to the primary SQL server with admin credential and run the below -

  Use [master]

  GO

  CREATE LOGIN [SQL_Login_Account_Name]  

  WITH PASSWORD = N 'Account_Password',

   DEFAULT_DATABASE=[master]

   GO

- Provide privilege to this account

- The account will not replicate automatically to the secondary SQL server. To have this account available in the secondary SQL server, we will have to re-create it in secondary. Before we do that, we need the SID of the account. Run the below to get the SID.

Select name, sid FROM sys.server_principals WHERE name = 'SQL_Login_Account_Name'

- Login to the secondary SQL server and run the below - 

   

  CREATE LOGIN [SQL_Login_Account_Name]

  WITH PASSWORD=N 'Account_Password',

  SID = SID_OF_THE_ACCOUNT_FROM_PRIMARY_SQL,

  DEFAULT_DATABASE=[master]

  GO

- Provide same privilege as given in primary 

- Go back to the primary SQL server and provide DB access to this account. The new user account will be automatically replicate in secondary SQL server with same permission.

If the accounts are already created in primary and secondary SQL server then the steps will remain same, except - 

1) Drop the account from the secondary sql server

2) Get the SID of the account from the primary sql server

3) Recreate the account in secondary sql server using the same SID

GUI doesn't support creation of accounts using same SID, hence this can be achieved only using TSQL.

SQL Server 2014 - Always on availability - Move MDF files

SQL Server 2014 - Always on availability - Move MDF files

The SQL Servers with always on availability allows you to have fail over options for databases. In an environment where you have always on availability, it requires that we remove the DB from the always on availability before we move the MDF files from one drive to another. 

In this post I am going to write about how to move the MDF files where always on availability is enabled and at the next half I will explain how to add the same DB in always on availability. So, before we start, it is expected that you have the knowledge of primary and secondary SQL server.

Move MDF Files

•          Login to the primary SQL server, expand Always On Availability
•          Select Availability Databases > Press F7
•          The object explorer window displays all DBs
•          Select the DB/s which you want to remove from Availability group and right click to "Remove Database from Availability Group"

            

•  After the DB is removed from availability, Detach the DB. Right Click on the DB and select Tasks > Detach
•  Login to Secondary SQL server
•  Remove the DB from the always on availability (refer the steps above)
•  Delete the DB/s from the secondary SQL server. Right click on the DB > click Delete > Select close all connections > click Delete
•  Copy the MDF files from source to target destination (ex. Disk2 to Disk1)
•   Login to Primary SQL Server
•  Right click on Databases > Attach > Add > Select the MDF file > click OK
• After the DB is attached we will work to add the DB in always on availability group of the primary and secondary server.

December '16 Cumulative Update for SharePoint 2013 Issue - Data View Web Parts Stopped working without workaround

The SharePoint Data View Web Part in On Prem environment is not working as expected after the Dec '16 CU is installed. This is fixed in April '17 CU. So, after you patch your SP 2013 environment with Dec '16 CU you would see your DVWP breaking. Below are the error details and workaround available.

If you add a DVWP in your page you would see an error like below.

Error On the Page :

Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor such as Microsoft SharePoint Designer. If the problem persists, contact your Web server administrator.

Correlation ID: -------

Error in ULS Log :

If you search the ULS log with the correlation id you will see an error like below.

Error while executing web part: System.NullReferenceException: Object reference not set to an instance of an object.   

 at Microsoft.SharePoint.WebControls.SPDataSourceView.ExecuteSelect(DataSourceSelectArguments selectArguments, String aggregateString, Boolean wantReturn, BaseXsltListWebPart webpart, SPListItem& listItem, SPListItemCollection& listItems, String[]& fieldList)   

 at Microsoft.SharePoint.WebControls.SingleDataSource.GetXPathNavigatorInternal()   

 at Microsoft.SharePoint.WebControls.SingleDataSource.GetXPathNavigator()   

 at Microsoft.SharePoint.WebPartPages.DataFormWebPart.GetXPathNavigator(String viewPath)   

 at Microsoft.SharePoint.WebPartPages.DataFormWebPart.PrepareAndPerformTransform(Boolean bDeferExecuteTransform)

Workaround :

Open the page in SharePoint Designer and remove the below tags from the data source.

<SelectParameters><WebPartPages:DataFormParameter ParameterKey="ListName" PropertyName="ParameterValues" DefaultValue="Project Study" Name="ListName"></WebPartPages:DataFormParameter><asp:Parameter DefaultValue="0" Name="StartRowIndex"></asp:Parameter><asp:Parameter DefaultValue="0" Name="nextpagedata"></asp:Parameter><asp:Parameter DefaultValue="1" Name="MaximumRows"></asp:Parameter>

OR

<asp:Parameter Name="StartRowIndex" DefaultValue="0"/><asp:Parameter Name="nextpagedata" DefaultValue="0"/>

Reference - https://answers.microsoft.com/en-us/msoffice/forum/msoffice_sharepoint-mso_win10/data-form-web-part-broken-in-spd-2013/c72ca036-8a86-451e-b90c-6c941e7212c8?rtAction=1493664482015

Observation :

The issue is not reproducible in environments where Dec '16 CU is unavailable. I have tested this.

Solution :

MS has confirmed that it is a known bug. The issue is fixed in April '17 CU, but I haven't installed and tested this to confirm. Patch installation requires planning, approval and downtime which is time consuming. Once I test this, I will update again to confirm.

SharePoint 2013 Site Collection Change Lock State using PowerShell

There are various situations where a site collection administrator has to make changes to the lock status of a/multiple site collections. It could be because you are migrating, or you want to execute some auditing and so on. Below is a script which can help you do the same. We can change the Lock state of a site collection to any of the available one. You can also add multiple site collections in the $sitelist parameter.

Hope it helps :) !


$sitelist = @("https://yoursitecollectionURL",)
foreach($site in $sitelist){
  $s = get-spsite $site
  $s.LockIssue = "Any text you want to add for logging purpose"
  //Set-SPSite -Identity $site -LockState [NoAccess|UnLock|ReadOnly]
  Set-SPSite -Identity $site -LockState NoAccess
                                         }

500 Internal Server Error - SharePoint STS Application Pool

Often we have noticed 500 Internal Server Error in SharePoint Site collections. The issue typically occurs when the service account that is used by the STS was used for an interactive log on and afterwards logged out. For example, an administrator logs on to a WFE server by using the farm account and then log off. By doing this, it forces the registry keys to be unloaded in the profile of that account. This condition makes the keys unavailable for future use. The most common errors which we notice in the ULS log would be like below.

SPSecurityContext: Request for security token failed with exception: System.ServiceModel.FaultException: The server was unable to process the request due to an internal error.  For more information about the error, either turn on IncludeExceptionDetailInFaults (either from ServiceBehaviorAttribute or from the <serviceDebug> configuration behavior) on the server in order to send the exception information back to the client, or turn on tracing as per the Microsoft .NET Framework SDK documentation and inspect the server trace logs

STS Call Claims Windows: Problem getting output claims identity. Exception: 'System.Runtime.InteropServices.COMException (0x800703FA): Illegal operation attempted on a registry key that has been marked for deletion. at System.DirectoryServices.DirectoryEntry.Bind(Boolean throwIfFail)  at System.DirectoryServices.DirectoryEntry.Exists(String path) 

STS Call: Failed to issue new security token. Exception: System.Runtime.InteropServices.COMException (0x800703FA): Illegal operation attempted on a registry key that has been marked for deletion. at System.DirectoryServices.DirectoryEntry.Bind(Boolean throwIfFail) at System.DirectoryServices.DirectoryEntry.Exists(String path) 

Workaround :

1.  Recycle the STS app pool

2.  If some users are still getting 500 Internal Server Error, clear the browser cache of the user machine and it should work

Solution :

There are two steps involved to fix the issue of 500 Internal Server Error.

Step 1 : Enable auto recycle of STS App pool during off business hours.

Step 2 : Enable "Load User Profile" feature in the IIS against STS app pool. You can refer this article to configure the same.

Get ULS Log Using Powershell

How to get the ULS log entries using powershell

It is not easy to search for an error using correlation id from the sharepoint log. We use ULS log viewer to make the ULS log look nicer. However, powershell provides us a way to extract the log between a specified time frame and provide a text file. Run the below command to get it.

Add-PSSnapin Microsoft.SharePoint.PowerShell
Get-SPLogEvent -StartTime "07/02/2016 11:35:00" -EndTime "07/02/2016 11:40:00" | ?{$_.Correlation -eq "3c768c9d-5f2e-70a4-962e-b5dddb8ca4f2"} | select Area, Category, Level, EventID, Message | Format-List | out-file "D:\Corelog.txt" 

SharePoint 2013 Server - The status code of response is '500'. The status text of response is 'System.ServiceModel.ServiceActivationException'.

SharePoint 2013 Server - Unexpected response from server. The status code of response is '500'. The status text of response is 'System.ServiceModel.ServiceActivationException'

When everything is working fine and suddenly you start receiving an error while the SharePoint page loads, "Unexpected response from server. The status code of response is '500'. The status text of response is 'System.ServiceModel.ServiceActivationException'". That too from only one front end server. First thing first;

• Remove the front end server from the load balancer.
• Now your site is fine for end users, but not for you ;)
• Login to the front end server where the issue is occurring.
• The issue occurs mostly because of occupied memory. If memory utilization is more then you will receive this error.
• Right click on Task manager and check the memory occupied.
• The users who are logged into the servers and forgot to log off has occupied space.
• Right click on the username and force log off them; except your userid ;)
• Refresh the SharePoint site, the error is no more.
• Enable the server in the load balancer again.

Cheers !

Build a Table of InfoPath Repeating Section and Send Email using Nintex Workflow

If you have followed my previous post on how to read the Repeating Section data then this section would help you. On this post I will explain on how to form a table out the repeating section and build a string to Send an Email using Nintex Workflow.

1. Add a Build String activity after the Log to History activity. (Please have a look at my previous post here). This activity is added inside the For Each.

2. Create a multiple lines of text variable varStoreChargeCodes and form the table like above. Store the table result in varStoreChargeCodes
3. After the For Each activity, add another Build String activity, this will form the Email Body. We will use the varStoreChargeCodes variable, which is nothing but our table, containing the Project Names and corresponding Charge Codes.

4. You can add any detail you want to add on the build string, like for me I created a table to add the Employee Name, Employee Badge ID# and then added the variable containing Project Names and Charge Codes. Store the Email body is a variable varFormTable.

5. Finally add a Send Email activity like below, where the email body would contain the varFormTable variable.

That's it. The repeating section data would appear in a table. For me the email looks like below.

Happy Reading !

Read InfoPath Repeating Section Details using Nintex Workflow

Read InfoPath Repeating Section Details using Nintex Workflow

My InfoPath Form looks like below, where I have a repeating section which contains Project Name and Charge Code (Auto populated based on Project selection). The form is used by the PMs to request charge code for employees. After the form is submitted, Nintex Workflow has to execute and extract the values from the repeating section and send an email to coordinators. 

1. Add a Query XML activity and configure like below.

2. Create Two collection variables, one for Project and one for Charge Codes.

3. You can use XPath Builder to provide the field XPath or can copy it from the InfoPath field as well.

4. Add a ForEach activity and configure it like below.

5. Your target collection would be PrjNames and store the result in PrjName (a single line of text variable). PrjIndex is another number variable.

6. Add a Collection Action to extract the Project Names. Notice the target collection is PrjNames, GET, Index is PrjIndex and Storing the result in Single Line of text PrjName.

7. Similarly add another collection operation to get the Charge Codes

8. Add a Log to History activity to read the PrjName and ChargeCodeM field. 

That's it ! Your repeating section data is extracted.

I will talk about how to use these details, form a table and send an email on my next post.

Thanks !

Submit InfoPath Each Repeating Table Data to SharePoint List as Individual Item

Submit InfoPath Each Repeating Table Data to SharePoint List as Individual Item

I received a requirement to create a time card entry and keep track of the number of hours consumed in their activities. Below is a sample of the form.

I had to design the form in such a way so that individuals can make multiple entries for a week and on different types, like, Project, Holiday, if it is Project then What is the name of the project and whether it is Design or Lab hours. So, I added all my controls on a repeating section. Initially I thought of merging the records in comma separated value but reporting is complicated. I decided to save them in a list as individual item to make life easy. Below are the steps which can help you achieve the same.

1. Create a List and add all your fields which are required on the form.

2. Open the InfoPath Form designer, Select SharePoint List form template and Select Design Form.

    

3. Provide the Site URL

4. Select the List and continue

5. Now before you click finish, notice there is a check box says, "Manage multiple list items with this form".

6. Select the checkbox and click on finish.

7. This would open the Form with all controls added inside a repeating section.

8. If the repeating section is not enabled to add sections at run time, click on the repeating section property and enable them.

For me the end result look like below.

Hope this helps ! Feel free to suggest any other alternative you know.

Cheers !