SharePoint Online - SLK ListMaker: Installation and Setup

UniversitySiteListMaker is a console application that is designed to be run as a scheduled task. This application can be used to create and update SharePoint lists containing UniversitySite content. These lists can then be used in SharePoint like any other SharePoint list.

Before you run this application the first time, you will need to specify all the settings for your UniversitySite web application and your SharePoint Online application. You specify these settings in the "UniversitySiteListMaker.exe.config" file (in the bin folder).

Each subsequent time you run the app, all SharePoint Online list items will be updated or deleted to match the items in UniversitySite. The UniversitySiteListMaker app is typically run once per day as a scheduled task.

You can also run this application passing either "/deleteAll" or "/listUsers as a parameter on the command line:

/deleteAll - deletes all UniversitySite lists.
/listUsers - lists the login names for all users found (to help you figure out how to set the "NetworkAliasPrefix" and "NetworkAliasSuffix" settings in "UniversitySiteListMaker.exe.config" (in the bin folder) properly if you want to PerformSecurityTrimming.)

NOTE: When run with no command line parameters, UniversitySiteListMaker will create and update all lists that do not have blank names in the "UniversitySiteListMaker.exe.config" file.

In the ListMaker folder, there are PowerShell scripts that run the UniversitySiteListMaker passing the above command line parameter, you can just run these PowerShell scripts or add the desired parameters to the command line when you run the app manually.

RunUniversitySiteListMaker.ps1 – runs the UniversitySiteListMaker to create and update the lists.
RunUniversitySiteListMaker-DeleteAll.ps1 - deletes all UniversitySite lists.
RunUniversitySiteListMaker-ListUsers.ps1 - lists the login names for all users found.

First, you will need to find a system that you can run the UniversitySiteListMaker on that can access both your UniversitySite server and your SharePoint Online server. Copy the ListMaker folder to this server and proceed with the following steps to setup SharePoint Online to allow the UniversitySiteListMaker to connect to it and to UniversitySite. The UniversitySiteListMaker is like a pipe connecting the two systems together.

Here is what we must do to connect the two ends of the pipe and get everything up and running:

Step 1: Create an Azure AD App-Only Account for the UniversitySiteListMaker to use to connect to SharePoint Online.
Step 2: Configure the UniversitySiteListMaker config file with the ID’s and Certificate information to allow the UniversitySiteListMaker to talk to SharePoint Online.
Step 3: Configure the UniversitySiteListMaker config file with the UniversitySite URL and Access ID to allow the UniversitySiteListMaker to talk to UniversitySite.
Step 4: Specify where the log file should go when the UniversitySiteListMaker logs messages as it runs.

Step 1: Create an Azure AD App-Only Account for the UniversitySiteListMaker.

These directions loosely follow the directions in this Microsoft document. https://docs.microsoft.com/en-us/sharepoint/dev/solution-guidance/security-apponly-azuread. However, the instructions included here may (or may not) be easier to follow and more up to date.

Create a self-signed certificate for your AD App-Only account by running the Create-SelfSignedCertificate.ps1 script in the Certs folder. (We downloaded this script from that Microsoft document, but you can copy it yourself from the document if you prefer.)
1. You must run the script as administrator so it can do what it needs to do so open PowerShell as administrator.
2. Change directory into the Certs folder. (for example: cd C:\SLK\SPO\ListMaker\Certs)
3. Run the script to create the certificate. (for example: .\Create-SelfSignedCertificate.ps1 -CommonName "UniversitySiteListMaker" -StartDate 2021-06-01 -EndDate 2031-06-01)
4. You will be prompted for a password to protect the private key of the certificate you are creating. You must save the password you use to create this certificate so you can enter it in the UniversitySiteListMaker settings later. You will see two files created. One will have the file extension of “.cer” and the other “.pfx.”
Now, we must register the UniversitySiteListMaker app in the Azure Active Directory tenant that is linked to your SharePoint Online.
1. First, go to https://admin.microsoft.com/. You should see a page similar to this one, click on “Show All”:
2. Now, click on “Azure Active Directory”
3. You should see a page like this now. Again click on “Azure Active Directory”.
4. Now you should be able to click on “App Registrations” on this page:
5. Now, we need to create a “New registration” for the UniversitySiteListMaker so click on “+ New Registration”.
6. Enter the Name (for example: UniversitySiteListMaker). Then click on the “Register” button.
7. You should see a page now like this with an Application ID, Directory (tenant) ID, etc. Click on “Add a certificate or secret”.
8. Now, we are going to upload that self-signed certificate that you create back in the beginning so click on “Upload Certificate”.
9. Select that certificate file to upload (the one with the “.cer” file extension). Remember that the file is in the “Certs” folder. After upload completes, click on the "Add" button on the bottom left of the panel.
10. Next, we need to grant API permissions to the App Registration we just created. Click on “API permissions”.
11. Now, click on “+ Add a permission”.
12. You will see a panel like this, scroll down until you find “SharePoint”.
13. Click on “SharePoint”.
14. Click on “Application permissions”
15. Scroll down and check the box that says “Sites.FullControl.All” and then click on the “Add permissions” button.
16. You are likely to see the status “Not granted for xxx” (#1 below). If so, click on #2 below to “Grant admin consent…”.
17. Believe it or not, that’s all we need to do to create an Azure AD App-Only Account with access to your SharePoint Online sites. Yes, I am being sarcastic. Thanks Microsoft, for making this so easy! The good news is that you don’t need to use a real user account for the UniversitySiteListMaker app. Even if you wanted to, you probably could not because you use MFA on all your user accounts, don’t you? Please say “yes”. I don’t know about you, but I’m exhausted after all that. Normally, I’d say go get a snickers bar as a reward, but I’d actually recommend at this point calling it a day and getting a beer. Tomorrow, let’s continue with the rest of the setup. It’s easy stuff compared to what we just did. When you are ready to proceed, click on “Overview” you will need some of these IDs visible so you can copy and paste them into the UniversitySiteListMaker settings.

Step 2: UniversitySiteListMaker Settings for SharePoint Online: These settings are in the "UniversitySiteListMaker.exe.config" file (in the bin folder).

Now, in case you closed your browser and don’t have the page shown in the previous picture shown anymore, go here: https://aad.portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/RegisteredApps. Then click on “UniversitySiteListMaker” (or whatever you named it) app registration to get back there. You need to copy and paste a few things from here into the UniversitySiteListMaker settings.

The following settings are used by the UniversitySiteListMaker to connect to SharePoint Online. You will find the values to copy and paste for most of these in the previous steps.

<add key="SiteUrl" value="" />
- URL to site where you want the UniversitySiteListMaker to create/update lists
<add key="ClientId" value="" />
- Application (client) ID from the registered app overview page in the previous paragraph
<add key="TenantId" value="" />
- Directory (tenant) ID from the registered app overview page in the previous paragraph
<add key="CertificatePath" value="" />
- Path to the .pfx file you created in step 1, 1-d above
<add key="CertificatePassword" value="" />
- password for the certificate you created in step 1, 1-d above

Here’s an example:

<add key="SiteUrl" value="https://slk.sharepoint.com/sites/universitysitelistmaker" />
<add key="ClientId" value="11111111-2222-3333-4444-555555555555" />
<add key="TenantId" value="66666666-7777-8888-9999-000000000000" />
<add key="CertificatePath" value="C:\slk\spo\ListMaker\Certs\UniversitySiteListMaker.pfx" />
<add key="CertificatePassword" value="myVerySecureCertificatePassword" />

Ok now open the "UniversitySiteListMaker.exe.config" file (in the bin folder) and copy and paste YOUR values from the previous steps into it like this and then save the file:

Step 3: UniversitySiteListMaker Settings for UniversitySite: These settings are also in the "UniversitySiteListMaker.exe.config" file (in the bin folder).

The following settings are used by the UniversitySiteListMaker to connect to UniversitySite.

<add key="UniversitySiteUrl" value=""/>
<add key="UniversitySiteAccessID" value=""/>

Here’s an example:

<add key="UniversitySiteUrl" value="https://XXX.universitysite.com/UniversitySiteXXX"/>
<add key="UniversitySiteAccessID" value="Ma3XSFgIvLd/OmNkLxxfhO3ehudHJFXXV6Y5M9tHXW5bz6qjXxvhbNTJWS0bSwuCI9"/>

You will find this information available in your UniversitySite web app. From InstructorSite, click on your name in the upper right corner and then click on the "Global Settings" item in the menu that pops down.
Scroll down on the “Global Settings” page, and click on "Extensions".
On the Extensions page, click on the "SharePoint Extension".
Now, you can:
- Click on the “Copy URL” button for the “Web Service URL” and paste that in as the value for “UniversitySiteUrl” in the config file.
- Click on the “Copy Key” button for the “API Key” and paste that in as the value for the “UniversitySiteAccessID” in the config file.
Now paste those 2 values into the config file and save the file:

Step 4: UniversitySiteListMaker Settings for the log file: These settings are also in the "UniversitySiteListMaker.exe.config" file (in the bin folder).

The log file is where informational, warning and/or error messages will be written whenever the UniversitySiteListMaker is run. This file will be needed to debug any problems with the app or the settings and provide status for each run of the app. You will need to make sure that whatever account is used to run the UniversitySiteListMaker as a scheduled task has write permissions on the folder you select for the log file.

The following setting is used by the UniversitySiteListMaker to specify the location of the log file:

<add key="LogFilePath" value="" />

Here is an example:

<add key="LogFilePath" value=" C:\SLK\SPO\ListMaker\UniversitySiteListMaker.log" />

Here’s where to set the log file path in the config file:

Step 5: Review UniversitySiteListMaker Settings. These settings are also in the "UniversitySiteListMaker.exe.config" file (in the bin folder).

You have completed all the required steps to run the UniversitySiteListMaker now.:

You’ve created an Azure AD App-Only Accountfor the UniversitySiteListMaker to use to connect to SharePoint Online.
You’ve configured the UniversitySiteListMaker config file with the ID’s and Certificate information to allow the UniversitySiteListMaker to talk to SharePoint Online.
You’ve configured the UniversitySiteListMaker config file with the UniversitySite URL and Access ID to allow the UniversitySiteListMaker to talk to UniversitySite.
And you’ve specified where the log file should go when the UniversitySiteListMaker logs messages as it runs.

You can review the rest of the UniversitySiteListMaker settings in the config file and change the names and/or descriptions of the lists if you want to. I don't recommend making any other changes.

How to run the UniversitySiteListMaker app is at the very beginning of this document, so you don’t have to scroll way down here to find it when you need it. Go ahead and give it a whirl. Don’t forget where you specified the log file should go. You’ll need that for debugging if anything goes wrong.

Advanced Topic: Perform Security Trimming. These settings are also in the "UniversitySiteListMaker.exe.config" file (in the bin folder).

By default, the UniversitySiteListMaker app creates all list items such that anyone who has access to the SharePoint site can view them. UniversitySite allows each item to be secured such that only certain people can view certain items. The UniversitySiteListMaker app can also restrict access to users in SharePoint who match users in UniversitySite. This is no easy to setup because we must find a way to match the SharePoint user ID to a UniversitySite user. To have some flexibility in doing this, the following settings are relevant to this process:

<add key="PerformSecurityTrimming" value="true" />
<add key="UseEmailForSecurityTrimming" value="true" />
<add key="NetworkAliasPrefix" value="" />
<add key="NetworkAliasSuffix" value="" />

The settings listed above seem to be the easiest to get the user matching working on most SharePoint Online systems.

Set PerformSecurityTrimming to true
Set UseEmailForSecurityTrimming to true
Leave the rest of the settings blank

These settings will cause the UniversitySiteListMaker app to use the email address in UniversitySite to match the user in SharePoint Online.

If that does not work or you want to match the user based on the NetworkAlias in UniversitySite, you can do the following instead:

Set PerformSecurityTrimming to true
Set UseEmailForSecurityTrimming to false
Set NetworkAliasPrefix to a value to prepend to the NetworkAlias, for example: "domainName\" or leave it blank.
Set NetworkAliasSuffix to a value to append to the NetworkAlias, for example: "@domainName.com" or leave it blank.