SharePoint PnP: get all sites and subsites in tenant / web app

I wanted to use SharePoint PnP to get all the sites and subsites in a web application (on-premises).  I decided to use search, similar to how a user named Unnie solved it here with CSOM.

Script code

$creds = "MyPnPScriptConnectionCredentials"; # <-- arbitrary name
$url = "https://webappofinterest.domain.com"; # <-- SP on-prem web app of interest

$cnx = Connect-PnPOnline `
    -Credentials $creds `
    -UseAdfs `
    -Url $url `
    -ReturnConnection;

$qry = Submit-PnPSearchQuery `
    -Connection $cnx `
    -Query "Path:$url AND ((ContentClass:STS_Site) OR (ContentClass:STS_Web))" `
    -All `
    -TrimDuplicates:$false;

$sites = $qry.ResultRows | % { $_.Path } | Sort-Object;
$sites;

Setup steps

  1. Installed SharePointPnPPowerShell{2013|2016|2019} while logged in as the user account that the script runs under.  (I downloaded PnP from here.)
  2. Cached one set of credentials in Credential Manager in Windows so that I could run the script without saving sensitive info in there…  Went to Control Panel – User Accounts – Credential Manager.  Scrolled to the Generic Credentials section.  Added generic credentials as follows:
    • Internet or network address = MyPnPScriptConnectionCredentials
    • User name = DOMAIN\FarmSearchCrawlAccount
    • Password = {password for farm search crawler account}
  3. Made sure that the farm search crawler account was the default content access account in the Search Service Application, which is found on the Search Administration page under System Status in Central Admin.

Issues seen and solved

I encountered errors about “collection has not been initialized”, “it may need to be explicitly requested”, etc.  To solve this, I had to:

  1. Assign the result of the PnP commands to PowerShell variables.
  2. Use the correct PnP distribution to match the SharePoint on-premises version.  (I had thought that I could use 2019 for 2019, 2016 and 2013, for instance, but that resulted in inexplicable nonsensical errors.)

Footnote

In SharePoint Online PnP, there exists a Get-PnPSubWebs cmdlet that is part of the SharePointPnPPowerShellOnline distribution.

Using CSOM with SAML Authentication (ADFS)

Topics: ADFS 3.0, SharePoint 2013, claims authentication, on-premise, Azure, CSOM, SAML

Description

Recently, I was tasked with making CSOM work with these SAML-enabled web applications and host-named site collections.  So I went to the great Google and Bing parts bins, found some things that I could build upon, and got to work.

My first iteration or attempt at making CSOM work with SAML was straightforward.  I found a couple of articles that described how to use a WebClient to pop the ADFS login dialog, which then set the security token that the CSOM ClientContext used.  Works great and it is not complicated.

Problem

But at about 3 a.m. one morning, I woke up with a thought: what if I wanted to do the same thing without prompting anybody, such as for running CSOM code as a scheduled task?  So I spent some evenings on the couch researching this, gathering useful “parts” from folks online, and writing additional code.  And Fiddler and Postman were my good friends, too.

The end result shown below is a functional console application that authenticates to ADFS, obtains a security token, extracts and repackages the FedAuth cookie, and uses the FedAuth cookie for the SharePoint CSOM ClientContext to do work against a SharePoint list.

In order to make this work, an intimate understanding of the ADFS configuration is needed.

Solution

Credit and thanks for certain sections of code go to the posts identified in the source code comments and bolded.  No wheels were reinvented in the making of this solution, only custom code.  Please visit those links highlighted in the source code below before examining this code.

Note that this code has a dependency on these NuGet packages, one of which delivers TokenHelper.cs and SharePointContext.cs.

<packages>
 <package id="AppForSharePointOnlineWebToolkit" version="3.1.2" targetFramework="net45" />
 <package id="Microsoft.AspNet.WebApi.Client" version="5.2.3" targetFramework="net45" />
 <package id="Microsoft.AspNet.WebApi.Core" version="5.2.3" targetFramework="net45" />
 <package id="Newtonsoft.Json" version="6.0.4" targetFramework="net45" />
</packages>
using System;
using System.IdentityModel.Protocols.WSTrust;
using System.IdentityModel.Tokens;
using System.IO;
using System.Net;
using System.Net.Security;
using System.Security.Cryptography.X509Certificates;
using System.ServiceModel;
using System.ServiceModel.Security;
using System.Text;
using System.Web;
using System.Xml;
using SP = Microsoft.SharePoint.Client;

namespace MyConsoleApp
{
 class Program
 {
 private static string adfsEndpoint = "https://somesite.xxxx.loc/_trust/"; // Endpoint for site from where we get the token 
 private static string adfsRealm = "urn:somesite.xxxx.loc:somesite"; // ADFS realm that contains the endpoint above
 private static CookieContainer fedAuth = new CookieContainer();
 private static string webUrl = "https://somesite.xxxx.loc/finance";
 private static string fedAuthRequestCtx = HttpUtility.UrlEncode(string.Format("{0}/_layouts/15/Authenticate.aspx?Source=%2ffinance", webUrl));

 static void Main(string[] args)
 {
 // BEGIN adapted for C# from https://blogs.msdn.microsoft.com/besidethepoint/2012/10/17/request-adfs-security-token-with-powershell/

 string adfsBaseUri = "https://sts.xxxx.loc"; // your ADFS farm URL
 string adfsTrustEndpoint = "usernamemixed"; // OOTB ADFS, do not change
 string adfsTrustPath = "adfs/services/trust/13"; // OOTB ADFS, do not change
 string domain = "EUROPE"; // domain of AD account; should be in config file or propmted from the command line
 string pwd = "Quercus9%%"; // password of domain account; should be a secure string from config file, but you get the idea
 string userName = "jdoe@xxxx.loc:"; // UPN of domain account in AD

 EndpointAddress ep = new EndpointAddress(adfsBaseUri + "/" + adfsTrustPath + "/" + adfsTrustEndpoint);
 WS2007HttpBinding binding = new WS2007HttpBinding(SecurityMode.TransportWithMessageCredential);
 binding.Security.Message.EstablishSecurityContext = false;
 binding.Security.Message.ClientCredentialType = MessageCredentialType.UserName;
 binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.None;

 WSTrustChannelFactory wstcf = new WSTrustChannelFactory(binding, ep);
 wstcf.TrustVersion = TrustVersion.WSTrust13;
 NetworkCredential cred = new NetworkCredential(userName, pwd, domain);
 wstcf.Credentials.Windows.ClientCredential = cred;
 wstcf.Credentials.UserName.UserName = cred.UserName;
 wstcf.Credentials.UserName.Password = cred.Password;
 var channel = wstcf.CreateChannel();

 string[] tokenType = { "urn:oasis:names:tc:SAML:1.0:assertion", "urn:oasis:names:tc:SAML:2.0:assertion" };
 RequestSecurityToken rst = new RequestSecurityToken();
 rst.RequestType = RequestTypes.Issue;
 rst.AppliesTo = new EndpointReference(adfsRealm);
 rst.KeyType = KeyTypes.Bearer;
 rst.TokenType = tokenType[0]; // Use the first one because that is what SharePoint itself uses (as observed in Fiddler).

 RequestSecurityTokenResponse rstr = new RequestSecurityTokenResponse();
 var cbk = new System.Net.Security.RemoteCertificateValidationCallback(ValidateRemoteCertificate);
 ServicePointManager.ServerCertificateValidationCallback = (sender, certificate, chain, sslPolicyErrors) => { return true; };
 SecurityToken token = null;
 try
 {
 token = channel.Issue(rst, out rstr);
 }
 finally
 {
 ServicePointManager.ServerCertificateValidationCallback = cbk;
 }

 // END adapted for C# from https://blogs.msdn.microsoft.com/besidethepoint/2012/10/17/request-adfs-security-token-with-powershell/

 // BEGIN adapted from https://github.com/OfficeDev/PnP-Sites-Core/blob/master/Core/SAML%20authentication.md

 Cookie fedAuthCookie = TransformTokenToFedAuth(((GenericXmlSecurityToken)token).TokenXml.OuterXml);
 fedAuth.Add(fedAuthCookie);

 // END adapted from https://github.com/OfficeDev/PnP-Sites-Core/blob/master/Core/SAML%20authentication.md

 // PERFORM CSOM OPS HERE:

 string listName = "Some Existing List";
 using (SP.ClientContext ctx = new SP.ClientContext(webUrl))
 {
 ctx.ExecutingWebRequest += ctx_ExecutingWebRequest;
 SP.CamlQuery query1 = new SP.CamlQuery();
 query1.ViewXml =
 "<View>" +
 "<Query>" +
 "<OrderBy>" +
 "<FieldRef Name='Title' Ascending='FALSE'/>" +
 "</OrderBy>" +
 "</Query>" +
 "<RowLimit>10</RowLimit>" +
 "</View>";
 SP.List list = ctx.Web.Lists.GetByTitle(listName);
 SP.ListItemCollection items = list.GetItems(query1);
 ctx.Load(items);
 ctx.ExecuteQuery();
 for (int i = 0; i < items.Count; i++)
 {
 SP.ListItem item = items[i];
 ctx.Load(item);
 ctx.ExecuteQuery();
 }
 }
 }

 static void ctx_ExecutingWebRequest(object sender, SP.WebRequestEventArgs e)
 {
 e.WebRequestExecutor.WebRequest.CookieContainer = fedAuth;
 }

 private static Cookie TransformTokenToFedAuth(string samlToken)
 {
 // Adapted from https://github.com/OfficeDev/PnP-Sites-Core/blob/master/Core/OfficeDevPnP.Core/IdentityModel/TokenProviders/ADFS/BaseProvider.cs

 samlToken = WrapInSoapMessage(samlToken, adfsRealm);
 string stringData = String.Format("wa=wsignin1.0&wctx={0}&wresult={1}", fedAuthRequestCtx, HttpUtility.UrlEncode(samlToken));
 HttpWebRequest req = HttpWebRequest.Create(adfsEndpoint) as HttpWebRequest;
 req.Method = "POST";
 req.ContentType = "application/x-www-form-urlencoded";
 req.CookieContainer = new CookieContainer();
 req.AllowAutoRedirect = false;
 Stream newStream = req.GetRequestStream();

 byte[] data = Encoding.UTF8.GetBytes(stringData);
 newStream.Write(data, 0, data.Length);
 newStream.Close();
 HttpWebResponse resp = req.GetResponse() as HttpWebResponse;
 var encoding = ASCIIEncoding.ASCII;
 string responseText = "";
 using (var reader = new System.IO.StreamReader(resp.GetResponseStream(), encoding))
 {
 responseText = reader.ReadToEnd();
 }
 return resp.Cookies["FedAuth"];
 }

 [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Globalization", "CA1303:Do not pass literals as localized parameters", MessageId = "System.Xml.XmlDocument.CreateTextNode(System.String)")]
 private static string WrapInSoapMessage(string stsResponse, string relyingPartyIdentifier)
 {
 // This method code is unchanged in its entirety from https://github.com/OfficeDev/PnP-Sites-Core/blob/master/Core/OfficeDevPnP.Core/IdentityModel/TokenProviders/ADFS/BaseProvider.cs.
 // I added in two places some inline commented code, which deals with using SAML 2.0 XML schema instead of 1.x. But SharePoint doesn't use 2.0. 

 XmlDocument samlAssertion = new XmlDocument();
 samlAssertion.PreserveWhitespace = true;
 samlAssertion.LoadXml(stsResponse);

 //Select the book node with the matching attribute value.
 String notBefore = /*samlAssertion.DocumentElement["Conditions"]*/samlAssertion.DocumentElement.FirstChild.Attributes["NotBefore"].Value;
 String notOnOrAfter = /*samlAssertion.DocumentElement["Conditions"]*/samlAssertion.DocumentElement.FirstChild.Attributes["NotOnOrAfter"].Value;

 XmlDocument soapMessage = new XmlDocument();
 XmlElement soapEnvelope = soapMessage.CreateElement("t", "RequestSecurityTokenResponse", "http://schemas.xmlsoap.org/ws/2005/02/trust");
 soapMessage.AppendChild(soapEnvelope);
 XmlElement lifeTime = soapMessage.CreateElement("t", "Lifetime", soapMessage.DocumentElement.NamespaceURI);
 soapEnvelope.AppendChild(lifeTime);
 XmlElement created = soapMessage.CreateElement("wsu", "Created", "http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd");
 XmlText createdValue = soapMessage.CreateTextNode(notBefore);
 created.AppendChild(createdValue);
 lifeTime.AppendChild(created);
 XmlElement expires = soapMessage.CreateElement("wsu", "Expires", "http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd");
 XmlText expiresValue = soapMessage.CreateTextNode(notOnOrAfter);
 expires.AppendChild(expiresValue);
 lifeTime.AppendChild(expires);
 XmlElement appliesTo = soapMessage.CreateElement("wsp", "AppliesTo", "http://schemas.xmlsoap.org/ws/2004/09/policy");
 soapEnvelope.AppendChild(appliesTo);
 XmlElement endPointReference = soapMessage.CreateElement("wsa", "EndpointReference", "http://www.w3.org/2005/08/addressing");
 appliesTo.AppendChild(endPointReference);
 XmlElement address = soapMessage.CreateElement("wsa", "Address", endPointReference.NamespaceURI);
 XmlText addressValue = soapMessage.CreateTextNode(relyingPartyIdentifier);
 address.AppendChild(addressValue);
 endPointReference.AppendChild(address);
 XmlElement requestedSecurityToken = soapMessage.CreateElement("t", "RequestedSecurityToken", soapMessage.DocumentElement.NamespaceURI);
 XmlNode samlToken = soapMessage.ImportNode(samlAssertion.DocumentElement, true);
 requestedSecurityToken.AppendChild(samlToken);
 soapEnvelope.AppendChild(requestedSecurityToken);
 XmlElement tokenType = soapMessage.CreateElement("t", "TokenType", soapMessage.DocumentElement.NamespaceURI);
 XmlText tokenTypeValue = soapMessage.CreateTextNode("urn:oasis:names:tc:SAML:1.0:assertion");
 tokenType.AppendChild(tokenTypeValue);
 soapEnvelope.AppendChild(tokenType);
 XmlElement requestType = soapMessage.CreateElement("t", "RequestType", soapMessage.DocumentElement.NamespaceURI);
 XmlText requestTypeValue = soapMessage.CreateTextNode("http://schemas.xmlsoap.org/ws/2005/02/trust/Issue");
 requestType.AppendChild(requestTypeValue);
 soapEnvelope.AppendChild(requestType);
 XmlElement keyType = soapMessage.CreateElement("t", "KeyType", soapMessage.DocumentElement.NamespaceURI);
 XmlText keyTypeValue = soapMessage.CreateTextNode("http://schemas.xmlsoap.org/ws/2005/05/identity/NoProofKey");
 keyType.AppendChild(keyTypeValue);
 soapEnvelope.AppendChild(keyType);

 return soapMessage.OuterXml;
 }

 private static bool ValidateRemoteCertificate(object sender, X509Certificate certificate, X509Chain chain, SslPolicyErrors policyErrors)
 {
 // It may be insightful to examine the parameters here, while debugging, although the function only returns a value of true regardless of those parameters.
 return true;
 }
 }
}

SharePoint Client Object Model, CAML, JavaScript – Get Item by ID – Errors Solved

I came across something of a “stumper” when trying to get a list item in JavaScript (SP.CamlQuery) using the item’s ID.  It would not work.  I found many articles and forum entries that were only partially helpful in that they made me look at things to try for the first error shown below.  But as it turned out, nothing worked exactly as shown for other folks, as far as I could find.

Maybe this is specific to a certain version of the Client Object Model in SharePoint 2013, not sure.  My current version of the Microsoft.SharePoint.Client.* DLLs is v4.0.30319.  At any rate, the solution shown below worked for me.

PROBLEM

ERROR 1
One or more field types are not installed properly. Go to the list settings page to delete these fields.

ERROR 2
The property or field has not been initialized. It has not been requested or the request has not been executed. It may need to be explicitly requested.

SOLUTION

So as it turned out, the only thing that would work for me was to use the following code (important parts in bold):

var query = new SP.CamlQuery();
query.set_viewXml(
“<View><Query><Where><And>” +
“<FieldRef Name=’ID’ LookupID=’True’ /><Value Type=’Counter’>” + currentId + “” +
“<FieldRef Name=’ContentTypeId’ />{some-contenttype-id-here-notimportant}” +
“</And></Where></Query></View>”);
listItems = myList.getItems(query);
ctxt.load(listItems, ‘Include(ID, Id, OtherField1, OtherField2, OtherFieldN)’);
ctxt.executeQueryAsync(mySuccessProc, myFailProc);

It would only work when the LookupID=’True’ was added, value type was Counter, and both ID and Id were in the Include() for ctxt.Load().

Not sure if this will apply to anybody else out there.  But if it helps, cool!

Migrate SharePoint site collection to host header site collection

I recently had to devise a way to migrate a non-host header site collection in one farm to another host header (named) site collection in a different farm. So I devised a PowerShell script to accomplish this.

The script was used from going from an older SP 2010 patch level farm at Company A to a newer patch level farm at Company B, and from a non-host header site collection to a host header site collection. Here is the script:

# Script - M Hector - migrate site collection farm A - B, 
# managed path site collection to host header site collection.

# Purpose was to restore a non-host header site collection from an old content DB to a host  
# header site collection in the current content DB.  Content DBs were from different farms.

# The web application may or may not also have its own host header name.  (In this case, it
# does.)

# NOTES:
# Loaded this script in PowerShell ISE on a WFE or CA server and put breakpoints on the
# lines of code below that say, "INSERT BREAKPOINT HERE!"

Add-PSSnapin Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")

$oldContentDBBackupFile = "c:\temp\wss-content-old.bak"
$oldContentDB = "WSS_Content_Old"
$currentContentDB = "WSS_Content_Current"

$sqlDMBS = "sqldbms-1"
$webApp = "http://www.yuccas.xyz"

$oldSiteColl = "$webApp/" <# (non-host header site collection named '/' in root of current web app) #>
$newSiteColl = "http://rosettes.yuccas.xyz" <# (host header site collection) #>

# Dismounted the current content DB (not necessary when an old site collection's GUID does not
# exist in the current farm).
Dismount-SPContentDatabase -Identity $currentContentDB

# Mounted the old content DB to the desired SP web application (which in my case also has a host
# header called www.yuccas.xyz, but again, not required by any means).
Mount-SPContentDatabase $oldContentDB -DatabaseServer $sqlDMBS -WebApplication $webApp

# Got a reference to the content DB just attached.
$contentdb = Get-SPContentDatabase | Where-Object { $_.Name -match $oldContentDB }

# Upgraded the old content DB so that its version is consistent with the current farm patch level.
Upgrade-SPContentDatabase -Identity $contentdb

Write-Host "INSERT BREAKPOINT HERE!"

# Backed up the non-host header site collection from the old content DB using stsadm.exe (on purpose).
# In my case, the site collection is '/', the root site collection, but the site collection could
# be something entirely different, like '/sites/pubsite' (http://www.yuccas.xyz/sites/pubsite).
stsadm.exe -o backup -url $oldSiteColl -filename $oldContentDBBackupFile

Write-Host "INSERT BREAKPOINT HERE!"

# Dismounted the old content DB, since I now have what we want.
Dismount-SPContentDatabase -Identity $oldContentDB

Write-Host "INSERT BREAKPOINT HERE!"

# Remounted the current content DB.  It was important to tweak the MaxSiteCount to something appropriate
# for whatever count of site collections plus one or two more so that the stsadm.exe command will
# succeed below!  (Otherwise stsadm.exe will err out.)
Mount-SPContentDatabase $currentContentDB -DatabaseServer $sqlDMBS -WebApplication $webApp -MaxSiteCount 5 -WarningSiteCount 0

Write-Host "INSERT BREAKPOINT HERE!"

# Used stsadm.exe to restore the backed-up non-host header site collection to the host header site
# collection whose name is $newSiteColl (http://rosettes.yuccas.xyz).
stsadm.exe -o restore -url $newSiteColl -filename $oldContentDBBackupFile -hostheaderwebapplicationurl $webApp -overwrite

# Must not forget that with host header site collections, we have to manually add IIS bindings to the
# web application on each WFE / CA / app server of the farm.  Plus we either either need a DNS entry
# or a HOSTS file entry for the named site collection before it will be accessible in the browser.
Write-Host "Done.  " -nonewline
Write-Host "Add IIS bindings to all WFEs, update internal DNS with new record, then navigate to $newSiteColl and test."

Error: InfoPath Forms Services – Runtime – Data Connections – Remote server returned an error: (401) Unauthorized

I received the following error when my full-trust administrator-approved InfoPath form tried to call a web service data source, even in a single server farm:

InfoPath Forms Services                   Runtime – Data Connections            xxxx        Warning                …age: The remote server returned an error: (401) Unauthorized. The remote server returned an error: (401) Unauthorized.)

I discovered that there were two reasons for this error.  Either:

  1. Claims authentication was enabled on the web application and should not have been. Disabling claims auth solved this issue. Had to use classic mode authentication. -or-
  2. Kerberos needed to be enabled on the web application (for farms that have a load balancer involved).

Some red herrings (incorrect conclusions) included:

  • Thinking that NT AUTHORITY\IUSR_xxxx needed to have permission somehow to the web service.  Not in my case.
  • Thinking that the app pool account or any other accounts of InfoPath Forms Services or other needed to be explicitly added to the Members or Visitors SharePoint group in the site.  Not so in my case.

[Old] – Fixed SharePoint 2010 configuration wizard error ‘Method not found: ‘GetWYukonSafeConnectionString”

I upgraded from SP 2007 to SP 2010 months ago. The following error occurred recently when I ran the SharePoint Products Configuration Wizard. When I attempted to specify the database server instance name in the wizard, I received the following error:

Method not found: 'System.String Microsoft.SharePoint.Administration.SPSqlConnectionString.GetWYukonSafeConnectionString'

I checked the Control Panel to see whether Microsoft Office SharePoint Server 2007 remained installed. It did. I uninstalled Microsoft Office SharePoint Server 2007 to fix this problem.