Sathyam Sharepoint Blog: 2020

Thursday, December 3, 2020

SharePoint 2013 Configuring Search to Crawl Web Applications Using Claims and ADFS 2.0

A Web Application can have multiple “Authentication Providers” configured for each zone (Default, Intranet, Extranet, Internet, Custom). In order for the SharePoint Search to crawl content we need to configure “Windows Authentication” on at least one zone in the Web Application. While it is possible to configure multiple “Claims Authentication Types” for each zone, doing so raises usability concerns with the authentication process as well as features in SharePoint such as the People Picker.

This article will try to demonstrate how you can use an ADFS 2.0 Trusted Identity Provider for user authentication and still be able to index content in the Web Application with the SharePoint Search Service.

Usability Issues When Configuring Multiple Claims Authentication Types

If multiple “Claims Authentication Types” are configured for a particular zone the user will be presented with a “Sign In” page when first visiting a site hosted in that zone of the web application. For example the default zone is configured to use both “Windows Authentication” and a “Trusted Identity Provider” in the following image:

If you leave this setting in place there are two undesirable side effects:

Side Effect #1

When using the People Picker users will see entries from both authentication providers. This can get confusing because a user might logon with their ADFS identity, but the content owner may have inadvertently setup permissions with the Windows AD credentials.

If both providers are selected you’ll see two entries in the People Picker as it tries to resolve the names:

With just the “ADFS20” trusted identity provider selected in the “Claims Authentication Types” section, there’s only one entry:

Side Effect #2

With two authentication types configured users will be prompted with an additional “Sign-In” page when browsing sites in the web application. The “Sign In” page will display a drop down box listing both authentication types. If this is left in place then users will need to know which option to select and what format the logon identity will be in based on the selected option (i.e. <domain>\<user> for “Windows Authentication” and <emailaddress> for “ADFS20”). To complicate matters even further, permissions would need to be setup for both types otherwise “Access Denied” messages might be displayed if the wrong account is selected.

How To Resolve the People Picker and Sign In Page Behavior

Both issues can be avoided by configuring the “Default” zone of the web application to use only one authentication provider. In this example we want to use just the “ADFS20” provider and can do so by unchecking the “Enable Windows Authentication” and “Integrated Windows authentication” check boxes for the “Default” zone. We are then left with only one authentication provider so the “Sign In” page will no longer be displayed.

Search Issue

Notice that when we uncheck the two options for windows authentication a red message is displayed indicating that search will not be able to crawl the web application. Search requires windows authentication to be selected on at least one zone in the web application and in the current state only the ADFS20 trusted identity provider is configured. If we were to run a crawl on the web application several “Access denied” errors would be displayed:

So at this point we know it’s necessary to have only one authentication provider configured on the default zone in order to get rid of the “Sign In” page and to only show values in the people picker related to the ADFS 2.0 trusted identity provider. We also know that without windows authentication being setup on at least one zone, that the crawl account will be unable to index the web application.

To work around these issues we can extend the web application to any of the other remaining 4 zones and configure windows authentication for that particular zone (i.e. Intranet). This will involve the following steps:

Create a DNS A-record for the extended web app
Create a SSL Certificate
Extend the Web Application
Bind Certificate to Extended Web Application
Configure Search Content Source
Configure Server Name Mappings in Search
Execute a Full Crawl

Create a DNS A-record

I always use DNS A-records instead of CNAME’s when working with SharePoint Web Applications just in case Kerberos is configured now or later as an authentication option.

Open the DNS console from the “Administrative Tools” folder
Expand the “Forward Lookup Zones”
Right Click the domain where you want to create the entry and select the “New Host (A or AAAA)…” option

Enter the Name and IP Address
Click Add Host

Create SSL Certificate

This step is optional, but typically you want to encrypt all traffic going to the web app even though users will be accessing content through https://portal.2008r2.local instead of extended web address of https://portalsearch.2008r2.local:444 .

The certificate can be self-signed or from an internal/external certificate authority. Since we outlined earlier how to setup “Active Directory Certificate Services”, the example below will outline how to request a certificate from our internal CA through the Certificates MMC Snap-In.

Logon to the WFE server (SP-WFE-1 in our example)
Open the MMC console (Start –> Run –> MMC)
Add the Certificates Snap-in (File –> Add/Remove Snap-in…)
Select “Certificates” followed by the “Add >” button
Select the “Computer account” radio button and click “Next >”
Leave the “Local computer:” radio button selected and click “Finish”
Click “OK”

You should now have the Certificates Snap-In loaded in the MMC and are ready to request the certificate.

Navigate to the “Personal” –> “Certificates” folder.
Right click the “Certificates” folder and select the “All Tasks” –> “Request New Certificate…” option
Click “Next” on the “Before You Begin” screen
Select the “Active Directory Enrollment Policy” and click “Next”
Select the “Web Server” template followed by clicking the “More Information…” link.
From the “Subject” tab, select the “Common name” drop down option located in the “Subject name:” section.
Type in the FQDN that was created in DNS previously and will be used to extend the web application in a later step. In our example we are using “portalsearch.2008r2.local”.
Click the “Add >” button to list a CN entry in the right hand column

Select the “General” tab
Enter the optional friendly name and description for the certificate
Select the “Private Key” tab
Expand the “Key options” section
Click the “Make private key exportable” check box

Click “OK”
You should now be presented with an “Enroll” button. Click it to begin the enrollment process.
Review the status message and click “Finish”

The certificate should now be displayed in the Personal –> Certificates folder of the Certificates console

Extend the Web Application

In this step we’ll extend the “Portal” web application and configure the “Intranet” zone to use Windows Authentication. This will create another site in IIS that points to the same content in the “Portal” web application, but will allow us to configure another authentication provider and access the site through a different URL.

Open the “Central Administration” site
Click the “Manage web applications” link in the “Application Management” section
Highlight the targeted web application (Portal in our example) and click on the “Extend” button in the ribbon bar

Select the “Create a new IIS web site” radio button and fill in the fields identified below while leaving the defaults for for the others. Please note that port 443 is already in use by the default zone so we’ll use port 444 for the extended web application.

Verify the “Public URL” section and that it’s configured to use the “Intranet” zone. We can use any zone, but for the sake of the example we’ll stick with the “Intranet” zone.

Click OK to provision the extended web application. ***Please note that after clicking the “OK” button in Central Administration that the dialogue display won’t immediately go away. Do not continue to click “OK” and rather just wait a couple of minutes for the action to complete***
To verify the action completed successfully highlight the web application and click on the “Authentication Providers” button in the ribbon bar.

You should now see two entries in the “Authentication Providers” dialogue box. If you click on the “Intranet” zone link you can verify that windows authentication is the only option selected in the “Claims Authentication Types” section

Bind Certificate to the Extended Web Application

At this point we won’t be able to browse to the extended web application and need to bind it to the correct IP address and SSL certificate.

On the WFE server, open the “Internet Information Services (IIS) Manager” console from the “Administrative Tools” folder in the Start Menu.
Highlight the IIS site created through the extended web application and click on the “Bindings” link in the right hand pane.

Highlight the portalsearch.2008r2.local entry and click the “Edit” button

In our example we have multiple IP addresses bound to the same network adapter. The DNS A record we created for portalsearch.2008r2.local was assigned 192.168.153.16 so we need to specify that address. In the “SSL certificate:” field click the drop down, select the certificate we created in the previous step, and click “OK” to apply it to the site.

Since we’ve modified IIS outside of SharePoint Central Administration you’ll need to perform these same steps on other servers in the farm. The site should now render with the new URL using SSL

Configure Search Content Source

We are now ready to configure Search to crawl the extended web application using Windows authentication now that the extended web application has been provisioned.

By default SharePoint creates a content source called “Local SharePoint sites” and will place all the web applications in that entry. To view the content sources you’ll need to open the “Search Administration” site in Central Admin:

Open the Central Administration site
Click on the “Manage service applications” link in the “Application Management” section
Click on the link for the “Search Service Application”
From the Search Administration page click on the “Content Sources” link located in the left hand pane under the “Crawling” section

The “Local SharePoint sites” entry should be present. Click the drop down menu, and click “Edit”

In the “Start Addresses” section we’ll remove the entry for the main web application “https://portal.2008r2.local” and replace it with the extended web app entry “https://portalsearch.2008r2.local:444”. By doing this search will no longer attempt to crawl the main web application and as a result we won’t get all those access denied errors in the crawl log.

Click OK to commit the change

Configure Server Name Mappings in Search

At this point if a crawl of the web application was performed we would be able to index the content via the extended web application, however the results would come back with https://portalsearch.2008r2.local:444 in the description instead of https://portal.2008r2.local. We don’t want to inadvertently direct users to the extended web application so we need to configure a “Server Name Mappings” in Search to replace any address that starts with https://portalsearch.2008r2.local:444 with https://portal.2008r2.local.

In the Search Administration page click on the “Server Name Mappings” link in the “Crawling” section of the left hand pane

Click the “New Mapping” link
Fill in the following values:
- Address in index: https://portalsearch.2008r2.local:444
- Address in search results: https://portal.2008r2.local .
Click OK
You should now see the following entry:

Execute a Full Crawl

In order for the “Server Name Mappings” entry to take effect a full crawl must be performed on the content source.

Navigate to the Central Administration site
Click on the “Manage service applications” link under the “Application Management” section
Click on the link to the “Search Service Application”
From the Search Administration page click on the “Content Sources” link under the “Crawling” section in the left hand pane
Click the drop down menu for the “Local SharePoint sites” content source and select the “Start Full Crawl” option

Click OK to proceed with the full crawl

Now if we execute a search query against the web application the results will modified to point to the main web applications URL of portal.2008r2.local.

Conclusion

Anyone who works with ADFS as a Trusted Identity Provider for SharePoint will more than likely come across little nuances that weren’t present when using only “Windows Authentication”. Hopefully this article provided some insight on some of those nuances and potential ways to work around them.

Wednesday, August 26, 2020

How To Extract Actual Addresses From Hyperlinks In Excel?

Supposing you have a range of cells which contain hyperlinks, and now you need to see the real destination of the hyperlinks and extract them from the hyperlinks as following screenshot shown. Are there any easy ways to solve this problem quickly? This article will guide you to extract actual addresses from multiple hyperlinks.

Extract actual addresses from hyperlinks with Edit Hyperlink feature

Extract actual addresses from hyperlinks with VBA code

Extract actual addresses from hyperlinks with User Definde Function

Extract actual addresses from hyperlinks with Kutools for Excel quickly and easily

Extract Actual Addresses From Hyperlinks With Edit Hyperlink Feature

Here, the Edit Hyperlink function can help you extract the underlying URL for each of these hyperlinks and place that actual address into a different cell. You can do as this:

1. Select the cell with hyperlink and right-click, from the context menu, choose Edit Hyperlink, see screenshot:

2. And an Edit Hyperlink dialog box will be appeared, select and copy (Ctrl+C) the entire URL from the Address field of the dialog box.

3. Then close the dialog box and paste the actual address into any cell you want.

Note:With this method, you can extract only one hyperlink address each time, but if you have multiple hyperlinks need to be extracted, you need to apply this function repeatedly.

Extract Actual Addresses From Hyperlinks With VBA Code

For a lot of hyperlinks, the above method will be tedious and time consuming, so the following VBA code can help you quickly extract multiple addresses from the hyperlinks at once.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

Sub Extracthyperlinks()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    If Rng.Hyperlinks.Count > 0 Then
        Rng.Value = Rng.Hyperlinks.Item(1).Address
    End If
Next
End Sub

3. Then press F5 key to run the code, and a dialog pops out for you to select the hyperlinks that you want to extract the real addresses, see screenshot:

4. Then click OK, and the selected cell contents have been converted to the real hyperlink addresses in the original range. See screenshots:

Extract Actual Addresses From Hyperlinks With User Definde Function

The following User Defined Function also can extract the actual URl from the hyperlinks.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

Function GetURL(pWorkRng As Range) As String
'Updateby Extendoffice
    GetURL = pWorkRng.Hyperlinks(1).Address
End Function

3. Save the code and close the window, select a blank cell to type this formula =GetURL(A2) (A2 is the cell that the hyperlink in), and press Enter button. You can see the real hyperlink address is extracted.

Extract Actual Addresses From Hyperlinks With Kutools For Excel Quickly And Easily

Here is a handy tool, called Kutools for Excel, with its Convert Hyperlinks feature, you can quickly deal with the following operations:

Extract actual addresses from hyperlinks;
Convert url text to clickable hyperlinks.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

Go to Download
Free Trial 30 days Purchase
PayPal / MyCommerce

If you have installed Kutools for Excel, please do as follows:

1. Select the range which contains the hyperlinks need extracting.

2. Click Kutools > Link > Convert Hyperlinks, see screenshot:

3. In the Convert Hyperlinks dialog box, select Hyperlinks addresses replace cell contents option, and click button from Result range to specify a cell to put the result.

4. And then click OK, the actual addresses have been extracted from the hyperlinks. See screenshot:

Note: If you want to put the actual addresses to the original range, please check Convert source range.

Click Convert Hyperlinks to know more about this feature.

Download and free trial Kutools for Excel Now !

Demo: Extract Actual Addresses From Hyperlinks With Kutools For Excel

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!

The Best Office Productivity Tools

Kutools For Excel Solves Most Of Your Problems, And Increases Your Productivity By 80%

Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Read More... Free Download... Purchase...

Office Tab Brings Tabbed Interface To Office, And Make Your Work Much Easier

Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!