Tuesday, November 15, 2016

User Profile Synchronization Service does not start

User Profile Sync Service is a must-have feature in SharePoint 2013 in order to look up AD users properly via the commonly used user lookup dialog or user lookup button.

While going through many installations and configurations projects of SharePoint, the User Profile Sync Service never started right the first time on me.  It does take extra steps to properly set up and configure.

Below is the common technique to resolve the issue.


  1. Add service account user to SharePoint Farm Admin Group on SharePoint Server.
  2. Add service account user to local admin group of the SharePoint Server.
  3. Restart the Timer Service
  4. Open ADSI Edit as domain admin.
    1. Right-click and Connect To "Default naming context". 
    2. Go to "Select a well known Naming context:" and choose "Configuration". 
    3. Click [OK].
    4. Expand Configuration. 
    5. Right Click [CN=Configuration, DC....]. Select [Properties]. Select [Security].
    6. Add service account user and assign [Read] and [Replicating Directory Changes] permissions.
    7. Click [OK].
  5. Now go to Central Admin Site.
  6. Go to "Manage Service Applications"
  7. Click [User Profile Service Application]. 
    1. Check [Permissions] at the top ribbon menu. Make sure the service account user exists.
    2. Check [Administrators] at the top ribbon menu.Make sure the service account user exists with full permission.
  8. Try starting User Profile Sync Service again and see if it works.

Saturday, November 12, 2016

SharePoint Workflow Manager - Useful Info

Here are some useful links and reminders when setting up or troubleshooting the SharePoint Workflow Manager 1.0.

Suppose the SharePoint environment is made of the following:
Domain Name: contoso.edu
SharePoint Server FQDN: sp.contoso.edu (admin site port = 13311)
SharePoint Server Computer Name: SP13
Workflow Manager FQDN: sqlwopi.contoso.edu

  • Check if Workflow Manager is connected:
    http://sp.contoso.edu:13311/_admin/WorkflowServiceStatus.aspx 
  • Check if Workflow Service is working (Do this from the Sharepoint Server):
    http://sqlwopi.contoso.edu:12291 (if http is used)
    https://sqlwopi.contoso.edu:12290 
  • To check the Workflow Manager Service status (Do this on the Workflow Manager Service Server):
    Get-WFFarmStatus
  • To connect SharePoint Farm to Workflow Manager Service (on SharePoint Server):
    Register-SPWorkflowService -SPSite "https://sp.contoso.edu" -WorkflowHostUri "http://sqlwopi.contoso.edu:12291" -AllowOAuthHttp -Force (when http is used)

Tuesday, July 26, 2016

Provider-Hosted Add-in CRUD example using CSOM

Below is a complete CRUD example of provider-hosted SharePoint Add-In using CSOM. In the controller, the CRUD is performed on a SharePoint list, "Products", which is made of [ID], [Title], [UnitPrice] and [UnitsOnStock] fields.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Microsoft.SharePoint.Client;
using ProviderHostedCloud1Web.Models;

namespace ProviderHostedCloud1Web.Controllers
{
    [SharePointContextFilter]
    public class ProductController : Controller
    {

        // GET: Product
        public ActionResult Index()
        {
            var spContext = SharePointContextProvider.Current.GetSharePointContext(HttpContext);
            using (ClientContext cc = spContext.CreateUserClientContextForSPHost())
            {
                if (cc != null)
                {
                    List productList = cc.Web.Lists.GetByTitle("Products");
                    cc.Load(productList);
                    cc.ExecuteQuery();

                    if (productList != null)
                    {
                        CamlQuery query = CamlQuery.CreateAllItemsQuery();
                        ListItemCollection products = productList.GetItems(query);
                        cc.Load(products);
                        cc.ExecuteQuery();

                        List retVal = new List();
                        foreach (var product in products)
                        {
                            retVal.Add(new Product
                            {
                                ID = product.Id,
                                Title = product["Title"].ToString(),
                                UnitPrice = Convert.ToDecimal(product["UnitPrice"]),
                                UnitsOnStock = Convert.ToInt32(product["UnitsOnStock"])
                            });
                        }
                        return View(retVal);
                    }

                    return View();
                }
                else
                {
                    ViewBag.ErrorMessage = "Error: ClientContext is null.";
                    return View();
                }
            }

        }

        // GET: Product/Details/5
        public ActionResult Details(int id)
        {
            try
            {
                var spContext = SharePointContextProvider.Current.GetSharePointContext(HttpContext);
                using (ClientContext cc = spContext.CreateUserClientContextForSPHost())
                {
                    if (cc != null)
                    {
                        ListItem item = cc.Web.Lists.GetByTitle("Products").GetItemById(id);
                        cc.Load(item);
                        cc.ExecuteQuery();

                        if (item != null)
                        {
                            Product retVal = new Product
                            {
                                ID = item.Id,
                                Title = item["Title"].ToString(),
                                UnitPrice = item["UnitPrice"] == null ? 0.00m : Convert.ToDecimal(item["UnitPrice"]),
                                UnitsOnStock = item["UnitsOnStock"] == null ? 0 : Convert.ToInt32(item["UnitsOnStock"])
                            };

                            return View(retVal);
                        }
                        else
                        {
                            return View();
                        }
                    }
                    else
                    {
                        ViewBag.ErrorMessage = "Error: ClientContext was null. ";
                        return View();
                    }
                }
            }
            catch (Exception ex)
            {
                Exception ie = ex;
                while (ie.InnerException != null)
                {
                    ie = ie.InnerException;
                }
                ViewBag.ErrorMessage = "Error: " + ie.Message;
                return View();
            }
        }

        // GET: Product/Create
        public ActionResult Create()
        {
            return View();
        }

        // POST: Product/Create
        [HttpPost]
        public ActionResult Create(System.Web.Mvc.FormCollection collection)
        {
            string title = collection["Title"].ToString();
            decimal unitPrice = collection["UnitPrice"] == null ? 0.00m : Convert.ToDecimal(collection["UnitPrice"]);
            int unitsOnStock = collection["UnitsOnStock"] == null ? 0 : Convert.ToInt32(collection["UnitsOnStock"]);

            try
            {
                var spContext = SharePointContextProvider.Current.GetSharePointContext(HttpContext);
                using (ClientContext cc = spContext.CreateUserClientContextForSPHost())
                {
                    List productList = cc.Web.Lists.GetByTitle("Products");
                    ListItemCreationInformation creationInfo = new ListItemCreationInformation();
                    ListItem item = productList.AddItem(creationInfo);
                    item["Title"] = title;
                    item["UnitPrice"] = unitPrice;
                    item["UnitsOnStock"] = unitsOnStock;
                    item.Update();
                    cc.ExecuteQuery();

                    return RedirectToAction("Index", new { SPHostUrl = Request.QueryString["SPHostUrl"] });
                }
            }
            catch (Exception ex)
            {
                Exception ie = ex;
                while (ie.InnerException != null)
                {
                    ie = ie.InnerException;
                }
                ViewBag.ErrorMessage = "Error: " + ie.Message;
                Product product = new Product { Title = title, UnitPrice = unitPrice, UnitsOnStock = unitsOnStock };
                return View(product);
            }
        }

        // GET: Product/Edit/5
        public ActionResult Edit(int id)
        {
            var spContext = SharePointContextProvider.Current.GetSharePointContext(HttpContext);
            using (ClientContext cc = spContext.CreateUserClientContextForSPHost())
            {
                if (cc != null)
                {
                    ListItem item = cc.Web.Lists.GetByTitle("Products").GetItemById(id);
                    cc.Load(item);
                    cc.ExecuteQuery();
                    if (item != null)
                    {
                        Product product = new Product
                        {
                            ID = item.Id,
                            Title = item["Title"].ToString(),
                            UnitPrice = item["UnitPrice"] == null ? 0.00m : Convert.ToDecimal(item["UnitPrice"]),
                            UnitsOnStock = item["UnitsOnStock"] == null ? 0 : Convert.ToInt32(item["UnitsOnStock"])
                        };
                        return View(product);
                    }
                    else
                    {
                        return View();
                    }
                }
                else
                {
                    ViewBag.ErrorMessage = "Error: ClientContext was null";
                    return View();
                }

            }
        }

        // POST: Product/Edit/5
        [HttpPost]
        public ActionResult Edit(int id, System.Web.Mvc.FormCollection collection)
        {
            try
            {
                string title = collection["Title"].ToString();
                decimal unitPrice = Convert.ToDecimal(collection["UnitPrice"]);
                int unitsOnStock = Convert.ToInt32(collection["UnitsOnStock"]);

                var spContext = SharePointAcsContextProvider.Current.GetSharePointContext(HttpContext);
                using (ClientContext cc = spContext.CreateUserClientContextForSPHost())
                {
                    if (cc != null)
                    {
                        ListItem item = cc.Web.Lists.GetByTitle("Products").GetItemById(id);
                        item["Title"] = title;
                        item["UnitPrice"] = unitPrice;
                        item["UnitsOnStock"] = unitsOnStock;
                        item.Update();
                        cc.ExecuteQuery();
                        return RedirectToAction("Index", new { SPHostUrl = Request.QueryString["SPHostUrl"] });
                    }
                    else
                    {
                        ViewBag.ErrorMessage = "ClientContext was null.";
                        Product product = new Product
                        {
                            ID = id,
                            Title = title,
                            UnitPrice = unitPrice,
                            UnitsOnStock = unitsOnStock
                        };
                        return View(product);
                    }
                }
            }
            catch (Exception ex)
            {
                Exception ie = ex;
                while (ie.InnerException != null)
                {
                    ie = ie.InnerException;
                }
                ViewBag.Error = "Error: " + ie.Message;
                return View();
            }
        }

        // GET: Product/Delete/5
        public ActionResult Delete(int id)
        {
            var spContext = SharePointContextProvider.Current.GetSharePointContext(HttpContext);
            using (ClientContext cc = spContext.CreateUserClientContextForSPHost())
            {
                if (cc != null)
                {
                    ListItem item = cc.Web.Lists.GetByTitle("Products").GetItemById(id);
                    cc.Load(item);
                    cc.ExecuteQuery();

                    if (item != null)
                    {
                        Product product = new Product
                        {
                            ID = item.Id,
                            Title = item["Title"].ToString(),
                            UnitPrice = item["UnitPrice"] == null ? 0.00m : Convert.ToDecimal(item["UnitPrice"]),
                            UnitsOnStock = item["UnitsOnStock"] == null ? 0 : Convert.ToInt32(item["UnitsOnStock"])
                        };
                        return View(product);
                    }
                    else
                    {
                        return View();
                    }
                }
                else
                {
                    ViewBag.Error = "Error: ClientContext was null";
                    return View();
                }
            }

        }

        // POST: Product/Delete/5
        [HttpPost]
        public ActionResult Delete(int id, System.Web.Mvc.FormCollection collection)
        {
            string title = collection["Title"].ToString();
            decimal unitPrice = Convert.ToDecimal(collection["UnitPrice"]);
            int unitsOnStock = Convert.ToInt32(collection["UnitsOnStock"]);
            try
            {
                var spContext = SharePointContextProvider.Current.GetSharePointContext(HttpContext);
                using (ClientContext cc = spContext.CreateUserClientContextForSPHost())
                {
                    if (cc != null)
                    {
                        ListItem item = cc.Web.Lists.GetByTitle("Products").GetItemById(id);
                        item.DeleteObject();
                        cc.ExecuteQuery();
                        return RedirectToAction("Index", new { SPHostUrl = Request.QueryString["SPHostUrl"] });
                    }
                    else
                    {
                        Product product = new Product
                        {
                            ID = id,
                            Title = title,
                            UnitPrice = unitPrice,
                            UnitsOnStock = unitsOnStock
                        };
                        ViewBag.Error = "Error: ClientContext was null";
                        return View();
                    }
                }
            }
            catch (Exception ex)
            {
                Exception ie = ex;
                while (ie.InnerException != null)
                {
                    ie = ie.InnerException;
                }
                ViewBag.ErrorMessage = "Error: " + ie.Message;
                return View();
            }
        }
    }
}

Monday, July 25, 2016

Bootstrap Popover() - useful example

Bootstrap popover() example below has a few useful features:
  1. Popover initiates via mouse-hover over <span class="paymentNum">
  2. trigger: 'manual' is used with onmouseenter and onmouseleave event to mimic trigger by hover
  3. User can move the mouse into the popover. Popover dissapears when mouse is completely out of the popover-initiating span tag or the shown popover
  4. Once popover is shown, it stays visible even when mouse moves between the popover-initiating span tag and the popover itself (This would not work if trigger: 'hover' was used. It was possible by using trigger: 'manual' and onmouseenter and onmouseover events).
  5. Popover title is dynamically loaded.
  6. Popover content is dynamically loaded via ajax. Loading... is shown if there is a delay. When ajax get is done, its response dynamically replaces popover content.
<span class="paymentNum">@payment.PaymentNum</span>
<input type="hidden" class="payment-id" value="@payment.PaymentID" />
                       
.....

<script type="text/javascript">
        $(function () {

            $('span.paymentNum').popover({
                trigger: 'manual',
                placement: 'right',                
                title: function(){
                    return "Payment " + $(this).text();
                },
                html: true,
                animation: false,
                container: 'body',  
                template: '<div class="popover" role="tooltip" style="min-width: 400px; background-color: #fff; ">' +
                            '<div class="arrow"></div>' +
                            '<h3 class="popover-title" style="background-color: #dedede"></h3>' +
                            '<div class="popover-content" style="height: 400px; overflow-y: auto; font-size: 0.9em;"></div>' +
                            '</div>',
                content: function () {
                    var div_id = "temp_" + $.now();
                    var payment_id = $(this).parent().find('.payment-id').val();
                    
                    $.ajax({
                        type: 'GET',
                        url: GL.site_root_path + '/Payment/_QuickSummary/' + payment_id,
                        cache: false,
                        beforeSend: function () { },
                        complete: function () { }
                    }).done(function (d) {
                        $('#' + div_id).html(d); // Genius!!!
                    });
                    return '<div id="' + div_id + '"><div style="margin-top: 180px; text-align: center; font-size: 1.2em;">Loading ...<i class="fa fa-refresh fa-spin"></i></div></div>';                    
                }
            }).on('mouseenter', function () {
                var _this = this;
                $(this).popover("show");
                $('.popover').on('mouseleave', function () {
                    $(_this).popover("hide");
                });
            }).on('mouseleave', function () {
                var _this = this;
                setTimeout(function () {
                    if (!$('.popover:hover').length) {
                        $(_this).popover("hide");
                    }
                }, 50);
            });
        });

    </script>

Wednesday, June 29, 2016

SharePoint - unlock a file that is exclusively locked by another user

It appears that both SharePoint 2010 and 2013 still have the bug to potentially lock a file even away from the very person who last checked out the file.

This buggy behavior is described in this Microsoft article.

Basically, when a document in SharePoint is opened by a client program like Word, Excel, SharePoint places a lock on the document on the server. The write lock times out after 10 minutes. Users cannot modify the document during the time when the document is locked.

In a scenario where the program (Word, Excel, etc) that opens the document unexpectedly quits or crashes and you try to open the document again before the write lock times out (10 minutes), you may receive the message that says the document is (exclusively) locked by another user. Even if you are the last person who opened the document, you may receive this message, too.

Now the problem may continue even if you wait for 10 minutes. When the document is coninued to be in locked status, we can use Powershell script to remove the lock as described here.

## To find out about locked info of the file
$web = Get-SPWeb "https://sharepoint.mycompany.com/sites/mysite"
$list = $web.Lists["Shared Documents"]
$item = $list.GetItemById(3)
$file = $item.File
$file

The LockType property from script above may show "None", yet the file may still be locked away from the last person who opened it. Now use Powershell to clear the lock.
$fileTime = New-Object System.TimeSpan(10000)
## Create a new file lock on the file
$file.Lock([Microsoft.SharePoint.SPFile+SPLockType]::Exclusive, "Test Lock", $fileTime)
## Remove the lock from the file
$file.UndoCheckOut()

If the script is actually locked by another user and the lock is not being released for any reason, you can use the following script to remove the lock via impersontion, too.
$web = Get-SPWeb "https://sharepoint.mycompany.com/sites/mysite"
$list = $web.Lists["Shared Documents"]
$item = $list.GetItemById(3)
$file = $item.File
$userID = $file.LockedByUser.ID
$user = $web.AllUsers.GetByID($userID)

$impersonatedSite = New-Object Microsoft.SharePoint.SPSite($web.Url, $user.UserToken)
$impersonatedWeb = $impersonatedSite.OpenWeb();
$impersonatedList = $impersonatedWeb.Lists[$list.Title]
$impersonatedItem = $impersonatedList.GetItemById($item.ID)
$impersonatedFile = $impersonatedItem.File
$impersonatedFile.ReleaseLock($impersonatedFile.LockId)

Tuesday, June 14, 2016

Change Title (linked to item with edit menu) to a different column in SharePoint List

By default, the automatically created Title column of custom SharePoint List functions as the link to item detail view and carries the ellipse button (item edit menu).

In order to hide Title column and associate the "link to item with edit menu" to another column:

  1. Go to List Settings --> Advanced Settings --> "Yes" on Allow management of content types. On the List Settings, go to Content Types --> Item --> Hide "Title" column. Afterwards, "Title" column will not appear in Create, Edit and Details forms.
  2. Go to the View(s) correcponding to List View and hide "Title" column.
  3. Use SharePoint 2013 Designer, open AllItems.aspx page or corresponding list page, look for <ViewFields> tag. Add linkToItem="TRUE" linkToItemAllowed="TRUE" listItemMenu="TRUE" to the column that should function as link to details view and carry the ellipse button (item edit menu). Highlighted column below is an example. Beware that these attributes and their values are case-sensitive!
<View Name="{EDF62A70-F0D9-4B36-B3C4-F35017C57868}" 
    DefaultView="TRUE" 
    MobileView="TRUE" 
    MobileDefaultView="TRUE" 
    Type="HTML" 
    DisplayName="All Items" 
    Url="/developer/Ken/Demo1/Lists/Classroom/AllItems.aspx" Level="1" 
    BaseViewID="1" 
    ContentTypeID="0x" 
    ImageUrl="/_layouts/15/images/generic.png?rev=23" >
    <Query><OrderBy><FieldRef Name="ID"/></OrderBy></Query>
    <ViewFields>
        <FieldRef Name="Title"  linkToItem="TRUE" linkToItemAllowed="TRUE" listItemMenu="TRUE" />
        <FieldRef Name="Subject"/>
        <FieldRef Name="Teacher"/>
    </ViewFields>
    <RowLimit Paged="TRUE">30</RowLimit>
    <JSLink>clienttemplates.js</JSLink>
    <XslLink Default="TRUE">main.xsl</XslLink>
    <Toolbar Type="Standard"/>
</View>

Tuesday, June 7, 2016

Recursive Example of Common Table Expression - GetAcronym

Below is an example of SQL function that takes text with space as separator and returns acronym with first letter of each seperated word.
Select dbo.GetAcronym('Hello World  Whats Up', ' ') 
/* There are 2 spaces between World and Whats */

will return HW WU

The function definition is as follows. It uses Common Table Expression with Union All to run a recursive query.
CREATE function dbo.GetAcronym
(
  @str varchar(4000), @separator char(1) = ' '
)
returns varchar(4000)
as
begin

declare @retVal varchar(4000)
if @separator is null
  begin
  set @separator = ' '
  end

;with tokens(p, a, b)
as
(
  select  1, 1, charindex(@separator, @str)
  union all
  select p + 1, b + 1, charindex(@separator, @str, b + 1)
  from tokens /* Recursive Query via Common Table Expression is used along with Union All */
  where b > 0
)

select @retVal = convert(
  varchar(4000), 
  (select substring (@str, a, 1) from tokens for xml path (''), type ), 
  1
)
return @retVal

end


Recursive example of Common Table Expression - SplitString

Below is an example of Recursive query using SQL Server's Common Table Expression.
declare @s varchar(2000)
set @s = 'Hello World SQL DBAs'

select *
from dbo.SplitString(@s, ' ');
go
will return
indexvalue
0Hello
1World
2SQL
3DBAs


Function definition using recursive query via Common Table Expression and Union All:
CREATE function [dbo].[SplitString] 
(
  @str nvarchar(4000), 
  @separator char(1)
)
returns table
AS
return (
        
  with tokens(p, a, b) AS 
  (
    select  1, 1, charindex(@separator, @str)
           
    union all
            
    select p + 1, b + 1, charindex(@separator, @str, b + 1)
    from tokens /* Recursive use of common table expression */
    where b > 0
    )
        
    select 
      p-1 as [index]
      ,substring(@str, a, case when b > 0 then b-a ELSE 4000 end) AS [value]
    from tokens
)

Wednesday, May 25, 2016

Excel Services - The workbook cannot be opened

After embedding excel file via Excel Web Access webpart, an error "The workbook cannot be opened." may occur if the service account running the Excel Service Application has not been granted access to the SharePoint web application yet properly.

The powershell script below will handle permission to SharePoint Web Application for the service account.

# Service Application that runs Excel Service Application is "Contoso\SPAppPool"
#
$web = Get-SPWebApplication -Identity "https://sp.contoso.edu"
$web.GrantAccessToProcessIdentity("Contoso\SPAppPool")

In fact, what happens as a result of executing this script at the database level is that

  1. The Excel Service Account (Contoso\SPAppPool) is granted  custom database role, "SPDataAccess", which basically provides READ access to all SharePoint objects.
  2. If there are multiple content databases under https://sp.contoso.edu Web Application, then the database role, "SPDataAccess" will be granted to the Service Account on all content databases.

Tuesday, May 24, 2016

Office Web Apps Server for SharePoint 2013

Here's a quick reminder about Office Web Apps for SharePoint 2013 (detail link)

There are basically three (3) steps to set up Office Web Apps.
  • Step 1: Install pre-requisites
  • Step 2: Install Office Web Apps and configure it
  • Step 3: Have SharePoint 2013 server join Office Web Apps Server.
Below is Powershelll script to set up a Office Web Apps Server in a dev SharePoint environment. Office Web Apps server is configured as a single server with HTTPS.

Step 1: Install pre-requisites via PowerShell:
Add-WindowsFeature Web-Server,Web-Mgmt-Tools,Web-Mgmt-Console,Web-WebServer,Web-Common-Http,Web-Default-Doc,Web-Static-Content,Web-Performance,Web-Stat-Compression,Web-Dyn-Compression,Web-Security,Web-Filtering,Web-Windows-Auth,Web-App-Dev,Web-Net-Ext45,Web-Asp-Net45,Web-ISAPI-Ext,Web-ISAPI-Filter,Web-Includes,InkandHandwritingServices,NET-Framework-Features,NET-Framework-Core,NET-HTTP-Activation,NET-Non-HTTP-Activ,NET-WCF-HTTP-Activation45


Step 2: Install Office Web Apps Server 2013 and Configure it via PowerShell.
# Install Office Web Apps Servers.
# Import SSL certificate via IIS
# (Script below uses an wildcard SSL with friendly name "*.contoso.edu")
# Then run the following on the Office Web Apps Server.
# "SQLWopi.contoso.edu" is the FQDN of the Office Web Server
# -ExternalUrl is only relevant if the server will be exposed to
# public-facing internet)
#
New-OfficeWebAppsFarm -InternalUrl "https://SQLWopi.contoso.edu" -ExternalUrl "https://SQLWopi.contoso.edu" -CertificateName "*.contoso.edu" -EditingEnabled


Step 3: Connect SharePoint 2013 server to Office Web Apps Server
# Run the following Powershell script on the SharePoint 2013 server.
#
New-SPWOPIBinding -ServerName "SQLWopi.contoso.edu"

Wrap Text in PRE tag

In order to wrap text in a <pre> tag, one can use the following:
<style>
  pre {
      white-space: pre-wrap;       /* Since CSS 2.1 */
      white-space: -moz-pre-wrap;  /* Mozilla, since 1999 */
      white-space: -pre-wrap;      /* Opera 4-6 */
      white-space: -o-pre-wrap;    /* Opera 7 */
      word-wrap: break-word;       /* Internet Explorer 5.5+ */
  }
</style>

Monday, May 23, 2016

Copy SharePoint List Items To Another Identical List

The following Powershell script could be useful while importing data into existing list. If such need arises, one can import data from spreadsheet into a new list and then copy its list items to the destination list.

Keep in mind though: ReadOnly fields, such as [CreatedDateTime] or [LastUpdatedDateTime] field, cannot be manipulated through the script below.
try
{
    $web = Get-SPWeb "http://site"

    $sList = $web.Lists["Movies2"]  #source-list
    $dList = $web.Lists["Movies"]   #destination-list

    Write-Host "Working on Web: "$web.Title -ForegroundColor Green

    if($sList)
    {
        Write-Host "    Working on List: " sList.Name -ForegroundColor Cyan

        $spSourceItems = $sList.Items
        $sourceSPFieldCollection = $sList.Fields

        
        foreach($item in $spSourceItems)
        {
            if($dList)
            {
                $newSPListItem = $dList.AddItem()

                #Copy all field data except Attachments
                foreach($spField in $sourceSPFieldCollection)
                {
                    if($spField.ReadOnlyField -ne $True -and $spField.InternalName -ne "Attachments")                   
                    {
                        $newSPListItem[$($spField.InternalName)] = $item[$($spField.InternalName)]
                    }
                }

                #Copy Attachments
                foreach($leafName in $item.Attachments)
                {
                    $spFile = $sList.ParentWeb.GetFile($($item.Attachments.UrlPrefix + $leafName))
                    $newSPListItem.Attachments.Add($leafName, $spFile.OpenBinary())
                }

                #Update new LisItem
                $newSPListItem.Update()
            }
            Write-Host "        Copying $($item["Name"]) completed"
        }
       
    }
    
}
catch
{
    Write-Host "Error: " $_.Exception.ToString() -ForegroundColor Red
}

Sunday, May 15, 2016

ADQuery.ReadOnly (Active Directory Query Utility)

using System;
using System.Collections;
using System.Data;
using System.DirectoryServices;
using System.DirectoryServices.ActiveDirectory;
namespace ADQuery
{
 public class ReadOnly
 {

  public enum DNObjectClass
  {
   user,
   group,
   computer
  }

  public enum DNReturnType
  {
   distinguishedName,
   ObjectGuid
  }

  public static string FriendlyDomainToLdapDomain(string friendlyDomainName)
  {
   string result = null;
   try
   {
    DirectoryContext context = new DirectoryContext(
      DirectoryContextType.Domain, friendlyDomainName);
    Domain domain = Domain.GetDomain(context);
    result = domain.Name;
   }
   catch (DirectoryServicesCOMException ex)
   {
    result = ex.Message.ToString();
   }
   return result;
  }

  public static ArrayList EnumerateDomains()
  {
   ArrayList arrayList = new ArrayList();
   Forest currentForest = Forest.GetCurrentForest();
   DomainCollection domains = currentForest.Domains;
   foreach (Domain domain in domains)
   {
    arrayList.Add(domain.Name);
   }
   return arrayList;
  }

  public static ArrayList EnumerateCatalogs()
  {
   ArrayList arrayList = new ArrayList();
   Forest currentForest = Forest.GetCurrentForest();
   foreach (GlobalCatalog globalCatalog in currentForest.GlobalCatalogs)
   {
    arrayList.Add(globalCatalog.Name);
   }
   return arrayList;
  }

  public static ArrayList EnumerateDomainControllers()
  {
   ArrayList arrayList = new ArrayList();
   Domain currentDomain = Domain.GetCurrentDomain();
   foreach (DomainController domainController in currentDomain.DomainControllers)
   {
    arrayList.Add(domainController.Name);
   }
   return arrayList;
  }

  public ArrayList EnumerateOU(string OuDn)
  {
   ArrayList arrayList = new ArrayList();
   try
   {
    DirectoryEntry directoryEntry = new DirectoryEntry("LDAP://" + OuDn);
    foreach (DirectoryEntry directoryEntry2 in directoryEntry.Children)
    {
     string text = directoryEntry2.Path.ToString();
     arrayList.Add(text.Remove(0, 7));
     directoryEntry2.Close();
     directoryEntry2.Dispose();
    }
    directoryEntry.Close();
    directoryEntry.Dispose();
   }
   catch (DirectoryServicesCOMException ex)
   {
    arrayList.Add("An Error Occurred: " + ex.Message.ToString());
   }
   return arrayList;
  }

  public static bool Exists(string objectPath)
  {
   bool result = false;
   if (DirectoryEntry.Exists("LDAP://" + objectPath))
   {
    result = true;
   }
   return result;
  }

  public ArrayList AttributeValuesMultiString(
    string attributeName, string objectDn, ArrayList valuesCollection, bool recursive)
  {
   DirectoryEntry directoryEntry = new DirectoryEntry(objectDn);
   PropertyValueCollection propertyValueCollection = directoryEntry.Properties[attributeName];
   IEnumerator enumerator = propertyValueCollection.GetEnumerator();
   while (enumerator.MoveNext())
   {
    if (enumerator.Current != null)
    {
     if (!valuesCollection.Contains(enumerator.Current.ToString()))
     {
      valuesCollection.Add(enumerator.Current.ToString());
      if (recursive)
      {
       this.AttributeValuesMultiString(attributeName, 
         "LDAP://" + enumerator.Current.ToString(), valuesCollection, true);
      }
     }
    }
   }
   directoryEntry.Close();
   directoryEntry.Dispose();
   return valuesCollection;
  }

  public string AttributeValuesSingleString(string attributeName, string objectDn)
  {
   DirectoryEntry directoryEntry = new DirectoryEntry(objectDn);
   string result = directoryEntry.Properties[attributeName].Value.ToString();
   directoryEntry.Close();
   directoryEntry.Dispose();
   return result;
  }

  public static ArrayList GetUsedAttributes(string objectDn)
  {
   DirectoryEntry directoryEntry = new DirectoryEntry("LDAP://" + objectDn);
   ArrayList arrayList = new ArrayList();
   foreach (string text in directoryEntry.Properties.PropertyNames)
   {
    string text2 = directoryEntry.Properties[text].Value.GetType().ToString();
    string text3 = string.Empty;
    if (!directoryEntry.Properties[text].Value.GetType().IsArray)
    {
     text3 = directoryEntry.Properties[text].Value.ToString();
    }
    if (text == "objectGUID")
    {
     Guid guid = new Guid((byte[])directoryEntry.Properties[text].Value);
     text3 = guid.ToString();
     text3 = text3 + "(" + directoryEntry.Guid.ToString() + ")";
    }
    if (text == "objectSID")
    {
     Guid guid = new Guid((byte[])directoryEntry.Properties[text].Value);
     text3 = guid.ToString();
     text3 = text3 + "(" + directoryEntry.Guid.ToString() + ")";
    }
    if (text == "memberOf")
    {
     ArrayList arrayList2 = new ArrayList((object[])directoryEntry.Properties[text].Value);
     ReadOnly readOnly = new ReadOnly();
     foreach (object current in arrayList2)
     {
      string text4 = (string)current;
      text3 = text3 + "" + text4 + "";
      text3 = text3 + " (objectGuid = " + readOnly.ConvertDnToGuid(text4) + ")";
     }
    }
    if (text == "member")
    {
     ArrayList arrayList2 = new ArrayList((object[])directoryEntry.Properties[text].Value);
     ReadOnly readOnly = new ReadOnly();
     foreach (object current in arrayList2)
     {
      string text4 = (string)current;
      text3 = text3 + "" + text4 + "";
      text3 = text3 + " (objectGuid = " + readOnly.ConvertDnToGuid(text4) + ")";
     }
    }
    arrayList.Add(string.Concat(new string[]
    {
     text,
     ", type = ",
     text2,
     ", value = ",
     text3,
     ""    
     }));
   }
   return arrayList;
  }

  public DataTable GetADUserAttributes(string objectDN)
  {
   DataTable result;
   if (!DirectoryEntry.Exists("LDAP://" + objectDN))
   {
    result = null;
   }
   else
   {
    try
    {
     DirectoryEntry directoryEntry = new DirectoryEntry("LDAP://" + objectDN);
     SearchResult searchResult = new DirectorySearcher(directoryEntry)
     {
      Filter = "(&(objectCategory=Person)(objectClass=user)))",
      SearchScope = SearchScope.Subtree
     }.FindOne();
     DataTable dataTable = new DataTable();
     dataTable.Columns.Add("CN");
     dataTable.Columns.Add("displayName");
     dataTable.Columns.Add("employeeID");
     dataTable.Columns.Add("givenName");
     dataTable.Columns.Add("sn");
     dataTable.Columns.Add("sAMAccountName");
     dataTable.Columns.Add("sAMAccountType");
     dataTable.Columns.Add("telephoneNumber");
     dataTable.Columns.Add("UserAccountControl");
     dataTable.Columns.Add("UserPrincipalName");
     dataTable.Columns.Add("extensionAttribute1");
     dataTable.Columns.Add("primaryGroupID");
     dataTable.Columns.Add("mail");
     dataTable.Columns.Add("whenChanged");
     dataTable.Columns.Add("whenCreated");
     dataTable.Columns.Add("ObjectGUID");
     DataRow dataRow = dataTable.NewRow();
     directoryEntry.Close();
     result = dataTable;
    }
    catch
    {
     throw new DirectoryServicesCOMException("There was an error searching for user in AD");
    }
   }
   return result;
  }

  public string GetDistinguishedName(string sAMAccountName, string LdapDomain)
  {
   string result = string.Empty;
   string text = "LDAP://" + LdapDomain;
   DirectoryEntry directoryEntry = new DirectoryEntry(text);
   DirectorySearcher directorySearcher = new DirectorySearcher(directoryEntry);
   directorySearcher.Filter = "(&(objectClass=user)(sAMAccountName=" + sAMAccountName + "))";
   SearchResult searchResult = directorySearcher.FindOne();
   if (searchResult == null)
   {
    throw new DirectoryServicesCOMException(string.Concat(new string[]
    {
     "Cannot locate distinguishedName for ",
     sAMAccountName,
     " in ",
     text,
     "."
    }));
   }
   DirectoryEntry directoryEntry2 = searchResult.GetDirectoryEntry();
   result = "LDAP://" + directoryEntry2.Properties["distinguishedName"].Value;
   directoryEntry.Close();
   directoryEntry.Dispose();
   directorySearcher.Dispose();
   return result;
  }

  public string GetObjectDistinguishedName(
    ReadOnly.DNObjectClass dnObjectClass, ReadOnly.DNReturnType dnReturnType, 
    string objectName, string LdapDomain)
  {
   string result = string.Empty;
   string path = "LDAP://" + LdapDomain;
   DirectoryEntry directoryEntry = new DirectoryEntry(path);
   DirectorySearcher directorySearcher = new DirectorySearcher(directoryEntry);
   switch (dnObjectClass)
   {
   case ReadOnly.DNObjectClass.user:
    directorySearcher.Filter = string.Concat(new string[]
    {
     "(&(objectClass=user)(|(cn=",
     objectName,
     ")(sAMAccountName=",
     objectName,
     ")))"
    });
    break;
   case ReadOnly.DNObjectClass.group:
    directorySearcher.Filter = string.Concat(new string[]
    {
     "(&(objectClass=group)(|(cn=",
     objectName,
     ")(dn=",
     objectName,
     ")))"
    });
    break;
   case ReadOnly.DNObjectClass.computer:
    directorySearcher.Filter = string.Concat(new string[]
    {
     "(&(objectClass=computer)(|(cn=",
     objectName,
     ")(dn=",
     objectName,
     ")))"
    });
    break;
   }
   SearchResult searchResult = directorySearcher.FindOne();
   if (searchResult == null)
   {
    throw new NullReferenceException(string.Concat(new string[]
    {
     "unable to locate the distinguishedName for the object ",
     objectName,
     " in the ",
     LdapDomain,
     " domain"
    }));
   }
   DirectoryEntry directoryEntry2 = searchResult.GetDirectoryEntry();
   switch (dnReturnType)
   {
   case ReadOnly.DNReturnType.distinguishedName:
    result = "LDAP://" + directoryEntry2.Properties["distinguishedName"].Value;
    break;
   case ReadOnly.DNReturnType.ObjectGuid:
    result = directoryEntry2.Guid.ToString();
    break;
   }
   directoryEntry.Close();
   directoryEntry.Dispose();
   directorySearcher.Dispose();
   return result;
  }

  public string ConvertDnToGuid(string objectDN)
  {
   string result = string.Empty;
   if (DirectoryEntry.Exists("LDAP://" + objectDN))
   {
    DirectoryEntry directoryEntry = new DirectoryEntry("LDAP://" + objectDN);
    result = directoryEntry.Guid.ToString();
    directoryEntry.Close();
    directoryEntry.Dispose();
   }
   return result;
  }

  public static string ConvertGuidToOctetString(string objectGuid)
  {
   Guid guid = new Guid(objectGuid);
   byte[] array = guid.ToByteArray();
   string text = "";
   byte[] array2 = array;
   for (int i = 0; i < array2.Length; i++)
   {
    byte b = array2[i];
    text = text + "\\" + b.ToString("x2");
   }
   return text;
  }

  public ArrayList LogonUserGroups()
  {
   return null;
  }

  public static string ConvertGuidToDn(string guid)
  {
   DirectoryEntry directoryEntry = new DirectoryEntry();
   string nativeGuid = directoryEntry.NativeGuid;
   directoryEntry.Close();
   directoryEntry.Dispose();
   return null;
  }
 }
}

Friday, May 13, 2016

Turn on or off SharePoint Developer Dashboard

SharePoint developer dashboard can be turned on or off via Powershell script as follows
Make sure that "Usage and Health Data Collection" service application has started first under Manage Service Applications.
Add-PSSnapin "Microsoft.SharePoint.PowerShell"
$contentService = [Microsoft.SharePoint.Administration.SPWebService]::ContentService
$dashboard = $contentService.DeveloperDashboardSettings
$dashboard.DisplayLevel = "On"  # "Off" will turn off dashboard
$dashboard.Update()
After the dashboard is turned on, you will see a perfmon icon in the right upper corner of SharePoint site next to "SHARE", "FOLLOW", 'EDIT" menu items.

***In case the "Usage and Health Data Collection Proxy" has not started, use the following Powershell to start it.
$SAP = Get-SPServiceApplicationProxy | 
 where-object { $_.TypeName -eq "Usage and Health Data Collection Proxy" }

Friday, April 29, 2016

SharePoint 2013 Dev Environment Installation and Configuration

Here's a refresher on how to install and configure SharePoint 2013 dev environment.



Domain = contoso.edu
  • Domain controller  = DC01 (DNS server,  Email Server, WorkflowMgr server, too.)
  • SharePoint Server = SP13 (Database Server, too)
  • Database Server instance = SP13\SQL12 on SP13 server.
  • Service Accounts:
    • contoso\SPFarm
      • local admin to sharepoint 2013 server.
      • db_creator and security_admin roles on SQL Server instance
    • contoso\SPAppPool
      • account used for Web Application's application pool.

Prerequisite Installation

  1. Log on to SP13 as local admin
  2. Run powershell command as follows (3 lines)
  3. Import-Module ServerManager
    
    Add-WindowsFeature NET-WCF-HTTP-Activation45, NET-WCF-TCP-Activation45, NET-WCF-Pipe-Activation45
    
    Add-WindowsFeature Net-Framework-Features,Web-Server,Web-WebServer,Web-Common-Http,Web-Static-Content,Web-Default-Doc,Web-Dir-Browsing,Web-Http-Errors,Web-App-Dev,Web-Asp-Net,Web-Net-Ext,Web-ISAPI-Ext,WEB-ISAPI-Filter,Web-Health,Web-Http-Logging,Web-Log-Libraries,Web-Request-Monitor,Web-Http-Tracing,Web-Security,Web-Basic-Auth,Web-Windows-Auth,Web-Filtering,Web-Digest-Auth,Web-Performance,Web-Stat-Compression,Web-Dyn-Compression,Web-Mgmt-Tools,Web-Mgmt-Console,Web-Mgmt-Compat,Web-Metabase,Application-Server,AS-Web-Support,AS-TCP-Port-Sharing,AS-WAS-Support,AS-HTTP-Activation,AS-TCP-Activation,AS-Named-Pipes,AS-Net-Framework,WAS,WAS-Process-Model,WAS-NET-Environment,WAS-Config-APIs,Web-Lgcy-Scripting,Windows-Identity-Foundation,Server-Media-Foundation,Xps-Viewer
    
  4. Reboot SP13.
  5. Logon to SP13 as local admin
  6. Install the following prerequisites
    1. dotnetfx45_full_x86_x64.exe 
    2. MicrosoftIdentityExtensions-64.msi
    3. setup_msipc_x64.msi (Active Directory Rights Management Services Client 2.0)
    4. sqlncli.msi (SQL Server 2008 R2 Native Client Setup)
    5. Synchronization.msi (Microsoft Sync Framework Runtime v1.0 SP1 (x64)
    6. WcfDataServices.exe (WCF Data Services 5.0 (OData v3)
    7. WcfDataServices56.exe (WCF Data Services 5.6 Tools)
    8. Windows6.1-KB974405-x64.msu --> if error 0x80096002 occurs, do not install it.
    9. Windows6.1-KB2506143-x64 --> if "not-applicable-to-your-computer" error occurs, do not install it.
    10. Use command prompt as local admin and run the following (single-line):
      C:\>WindowsServerAppFabricSetup_x64.exe  /i  CacheClient,CachingService,CacheAdmin  /gac
      
    11. Install "AppFabric1.1-RTM-KB2671763-x64-ENU.exe" (Windows Server AppFabric v1.1 CU1 [KB 2671763]). If error occurs, reboot and try again.
  7. Reboot SP13.

SharePoint 2013 Installation
  1. Log on to SP13 as local admin.
  2. Mount SharePoint 2013 server iso file.
  3. Run setup.exe.
  4. Enter product key.
  5. On Server Type, select "Complete".
  6. Finish installation.
SharePoint 2013 Configuration
  1. Run SharePoint Products Configuration Wizard.
  2. Select "Create a new server farm".
  3. Specify configuration database settings
    1. Database Server = "SP13\SQL12"
    2. Database Name = "SharePoint_Config
    3. Username = contoso\spfarm
  4. Specify port number: 13311
  5. Select NTLM
  6. Do not use configuration wizard when "Welcome" page opens. Click Cancel.
  7. Change auto-generated Central Admin Content database name if needed as follows..
    1. Run the following powershell script in SharePoint Management Shell.
      $db = Get-SPContentDatabase -WebApplication "http://sp13:13311"
      Dismount-SPContentDatabase $db
      
    2. Rename the database in SQL Management Studio from "SharePoint_AdminContent_d95a4420-b726-4a73-a433-df83a2058890" to "SP13_AdminContent"
    3. User powershell to re-mount the database:
      Mount-SPContentDatabase "SP13_AdminContent" -DatabaseServer "SP13\SQL12" 
      -WebApplication "http://sp13:13311"

  8. Provision Usage and Health Data Collection Service Application
  9. $UsageService = Get-SPUsageService
    New-SPUsageApplication -Name "Usage Service Application" -DatabaseServer "SP13\SQL12" 
     -DatabaseName "SP13_UsageService" -UsageService $UsageService
    
  10. Provision State Service Application
  11. New-SPStateServiceDatabase -Name "SP13_StateService" |New-
    SPStateServiceApplication -Name "State Service" | New-
    SPStateServiceApplicationProxy -DefaultProxyGroup
    
  12. Provision Subscription Settings Service Application (required for SharePoint App)
  13. $Account = New-SPManagedAccount
    ##Enter "contoso\SPAppPool" with password on prompt.##
    
    $AppPool = New-SPServiceApplicationPool -Name
     "SP_ServiceApplicationDefaultAppPool" -Account $Account
    
    $App = New-SPSubscriptionSettingsServiceApplication 
     -ApplicationPool $AppPool -Name "Subscription Settings Service" 
     -DatabaseName "SP13_SubscriptionSettingsService"
    
    New-SPSubscriptionSettingsServiceApplicationProxy -ServiceApplication $App
    
  14. Provision App Management Service Application (required for SharePoint App)
  15. $Account = Get-SPManagedAccount "Contoso\SPAppPool"
    ## If not found, try creating a new one via "New-SPManagedAccount" ##
    
    $AppPool = New-SPServiceApplicationPool -Name 
      "AppMgmtServiceAppPool" -Account $Account
    
    $App = New-SPAppManagementServiceApplication 
      -ApplicationPool $AppPool -Name "App Management Service Application"
      -DatabaseName "SP13_AppMgmtService"
    
    New-SPAppManagementServiceApplicationProxy -ServiceApplication $App
    
  16. (Optional) Provision Search Service Application
    Important: Not recommended for dev environment. Search Service Application will make the single-server dev environment very very slow.  It is not recommended to configure and run Search Service Application for dev environment.
    $AppPool = Get-SPServiceApplicationPool 
     -Identity "SP_ServiceApplicationsDefaultAppPool"
    
    $ServerName = (Get-ChildItem env:computername).Value
    $ServiceAppName = "Search Service"
    $DatabaseName = "SP13_SearchService"
    Start-SPEnterpriseSearchServiceInstance $ServerName
    Start-SPEnterpriseSearchQueryAndSiteSettingsServiceInstance $ServerName
    
    $App = New-SPEnterpriseSearchServiceApplication -Name $ServiceAppName 
     -ApplicationPool $AppPool -DatabaseName $DatabaeName
    New-SPEnterpriseSearchServiceApplicationProxy -Name "$ServiceAppName Proxy" 
     -SearchApplication $App
    
    $clone = $App.ActiveTopology.Clone()
    $Instance = Get-SPEnterpriseSearchServiceInstance
    
    New-SPEnterpriseSearchAdminComponent -SearchTopology $clone 
     -SearchServiceInstance $Instance
    
    New-SPEnterpriseSearchContentProcessingComponent -SearchTopology $clone 
     -SearchServiceInstance $Instance
    
    New-SPEnterpriseSearchAnalyticsProcessingComponent -SearchTopology $clone
     -SearchServiceInstance $Instance
    
    New-SPEnterpriseSearchCrawlComponent -SearchTopology $clone 
     -SearchServiceInstance $Instance
    
    New-SPEnerpriseSearchIndexComponent -SearchTopology $clone 
     -SearchServiceInstance $Instance
    
    New-SPEnterpriseSearchQueryProcessingComponent -SearchTopology $clone
     -SearchServiceInstance $Instance
    
    $clone.Activate()
    
    
  17. Configure the Outgoing e-mail server information
    1. In Central Admin site, go to System Settings
    2. Click Configure outgoing e-mail settings
    3. On the Outgoing E-Mail Settings page
      1. Outbound SMTP server = dc01.contoso.edu
      2. From address = "sharepoint@contoso.edu"
  18. Create a Web Application
    1. Central Admin --> Application Management --> Web Application --> Manage Web Applications
    2. Click New
    3. Under Create New Web Application
      1. Select "Create a new IIS web site" 
        1. Name = SharePoint - 80
        2. Port = 80
        3. Path = (default) C:\inetpub\wwwroot\wss\VirtualDirectories\80
      2. Allow Anonymous = No
      3. Use Secure Sockets Layer (SSL) = No
      4. Enable Windows Authentication = checked
        1. Integrated Windows Authentication (checked) = NTLM
      5. Default Sign In Page (selected)
      6. Url = http://sp13:80/
      7. Zone = Default
      8. Create a new application pool
        1. application pool name = SharePoint - 80
        2. security account for this application pool
          1. Configurable
            1. contoso\SPAppPool
      9. Database Server = SP13\SQL12
      10. Database Name = SP13_Content
      11. Database authentication = Windows authentication (recommended)
      12. Service Application Connections = default
      13. Click [OK].
  19. Create Managed Metadata Service Application
    1. Central Admin --> Manage Service Application 
    2. Click [New]
    3. Click "Managed Metadata Service Application"
      1. Name = "Managed Metadata Service"
      2. Database Server = "SP13\SQL12"
      3. Database Name = "SP13_ManagedMetadataService"
      4. Windows Authentication (checked)
      5. New application pool name = "ManagedMetadataServiceAppPool"
      6. Security Account = configurable (checked): contoso\SPAppPool.
    4. Click [OK].
  20. Create User Profile Service Application
    1. Managed Metadata Service Application instance above (#15) is prerequisite to the User Profile Service Application. Make sure Managed Metadata Service Application has been created.
    2. Reboot SP13 (recommended) or restart SharePoint Timer Services.
    3. Log on as Contoso\SPFarm. Important!
    4. Right-click on IE and run IE as local admin. 
    5. Go to http://sp13:13311.
    6. Go to Manage Services on Server.
    7. Click "Start" link on User Profile Service. This should start really quick.
    8. Go to Manage Service Applications.
    9. Click [New] from the ribbon menu. 
    10. Select "New User Profile Service Application".
      1. Name = "User Profile Service Application"
      2. New app pool name = "UserProfileServiceAppPool"
      3. security account for app pool = configurable (Contoso\SPFarm)
      4. Profile Database:
        1. Database Server = SP13\SQL12
        2. Database Name = SP13_UserProfileService_Profile
        3. Database authentication = Windows auth
      5. Synchronization Database:
        1. Database Server = SP13\SQL12
        2. Database Name = SP13_UserProfileService_Sync
        3. Database authentication = Windows auth
      6. Social Tagging Database:
        1. Database Server = SP13\SQL12
        2. Database Name = SP13_UserProfileService_Social
        3. Database authentication = Windows auth
      7. Profile Synchronization Instance = SP13
      8. My Site Host URL = (skip this for now)
      9. My Site Managed Path = (skip this for now)
      10. Site Naming Format = User name (resolve conflicts by using domain_username)
      11. Default Proxy Group = Yes
      12. Click [Create].
    11. After successful creation of User Profile Service Application, reboot SP13 (recommended) or at least restart SharePoint Timer Service.
    12. Log on to DC01 server as domain admin.
    13. Assign "Replicating Directory Change" permission to contoso\spfarm account.
      1. Run "Active Directory Users and Computers" on DC01 as domain admin (Contoso\Administrator).
      2. Right-click the domain (contoso.edu) and select Delegate Control.
      3. Click [Next]. Click [Add]. 
      4. Enter SPFarm. Click [Check Names]. Click [OK].
      5. Click [Next]. 
      6. On the "Tasks to Delegate" page, select "Create a custom task to delegate" radio button. Click [Next].
      7. On the "Active Directory Object Type" page, 
        1. Select "This folder, existing objects in this folder, and creation of new objects in this folder" radio button. Click [Next].
      8. On the "Permissions" page, scroll down and select "Replicating Directory Changes". Click [Next]. Click [Finish].
    14. Log on to SP13 server as Contoso\SPFarm.
    15. Run IE as local admin. Go to "http://sp13:13311".
    16. Go to Manage Services on Server.
    17. Locate User Profile Synchronization Service. Click Start link.
    18. Enter SPFarm's password. Click OK.
    19. User Profile Synchronization Service is a pre-requisite for User Profile Service Application. Without it, User Profile Service Application cannot have any connections to the AD at all. 
    20. If User Profile Synchronization Service is stuck in Starting
      1. Restart SharePoint Timer Service and check again. Repeat a few times.
      2. We have to make sure both "User Profile Service" and "User Profile Synchronization Service" are in Started status. If "User Profile Synchronization Service" is stopped, start it. Enter credential of "contoso\spfarm" account on the next page. It may take several minutes to start the User Profile Sync Service.
        If it is stuck on Starting, restart the SharePoint Timer Service or Reboot (recommended). If still stuck on "Starting" status,  use the following PowerShell to stop the service and repeat this step to start it. Often enough, it takes more than a couple of trials of start - stop - start - stop until it finally starts. (source)
        #First, get GUID of the User Profile Synchronization Service 
        Get-SPServiceInstance | Where { $_.TypeName -like "User Profile Synchronization*"} | Select TypeName, Id
        #Then copy the Id value and paste it on the next command.
        Stop-SPServiceInstance [id-value]
      3. If all repeated efforts fail, we need to delete User Profile Service Application and start over its configurations.
    21. If User Profile Synchronization Service successfully started,  go to "Manage Profile Service" page by clicking "User Profile Service Application" in Service Applications Page and start setting up Profile Sync Settings.
    22. Configure SharePoint Profile Sync
      1. Go to "Manage Service Application"
      2. Click "User Profile Service Application"
      3. On "Manage Profile Service" page, click "Configure Synchronization Connections" and "Create New Connection".
        1. Connection Name = Contoso_AD
        2. Type = Active Directory
        3. Forest Name = contoso.edu
        4. Connection Settings
          1. Auto discover domain controller (checked)
          2. Authentication Provider Type = Windows Authentication
          3. Account name = contoso\spfarm
          4. port = 389
        5. Containers: Users directly below CONTOSO
      4. Click [OK].
      5. Go to "Manage Profile Service" page.
        1. Go to "Manage User Properties".
        2. Edit mapped attributes of the following:
          1. Work Email
            1. Direction = Import
            2. Attribute = mail
          2. Picture (optional)
            1. Direction = Export
            2. Attribute = thumbnailPhoto
            3. Description: Upload a photo to help others easily recognize you at meetings and events. Your picture will show up for contacts in Outlook and Lync as well as in different parts of SharePoint, but it may take a day or so to sync your changes with all of these systems.
          3. SIP Address (optional)
            1. Direction = Import
            2. Attribute = msRTCSIP-PrimaryUserAddress
        3. Find "Work Email". Click and Select [Edit].
        4. Go to "Add New Mapping" section 
          1. Manually enter Attribute = mail  and click [Add].
          2. Click [OK]. This will map mail field in AD to Work Email in User Profile Synchronization Properties.
      6. Go to Manage Profile Service page. 
        1. Click "Start Profile Synchronization"
        2. Choose "Start Full Synchronization". Click [OK].
    23. Go to "Services on Server". 
    24. If "User Profile Synchronization Service" is not in "Started" status, Click [Start] link on "User Profile Synchronization Service".
    25. Enter password for selected account name (contoso\SPFarm).
    26. Wait for ~5 min. Monitor activities on Task manager until activities slows down. 
    27. Check if status of "User Profile Synchronization Service" is in "Started".
  21. Create a new web application
    1. Create a new IIS web site: Name = SharePoint - 80
    2. Port = 80
    3. Take all default options. Click OK.
  22. Create a new Site Collection using URL http://sp13. 
  23. Install and hMailServer on DC01 server and add new domain for contoso.edu. Set up email accounts for spfarm and other SharePoint users in the active directory.
  24. Install and set up email client like Mozilla Thunderbird to test email accounts.
  25. Install Visual Studio as needed on SharePoint server.

Wednesday, March 23, 2016

How to Update DataTable and Commit it to the Database

In SSIS Script Task, it seems helpful to use a select statement to consturct a DataTable, update the DataTable as necessary, and commit the DataTable back to database's table. Below is a working example of this case.

protected int updateDeathLog()
{
    int retVal = 0;
    string connString = Dts.Variables["ConnString1"].Value.ToString();
    string sql = "SELECT ObjectId, NewFileName, PageNum, TargetFileExists, TargetFileExistsDT " +
            "FROM MyTable WHERE SourceFileExists = 1 AND TargetFileExists = 0";

    using (SqlConnection conn = new SqlConnection(connString))
    {
        try
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            string newFileFullPath = String.Empty;

            DataTable dt = new DataTable();
            adapter.Fill(dt);
            foreach(DataRow row in dt.Rows)
            {
                // If files exists in target folder, update flags and 
                // datetime field in the DataTable.
                newFileFullPath = Dts.Variables["TargetFolderDeath"].Value.ToString()
                  + "\\" + row["NewFileName"].ToString();

                if(File.Exists(newFileFullPath))
                {
                    row["TargetFileExists"] = true;
                    row["TargetFileExistsDT"] = DateTime.Now;
                }
                else
                {
                    row["TargetFileExists"] = false;
                    row["TargetFileExistsDT"] = System.DBNull.Value;
                }
            }

            // Commit update DataTable to the MyTable.
            // Prior to the commit, set up primary keys in the DataTable 
            // and create a CommandBuilder.
            DataColumn[] keyColumns = new DataColumn[2];
            keyColumns[0] = dt.Columns["ObjectId"];
            keyColumns[1] = dt.Columns["PageNum"];
            dt.PrimaryKey = keyColumns;

            SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adapter);
            adapter.Update(dt);

            retVal = dt.Rows.Count;
        }
        catch (Exception ex)
        {
            retVal = -1;
            errorMsg = ex.Message;
            stackTrace = ex.StackTrace;
        }
    }

return retVal;
}

Tuesday, March 15, 2016

Change [all files and folders] of a [directory] to 644 and 755

I came across a situation to run chmod 755 [folder] and chmod 644 [file] on my php dev folder. The following would handle all files and folders of current working folder.

sudo find . -type f -exec chmod 644 {} \;
sudo find . -type d -exec chmod 755 {} \;

Thursday, March 10, 2016

Use Bootstrap Popover to load external content through Ajax

It seemed easy to load an external contents through Ajax onto Bootstrap Popover at first. In fact, it wasn't as straightforward as I would like.
Using the content() function, it is straightforward to load any static content.
<a class="pop">Link</a>
<div id="contentDiv" style="display:none;">Some content...</div>

<script type="text/javascript">
$('.pop').popover({
    html: true,
    content: function(){
        return $('#contentDiv').html();
    }
});
</script>
However, loading an external content dynamically through Ajax plus "Loading ..." message took some thoughts.
<a class="pop" href="Product/100">Link</a>

<script type="text/javascript">
$('.pop').popover({
    html: true,
    content: function(){
        // Create a random temporary id for the content's parent div
        // with a unique number just in case.
        var content_id = "content-id-" + $.now();

        $.ajax({
            type: 'GET',
            url: $(this).prop('href'),
            cache: false,
        }).done(function(d){
            $('#' + content_id).html(d);
        });

        return '<div id="' + content_id + '">Loading...</div>';

        // Initially, the content() function returns a parent div, 
        // which shows "Loading..." message.
        // As soon as the ajax call is complete, the parent div inside 
        // the popover gets the ajax call's result.

    }
});
</script>

Keeping the Bootstrap Popover alive while mouse moves from triggering element to the popover

I tried to use the Bootstrap Popover as a quick dynamic ajax form within which I can perform further actions. By default, when popover appears as a result of hovering over the triggering element, it disappears when the triggering element is no longer hovered.

One easy way to keep the popover stay put until I am no longer hovering over the triggering element or the popover itself is to use { trigger: "manual", html: true, animation: false } and handle the trigger via "mouseenter" and "mouseleave" event.

// the triggering element has class of 'pop'
$(".pop").popover({
    trigger: "manual", 
    html: true,
    animation: false
}).on("mouseenter", function(){
    var _this = this;
    $(this).popover("show");
    $(".popover").on("mouseleave", function(){
      $(_this).popover('hide');
    });
}).on("mouseleave", function(){
    var _this = this;
    setTimeout(function(){
      if (!$(".popover:hover").length){
        $(_this).popover("hide");
      }
    }, 50); 
    // If popover is not hovered within 50 milliseconds 
    // after the mouse leaves the triggering element, 
    // the popover will be hidden. 
    // 50 milliseconds seem fine in most cases.
});

Thursday, March 3, 2016

@Html.CheckBoxFor( ) helper method problem when posting to Controller method with Bind(Include=...) annotation

@Html.CheckBoxFor(model => model.IsDeposit) will generate html
<input type="checkbox" name="IsDeposit" value="true" />
<input type="hidden"   name="IsDeposit" value="false" />
given that IsDeposit is a boolean value. Then the form is submitted to a method in controller such as
[HttpPost, ValidateAntiForgeryToken]
public ActionResult Create([Bind(Include ="PaymentID,IsDeposit")] Payment payment)
{ . . . }        
The problem is that the checkbox value will always be false whether the checkbox is checked or not.

The cause of this problem seems to be the IsDeposit in the Bind(Include=...) annotation is not handled properly yet in ASP.NET MVC. @Html.CheckBoxFor(..) creates two elements with the same name attribute (checkbox and hidden). Possibly only the hidden element is accepted to the method when Bind(Include=..) is used. I tried including "IsDeposit" twice (for example, Bind[Include=IsDeposit,IsDeposit,,]) just in case, but it did not help.

The best bet is to avoid using the Bind(Include=...) annotation when @Html.CheckBoxFor() helper method is used. Alternatively, you can also create the checkbox element manually yourself instead of using the helper method @Html.CheckBoxFor(). For example,
<input type="checkbox" name="IsDeposit" id="IsDeposit" value="true" />

Tuesday, February 9, 2016

jQuery UI Library - necessary jQuery file and version

The latest versions of QueryUI library have some quirks to note when adding its necessary references in the section. Below is examples that work and examples that do not work.

Basically when using jQuery UI version 1.11.4 or later, do not use jQuery version 2.x.x. Use jQuery version 1.10.x ~ 1.12.x without the jquery-migrate library.

// Works!
<link rel="stylesheet" href="jquery-ui-1.11.4/jquery-ui.css">
<script type="text/javascript" src="jquery-1.12.0.js"></script>
<script type="text/javascript" src="jquery-ui-1.11.4/jquery-ui.js"></script>

// Works!
<link rel="stylesheet" href="jquery-ui-1.11.4/jquery-ui.css">
<script type="text/javascript" src="jquery-1.10.2.js"></script>
<script type="text/javascript" src="jquery-ui-1.11.4/jquery-ui.js"></script>

// Does Not Work!
<link rel="stylesheet" href="jquery-ui-1.11.4/jquery-ui.css">
<script type="text/javascript" src="jquery-1.12.0.js"></script>
<script type="text/javascript" src="jquery-migrate-1.2.1.js"></script>
<script type="text/javascript" src="jquery-ui-1.11.4/jquery-ui.js"></script>

// Does Not Work!
<link rel="stylesheet" href="jquery-ui-1.11.4/jquery-ui.css">
<script type="text/javascript" src="jquery-1.10.2.js"></script>
<script type="text/javascript" src="jquery-migrate-1.2.1.js"></script>
<script type="text/javascript" src="jquery-ui-1.11.4/jquery-ui.js"></script>

// Does Not Work!
<link rel="stylesheet" href="jquery-ui-1.11.4/jquery-ui.css">
<script type="text/javascript" src="jquery-2.1.4.js"></script>
<script type="text/javascript" src="jquery-ui-1.11.4/jquery-ui.js"></script>

Thursday, February 4, 2016

RDLC on WebForm CodeBehind example

Below is a complete source code of a RDLC rendering on aspx webform page.

One important thing to note is that when parameter is used to perform a calculation within the report, the parameter needs to be supplied to the RDLC local report inside ReportViewer. This was a tricky part to understand: I wanted to grab the parameter value from the querystring and feed it directly into a stored procedure in the code-behind, so that I can generate the dataset under the matching "dataset name" embedded in the RDLC report. However, even though the RDLC does not actually use the parameter value to perform query on its own, I still needed to supply the parameter value to the RDLC report separately from feeding it to the stored procedure in the code-behind. Without doing this, the RDLC report would show blank screen.

Another important thing to use is the "SetBasePermissionsForSandboxAppDomain( )" function to allow the local report in ReportViewer to have enough permission so that it is rendered properly.

// RDLC on Webform ("RRA_fax.aspx.cs")
//
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Security;
using System.Security.Permissions;
using Microsoft.Reporting.WebForms;
using System.Configuration;
using COR.WEB.Helpers;

namespace COR.WEB.Reports
{
    public partial class RRA_fax : System.Web.UI.Page
    {
        int productID;

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                initQueryString();
                initReport();
            }
        }

        protected void initQueryString()
        {
            Int32.TryParse(Request.QueryString["ProductID"], out productID);
        }

        protected void initReport()
        {
            string connStr = ConfigurationManager
                .ConnectionStrings["MyConnString"].ConnectionString;
            
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = new SqlCommand("proc_ProductDetails", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    // supplying querystring value as parameter
                    cmd.Parameters.AddWithValue("@productID", productID);

                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet dataSet = new DataSet();
                        adapter.Fill(dataSet);
                        
                        // ds_ProductDetails is the dataset name in RDLC file.
                        ReportDataSource rds1 = 
                            new ReportDataSource("ds_ProductDetails", dataSet.Tables[0]);

                        ReportViewer1.LocalReport.SetBasePermissionsForSandboxAppDomain(
                            new PermissionSet(PermissionState.Unrestricted));
                        ReportViewer1.SizeToReportContent = true;   
                     
                        ReportViewer1.LocalReport.ReportPath = 
                            Server.MapPath("~/RDLC/ProductDetails.rdlc");
                        // important! - if RDLC originally has a parameter, 
                        // we still have to provide a value for it,
                        // even though the RDLC does not actually use it 
                        // to perform a query.
                        ReportParameter param1 = 
                            new ReportParameter("productID", productID.ToString());
                        ReportViewer1.LocalReport.SetParameters(param1);

                        ReportViewer1.LocalReport.DataSources.Clear();
                        ReportViewer1.LocalReport.DataSources.Add(rds1);

                        ReportViewer1.LocalReport.Refresh();
                    }
                }
            }
        }
    }
}

Render page in different IE version mode

IE 11 has the edge mode set as default rendering engine. Going forward, IE 11 seems to be breaking away with some of the backward-compatible.

I have run into issues with RDLC reports. The letterhead logos are embedded into the report files. They would only appear normally when rendered in IE 9 or 10 mode. A vertical line appears instead when the report is rendered in IE 5, 7, 8 and Edge-mode.

In order to have IE 11 to render the images on the page that contains RDLC report, we can use meta tag as follows to force IE to select a desired version.

<configuration> 
  ...
  <system.webServer>
    <httpProtocol>
      <customHeaders>
        <add name="X-UA-Compatible" value="IE=edge" />
        <!-- To force IE to render in edge mode. IE=9, IE=10, etc can be used, too. -->
      </customHeaders>
    </httpProtocol>
  </system.webServer>
</configuration>

Monday, January 25, 2016

RDLC report error on MVC/Webform hybrid website: The Value expression for the query parameter contains an error : Request for the permission of type System.Security.Permissions.SecurityPermission failed.

When creating RDLC report on MVC/Webform hybrid website, the following error can occur.

The Value expression for the query parameter contains an error : Request for the permission of type System.Security.Permissions.SecurityPermission failed.

Quick workaround to address this issue is by setting up permission on local report like below.

// Default.aspx.cs
...
using System.Security;
using System.Security.Permissions;

namespace MyProj.Web.Reports
{
  protected void Page_Load(object sender, EventArgs e)
  {
    if(!Page.IsPostBack)
    {
      ...
      ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/RDLC/Report1.rdlc");
      ReportViewer1.LocalReport.SetBasePermissionsForSandboxAppDomain(
        new PermissionSet(PermissionState.Unrestricted));
      ReportViewer1.LocalReport.Refresh();
    }
  }
}

Friday, January 22, 2016

Remove Password Change Requirement in AD

For test AD environment, it is often convenient to disable the default password requirements such as minimum length, expiration days, etc. Here's a quick cheat sheet on how to do it.


  1. Remote in to Domain Controller
  2. Run "Group Policy Management"
    1. Expand Forest, Domains and your AD domain
    2. Right-click the "Default Domain Policy" and select "Edit"
    3. Group Policy Management Editor appears.
      1. Expand Computer Configuration
      2. Expand Policies
      3. Expand Windows Settings
      4. Expand Security Settings
      5. Expand Account Policies
      6. Select Password Policy
        1. Enable policies with 0 values as needed in the right pane.
      7. Select Account Lockout Policy
        1. Make changes as needed.
  3. On Windows 2012 or later
    1. Use Powershell command "Invoke-GPUpdate"
  4. On Windows 2008 or earlier
    1. Use command "gpupdate /force"