Wednesday, November 30, 2016

SharePoint Online: Update Created By / Modified By, Created At / Modified At Field values using PowerShell CSOM

At times, you may have to create list items or set existing item's metadata fields such as Created by, Modified by, Created at, Modified values to a specific user - time stamp. Say, You are importing or migrating data from a network file share to SharePoint online and you want to keep these metadata as same as the source content.

Because, SharePoint doesn't let you to set these values from web user interface, we've to use PowerShell. Here is my PowerShell script to change the details of who created an item, modified it and when they created and modified it.

This script sets system fields (created, created by, modified, modified by) for a specific item in the list/library. Set the values in variables section appropriately and run the script.

PowerShell script to update Created by, Modified By, Created at, Modified at field values:
This PowerShell script sets item's metadata field values in SharePoint online
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
  
##Variables for Processing
$SiteUrl = "https://crescent.sharepoint.com/sites/sales/"
$ListName= "Projects"
$ID=6
$UserID="Salaudeen@crescent.com"
$TimeStamp = "2015/12/01 02:10:00 AM"

#Get Credentials to connect
$Cred = Get-Credential
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
$Ctx.Credentials = $Credentials

#Get the User
$User = $Ctx.Web.EnsureUser($UserID)
$ctx.Load($user)

#Get the list Item
$List=$Ctx.Web.Lists.GetByTitle($ListName)
$ListItem = $List.GetItemById($ID) 
$Ctx.Load($ListItem)

#Update Created by & Modified By
$ListItem["Author"] = $User
$ListItem['Editor'] = $User

#Set Created on & Modified on Time values
$ListItem["Created"] =  $TimeStamp
$ListItem["Modified"] = $TimeStamp

#Update List item
$ListItem.Update()
$ctx.ExecuteQuery()

Write-host "Metadata values updated Successfully!" -f Green
and the result goes here:
sharepoint online powershell to update created by modified by created at modified at values
For Server side PowerShell script, refer: Update "Created By", "Last Modified" Metadata Fields of a List Item using PowerShell

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Tuesday, November 29, 2016

SharePoint Online: PowerShell to Send Email

We can send emails from SharePoint online, either with SPUtility's SendEmail function or with PowerShell cmdlet Send-MailMessage. Here are examples in both the cases:

PowerShell to Send Email in SharePoint Online:
Here is how to send an E-mail from SharePoint Online using PowerShell Client Side Object Model (CSOM) script.
Import-Module Microsoft.Online.SharePoint.Powershell -DisableNameChecking

#Config Parameters                                                                 
$AdminSiteURL = "https://crescent-admin.sharepoint.com/"
$EmailFrom ="SPAdmin@crescent.com"
$EmailTo = "Salaudeen.Rajack@crescent.com"
$Subject ="SharePoint Online Storage Report"

#Setup Credentials and connect
$Cred = Get-Credential
Connect-SPOService -Url $AdminSiteURL -Credential $Cred

#Get Storage Usage of All Site collections
$SiteStorage = Get-SPOSite -Detailed | Select Url, StorageUsageCurrent
       
#Setup the site context for SPUtility SendEmail
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)

#Setup Email
$EmailProperties = New-Object Microsoft.SharePoint.Client.Utilities.EmailProperties
$EmailProperties.From = $EmailTo
$EmailProperties.To = [String[]] $EmailTo
$EmailProperties.Subject = $Subject
$EmailProperties.Body = $SiteStorage | Convertto-html
[Microsoft.SharePoint.Client.Utilities.Utility]::SendEmail($Ctx,$EmailProperties)
$Ctx.ExecuteQuery()
This sends Email using SPUtility's SendMail method with storage consumption of all site collections in SharePoint Online. Here from and To emails are Email addresses within the organization.
SharePoint Online PowerShell to Send Email

SharePoint Online PowerShell to Send Email:
This time, lets use PowerShell's native cmdlet Send-Mail message to send an Email from SharePoint online site.
Import-Module Microsoft.Online.SharePoint.Powershell -DisableNameChecking

#Config Parameters
$AdminSiteURL = "https://crescent-admin.sharepoint.com/"
$EmailFrom ="SPAdmin@crescent.com"
$EmailTo ="salaudeen@crescent.com"
$EmailSubject ="Site Collection Storage Utilization Report"
$SMTP ="smtp.office365.com"

#Setup Credentials and connect
$Cred = Get-Credential
Connect-SPOService -Url $AdminSiteURL -Credential $Cred

#Get Storage Usage of All Site collections
$SiteStorage = Get-SPOSite -Detailed | Select Url, StorageUsageCurrent | Convertto-html | Out-String

#Send Email
Send-MailMessage -from $EmailFrom -To $EmailTo -Subject $EmailSubject -Body $SiteStorage -BodyAsHtml -smtpserver $SMTP -usessl -Credential $Cred -Port 587 


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Sunday, November 27, 2016

Copy Group Memberships of a User in SharePoint using PowerShell

Requirement: Copy SharePoint Group Memberships of one user to another user

Solution: You can copy group memberships of one user to another user to have identical SharePoint Permissions for a SharePoint Site. Here is the PowerShell Script:

PowerShell Script to copy Group Memberships from One user to another:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Configuration Parameters
$SiteURL="http://intranet.crescent.com"

$SourceUserID="i:0#.w|Crescent\Salaudeen"
$TargetUserID="Crescent\Opera"

#Get the web
$web  = Get-SPWeb $SiteURL

#Get All group membershipss of the source user
$UserGroups = Get-SPUser $SourceUserID -web $SiteURL | Select -ExpandProperty Groups

#Get the Target User
$TargetUser = $web.EnsureUser($TargetUserID) 

#Add target user to each group
Foreach($GroupName in $UserGroups)
{
    Write-Host "Adding User to the Group:"$GroupName
    
    #Get the Group
    $Group = $Web.SiteGroups[$GroupName]
    #Add User to Group
    $Group.AddUser($TargetUser)
}
Alternatively, to add a user to SharePoint group, you can use Set-SPUser cmdlet
Set-SPUser -Identity $TargetUser -Web $SiteURL -Group $GroupName
However, if the group doesn't has access to the site, you'll get an error message: "The specified group does not exist."

If you want to clone a particular user's permissions, use this PowerShell script:  PowerShell to Copy Permissions in SharePoint

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


SharePoint Online: How to Change Site Logo using PowerShell?

Changing logo is a common requirement when it comes to branding SharePoint online sites.

How to change Logo in SharePoint Online:
Changing logo in sharepoint online is a matter of few clicks. To add logo to sharepoint online, follow these steps
  • Login as a site Administrator to your SharePoint Online site. Click on Settings gear >> Select Site Settings >> On Site settings page, Click on "Title, description, and logo" link.
    SharePoint Online PowerShell Change Logo
  • That takes you to a page where you can set Logo for the site. you can use either a logo from your computer, or any existing logo from your SharePoint site (Even any existing image from the Internet too).
  • Click on "From Computer" link >> Browse and upload your logo file. Once you upload, the logo file gets saved into "Site Assets" Library of the site.SharePoint Online How to Change Site Logo using PowerShell
  • As soon as you upload a image for logo, You'll find the new logo reflected immediately in the top left hand side of the site. Optionally, you can enter description for the logo. This description becomes tool tip for your new logo. Click on "OK" button to save your changes. Click cancel to discard.
SharePoint Online PowerShell to Change Logo:
Lets use PowerShell script to change site logo in SharePoint online.
#Add PowerShell Module for SharePoint Online
Import-Module Microsoft.Online.SharePoint.Powershell -DisableNameChecking

##Configuration variables
$SiteUrl = "https://crescent.sharepoint.com/"
$LogoURL="/SiteAssets/Logo.png" #Existing file

Try {
    #Get Credentials to connect
    $Cred = Get-Credential
    $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
    $Ctx.Credentials = $Credentials
    $Web = $Ctx.Web

    #Change Logo
    $Web.SiteLogoUrl = $LogoURL
    $Web.Update()
    $Ctx.ExecuteQuery()

    Write-host "Logo Updated Successfully!" -ForegroundColor Green
}
Catch {
    write-host -f Red "Error updating Logo!" $_.Exception.Message
} 
Please note, the file URL at LogoURL must be an existing file in SharePoint online site. So, upload the logo to root of your site collection first.

PowerShell to Change the site logo for a site collection and its sub sites:
Lets change logo for all sites within a SharePoint online site collection.
#Add PowerShell Module for SharePoint Online
Import-Module Microsoft.Online.SharePoint.Powershell -DisableNameChecking

##Configuration variables
$SiteUrl = "https://crescent.sharepoint.com/Sites/Sales"
$LogoURL="/SiteAssets/Logo.png"

Try {
    #Get Credentials to connect
    $Cred = Get-Credential
    $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
    $Ctx.Credentials = $Credentials

    #Get the Root web
    $Web = $Ctx.Web
    $Ctx.Load($Web)
    $Ctx.ExecuteQuery()

    #Function to change Logo for the given web
    Function Update-Logo($Web)
    {
        #Update Logo
        $Web.SiteLogoUrl = $LogoURL
        $Web.Update()
        $Ctx.ExecuteQuery()
        Write-host "Updated Logo for Web:" $Web.URL

        #Process each subsite in the site
        $Subsites = $Web.Webs
        $Ctx.Load($Subsites)
        $Ctx.ExecuteQuery()        
        Foreach ($SubSite in $Subsites)
        {
            #Call the function Recursively
            Update-Logo($Subsite)
        }
    }
    
    #Call the function to change logo of the web
    Update-Logo($Web)
}
Catch {
    write-host -f Red "Error updating Logo!" $_.Exception.Message
}


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Saturday, November 26, 2016

SharePoint Online: Get List in PowerShell

SharePoint Online: Get List from PowerShell
Here is the PowerShell to Get SharePoint Online List

Import-Module Microsoft.Online.SharePoint.Powershell -DisableNameChecking

#Config Variables for Site URL, List Name
$SiteURL= "https://crescent.sharepoint.com/sites/sales/"
$ListName="Sales Contacts"
 
#Setup Credentials to connect
$Cred = Get-Credential
$Cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)
 
Try {
    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
    $Ctx.Credentials = $Cred
 
    #powershell sharepoint online get list
    $List = $Ctx.Web.Lists.GetByTitle($ListName)
    $Ctx.Load($List)
    $Ctx.ExecuteQuery()

    Write-host "Total Number of Items in the List:"$List.ItemCount
}
 Catch [Exception] {
      Write-host $_.Exception.Message -f Red
} 
This script gets the given list and number of items from the list.

Related Post: SharePoint Online PowerShell to Get All Lists

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Import-Module: The specified module 'Microsoft.Online.SharePoint.Powershell' was not loaded because no valid module file was found in any module directory.

Problem:
When trying to add PowerShell module for SharePoint online, using "Import-Module Microsoft.Online.SharePoint.PowerShell", it resulted in below error in both PowerShell console and in PowerShell ISE.
"Import-Module : The specified module 'Microsoft.Online.SharePoint.Powershell' was not loaded because no valid module file was found in any module directory."
Import-Module-The specified module 'Microsoft.Online.SharePoint.Powershell' was not loaded because no valid module file was found in any module directory.

Solution: 
  • Make sure you have SharePoint Online Management Shell installed on the machine you are getting the error. You can download and install SharePoint Online Management Shell from: https://www.microsoft.com/en-us/download/details.aspx?id=35588
    download and install sharepoint online managment shell
    You can check installed modules by: Get-Module -listavailable
  • If you are getting this error in SharePoint Online Management Shell, Make sure you removed x86 version of SharePoint online management shell from your computer, and Try download and Re-Install 64bit version!
  • Try running: Set-ExecutionPolicy Unrestrict once
  • Run PowerShell or PowerShell ISE as a administrator


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Tuesday, November 22, 2016

SharePoint Online: Create Permission Level using PowerShell

Requirement: Create a new permission level in SharePoint online site collection for contribute without delete permissions.

SharePoint Permission levels are set of actions user can perform in SharePoint, packaged as a group to make permission management easier. So, Instead of providing individual permissions to users and groups, you pick a permission level and assign it to the new user. (or even Add the user to a group which has a specific permission level associated).

Contribute without delete permission level is often required in real world scenarios. Lets say, You want your users to be able to add files to the library but not delete files from the library. To achieve, we can simply copy the "Contribute" permission level and take off "Delete Items" permission from it!

How to create a permission level in SharePoint?
  • Go to the Site Settings >> Click on Site Permissions
  • Click on Permission Levels button from the ribbon
This takes you to the page which lists all default permission levels available in SharePoint with their  corresponding description.  Now you can either Add a Permission Level or click on any existing permission level, Copy and then Edit the new permission level to fill your requirements.

Do not change any default permission levels such as "Full Control" or "Contribute".

SharePoint Online PowerShell to Create Permission Level 
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
  
##Variables for Processing
$SiteUrl = "https://crescent.sharepoint.com/"
$SourcePermissionLevelName ="Contribute"
$TargetPermissionLevelName ="Contribute Without Delete"

Try {
    #Get Credentials to connect
    $Cred = Get-Credential
    $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
    $Ctx.Credentials = $Credentials
    $Web = $Ctx.Web

    #Get the source permission level
    $RoleDefinitions = $web.RoleDefinitions
    $Ctx.Load($RoleDefinitions)  
    $SourceRoleDefinition = $RoleDefinitions.GetByName($SourcePermissionLevelName)
    $Ctx.Load($SourceRoleDefinition)
    $Ctx.ExecuteQuery()

    #get base permissions from the source and remove "Delete"
    $TargetBasePermissions = $SourceRoleDefinition.BasePermissions
    $TargetBasePermissions.clear([Microsoft.SharePoint.Client.PermissionKind]::DeleteListItems)

    #check if the given permission level exists already!
    $TargetPermissionLevel = $RoleDefinitions | Where-Object { $_.Name -eq $TargetPermissionLevelName } 
    if($TargetPermissionLevel -eq $null)
    {
        #Create new permission level from source permission level
        $PermissionCreationInfo = New-Object Microsoft.SharePoint.Client.RoleDefinitionCreationInformation
        $PermissionCreationInfo.Name = $TargetPermissionLevelName
        $PermissionCreationInfo.Description = $TargetPermissionLevelName
        $PermissionCreationInfo.BasePermissions = $TargetBasePermissions

        #Add the role definitin to the site
        $TargetPermissionLevel = $Web.RoleDefinitions.Add($PermissionCreationInfo)
        $Ctx.ExecuteQuery() 
 
        Write-host "New Permission Level Created Successfully!" -ForegroundColor Green
    }
    else
    {
        Write-host "Permission Level Already Exists!" -ForegroundColor Red
    }
}
Catch {
    write-host -f Red "Error Creating Permission Level!" $_.Exception.Message
}
Instead of copying an existing permission level and manipulating it, You can also create new permission level from the scratch.
#Create base Permission set
$Permissions = New-Object Microsoft.SharePoint.Client.BasePermissions
#Add permissions to it
$Permissions.Set([Microsoft.SharePoint.Client.PermissionKind]::ViewListItems)
$Permissions.Set([Microsoft.SharePoint.Client.PermissionKind]::ViewVersions)  
This script copies existing permission level and creates the new permission level


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Sunday, November 20, 2016

SharePoint Online: PowerShell to Create Document Library

Requirement: Create a document library in SharePoint online using PowerShell

SharePoint online: PowerShell to Create Document Library:
How to Create document library in SharePoint online using PowerShell?
#Load SharePoint CSOM Assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime") | Out-Null

#Variables for Processing
$SiteURL = "https://Crescent.sharepoint.com/Sites/Sales"
$LoginName ="Salaudeen@Crescent.OnMicrosoft.com"
$LoginPassword ="Password" 

#Get the Client Context
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)

#supply Login Credentials
$SecurePWD = ConvertTo-SecureString $LoginPassword –asplaintext –force  
$Credential = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($LoginName,$SecurePWD)
$Context.Credentials = $Credential

#Create new document library
$ListInfo = New-Object Microsoft.SharePoint.Client.ListCreationInformation
$ListInfo.Title = "Project Docs"
$ListInfo.TemplateType = 101 #Document Library
$List = $Context.Web.Lists.Add($ListInfo)
$List.Description = "Repository to store project artifacts"
$List.Update()
$Context.ExecuteQuery()

write-host "New Document Library has been created!"
and the result:
sharepoint online powershell create document library
Related post: How to create a list in sharepoint online using PowerShell

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Wednesday, November 16, 2016

SharePoint Online: Import Terms to Term Set from CSV using PowerShell

Requirement: Import terms to term set in SharePoint Online from a CSV file using PowerShell.
Here is my CSV file with list of Terms for a Term set called "Job Title"
sharepoint online import term set

SharePoint Online Import Terms to Term Set using PowerShell: 
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Taxonomy.dll"
  
#Variables for Processing
$AdminURL = "https://crescent-admin.sharepoint.com/"
$TermGroupName= "People"
$TermSetName="Job Title"
$CSVFile ="C:\Temp\ImportTerms.csv" 
$TermHeaderInCSV ="JobTitle"

Try {
    #Get Credentials to connect
    $Cred = Get-Credential
    $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($AdminURL)
    $Ctx.Credentials = $Credentials

    #Get the term store
    $TaxonomySession=[Microsoft.SharePoint.Client.Taxonomy.TaxonomySession]::GetTaxonomySession($Ctx) 
    $TaxonomySession.UpdateCache()
    $TermStore =$TaxonomySession.GetDefaultSiteCollectionTermStore()
    $Ctx.Load($TaxonomySession)
    $Ctx.Load($TermStore)
    $Ctx.ExecuteQuery()

    #Get Termstore data from CSV and iterate through each row
    Import-Csv $CSVFile | ForEach-Object {
      
        #Get the Term Group
        $TermGroup=$TermStore.Groups.GetByName($TermGroupName)

        #Get the term set
        $TermSet = $TermGroup.TermSets.GetByName($TermSetName)

        #CSV File Header Row in Term to Add
        $TermName = $_.$($TermHeaderInCSV)
 
        #Check if the given term exists already
        $Terms = $TermSet.Terms
        $Ctx.Load($Terms)
        $Ctx.ExecuteQuery()
        $Term = $Terms | Where-Object {$_.Name -eq $TermName}
    
        If(-not $Term)
        {
            #Create Term Set
            Write-host "Creating Term '$TermName'" -ForegroundColor Cyan
            $Term = $TermSet.CreateTerm($TermName,1033,[System.Guid]::NewGuid().toString())
            $Ctx.Load($Term)
            $Ctx.ExecuteQuery()
            $Term.TermStore.CommitAll()
            $TaxonomySession.UpdateCache()
            Write-host "New Term '$TermName' Added Successfully!" -ForegroundColor Green
        }
        else
        {
            Write-host "Term '$TermName' Exists Already!" -ForegroundColor Yellow
        }
    }
 }
Catch {
    write-host -f Red "Error Importing Term store Data!" $_.Exception.Message
} 
and the result:
sharepoint online import terms


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Tuesday, November 15, 2016

Fix for "Cannot Connect to Database Master at SQL Server at Server Name. The Database might not exist, or the current user does not have permission to connect to it" SharePoint Error

Problem:
When trying to create a new SharePoint farm using PowerShell, the SharePoint 2016 management Shell gave an error message:
New-SPConfigurationDatabase : Cannot connect to database master at SQL server at <SQL-Server-Alias>. The database might not exist, or the current user does not have permission to connect to it.
New-SPConfigurationDatabase : Cannot connect to database master at SQL server at SP16_SQL. The database might not exist, or the current user does not have permission to connect to it.
Tried using SharePoint products configuration wizard as well and got the same issue!

Troubleshooting Checklist:
  1. Check the permissions for the Setup account which you are using to run the wizard or PowerShell. Make sure the setup account is granted with "DB_Creator" and "Security_Admin" Server Roles.
  2. In case of Named instance, it should be "Server\InstanceName". If is is SQL Alias, make sure the alias is properly configured and should be accessible from SharePoint Server. Verify if the SQL Server Alias is created for both 32 and 64-bit! How to Create SQL Server Alias for SharePoint
  3. Check if the SQL Server Service is running on your Database Server. Verify "TCP/IP" protocol is enabled under Network Configuration section of SQL Server Configuration Manager. 
  4. Test the connectivity between SharePoint and SQL Server. Try adding a HOST file entry in your SharePoint Server to point the SQL Server's IP!
  5. Check the firewall rules on SQL Server. If firewall is enabled on SQL Server, Create inbound TCP rule with ports: 1433,2383,2382 (or whatever dynamic port your SQL server is running) and inbound UPD rule with port: 1434.
  6. Double check if you have not fat-fingered User Name or Password!


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Tuesday, November 8, 2016

How to Create New Target Application ID in Secure Store Service for BCS in SharePoint 2016

When you want to consume external data in SharePoint, such as data from your other business applications, you can use Business Connectivity Services (BCS) together with Secure Store Service. The external data source that you can connect to is called a Secure Store Target Application. BCS makes it possible for you to set up a connection to the Target Application, and the Secure Store enables you to manage the credentials that are required by the external data source

Before proceeding, You have created and configured Secure store service application and generated the master key from SharePoint 2013/2016 Central Administration site, isn't it? If not, Refer:
Now you are all set to create new Target Applications and use in SharePoint. Here you go!

How to Create New Target Application ID?
Here are the steps to Add new Target Application ID in SharePoint 2016 Secure store Service:
  • Go to SharePoint Central Administration site >> Click on "Manage Service Applications"
  • Locate and pick your Secure store service application
  • In the Ribbon, Under the Edit tab click on New button
  • Enter the Target Application ID (Make sure the ID is unique - and you can't change it later), Display Name, Contact E-Mail. Choose the application type - Group: Maps group of users to a single set of stored credentials Individual: Maps a single user to a single set of stored credentials. Click Next.
    sharepoint secure store application id
  • The next window gives you the ability to Add/Change credential fields associated with the external data source. In my case, I left it with Windows User Name and Windows Password fields as they are sufficient to connect with SQL Server database using windows credentials. Click on Next.
    If you want to use SQL authentication , Select "User Name" and "Password" in Field type dropdown
    sharepoint secure store application id 2013
  • Enter the name(s) of the users that will administer the target application and Group who will use the target application and click OK
    sharepoint 2013 secure store target application type
Now we have successfully created target application.  

Set up Credentials for New Target Application ID:
The next step is to set the credentials for the target application ID.
  • Click on Context menu of newly created Target Application Id and choose Set Credential
    sharepoint 2016 create secure store application id
  • Enter the User Name and Password & Confirm Password which will be used to connect to the target data source on behalf of authorized users and click on OK to complete the creation of secure store target application ID. Make sure this access account (In my case its: Crescent\DBAccess) have proper rights on external database for the operation such as Read/Write.
    sharepoint designer secure store application id
Now All users of the given group can use the Application ID to connect with external data source with the application ID generated! Once the target application is created in Secure store service, you can associate it with any application to interact with the external database or application model, such as from SharePoint Designer, Excel, etc.

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


SharePoint Online: How to Hide a List or Library using PowerShell

Requirement: Hide a list or library from SharePoint Online site.

SharePoint Online: Hide Document Library using PowerShell
There are many ways to hide a SharePoint online list. Here is the PowerShell CSOM script to create hidden lists in SharePoint online.  
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
  
#Config Parameters
$SiteURL= "https://crescent.sharepoint.com/sites/sales/"
$LibraryName="ProjectConfig"

#Setup Credentials to connect
$Cred = Get-Credential
$Cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)

Try {
    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
    $Ctx.Credentials = $Cred
  
    #Get the web and Library
    $Web=$Ctx.Web
    $List=$web.Lists.GetByTitle($LibraryName)

    #Hide the list
    $List.Hidden = $True
    $List.Update()
    $Ctx.ExecuteQuery()
     
    Write-host -f Green "List hidden Successfully!"
}
Catch {
    write-host -f Red "Error hiding List: " $_.Exception.Message
}
You can also use SharePoint designer to mark a list hidden from browser. Read more here: How to hide a list in SharePoint?
how to hide list in sharepoint online

Tags: sharepoint online hidden list, sharepoint online hide task list, sharepoint online create hidden list, sharepoint online hide document library, sharepoint online hide library

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Monday, November 7, 2016

SharePoint Online: Bulk Add Terms, Term Sets and Term Groups using PowerShell

Requirement: Bulk Add Terms, Term Sets and Term Groups from a CSV File into SharePoint Online term store.

Here is the format and data to import:

PowerShell Script to Bulk Add Terms, Term Sets and Groups to SharePoint Online Term Store:
##Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Taxonomy.dll"
  
#Variables for Processing
$AdminURL = "https://crescent-admin.sharepoint.com/"

Try {
    #Get Credentials to connect
    $Cred = Get-Credential
    $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($AdminURL)
    $Ctx.Credentials = $Credentials

    #Get the term store
    $TaxonomySession=[Microsoft.SharePoint.Client.Taxonomy.TaxonomySession]::GetTaxonomySession($Ctx) 
    $TaxonomySession.UpdateCache()
    $TermStore =$TaxonomySession.GetDefaultSiteCollectionTermStore()
    $Ctx.Load($TaxonomySession)
    $Ctx.Load($TermStore)
    $Ctx.ExecuteQuery()

    #Get Termstore data from CSV and iterate through each row
    Import-Csv "C:\Users\salaudeen\Desktop\TermStoreData.csv" | ForEach-Object {
        
        $TermGroupName = $_.TermGroup
        $TermSetName = $_.TermSet
        $TermName = $_.Term

        #**** Step 1: Create Term Group ****/
        #Check if the given group exists already
        $TermGroups = $TermStore.Groups
        $Ctx.Load($TermGroups)
        $Ctx.ExecuteQuery()
        $TermGroup = $TermGroups | Where-Object {$_.Name -eq $TermGroupName}
     
        If(-not $TermGroup)
        {
            #Create Term Group
            Write-host "Creating Term Group '$TermGroupName'" -ForegroundColor Cyan
            $TermGroup = $TermStore.CreateGroup($TermGroupName, [System.Guid]::NewGuid().toString())
            $Ctx.Load($TermGroup)
            $Ctx.ExecuteQuery()
            $TermGroup.TermStore.CommitAll()
            $TaxonomySession.UpdateCache()

            Write-host "Term Group '$TermGroupName' Created Successfully!" -ForegroundColor Green
        }
        else
        {
            Write-host "Term Group '$TermGroupName' Exists Already!" -ForegroundColor Yellow
        }


        #**** Step 2: Create Term Set ****#
        #Check if the given term set exists already
        $TermSets = $TermGroup.TermSets
        $Ctx.Load($TermSets)
        $Ctx.ExecuteQuery()
        $TermSet = $TermSets | Where-Object {$_.Name -eq $TermSetName}
     
        If(-not $TermSet)
        {
            #Create Term Set
            Write-host "Creating Term Set '$TermSetName'" -ForegroundColor Cyan
            $TermSet = $TermGroup.CreateTermSet($TermSetName,[System.Guid]::NewGuid().toString(),1033)
            $Ctx.Load($TermSet)
            $Ctx.ExecuteQuery()
            $TermSet.TermStore.CommitAll()
            $TaxonomySession.UpdateCache()
     
            Write-host "Term Set '$TermSetName' Created Successfully!" -ForegroundColor Green
        }
        else
        {
            Write-host "Term Set '$TermSetName' Exists Already!" -ForegroundColor Yellow
        }


        #*** Step 3: Create Term ***#
        #Check if the given term exists already
        $Terms = $TermSet.Terms
        $Ctx.Load($Terms)
        $Ctx.ExecuteQuery()
        $Term = $Terms | Where-Object {$_.Name -eq $TermName}
    
        If(-not $Term)
        {
            #Create Term Set
            Write-host "Creating Term '$TermName'" -ForegroundColor Cyan
            $Term = $TermSet.CreateTerm($TermName,1033,[System.Guid]::NewGuid().toString())
            $Ctx.Load($Term)
            $Ctx.ExecuteQuery()
            $Term.TermStore.CommitAll()
            $TaxonomySession.UpdateCache()
            Write-host "New Term '$TermName' Added Successfully!" -ForegroundColor Green
        }
        else
        {
            Write-host "Term '$TermName' Exists Already!" -ForegroundColor Yellow
        }
    }
 }
Catch {
    write-host -f Red "Error Importing Term store Data!" $_.Exception.Message
} 


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Sunday, November 6, 2016

SharePoint Online: Create Term in Term Store using Powershell

Requirement: Create a Term in SharePoint Online Term Store.

How to Create a Term in SharePoint Online Term Store?
To create a new term in SharePoint online term store, follow these steps:
  • Navigate to your SharePoint admin center site. (E.g. https://yourdomain-admin.sharepoint.com)
  • Click the "term store" link on the left navigation menu.
  • Expand and locate the Term set in which you want to add a new Term. Click on the little arrow in the Term set Header >> Click on "Create Term" option
    SharePoint Online Create Term in Term Store using Powershell
  • Start typing your new term, Hit enter to complete creating the term.
    PowerShell to Create New Term in SharePoint Online Term Store

PowerShell to Create New Term in SharePoint Online Term Store:
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Taxonomy.dll"
  
#Variables for Processing
$AdminURL = "https://crescent-admin.sharepoint.com/"
$TermGroupName ="Regions"
$TermSetName="MENA"
$TermName="United Arab Emirates"

Try {
    #Get Credentials to connect
    $Cred = Get-Credential
    $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($AdminURL)
    $Ctx.Credentials = $Credentials

    #Get the term store
    $TaxonomySession=[Microsoft.SharePoint.Client.Taxonomy.TaxonomySession]::GetTaxonomySession($Ctx) 
    $TermStore =$TaxonomySession.GetDefaultSiteCollectionTermStore()
    $Ctx.Load($TaxonomySession)
    $Ctx.Load($TermStore)
    $Ctx.ExecuteQuery()

    #Get the Term Group    
    $TermGroup = $TermStore.Groups.GetByName($TermGroupName)
    $Ctx.Load($TermGroup)

    #Get the Term Set
    $TermSet = $TermGroup.TermSets.GetByName($TermSetName)
    $Ctx.Load($TermSet)

    #Check if the given term exists already
    $Terms = $TermSet.Terms
    $Ctx.Load($Terms)
    $Ctx.ExecuteQuery()
    $Term = $Terms | Where-Object {$_.Name -eq $TermName}
    
    If(-not $Term)
    {
        #Create Term Set
        $NewTerm = $TermSet.CreateTerm($TermName,1033,[System.Guid]::NewGuid().toString())
        $Ctx.Load($NewTerm)
        $Ctx.ExecuteQuery()
    
        Write-host "New Term '$TermName' Created Successfully!" -ForegroundColor Green
    }
    else
    {
        Write-host "Term '$TermName' Exists Already!" -ForegroundColor Yellow
    }
}
Catch {
    write-host -f Red "Error Creating Term!" $_.Exception.Message
}
PowerShell to add Multiple Terms to Term set in SharePoint Online:
The above script adds a single term to specific term set in the term store. Lets add multiple terms to a term set.
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Taxonomy.dll"
  
#Variables for Processing
$AdminURL = "https://crescent-admin.sharepoint.com/"
$TermGroupName ="Regions"
$TermSetName="MENA"
$TermsToAdd =@("Bahrain", "Egypt", "Iran", "Iraq", "Jordan", "Kuwait", "Lebanon", "Oman", "Palestine", "Qatar", "Saudi Arabia", "Syria", "Turkey", "United Arab Emirates", "Yemen")

Try {
    #Get Credentials to connect
    $Cred = Get-Credential
    $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($AdminURL)
    $Ctx.Credentials = $Credentials

    #Get the term store
    $TaxonomySession=[Microsoft.SharePoint.Client.Taxonomy.TaxonomySession]::GetTaxonomySession($Ctx) 
    $TermStore =$TaxonomySession.GetDefaultSiteCollectionTermStore()
    $Ctx.Load($TaxonomySession)
    $Ctx.Load($TermStore)
    $Ctx.ExecuteQuery()

    #Get the Term Group    
    $TermGroup = $TermStore.Groups.GetByName($TermGroupName)
    $Ctx.Load($TermGroup)

    #Get the Term Set
    $TermSet = $TermGroup.TermSets.GetByName($TermSetName)
    $Ctx.Load($TermSet)

    #Create Terms
    Foreach ($TermName in $TermsToAdd)
    {
        #Check if the given term exists already
        $Terms = $TermSet.Terms
        $Ctx.Load($Terms)
        $Ctx.ExecuteQuery()
        $Term = $Terms | Where-Object {$_.Name -eq $TermName}
    
        If(-not $Term)
        {
            #Create Term Set
            $NewTerm = $TermSet.CreateTerm($TermName,1033,[System.Guid]::NewGuid().toString())
            $Ctx.ExecuteQuery()
            $TermStore.CommitAll()
    
            Write-host "New Term '$TermName' Added Successfully!" -ForegroundColor Green
        }
        else
        {
            Write-host "Term '$TermName' Exists Already!" -ForegroundColor Yellow
        }
    }
}
Catch {
    write-host -f Red "Error Creating Term!" $_.Exception.Message
}


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


You might also like:

Related Posts Plugin for WordPress, Blogger...