Temporal Tables in SQL 2016 and SQL Azure


Have you ever been asked to create a History/audit table for your database? Do you need to? If so, then read this blog post on the awesome feature now built into SQL 2016 and SQL Azure.

What is a temporal table?

A temporal table is a new type of user table in SQL Server 2016 and SQL Azure. These tables allow a point-in-time analysis by keep a full history of data changes, without the need of custom coding using triggers etc. You can create any new user table as a temporal table, or convert an existing table into a temporal table. By converting an existing table to a temporal table you will not need to do anything to any stored procedures or T-SQL statements to allow your application to continue working, it will just continue working, while storing the history data of any changes. These tables can also be known as system-versioned temporal tables because each row is managed by the system.

Every temporal table has two explicitly defined datetime2 columns. These columns are referred to as period columns and are used by the system to record period of validity for each row whenever a row is modified. A temporal table also has reference to another table with the same schema as itself. This is the history table and automatically stores the previous version of the row each time a row in the temporal table gets updated or deleted. This allows the temporal table to remain as the current table, and the history table to hold… well the history data. During temporal table creation users can specify existing history table (which must match the schema of the temporal table) or let the system create a default history table.

How does temporal work?

All current entries are stored within the Temporal table with a Start time and non-ending End time. Any changes will cause the original row to be stored in the history table with the start time and end time for the period for which is was valid.

Let me show you an example.

On first input on a row, the value would be entered only into the Temporal table.

Temporal

ID Value StartTime EndTime
1 My First Value 2016-05-01 10:26:45.15 9999-12-31 23:59:59.99

History

ID Value StartTime EndTime

On Update to ID 1, the original inputted value is entered into the history table with the EndTime updated to match when the Update took place, and Temporal table is updated with the updated value and the new start time.

Temporal

ID Value StartTime EndTime
1 My Second Value 2016-05-14 14:54:44.54 9999-12-31 23:59:59.99

History

ID Value StartTime EndTime
1 My First Value 2016-05-01 10:26:45.15 2016-05-14 14:54:44.54

On second update to ID 1, again the current value is entered into the history table with the EndTime updated to match when the Update took place, and the Temporal table is updated with the new version and new start time.

Temporal

ID Value StartTime EndTime
1 My Third Value 2016-05-24 01:59:41.82 9999-12-31 23:59:59.99

History

ID Value StartTime EndTime
1 My First Value 2016-05-01 10:26:45.15 2016-05-14 14:54:44.54
1 My Second Value 2016-05-14 14:54:44.54 2016-05-24 01:59:41.82

On deletion of ID 1, the current value is entered into the history table, with the EndTime updated to match when the row was deleted. The row is then removed from the Temporal table.

Temporal

ID Value StartTime EndTime

History

ID Value StartTime EndTime
1 My First Value 2016-05-01 10:26:45.15 2016-05-14 14:54:44.54
1 My Second Value 2016-05-14 14:54:44.54 2016-05-24 01:59:41.82
1 My Third Value 2016-05-24 01:59:41.82 2016-06-01 13:12:17.72

Creating or converting exiting table to a temporal table.

You can create a temporal table by specifying the Transact-SQL statements directly as show below. I recommend using SQL Management Studio 2016 which can be obtained and downloaded from here. You do not need a SQL Server license to install and use this, and it can be used with SQL Azure.

By using SQL Management Studio 2016, you can obtain the correct T-SQL by right clicking Tables > New > Temporal Table > System-Versioned Table..


I’m going to create an Employee Table.

CREATE TABLE dbo.Employee
(
  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED
  , [Name] nvarchar(100) NOT NULL
  , [Position] varchar(100) NOT NULL
  , [Department] varchar(100) NOT NULL
  , [Address] nvarchar(1024) NOT NULL
  , [AnnualSalary] decimal (10,2) NOT NULL
-- This point below is the Period/Temporal set up on the table.
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));
GO

If I was going to convert my existing Employee Table into a Temporal table, I would use the following T-SQL Statement

ALTER TABLE Employee
ADD
    ValidFrom datetime2 (0) GENERATED ALWAYS AS ROW START HIDDEN
        constraint DF_ValidFrom DEFAULT DATEADD(SECOND, -1, SYSUTCDATETIME())
    , ValidTo datetime2 (0)  GENERATED ALWAYS AS ROW END HIDDEN
        constraint DF_ValidTo DEFAULT '9999.12.31 23:59:59.99'
    , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);
ALTER TABLE Employee
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));
GO

AS you can see from above, SQL Management Studio indicates the System-Versioned and History table.

Inserts, updates and deleting data

When you come to doing your Inserts, Updates and Deletes there are no changes to T-SQL code, you would perform all against the Temporal table, (Employee table in my case). The T-SQL code below is demo code, that inserts 3 people a minute apart and then every 5 minutes something else will happen to the data. Either an update, inserting a new record, or delete.

--Create Lisa Fane
INSERT INTO [dbo].[Employee] ([EmployeeID],[Name],[Position],[Department],[Address],[AnnualSalary])
VALUES    (1234,'Lisa Fane','Sales Rep','Sales','Hertforshire', 25000)
GO

WAITFOR DELAY '00:01'
--Create Dan Wilson
INSERT INTO [dbo].[Employee] ([EmployeeID],[Name],[Position],[Department],[Address],[AnnualSalary])
VALUES    (2435,'Dan Wilson','Developer','Development','Kent', 35500)
GO

WAITFOR DELAY '00:01'
--Create David Hamilton
INSERT INTO [dbo].[Employee] ([EmployeeID],[Name],[Position],[Department],[Address],[AnnualSalary])
VALUES    (3445,'David Hamilton','Developer','Development','Croydon', 20000)
GO

WAITFOR DELAY '00:05'
--Update Lisa Fane with new job title and payrise.
UPDATE [dbo].[Employee]
SET  [Position] = 'Lead Sales Rep',[AnnualSalary] = 32000
WHERE EmployeeID = 1234
GO

WAITFOR DELAY '00:05'
-- Give Lisa Fane a Pay Rise.
UPDATE [dbo].[Employee]
SET  [AnnualSalary] = 33000
WHERE EmployeeID = 1234
GO

WAITFOR DELAY '00:05'
-- Give Dan Wilson a new job title and payrise
UPDATE [dbo].[Employee]
SET  [Position] = 'Development Manager',
[AnnualSalary] = 45500
WHERE EmployeeID = 2435
GO

WAITFOR DELAY '00:05'
--Employ Lucy Williamson
INSERT INTO [dbo].[Employee] ([EmployeeID],[Name],[Position],[Department],[Address],[AnnualSalary])
VALUES    (8875,'Lucy Williamson','Project Management','PMO','Sutton', 20000)
GO

WAITFOR DELAY '00:05'
--Lisa Fane change address
UPDATE [dbo].[Employee]
SET  [Address] = 'Barnet'
WHERE EmployeeID = 1234
GO

WAITFOR DELAY '00:05'
--Adam Crane joins the team
INSERT INTO [dbo].[Employee] ([EmployeeID],[Name],[Position],[Department],[Address],[AnnualSalary])
VALUES    (4454,'Adam Crane','Sales Rep','Sales','Islington', 26000)
GO
WAITFOR DELAY '00:05'

--David Hamilton has a payrise
UPDATE [dbo].[Employee]
SET  [Position] = 'Manage Services',[AnnualSalary] = 20500
WHERE EmployeeID = 3445
GO

WAITFOR DELAY '00:05'
--Lucy Williamson left the company.
Delete From Employee
Where EmployeeID = 8875

Running the above script takes about 30-35 mins.

Querying Temporal data

To obtain the current information in the Temporal table, there is no changes to your typical SQL Select statements.

SELECT * FROM Employee

As you can see from the above image, the results are as if it’s not a temporal table.

To view history data, there is a new clause you can use within the SELECT FROM statement. This is the FOR SYSTEM_TIME clause with 5 temporal-specific sub-clauses to query data across the current and history tables. This new SELECT statement syntax is supported directory on a single table, propagated through multiple joins, and through views on top of multiple temporal tables.

View All Data

Use the following command to see current and past records, the IsActual flag indicates if the row is current. This query is also useful as a view so that BI tools such as PowerBI can display a graph over time.

SELECT Name, Position, Department, [Address], AnnualSalary, ValidFrom, ValidTo, IIF (YEAR(ValidTo) = 9999, 1, 0) AS IsActual FROM Employee
FOR SYSTEM_TIME All
ORDER BY ValidFrom

Between two dates

Using BETWEEN <startDateTime> AND <endDateTime> will return rows that were active at least for a portion of period between the two times.

SELECT * FROM Employee
FOR SYSTEM_TIME
BETWEEN '2016-06-18 10:27:00' AND '2016-06-18 10:47:00'
ORDER BY ValidFrom

Contains two dates

Using CONTAINS IN (<startDateTime>,<EndDateTime>) will return rows that were only active within a period (and not outside it). This only queries the history table. As you can see below Lisa Fane was updated 3 times within the time period.

SELECT * FROM Employee
FOR SYSTEM_TIME CONTAINED IN ('2016-06-18 10:25:00', '2016-06-18 11:50:00')
ORDER BY ValidFrom

Point in time search

Using AS OF <dateTime> will return how the database looked at that given moment in time. Below are multiple statements which returns results from those points in the database. I’ve highlighted within the next result set what has changed. This type of query is perfect for BI tools such as Power BI to query the data 24 hours, 7 days, 30 day etc.

SELECT * FROM Employee
FOR SYSTEM_TIME
AS OF '2016-06-18 10:28:00'
ORDER BY EmployeeID

SELECT * FROM Employee
FOR SYSTEM_TIME
AS OF '2016-06-18 10:31:00'
ORDER BY EmployeeID

SELECT * FROM Employee
FOR SYSTEM_TIME
AS OF '2016-06-18 10:36:00'
ORDER BY EmployeeID

SELECT * FROM Employee
FOR SYSTEM_TIME
AS OF '2016-06-18 10:46:00'
ORDER BY EmployeeID

SELECT * FROM Employee
FOR SYSTEM_TIME
AS OF '2016-06-18 10:56:00'
ORDER BY EmployeeID

SELECT * FROM Employee
FOR SYSTEM_TIME
AS OF '2016-06-18 11:01:00'
ORDER BY EmployeeID

SELECT * FROM Employee
FOR SYSTEM_TIME
AS OF '2016-06-18 11:06:00'
ORDER BY EmployeeID

References

I found quite a bit of good information available to help me understand Temporal tables, I have listed the main sites below.

Temporal Tables – https://msdn.microsoft.com/en-IN/library/dn935015.aspx 

Getting Started with Temporal Tables in Azure SQL Database – https://azure.microsoft.com/en-us/documentation/articles/sql-database-temporal-tables/

Getting Started with System- Versioned Temporal Tables – https://msdn.microsoft.com/en-us/library/mt604462.aspx

Temporal in SQL Server 2016 (Video) – https://channel9.msdn.com/Shows/Data-Exposed/Temporal-in-SQL-Server-2016 


SharePoint online new Site Content Page. How does it know to render classic view? (Cookies!)


If you are lucky enough today, you might have noticed the new Site Contents page. It appears if your SharePoint online version is 16.0.532.1205 or above. You can find out which version you are running by putting the following in the URL:

https://<tenant>.sharepoint.com/_vti_pvt/buildversion.cnf

This new page is similar to the new Document library pages Microsoft have implemented. The new pages also have lost the ability to run scripts on the page.

In the bottom left of the page, there is a link that states “Return to classic SharePoint” when this is clicked, you return back to the Site Contents we all know and love. (Love might be a bit strong).

However, after I clicked the link, I couldn’t see any way of turning it back to the new Site Content look and feel. How does SharePoint know to display all Site Content pages in classic mode?

It turns out there is a session cookie called splnu set to the value of 0. When this is set in the cookies it displays the old look. Just by closing the page down and re-opening it removes this cookie and then displays the new look and feel again. You can prove that it is this cookie by using chrome tools.

Open the site in Chrome. Switch to the classic mode by clicking the link in the left hand bottom corner of the screen. If you refresh the page, you stay in classic mode. Open the developer tools (F12), and swtich to Resources and expand the Cookies section.

Click on spInu and click delete, then refresh the page. The page reverts back to the new look and feel.

Exporting and Importing SQL Azure database in different Tenants


This was easier than I thought it was going to be. Using purely point and click, and Microsoft Azure Storage Explorer.

To be able to backup/export a database you need to have an Azure Blob Storage. If you don’t have one the steps below will show you how to create one.

Creating an Azure Blob Storage

  • Open up https://portal.azure.com and log in with your credentials, on the tenant where your SQL server source is.
  • Click New and select Data + Storage and then select Storage account
  • On the Create storage account blade you will be asked the following information:
    • Name: <Give a unique name>
    • Deployment model: Resource manager
    • Account Kind: Blob Storage
    • Performance: Standard
    • Replication: Locally-redundant storage (LRS) <- This may be different for you. I’m just doing a simple export and restore, not planning on keeping this storage.
    • Subscription: <Your subscription>
    • Resource group: Create New <- You might wish to use an existing resource group.
    • Resource Group Name: <Resource Group Name>
    • Location: <Your closest location>
  • Click Create
  • This will take a small amount of time while Azure creates this storage

Exporting Source Database

  • If not continuing from last step, open up https://portal.azure.com and log in with your credentials, on the tenant where your SQL server source is.
  • Go into SQL Database, and select the database you wish to export.
  • At the top of the blade there is a menu button item called ‘Export’. Click this button.

  • On the Export database blade, you will be asked the following information:
    • File name: Give a meaningful export name.
    • Subscription: Select the subscription that you can find your storage account in that you created earlier.
    • Storage: Select the storage account you created earlier.
      • Containers: Add a container name, and give it private access type, then select it.
    • Server admin login: Provide the Server Admin Username.
    • Password: Provide the password for the Server Admin.
  • Click OK.
  • Once you have clicked OK, your request to export the database is sent Azure, and is put into some sort of Microsoft Azure queue behind the scenes. Here you have to wait until the job has run, this can take some time. (Mine took 15 mins before complete) Please see Viewing the Import/Export history later in this blog post for job status.

Downloading the Blob file.

After the SQL export has completed, you will need to download the blob file so that you can then upload it to your destination tenant. To do this follow these steps:

  • In https://portal.azure.com select your Storage account where you exported SQL to.
  • Under the container selector you should find the container you created when exporting SQL data. Select this.
  • This container blade shows you all the files within this container. As I’ve just created it for this purpose the only file within here is my export file. Select this file.
  • Then click the download button.

Uploading export file to new tenant storage.

Before you can upload your export file to your new tenant, first you will need to ensure you have a storage account to upload to. If there isn’t one, follow my previous steps in this blog about creating an Azure Blob Storage.

Once you have a blob storage on your destination tenant, download and open Microsoft Azure Storage Explorer

  • Sign in with your destination tenant credentials.
  • Select the Storage account and then blob container.
  • Click Upload.
  • Upload your export file.

Importing to Destination Database

  • Open up https://portal.azure.com and log in with your credentials, on the tenant where your destination SQL server is.
  • Go into SQL Servers, and Add/Select the SQL server you wish to import the database too.
  • At the top of the blade there is a menu button item called ‘Import database’. Click this button.

  • On the Import database blade, you will be asked the following information:
    • Subscription: Select the subscription that you can find your storage account in that you created earlier.
    • Storage: Select the storage account you created earlier.
      • Containers: Select the Container
        • File: Select the export file.
    • Select Pricing Tier: <Select a pricing tier>
    • Database name: <Name the database>
    • Collation: Leave as is, or change if you require too.
    • Server admin login: Provide the Server Admin Username for this tenant.
    • Password: Provide the password for the Server Admin.
  • Click OK.
  • Once you have clicked OK, your request to import the database is sent Azure, and is put into some sort of Microsoft Azure queue behind the scenes. Here you have to wait until the job has run, this can take some time. (Mine took less than two minutes to import.) Please see Viewing the Import/Export history below for job status.

Viewing the Import/Export history.

After you have imported/exported a database, you can view the progress of the request by following these steps:

  • In https://portal.azure.com select SQL servers
  • Select your server where the import/export is taking place.
  • If you scroll down to Operations on the Server blade, you will see a tile called Import/Export history. Click this.



Simple SQL statement to see membership within a database


In SQL Azure if you connect up Microsoft SQL Server Management Studio, you have to do everything using SQL statements, there is no ability to point and click your way through creating accounts, memberships, new tables etc. I’m sure a good DBA would tell me that this is the correct way of building any database. Unfortunately, (or fortunately) I’m not a DBA, and I like point and click tools.

So the other day I was having a problem seeing what accounts had what access to a given database. I found running this SQL statement on a given database gave me the information I needed. I have written this blog post today mainly so I have reference to this in the future.

SELECT DP1.name AS DatabaseRoleName,
isnull (DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
ORDER BY DP1.name;

Fig 1. Example results.

Entity Framework Error – The server principle “user” is not able to access the database “master” under the current security context


I’m pretty new at Entity Framework and really haven’t even skimmed the surface of what it can do. But as a newbie, I encountered this error right from the start and my bet is other people have too. Let me first explain the setup of my environment.

I’ve created a code first entity framework application. I have created an Azure Server and database (doesn’t have to be Azure database), and created a SQL user so that they are db_owner on the database. On first running of my application I hit the following error: –

“The server principal “demouser” is not able to access the database “master” under the current security context.

Database ‘master’ on server ‘xxxxxxxx.westeurope1-a.worker.database.windows.net’ is not currently available. Please retry the connection later. If the problem persists, contact customer support, and provide them the session tracing ID of ‘6214BE0A-7962-4728-B0BE-B129401F98B0’.

Login failed for user ‘demouser’.”

So why does it need access to the master database?

It requires this to log into SQL server.  If the user database doesn’t exist and you want the code to create your database, you will also need to give your account dbo access to your master database, I don’t recommend doing this.

What is the solution?

Add the account to the master database so that it has public access. The SQL Script below is the full script I now use to create the account. The line highlighted is the line you need to run on against your master database to fix your error message.

use [master]
--Run within the master database.
-Create the Login account.
CREATE LOGIN demouser WITH password 'P@ssword123'
--This is the line you need
CREATE USER demouser FOR LOGIN demouser

--Run within your Database
use [demoDatabase]
CREATE USER demouser FOR LOGIN demouser
ALTER ROLE [db_owner] ADD MEMBER demouser

Updating an expired Client Secret of SharePoint Add-in


Been working with SharePoint Add-in tokens for a while now, but this week has been the first time I’m still working with an add-in longer than a year in one environment. My application start throwing the error message:

Error:Token request failed., InnerMessage:System.Net.WebException: The remote server returned an error: (401) Unauthorized.”

I knew that they did expire after a year, but never really thought about (until now) how to go about renewing them. Luckily Microsoft documentation nowadays is a lot better than it used to be. I found this walk through https://msdn.microsoft.com/en-us/library/office/dn726681.aspx. In case Microsoft takes the link down, or changes the URL, I will explain the steps below. I have also changed the code slightly for the Report on Client ID expiry dates, as the Microsoft one didn’t return the results I expected.

Report on Client ID expiry dates.

  • Open Windows Powershell and run the following cmdlet:
Connect-MsolService
  • A login prompt will appear, here enter the tenant-administrator credentials for the Office 365 tenancy where the add-in was registered with AppRegNew.aspx
  • You can generate a report that list each add-in in the tenant with the date that the secret expires with the following PowerShelll code.

    $applist = Get-MsolServicePrincipal -all  |Where-Object -FilterScript { ($_.DisplayName -notlike &quot;*Microsoft*&quot;) -and ($_.DisplayName -notlike &quot;autohost*&quot;) -and  ($_.ServicePrincipalNames -notlike &quot;*localhost*&quot;) }
    $output = &quot; &quot;
    foreach ($appentry in $applist)
    {
        $principalId = $appentry.AppPrincipalId
        $principalName = $appentry.DisplayName
    
        $results =  Get-MsolServicePrincipalCredential -AppPrincipalId $principalId -ReturnKeyValues $false | Where-Object { ($_.Type -ne &quot;Other&quot;) -and ($_.Type -ne &quot;Asymmetric&quot;) }
        if($results.count -gt 0)
        {
          $output += &quot;PrincipalId`t:`t$principalId`n&quot;
          $output += &quot;PrincipalName`t:`t$principalName`n&quot;
          $output += &quot;Keys`n&quot;
         foreach($result in $results)
         {
            $output += &quot;Type`t:`t&quot; + $result.Type + &quot;`n&quot;
            $output += &quot;Value`t:`t&quot; + $result.Value + &quot;`n&quot;
            $output += &quot;KeyId`t:`t&quot; + $result.KeyId + &quot;`n&quot;
            $output += &quot;StartDate`t:`t &quot; + $result.StartDate + &quot;`n&quot;
            $output += &quot;EndDate`t:`t&quot; + $result.EndDate + &quot;`n&quot;
            $output += &quot;Usage`t:`t&quot; + $result.Usage+ &quot;`n&quot;
            $output += &quot;`n&quot;
         }
         $output += &quot;-----------------------------------------------`n&quot;
        }
    }
    $output | Out-File &quot;c:\temp\appsec.txt&quot; 

     

    • The above code first filters out Microsoft’s own applications, add-ins still under development (and a now-deprecated type of add-in that was called autohosted).
    • Filters out non-SharePoint add-ins and add-in like workflow.
  • Open the file at c:\temp\appsec.text to see the report.

An example of the report below:

Note: The PrincipalID is your Client ID

Generate a new secret for another year.

To generate a new secret, you just need to run the following PowerShell script:

$clientId = &lt;#Replace with your ClientID of the add-in#&gt;
$bytes = New-Object Byte[] 32
$rand = [System.Security.Cryptography.RandomNumberGenerator]::Create()
$rand.GetBytes($bytes)
$rand.Dispose()
$newClientSecret = [System.Convert]::ToBase64String($bytes)
New-MsolServicePrincipalCredential -AppPrincipalId $clientId -Type Symmetric -Usage Sign -Value $newClientSecret
New-MsolServicePrincipalCredential -AppPrincipalId $clientId -Type Symmetric -Usage Verify -Value $newClientSecret
New-MsolServicePrincipalCredential -AppPrincipalId $clientId -Type Password -Usage Verify -Value $newClientSecret
$newClientSecret

The output of the above PowerShell file will give you a new Client Secret, take note of this:

Generate a new secret for 3 years.

It is possible to create a secret that will last 3 years, the PowerShell script is very similar to the above script, but now it has a start and end date.

$clientId = &lt;#Replace with your ClientID of the add-in#&gt;
$bytes = New-Object Byte[] 32
$rand = [System.Security.Cryptography.RandomNumberGenerator]::Create()
$rand.GetBytes($bytes)
$rand.Dispose()
$newClientSecret = [System.Convert]::ToBase64String($bytes)
$dtStart = [System.DateTime]::Now
$dtEnd = $dtStart.AddYears(3)
New-MsolServicePrincipalCredential -AppPrincipalId $clientId -Type Symmetric -Usage Sign -Value $newClientSecret -StartDate $dtStart -EndDate $dtEnd
New-MsolServicePrincipalCredential -AppPrincipalId $clientId -Type Symmetric -Usage Verify -Value $newClientSecret -StartDate $dtStart -EndDate $dtEnd
New-MsolServicePrincipalCredential -AppPrincipalId $clientId -Type Password -Usage Verify -Value $newClientSecret -StartDate $dtStart -EndDate $dtEnd
$newClientSecret

Updating the remote web application using the new secret

  • If you are redeploying from Visual Studio you will need to update the client Secret in your Web.Config
  • If you are just updating directly in Azure, you can just go to the configurations and update the new secret there.

If you are using the default TokenHelper.cs file in your project, and it’s not the prerelease version then you can add a second app setting called SecondaryClientSecret. Here you would put in your old secret, and in the ClientSecret put in the new one. This is so if your token is going to expire soon, the application will still work as it will try one first then the other.

Time to propagate Client Secret to SharePoint

According the Microsoft link you should wait at least 24 hours to propagate the ClientSecret. However, I found as soon as I changed the secret I could use it straight away. After changing your Client Secret if you run the ‘Report on Client ID expiry dates’ powershell again, those dates didn’t update for me until the following day.

I ran the report the following day, and as you can see below, Demo App 1 which was shown in the screen shot above now has 3 new keys with new dates.

Deleting expired client secrets

As expired client secrets do not seem to get removed, it is recommended to delete them. The code below will grab any keys that are expired for a ClientID and delete them.

$clientId = &lt;#Replace with your ClientID of the add-in#&gt;
$keys = Get-MsolServicePrincipalCredential -AppPrincipalId $clientId -ReturnKeyValues $false
$dtNow = [System.DateTime]::Now
foreach($key in $keys)
{
 if($key.EndDate -lt  $dtNow)
 {
   write-host $key.KeyId &quot; Expired&quot;
   Remove-MsolServicePrincipalCredential -KeyIds @($key.KeyId) -AppPrincipalId $clientId
 }
}

Adding to Property Bag and Index using PowerShell in SharePoint Online


The other day, I very lazily, used SharePoint Designer to add a few items to the property bag. Soon as I done it, someone then said they wanted to search upon the values inside the property bag. Unfortunately this isn’t something that can be done via SharePoint Designer. After spending 5 minutes or so searching online for a pre-made PowerShell code, I could only find the solution for On-Prem.

The only piece of code I found to do this to SharePoint Online was through the Office Developer PNP CSOM code. I really didn’t want to create a Visual Studio project just to use the PNP Core. (See http://dev.office.com/patterns-and-practices for more information Office Dev PNP, there is so much information and cool videos there, there really is no need to re-hash anything in my own blog.) So I decided to just create PowerShell script which is pretty close to being like for like copy of the PNP Core method:

Web.AddIndexedPropertyBagKey(this Web web, string key)

Before I go into the code, I will explain how SharePoint knows which property bag items need to be indexed for search. Just by adding the item doesn’t make it visible to search, it has to be added to another property bag item called “vti_indexedpropertykeys“. The value of this property bag item is a pipe delimited Base64String. (Example: RABhAHUAZwBoAHQAZQByAA==|UwBvAG4A|cwBvAG4A| ). As you can see, it’s not just as simple as adding the string name to the vti_indexedpropertykeys value.

To encode a single value in PowerShell the following line of code works:

[System.Convert]::ToBase64String([System.Text.Encoding]::Unicode.GetBytes($value))

To decode a single value from Base64String to text the following PowerShell code works:

[System.Text.Encoding]::Unicode.GetString([System.Convert]::FromBase64String($EncodedValue))

The below code will ask you for the Web Url, your username, password (Secure string), propertybag key name and value. The code will first add or update the value in the property bag, and if it hasn’t been added to the index it will add it.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime")| Out-Null
$indexPropertyKeyConst = "vti_indexedpropertykeys"

function GetIndexedPropertyBagKeys($ctx)
{
  $results = @()
  $web = $ctx.Web;
  $ctx.Load($web.AllProperties)

  try
  {
  $ctx.ExecuteQuery();
  }
  catch{
   Write-host "Error accessing property bag " $_.Exception.Message -ForegroundColor Red
   exit 1
  }

  $indexPropertyBase64 = $web.AllProperties[$indexPropertyKeyConst];
  $separator = "|"
  $option = [System.StringSplitOptions]::RemoveEmptyEntries

  if(![string]::IsNullOrEmpty($indexPropertyBase64))
  {
    $resultsBase64 = $indexPropertyBase64.Split($separator, $option)

    foreach($r in $resultsBase64)
    {
     $results += [System.Text.Encoding]::Unicode.GetString([System.Convert]::FromBase64String($r))
    }
  }
    #comma not a mistake, required to ensure correct type is returned on empty or single array value.
    return ,$results
}

function GetEncodedValueForSearchIndexProperty($keysArray)
{
  $encode64Keys = [String]::Empty
  foreach($key in $keysArray)
  {
   $encode64Keys += [System.Convert]::ToBase64String([System.Text.Encoding]::Unicode.GetBytes($key))
   $encode64Keys += "|"
  }

  return $encode64Keys;
}

function AddIndexedPropertyBagKey($ctx, $propertyKey)
{
  [bool]$result = $false;
  [bool]$addValue = $false;
  $keys = GetIndexedPropertyBagKeys($ctx)

  if($keys -cnotcontains $propertyKey)
  {
     $addValue = $true;
  }

 if($addValue)
 {
   try
   {
   $keys += $propertyKey
   $keysBase64String = GetEncodedValueForSearchIndexProperty($keys)
   $ctx.Web.AllProperties[$indexPropertyKeyConst] = $keysBase64String
   $ctx.Web.Update()
   $ctx.ExecuteQuery()
   $result = $true
   }
   catch
   {
    Write-host "Error adding $propertyKey to index. " $_.Exception.Message -ForegroundColor Red
    exit 1
   }
 }

  return $result
}

function CreateUpdatePropertyBag($ctx, $propertyKey, $propValue)
{
  $web = $ctx.Web;
  $ctx.Load($web.AllProperties)
  try
  {
    $ctx.ExecuteQuery();
    $web.AllProperties[$propertyKey] = $propValue;
    $web.Update();
    $ctx.ExecuteQuery();
  }
  catch{
   Write-host "Error adding $propertyKey to property bag " $_.Exception.Message -ForegroundColor Red
   exit 1
  }
}

$webUrl = Read-Host -Prompt "Enter the WebUrl"
$username = Read-Host -Prompt "Enter your Email login"
$password = Read-Host -Prompt "Password for $username" -AsSecureString
$propKeyToIndex = Read-Host -Prompt "Enter the property key name to index"
$propValue = Read-Host -Prompt "Enter the $propKeyToIndex value"

$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($webUrl)
$ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $password)

CreateUpdatePropertyBag $ctx $propKeyToIndex $propValue;
AddIndexedPropertyBagKey $ctx $propKeyToIndex;

Write-Host "Complete" -ForegroundColor Green

You can download the source code directly from my OneDrive.

Thank you to my colleague Paul Perry with his help around PowerShell arrays.