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


Thursday, October 27, 2016

Copy Permissions from One SharePoint Site to Another using PowerShell

Requirement: Copy permissions from one site to another in SharePoint!

Solution: If you want to copy one SharePoint site to another site, there is no OOTB ways! However, You can use PowerShell to copy permissions between sites. Here is my PowerShell script to copy site permissions.

Copy Permissions from one site to another using PowerShell:
Add-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue
 
#PowerShell Function to copy permissions from one site to another
Function Copy-SitePermissions()
{
 param(
     $SourceWebURL,
     $TargetWebURL
 )
 Try {
  #Set the Error Action
    $ErrorActionPreference = "Stop"
  
  #Get the Source and Target Webs
  $SourceWeb = Get-SPWeb $SourceWebURL
  $TargetWeb = Get-SPWeb $TargetWebURL
  
  #if permissions are Inherited in the target, Break it!
  if($TargetWeb.Permissions.Inherited)
  {
   #Reset the Inheritence in Target Web
   $TargetWeb.BreakRoleInheritance($false)
  }
   #Copy permissions from Source to Target Web
     $SourceWeb.RoleAssignments | foreach-object {
     $TargetWeb.RoleAssignments.Add($_)
     }
    $TargetWeb.Update()
    Write-Host "Permissions copied from Source Site to the Target!" -f Green
 }
 catch {
   Write-Host $_.Exception.Message -ForegroundColor Red
 }
 finally {
  #Reset the Error Action to Default
  $ErrorActionPreference = "Continue"
 }
}

#Call the function to copy Web permissions 
Copy-SitePermissions -SourceWebURL "http://portal.crescent.com/ProjectHub/" -TargetWebURL "http://portal.crescent.com/TaxAudits/" 


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


Sunday, October 23, 2016

Customize Suitebar Text and Link in SharePoint 2016 using PowerShell

Requirement:
By default, SharePoint 2016 comes with "SharePoint" as the suitebar branding text.  What if you would like to change it to something meaningful, say "Your Company Intranet"? Well, PowerShell can help to customize the branding text, Logo and URL in SharePoint 2016.
sharepoint 2016 add link to suite bar powershell
PowerShell Script to change Suitebar navigation text and link:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

$WebApp= Get-SPWebApplication "http://intranet.crescent.com"
$WebApp.SuiteNavBrandingText = "Crescent Portal"

#Blank image from Layouts folder
$webapp.SuiteNavBrandingLogoUrl = "/_layouts/images/dot_ruleper.gif"
$webapp.SuiteNavBrandingLogoTitle = "Crescent Logo"
$webapp.SuiteNavBrandingLogoNavigationUrl = "http://portal.crescent.com"
$webapp.Update()

Here is the result of customized SharePoint 2016 suite bar branding using PowerShell!
customizing the sharepoint 2016 suite bar branding using powershell
My another post Customizing the SharePoint 2013 suite bar branding using PowerShell

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


Monday, October 10, 2016

Add-Remove User to SharePoint Multi-Valued People Picker Field using PowerShell

Requirement: 
We've a SharePoint list called "Projects" with 1000's of items. The list has a field called "Team Members" which allows multiple user values. We often get a requirement to either add or remove a particular user to all items or specific items filtered by other columns in the list.

Solution: Adding-removing user from multiple items in a bulk can be achieved with PowerShell. Lets use PowerShell to add or remove user from SharePoint people picker (person or group) field value.
powershell to add remove user to people picker column value in sharepoint


PowerShell to Add new user to Person or Group Field:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
 
#Configuration parameters
$SiteURL = "http://portal.crescent.com/projects/"
$ListName = "Projects"                
$FieldName= "TeamMembers"
$UserToAdd="Crescent\Sam"

#Get site and List objects
$web = Get-SPWeb $SiteURL
$List = $web.Lists.TryGetList($ListName)

#Item Ids to update
$IDColl= @(4,5,7,14,55,169,258,260,261)

#Iterate through each item
foreach($ItemID in $IDColl)
{
    #Get the Item
    $Item = $List.GetItembyID($ItemID)
 Write-Host "Processing: "$item["ProjectName"]
    
    #Get Existing field value
    $MultiUserCollection = [Microsoft.SharePoint.SPFieldUserValueCollection]$item[$FieldName]
          
    #Prepre the user to Add
    $User = $Web.EnsureUser($UserToAdd)

 #Add new user to the collection
 $NewUser = new-object Microsoft.SharePoint.SPFieldUserValue($Web, $User.ID,$User.Name)
 $MultiUserCollection.Add($NewUser)

 #Update the field value
 $item[$FieldName] = $MultiUserCollection
 $item.update()   
 
 write-host "Team Member Added!"     
}

PowerShell to remove user from Multi-user People Picker field:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
 
#Configuration parameters
$SiteURL = "http://portal.crescent.com/projects/"
$ListName = "Projects"                
$FieldName= "TeamMembers"
$UserToRemove="Crescent\Sam"

#Get site and List objects
$web = Get-SPWeb $SiteURL
$List = $web.Lists.TryGetList($ListName)

$IDColl= @(184,259,281,282,306,318,331,378,404,410)
foreach($ItemID in $IDColl)
{
        #Get the Item
        $Item = $List.GetItembyID($ItemID)
        Write-Host "Processing: "$item["ProjectName"]
        
        #Get Existing field value
        $MultiUserCollection = [Microsoft.SharePoint.SPFieldUserValueCollection]$item[$FieldName]
        $NewUserCollection = new-object Microsoft.SharePoint.SPFieldUserValueCollection
       
        #Prepre the user to remove
        $User = $Web.EnsureUser($UserToRemove)
  
        #Create a new collection - exclude user to remove      
        Foreach($MultiUser in $MultiUserCollection)
        {
            if($MultiUser.User.LoginName -ne $User.LoginName)
            {
                #Add user to new collection
                $NewUser = new-object Microsoft.SharePoint.SPFieldUserValue($Web, $MultiUser.User.ID,$MultiUser.User.Name)
                $NewUserCollection.Add($NewUser)
            }
         }
        #Update the list item 
        $item[$FieldName] = $NewUserCollection
        $item.update()   
  
        write-host "User Removed From Existing People Picker field Value!"     
 }


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


Monday, October 3, 2016

Update SharePoint List Items from CSV File using PowerShell

Requirement: Update SharePoint List Items from CSV file.

Bit background: We've a list with 1000's of rows of organization wide experts. Later we added a new field for "Department". Now the requirement is to update Department field for specific list of users. While the datasheet view can be used for bulk editing, filtering and editing 100's of rows would be time consuming. So the solution is: Have list of user names and their departments in CSV file format, give it to PowerShell!

PowerShell Script to Read from CSV file and Update SharePoint List Items:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
 
#Read the CSV file
$CSVData = Import-CSV -path "C:\UserData.csv" 
 
#Get the Web
$web = Get-SPWeb -identity "http://portal.crescent.com"
 
#Get the Target List
$List = $web.Lists["ExpertProfiles"]
 
#Iterate through each Row in the CSV
foreach ($Row in $CSVData) 
{
    #Get the List Item matching "Name" field in the CSV    
    $Item = $List.Items | Where-Object { $_["Title"] -eq $Row.Name }

    if($item -ne $null)
    {
 #Update List Item - Internal Name!
        $item["Department"] = $row.Department
        $item.Update()
        Write-Host "Updated:"$row.Name -ForegroundColor Green
    }
    else
    {
        write-host "No matching Item Found for:"$row.Name -f Red
    }
}
While the above code serves the purpose, Lets optimize it with CAML-SPQuery

PowerShell to Update SharePoint List from CSV:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
 
#Read the CSV file
$CSVData = Import-CSV -path "C:\UserData.csv" 
 
#Get the Web
$web = Get-SPWeb -identity "http://portal.crescent.com"
 
#Get the Target List
$List = $web.Lists["ExpertProfiles"]
 
#Iterate through each Row in the CSV
foreach ($Row in $CSVData) 
{
    #Filter using CAML Query
    $CAMLQuery="<Where><Eq><FieldRef Name='Title'/><Value Type='Text'>$($Row.Name)</Value></Eq></Where>"
    $SPQuery=New-Object Microsoft.SharePoint.SPQuery
    $SPQuery.ViewAttributes = "Scope='Recursive'"  #Get all items including Items in Sub-Folders!
    $SPQuery.Query=$CAMLQuery
    $SPQuery.RowLimit = 1 
     
    #Get the List item based on Filter 
    $Item=$List.GetItems($SPQuery)[0]

    If($Item -ne $null)
    {
        #Update List Item
        $Item["Department"] = $Row.Department
        #$item.Update()
        Write-Host "Updated:"$row.Name -ForegroundColor Green
    }
    else
    {
        write-host "No matching Item Found for:"$row.Name -f Red
    }
}
Related Posts:


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...