Grant Application and Delegate Permissions using an App Registration


This blog post came about because I wanted a way to create new Application Registrations and grant consent for the tenant, all programmatically. This is so I can use Devops pipelines to create and deploy my code without any human interaction or using a person account.

The AZ cli can grants permissions, but it does not seem to work for Admin consented permissions. I found the following post by Sam Coganhttps://samcogan.com/provide-admin-consent-fora-azure-ad-applications-programmatically/ saying that it was possible if you use REST API calls. This did work for me; however, it was just the Delegated permissions.

By reading through Sam’s post it helped me understand the connection between Application Registrations, Service Principals and Oauth2permission, and helped me on the quest of understand how to grant the Application permissions through appRoleAssignments.

I also want to credit Sahil Malik as I found his post https://winsmarts.com/how-to-grant-admin-consent-to-an-api-programmatically-e32f4a100e9d after I worked it all out myself, and was able to confirm that what I was doing was right.

At my Github project https://github.com/pmatthews05/CFAppOnlyGrantPermissions the README.md will walk you through how to set up and run the code. At the end of the README.md file you should have 2 Application Registration, where the Azure API Registration app would have created the second app (in my case CFCodeApp) for you. This code is idempotent. You can change the permissions for an existing Application Registration by providing it a different Permission.json file.

As the README.md file gives the instruction on how to run the code, I will not replicate it here. I will use the rest of this post to explain how the code works.

Permissions required for ‘Azure API Registration’

To allow the Azure API Registration to create new Application Registrations using AZ cli it requires to use both the legacy Azure Active Directory Graph and Microsoft Graph permissions. It seems that some of the commands in the az cli still points to https://graph.windows.net when it makes calls, according to some issue notes in the az cli git hub repository, it looks like this is in the process of being changed.

With Azure Active Directory Graph we need 2 permissions

  • Application.ReadWrite.All – This allows us to read and write the Application Registrations.
  • Directory.ReadWrite.All – This allows us to read the application registration permission list, and service principal information.

With Microsoft Graph we also need permission.

  • AppRoleAssignment.ReadWrite.All – This allows us to call the REST API to grant permissions and assign Role assignment permissions.

Steps in the code

  • Set-AppRegistration
  • Set-AppCredentials
  • Set-ServicePrincipalForAppId
  • Remove-CurrentAppPermissions
  • Set-DelegatePermissions
    • Remove-CurrentOauth2PermissionGrants
  • Set-ApplicationPermissions
    • Remove-CurrentServicePrincipalGrants

Please note, the snippets of code I am showing here in the blog, are showing the command(s) that are performing the main action, not the full function.

Set-AppRegistration

We need an App Registration to be created first. If the name already exists it just returns the existing App Registration

Set-AppCredentials

This will create a secret for the App Registration with a random secret with the description set to Registration. There are a couple of override parameters that I am not using, where you can give it your own Description, and provide your own SecureString secret. This returns the appCredentials that supply the appId, name, password and tenantId. In the script it outputs this to screen at the end, however, if using in production environment, you would probably want to put the secret value in a keyvault, without displaying to the user what the value is.

Set-ServicePrincipalForAppId

All App Registrations require a Service Principal behind them. When you manually create an App Registration and assign permissions, it automatically creates a service principal for you. When you create an App Registration programmatically, it is your responsibility to also create the Service Principal. It is the Service Principal that defines the access policy and permissions for the user/application in the Azure AD tenant. A multi-tenant App Registration would have the same app Id in all tenants, but all have a different Service Principal which allows them access within that tenant. For example, in all tenants the AppId for the Microsoft Graph API is ‘00000003-0000-0000-c000-000000000000’ and in your tenant it has an associated Service Principal, which is a different object Id in your tenant compared to mine. When I finally understood this, it made more sense how this all ties together.

Reference: https://docs.microsoft.com/en-us/azure/active-directory/develop/app-objects-and-service-principals

The above az command is not idempotent, and therefore a check to see if it already exists is required.

Remove-CurrentAppPermissions

To allow idempotency of my script, I wanted to ensure that it removes all existing permissions before adding them back in. This piece of code does not remove the permission from the service principal, and if you stop the code after this command, you will see that your API Permissions in the GUI would look like this.

Seeing the permissions separated at the bottom of the screen, now understanding the relationship between Application Registrations and Service Principal, it makes a lot more sense to me now. The service principal still has access at this point and calls to these API’s will still work.

The code gets a list of all the permissions assigned to the Application Registration, then loops through each resourceAppId (the objectId value of the API permission service principal e.g, Microsoft Graph, SharePoint)*2 and deletes the permission.

Set-DelegatePermissions

To ensure the code is idempotent the first thing I am doing is removing the delegate permission from the Service Principal. See the next section on how this works.

Now we need to assign the Application Registration permissions for the delegate permissions. We do this by providing the AppID of our Application Registration, the API Permission AppID (the appID of the API Permission service principal e.g, Microsoft Graph)*2 and the oauth2Permissions scope Id*4

Next, we need to assign the Application Registration associated Service Principal oauth2Permissions to grant these permissions to the tenant.

Using Graph API explorer, you can view all the delegate permissions in your tenant using the following URL:

https://graph.microsoft.com/v1.0/oauth2permissiongrants

To find all the permissions grants for your Application Registration you will need the Service Principal Object ID*1 and then use the following URL:

https://graph.microsoft.com/v1.0/oauth2permissiongrants?$filter=clientId eq ‘<servicePrincipalObjectId>’ and consentType eq ‘AllPrincipals’

  • clientId: This is the Service Principal Object ID that is tied to your App Registration
  • consentType: Set to AllPrincipals when granted to the entire tenant, or Principal when granted to an individual user
  • id: The ID of the oauth2permissiongrants
  • principalId: This is set to null if using AllPrincipals, otherwise it will contain the objectID of the User that has been granted the permission
  • resourceId: This is the Serivce Principal Object ID value of the API Permission*2
  • scope: This is a string array of granted scope values for the given ResourceId. (e.g User.Read Directory.Read.All etc)

If the oauth2permissiongrants with the App Registration Service Principal Object ID and API Permission Service Principal Object ID (clientId and resourceId) doesn’t exist in your tenant, then you will need to POST a new oauth2permissiongrants, otherwise you will require to PATCH an existing oauth2permissiongrants/<id> with the new string array of scope values.

You must add a startTime and expiryTime, it does not matter what the datetime is, as long as expiryTime is later than the startTime.

Remove-CurrentOauth2PermissionGrants

To remove the permissions from the Service Principal for the Delegate Permissions, we need to remove the Oauth2PermissionGrants.

Unfortunately, with App Only permissions you cannot delete an oauth2permissiongrants. You require to access the directory as a person to delete. I found that by setting the scope to empty string, gives the same desired effect as removing them.

Please Note: I am using Invoke-RestMethod instead of az rest because I have not been able to get it to work without an error message.

Set-ApplicationPermissions

To ensure the code is idempotent the first thing I am doing is removing the application permission grants from the Service Principal. See the next section on how this works.

Now we need to assign the Application Registration permissions for the application permissions. We do this by providing the AppID of our Application Registration, the API Permission AppID (the appID of the API Permission service principal e.g, Microsoft Graph)*2 and the AppRoles scope Id*3

Next, we need to assign the Application Registration associated Service Principal AppRoleAssignments to grant these permissions to the tenant.

Using Graph API explorer, you can view all the Application Role Grants for your Application Registration. You will need the Service Principal Object ID*1 and then use the following URL:

https://graph.microsoft.com/v1.0/servicePrincipals//appRoleAssignments

Unlike the Oauth2PermissionGrants, where there is only one entry per clientid and resourceId which contains all the scopes, with AppRoleAssignments there is an entry for each scope, and it uses the appRoleId scope Id*3 instead of the scope string value.

  • id: The ID of the appRoleAssignment
  • principalId: The Service Principal Object ID that is tied to your App Registration
  • resourceId: This is the Serivce Principal Object ID value of the API Permission*2
  • appRoleId: This is the scope Id*3

Remove-CurrentServicePrincipalGrants

To remove the permission from the Service Principal for the Application Role permissions, we need to remove the AppRoleAssignments ID’s for the service principal

Please Note: I am using Invoke-RestMethod instead of az rest because I have not been able to get it to work without an error message.

Conclusion

That is it. In the Data folder of the github project there is an examplePermission.json file. As you can see that the JSON format is very flexible to add more or remove permissions. The name can be the appDisplayName or the AppId of the API Permission.

Run the Add-RegistrationAndGrantPermissions.ps1 script passing in the name of your App Registration you wish to create / update and your custom permission file. The script will run fine with user logged in, or an App Only with the correct permissions.

Please feel free to use/enhance the github project.

Footnotes

*1 How to find your Service Principal Object ID of your Application Registration

Using the GUI, the quickest way to find your Service Principal Object ID is to first go to the overview of the Application Registration. Then on the right-hand side of the screen, click the name of your Application Registration where Manage application in local directory is.

This will take you to the Service Principal information in your tenant. It is here you can get the ObjectID.

*2 How to find the Service Principal Object Id for the Permission API.

You may already know that Microsoft Graph API appId is 00000003-0000-0000-c000-000000000000, this is the same on all tenants, but the service principal object ID is different in each tenant. This is the resourceId. To find out what the objectID is in your tenant run the following script.

Remove “?appId== ‘00000003-0000-0000-c000-000000000000′” between the [ ] and it will list all for your tenant.

*3 How to find the Application Role Id of a Permission API Scope.

For each Permission API such as Microsoft Graph API, there are Application Role. No matter what tenant you are in, the id of them is always the same. The below example gets the Application Scope of Directory.Read.All from the Permission API Microsoft Graph.

Output:

*4 How to find the oAuth2Permission Id of a Permission API Scope.

For each Permission API such as Microsoft Graph API, there are oauth2Permissions. No matter what tenant you are in, the id of them is always the same. The below example gets the Delegation Scope of Directory.Read.All from the Permission API Microsoft Graph.

Output:

Removing External Users fully from a SharePoint Tenancy using PowerShell


This blog post has all come about as the client I was working for was having problems sharing documents in SharePoint with some external users. It turned out that the user was already in Azure AD as a Contact which is part of Exchange. This meant when an internal person attempted to share/Invite into SharePoint/MSTeams it all appeared to work correctly for the external user, but sometimes it didn’t. When looking at external users through the Admin portal, this external user was showing, but their email address was blank. After speaking with Microsoft, it turns out, because the email address was already found within the tenancy, it creates a unique violation when adding the external user to the Active Directory.

I have been working with Microsoft support regarding this, and the resolution was that this is as design!!??! Only by feeding back on the Office 365 uservoice this issue “might” looked at and fixed. See resolution notes below:

Symptom:
When you invite external users who exist as contacts in your environment, their email does not get populated in their guest user ID which results in them not being able to login to your environment and access the shared data.
Cause:
The issue is coming from a conflict caused by the email address which is already populated for the mail contact.
Resolution:
This is behavior by design as all objects in Azure AD have to be unique.
You cannot have 2 objects with the same email address.

When you invite one of your contacts to your content in O365, it actually creates a completely new guest user object in your environment and since the email address which is supposed to be populated in the email attribute is already in use by the contact, the email address does not get populated.

The only way to resolve this issue at the moment is to eliminate any conflicts that are in place, by removing the conflicting email contact and re-invite the user to your content.
More information:
The best thing I can offer to you is the following:

Please go to our UserVoice portal where other people are facing the same behavior and up-vote it, comment and have the whole IT department do the same as well.

Allow a “Guest User” to be converted to a different account type
https://office365.uservoice.com/forums/273493-office-365-admin/suggestions/19966537-allow-a-guest-user-to-be-converted-to-a-differen

This led me to working on a process and script that would remove the users from everywhere.

Locations to remove the External User from:

  • Contacts
  • Azure AD Guest Users
  • Azure AD Deleted Users
  • All SharePoint Sites
  • All SharePoint Hidden User lists
  • SharePoint User Profile

Contacts

To remove the External User from the contacts you will need to use the MSOL PowerShell module.

Or you can manually do this by going to admin.microsoft.com and under Users -> Contacts select the user and click Delete contacts.

Azure AD

To remove the External User from Azure AD you will still require using the MSOL PowerShell module. In fact, this script and the above script could be merged.

To do this manually, in admin.microsoft.com under Users -> Guest Users, select the user and click delete.

Then go into Users -> Deleted users and remove them from there.

Remove from SharePoint

To remove from SharePoint, if you have a large tenancy and you don’t know all the places where the external user could have been shared with, then you will have to use the following script. This script will remove the external user from the SharePoint Site, ensure that they are removed from the User Information list, and then lastly it will clear the person from the SharePoint User Profile.

I discovered that if I didn’t remove them from the User Profile, when attempted to reshare a document with that user, the people picker would grab the internal userprincipalname (<ExternalUserEmail>#EXT#@<Tenant>.onmicrosoft.com) as the email address and then prevent me clicking the Sharing button. This is because the people picker uses Graph API /Me/People and grabs the value from there. Once removed from everywhere, including the User Profile this no longer happens.

The following script uses SPO PowerShell Module and you will need to connect first using Connect-SPOService. The account that you use, needs to be a SharePoint Global Administrator.

The script checks if it can find the ExternalUser, and if it can remove the user using Remove-SPOExternalUser.

Then it loops through every site collection and looks for the user using Get-SPOUser with the internal userprincipalname. If found it removes the user using Remove-SPOUser. Once it has looped through all SharePoint sites, it then checks the SharePoint User Profile and removes the user from UserProfile Remove-SPOUserProfile. This command will remove a user from the UserProfile if they in the “Active Profiles” or the “Profiles Missing from Import”

If the plan is to add the external person back into your tenant, once the script has run, you will need to wait at least a few hours (maybe leave it for a day to be sure) to ensure all back end processes of Microsoft have completed.

When you share a document/folder with the external user they will get the invited link and enter a code experience, this way they do not turn up inside you Azure AD. However, if you share a site with them, or add them to a MS Teams, they will appear in your Azure AD correctly.

Dive into the code for O365 Audit logs webhooks


This is part two of a 2-part blog post.

  1. Walkthrough Setting up WebHook for O365 Audit Logs
  2. Dive into the code for O365 Audit Log webhooks to see how it works – (This Post)

The previous blog post showed how to get you up and running with O365 Audit logs and webhooks. In this blog post I’m going to show and explain parts of the code that ties everything together.

The full code can be found at my Github repo https://github.com/pmatthews05/O365AuditWebHook

PowerShell to initialize the Webhook to the Audit logs

Run on one line.

From inside the PowerShell folder (.\O365AuditWebhook\PowerShell) there is a PowerShell file called Set-AuditLogs.ps1 This PowerShell file Starts a subscription to the given Audit Content Type. This is done by calling:

https://manage.office.com/api/v1.0/{tenant_id}/activity/feed/start?contentType={ContentType}

The above call is a POST call and uses the ClientID and Secret to authenticate against the tenant. The body is a Json object

  • authId – Optional string that will be included as the WebHook-AuthID header in notifiations sent to the webhook as a means of identifying and authorizing the source of the request to the webhook
  • expiration – Optional datetime that indicates the datatime after which notifications should no longer be sent to the webhook. By leaving it empty, indicates the subscription will be active for the next 180 days.
  • address – Required HTTPS endpoint that can receive notifications. A test message will be sent to the webhook to validate the webhook before creating the subscription.

When the /start operation is called, the webhook URL specified in the address will be sent a validation notification to validate that an active listener can accept and process notifications.

The Azure Function AuditWebhook found in the O365AuditWebhook.cs file has two parts to it.

The first part, as shown above, handles the validation. It looks for a validation code within the content, and if found it response back with a 200 status (OK) and includes the validation code.

If an OK is not received back, then the webhook will not be added and the subscription will remain unchanged.

The second part of the AuditWebhook Azure function is explained in the next section.

Webhook handling O365 notifications

After the initial validation, notifications will be sent to the webhook as the content logs become available.

From the first part of the AuditWebHook Azure Function, notifications do not have the validationCode, this allows us to determine that notifications have been sent, instead of a new subscription.

The content of these notifications contains an array of one or more JSON objects that represent the available content blobs.

On line 5 of the above code, show where I handle the content of deserialize json object (notifications) to a list of AuditContentEntity.

The notification/AuditContentEntity contains the following:

  • tenantId
    The GUID of the tenant to which the content belongs
  • clientId – The GUID of your application that created the subscription
  • contentType – Indicates the content type
  • contentId – An opaque string that uniquely identifies the content
  • contentUri – The URL to use when retrieving the content
  • contentCreated – The datetime when the content was made available
  • contentExpiration – The datetime after which the content will no longer be available for retrieval.

At this point you do not have any log information, you just have a collection of contentUri which when called will provide you with the logs. To ensure that the webhook response quickly so that it can continue to handle incoming requests, we place the contentUri, contentType, and TenantId onto an Azure Storage Queue. This allows a different Azure function to handle getting the actual logs.

Lines 9-16 will set up the storage queue if it doesn’t exist.

Lines 19-26 prepares my queue object and serialize it to a json string.

Line 28 adds the message to the Azure Storage Queue.

Once all notifications/AuditContentEntity have been processed, a 200 status (OK) is passed back. The subscription that calls our webhook is waiting for an OK response. If it encounters failure, it has a built in retry mechanism that will exponentially increase the time between retries. If the subscription continues to receive failure response, the subscription can disable the webhook and stop sending notifications. The subscription will need to be started again to re-enable the disabled webook.

Processing the Storage Queue AuditContentUri

As items are put on the Storage Queue the Azure Function AuditContentUri found in the O365AuditWebhook.cs file fires.

First you need an authorization token to read the audit logs, we do this with AcquireTokenForApplication method. This uses the Tenant Name, ClientId and Secret that is stored within your Azure configurations. See ‘How to acquire token for application?’ below.

It grabs the ContentUri and then goes into a do loop. This is because the logs that come back, if it is a very busy tenant, not all the logs will be returned, and there will be a NextPageUri value in the header of the response to allow you to obtain the next page of logs.

Line 7 – This adds your tenantID to the end of the URI as a PublisherIdentifier. This parameter is used for throttling the request rate. Make sure this parameter is specified in all issued requests to get a dedicated quota. All requests received without this parameter will share the same quota. The IF statement ensures it is added to the end of the URI correctly.

Line 9 – This calls the ContentUri and gets a results and request headers. You can see the file .\O365AuditWebHook\AuditWebHook\Utilities\RestAPI.cs
The Method GetRestDataAsync is very similar to the GetRestData call you find within PNP Core code. Creates a HttpWebRequest, passing in Authorization Token, and calling the ContentUri. Only difference in my code is that I’m grabbing the response.Headers to find out if there are additional logs, and passes them back with the results.

Line 10 – This parse the results into a JArray. (Json Array object). Here you can manipulate what comes back. For example, instead of grabbing all results and then displaying them out, you can query the results for a particular log type.

In the example code below, this would be using the Audit.General logs, and it will grab any logs that are of RecordType 25 (Indicates Microsoft Teams event) where the operation is creating a new channel, and the Channel type is Private. I then convert the JArray to an object list of AuditGeneralEntity.

For further details about properties of the audit logs can be found here: https://docs.microsoft.com/en-us/microsoft-365/compliance/detailed-properties-in-the-office-365-audit-log

Line 14 – Logs out an individual log entry, this is in a json format. Different schema’s can be found here: https://docs.microsoft.com/en-gb/office/office-365-management-api/office-365-management-activity-api-schema

Line 17 – If there are any additional pages, then this will return a value, and the loop will loop until no more pages are found.

How to acquire token for application?

In the previous section, I called a method AcquireTokenForApplication. This is a helper class and method that I use quite often, when I need to obtain an AccessToken. You can find this in the repo at .\O365AuditWebHook\AuditWebHook\Utilities\AuthenticationHelper.cs. This solution has a cut down version of the helper class I use. It is cut down as it just gets an access token for Audit Logs using AppId and Secret.

Above is a snippet, as you can see it is wrapped in a retry method in case there is throttling.

PowerShell to stop the Audit logs

Within the PowerShell folder I have also included a file called Remove-AuditLogs.ps1

Run on one line.

This works exactly like the Set-AuditLogs.ps1 file except it calls the /stop endpoint:

https://manage.office.com/api/v1.0/{tenant_id}/activity/feed/stop?contentType={ContentType}

Once the subscription is stopped, no notifications will be sent to your webhook, and you will not be able to retrieve available content. Please note, if you decide to start the subscription again later using the Set-AuditLogs.ps1 you will not receive any content that was available between the stop and start time of the subscription.

 

This is quite a heavy post; I hope it has helped you in some way. It is just a starter, as you will probably want to do something with the logs instead of just writing them out to the Azure Logs. Maybe capturing a given process to then implement some logic to react. You might also want to put different Audit content types ContentUri onto different Azure Storage queue, so that different Azure Functions can process the ContentUri.

Setting up Webhook for O365 Audit logs


This is part one of a 2-part blog post.

  1. Walkthrough Setting up WebHook for O365 Audit Logs – (This Post)
  2. Dive into the code for O365 Audit Log webhooks to see how it works

In this blog post I’m going to show you how to get the O365 Audit logs using WebHooks. The full code can be found at my Github repo https://github.com/pmatthews05/O365AuditWebHook. My post will show you how to set up with screenshots and the expected results. In my next blog post I will dive into the important parts of the code to get this Audit WebHook connected and working.

Set up – Walkthrough

Creating an App Only Token

Once you have downloaded a copy from my Repo you will need to set up your environment. First thing we are going to do is create an App Only Token that will be able to read the Audit Logs.

  • For your Office 365 Tenant go to https://portal.azure.com
  • Select Active Directory
  • Select App Registrations
  • Click Create New Registration
    • Name: Audit Logs Retrieval
    • Supported Account types:
      Accounts in this organizational directory only
    • Click Register
  • Take a copy of the Application (client) ID
  • Take a copy of the Directory (tenant) ID
  • Click View API Permissions
  • Click Add a Permission
  • Select Office 365 Management APIs -> Application Permissions -> ActivityFeed.Read
  • Click Add permissions

  • Click Grant Admin Consent for [tenant] and accept the permissions.
  • Click on Certificates & Secrets
  • Click New Client Secret
    • Description: Audit Web Hook
    • Expires: Never
  • Take a copy of the Secret value

Setting up Azure

You will need to set up your Azure Environment, this will consist of the following:

  • Resource Group
  • Azure Function V1
  • Applications Insights
  • Storage Account

I like to automate where I can, also it saves me creating loads of screenshots which are probably all out of date after 2 months. I have written an Az CLI PowerShell script that will create the above for you in your Azure Environment. In the next blog post I will explain the code.

  • Download the latest version of Az Cli.
  • Using a PowerShell window – Sign into your Azure Environment using ‘az login’
  • If you have multiple subscriptions, ensure you are pointing to the correct subscription ‘az account set –subscription [SubscriptionName]
  • Change the directory to .\O365AuditWebhook\powershell
  • Run the following: ‘.\Install-AzureEnvironment.ps1 -Environment “[Environment]” -Name:”AuditWebHook”‘ replacing the [Environment] with your tenant name. For example, I’ve used cfcodedev.
  • Once the script has run, you will have the basic template Azure resources you need within the Resource group named [Environment]-AuditWebHook

Deploying Azure Function from Visual Studio 2019

Firstly, you don’t have to deploy this way. If you prefer to use Visual Studio code, create an AZ install script or manually deploy using Kudu, that is your choice, and all are valid. My choice of doing this is simplicity for screen shots and steps.

  • Open the solution using Visual Studio Code 2019
  • Right click on the project AuditWebHook and select Publish
  • From the Pick a publish target dialog (click Start if you are not seeing a dialog), and under Azure Functions Consumption Plan click Select Existing, and select Create Profile.
  • Sign into your account if you need to, then pick your subscription, resource group, and then you can either search, or just pick the Azure Function. Click OK.
  • This takes you back to the Summary page. Under Actions click Edit Azure App Service settings
  • The Application Settings dialog will show you the values Local and what is found within Azure Function in the cloud. You will need to update the Remote value for the following:
    • FUNCTIONS_EXTENSION_VERSION: ~1
  • You will need to add the following Settings, by clicking on Add Setting creating the setting name, and put the value in afterwards. Repeat for each setting below.
    • Tenant: [Name of your Tenant, do not include .onmicrosoft.com]
    • ClientId: [Client ID created in step ‘Creating an App Only Token’ earlier]
    • AppSecret: [Secret Value created in step ‘Creating an App Only Token’ earlier]
  • Click OK
  • Back on the Publish screen, click the Publish button. This will push the code to your environment, with the correct Application Settings.
  • By going to your Azure Function at portal.azure.com, you will see 2 Azure Functions
  • Then clicking on Configuration, it will take you to the Application settings page, click Show Values and you will see your values.

At this point you just have the Azure function as a Webhook in place. Next steps are to tie the O365 Audit log to the WebHook.

Connecting O365 Audit Logs to your webhook

The last step is tying the Audit logs to your webhook. The webhook can be used for the different Audit logs. There are 5 different types of logs.

  1. Audit.AzureActiveDirectory
  2. Audit.Exchange
  3. Audit.SharePoint
  4. Audit.General
  5. DLP.All -Note: DLP sensitive data is only available in the activity feed API to users that have been granted “Read DLP Sensitive Data” permission.

I have written a PowerShell script for you that will register the webhook for you. You will find this in the repo.

  • Open PowerShell
  • Change the directory to .\O365AuditWebhook\powershell
  • Run the following PowerShell script (Run on one line), change the parameters to match your environment. I’ve picked Audit.SharePoint, but you can use any listed above, and run the PowerShell script multiple times to connect all logs to the webhook.

The above codes login with the ClientID and Secret and Starts a subscription to the given ContentType audit, using the WebHookUrl for the webhook.

If successful, you will receive a 200 Status Code message like below.

Your Azure Function (AuditWebHook) would have fired, and you would see something like the following within your logs.

Viewing the results

Directly from the Microsoft Page on Office 365 management api it states in this note:

When a subscription is created, it can take up to 12 hours for the first content blobs to become available for that subscription. The content blobs are created by collecting and aggregating actions and events across multiple servers and datacenters. As a result of this distributed process, the actions and events contained in the content blobs will not necessarily appear in the order in which they occurred. One content blob can contain actions and events that occurred prior to the actions and events contained in an earlier content blob. We are working to decrease the latency between the occurrence of actions and events and their availability within a content blob, but we can’t guarantee that they appear sequentially.

If you are using a Development environment – like myself – and setup the Audit.SharePoint content type then I suggest you go into SharePoint, and start using SharePoint. Just so the logs start to fill.

Please note, it can take up to 30 minutes or up to 24 hours after an event occurs for the corresponding audit log entry to be displayed in the search results, depending on the service of Office 365. See the table at the bottom of this section Search the audit log in security and compliance – Before you begin

Viewing the AuditWebHook azure function, you will see that it has fired more times since your initial setup.

If you look at your latest call, (note: logs can display out of order in azure functions) you will see that it attempts to find the validation code, which is what it needs to set up the webhook. When it is unable to find the validation code, the code assumes that content contains log information. It grabs the URI of the log that has been created and then it adds it to our Azure Storage Queue for our other azure function to process. Depending on how busy your environment is, this request could hold multiple URL’s to logs. A webhook has to respond quickly back to the calling code with a 200 status code. Therefore we are adding the URI’s of the logs directly to a Storage Queue to allow a different process to interrogate the logs.

The second Azure Function (AuditContentUri) will fire every time an item lands on the Storage Queue. This will grab the information from within the log file by calling the URI.

If we select one of the calls and view the logs of that Azure Function call, every entry within that Audit log file URI will be displayed in a JSON format. Clicking on a row in the logs, will display the full details of the line. At this point in the code, would be where you process the line and do whatever you need to do with the Audit log. I’m just printing it out to the Azure Function Logs.

Remove O365 Audit Logs from your webhook

To remove the webhook from the Audit log just run the following PowerShell script. You will find this in the repo.

  • Open PowerShell
  • Change the directory to .\O365AuditWebhook\powershell
  • Run the following PowerShell script (Run on one line), change the parameters to match your environment. I’ve picked Audit.SharePoint, but you can use any listed above, and run the PowerShell script multiple times to remove all logs to the webhook.

The below codes login with the ClientID and Secret and stops the subscription of the given ContentType audit.

Hopefully, if you have followed this correctly, (and I have written decent enough instructions for you), you should have a basic Audit Log Webhook working in your environment. This isn’t anywhere near production ready code, but it gives you an idea where to start. In my next blog post I will be going though parts of the code, to explain how it all fits together.

Connecting to Azure Devops with a Service Principal


**Updated for new screens, and new way to connect.

I see a lot of blogs and examples on the internet that shows you how to connect to environments using a username and password. This is all well and good for testing, but I believe it is bad for real world scenarios.

I’m a contractor, and my time at the job is defined, after I leave the contract and move onto the next one, the company should disable my account. What happens next? Everything I have built using a username/password, stops working. Yes, I could argue, it ensures I get a call back, but most contracts I’ve been involved in have a clause that any bugs found will be fixed for free up to 3-6 months after the job is done. Also, I like to leave a place with them thinking “That guy is awesome, lets get him back for the next project!”.

This post is going to show you how to set up a Service Principal for your Azure Devops CI/CD. At the end, I will give a very basic deployment that creates a Resource Group in Azure. Please note, this example is to show how to set up when your Azure Devops is not part of the same Directory as your Azure Resource Tenant. When it is part of the same tenant.

First, we need to start in Azure and create a Service Principal. The Service Principal will need to be a contributor on the Subscription or the Resource group that your Devops project is going to manage.

Create Service Principal

  • Open Azure Portal
  • Navigate to Azure Active Directory
  • Click App registration
  • Click New Registration
    • Name: Devops-<Company>-<ProjectName> (E.g, Devops-CFCode-OperationsDemo)
    • Supported account types: Accounts in this organizational directory only (Single Tenant)
    • Redirect URI: (Leave blank)
    • Click Register
  • Make a note of Application (client) ID and your Directory (tenant) ID.

Create a Secret for the Service Principal

  • In the App Registration for the above app, click Certificates & secrets.
  • Under Client secrets, click New client secret
    • Description: DEVOPS
    • Expires: Never
    • Click Add
  • Make note of the secret

Assign Service Principal permission to Subscription

  • Open Azure Portal
  • Navigate to Subscriptions and select your subscription
  • Click Access control (IAM)
  • Click Add -> Add role assignment
    • Role: Contributor
    • Assign access to: Azure AD user, group, or service principal
    • Select: <Name of service Principal>
    • Click Save
  • From the Overview blade, grab the Subscription ID and Subscription Name.

You can also add API permissions, such as Graph, and then make direct calls to Graph API using PowerShell using this service principal within the pipeline. Now this side has all been set up, we can head over to our Devops.

Create Service Connection in Devops

  • Go into your project.
  • At the bottom left of your screen click Project Settings
  • Within Project settings, underneath Pipelines click Service connections*. If you have a star next to the Service connections word, it means that you are viewing the preview version. I’m going to show the following screens using a preview version.
  • Click Create service connection
  • Select Azure Resource Manager, click Next
  • Select Service principal (manual), click Next
  • On the New Azure service connection blade, (replace values with your values you grabbed earlier)
    • Environment: Azure Cloud
    • Scope Level: Subscription
    • Subscription Id: <SubscriptionID>
    • Subscription Name: <Subscription Name>
    • Service Principal Id: <Application (client) ID>
    • Credential: Service principal key
    • Service Principal Key: <Secret>
    • Tenant ID: <Directory (tenant) ID>
    • Details (This section is your choice)
      • Service connection name: <Name of Tenant>-<SubscriptionName>
      • Description:
    • Security: Tick – Grant access permission to all pipelines.
  • Click Verify, a Verification Succeeded should show if all the details are correct, and the service account has permission.
  • Click Verify and save

The Service Principle is now connected

Proving the Service Principal connections works

Within your Dev Ops project, click on Pipelines and select releases. We are going to create a Resource Group within our subscription.

  • Add an Azure CLI task.
    • Task verison: 2*
    • Azure Resource Manager connection: Pick the subscription you have created in the previous section.
    • Script Type: PowerShell
    • Inline script: Inline Script
    • Inline script: az group create –name “Demo-rg” –location uksouth
  • Click Save and create a release. After the release has run, and you have received success, an empty resource group should have been created within the subscription.

AZ CLI putting message on a storage queue – not a valid Base-64 string


Using Az CLI a lot recently, it has made interactions with Azure so much easier using PowerShell.

https://docs.microsoft.com/en-us/cli/azure/install-azure-cli?view=azure-cli-latest

I had to write a simple PowerShell script that added items to a Storage queue. Once the items were added to a queue an Azure function picked up the items and processed them.

According to the documentation of Azure CLI you need to use az storage message put.

#Login
az login
#Get the connection string
$connectionString = az storage account show-connection-string --name "mystorageaccountname" –resource-group "MyResourceGroup" --query connectionString | ForEach-Object { $PSItem -join '' } | ConvertFrom-Json
#Create message
$message = "{""Name"":""Paul"",""LastName"":""Matthews"",""ID"":""d4b2ffc9-4380-46e4-a0bf-8a9ca58734d2""}"
#Add item to the queue
az storage message put --content $message --queue-name "myQueueName" --connection-string $connectionString

This fires successfully and I can see the item on the queue. However, when my Azure Function starts to run, I get the error message:

The input is not a valid Base-64 string as it contains a non-base 64 character, more than two padding characters, or an illegal character among the padding characters.

To solve this problem you just need to convert the string.

#Login
az login
#Get the connection string
$connectionString = az storage account show-connection-string --name "mystorageaccountname" –resource-group "MyResourceGroup" --query connectionString | ForEach-Object { $PSItem -join '' } | ConvertFrom-Json
#Create message
$message = "{""Name"":""Paul"",""LastName"":""Matthews"",""ID"":""d4b2ffc9-4380-46e4-a0bf-8a9ca58734d2""}"
#Encode to bytes
$b = [System.Text.Encoding]::UTF8.GetBytes($message)
#Convert to Base64String
$message64Base = [System.Convert]::ToBase64String($b)
#Add item to the queue
az storage message put --content $message64Base --queue-name "myQueueName" --connection-string $connectionString

Amazingly the value you push upto the queue is something like below:

“eyJOYW1lIjoiUGF1bCIsIkxhc3ROYW1lIjoiTWF0dGhld3MiLCJJRCI6ImQ0YjJmZmM5LTQzODAtNDZlNC1hMGJmLThhOWNhNTg3MzRkMiJ9”

But it appears on the queue correctly.

Branding your Office 365 sign-in pages


Are you fed up when you go to sign into your SharePoint Online sites, that you see the default picture that Microsoft provides? Wouldn’t it be nice that the sign in page matched your branding, gave your users a consistence look and feel? Well this article is going to show you how to do that.

Note: A branded sign-in page only appears when you visit a service with a tenant-specific URL such as https://outlook.com/contoso.com. When you visit a service with non-tenant specific URLs (e.g https://myapps.microsoft.com) a non-branded sign-in page appears until you have entered your User ID.

The following screen shot shows an example of the Office 365 sign-in page on a desktop after a customisation:

The following screen shot shows an example of the Office 365 sign-in page on a mobile device after customisation:

What can you customise?

On the screenshot below I have highlighted the areas that you can customise.

  1. Large Image / Background colour – You can change the image, or show a background colour which will be used in place of the image on low bandwidth or narrow screens.
  2. Logo – Your logo can be shown at the top right of the screen instead of the Office 365 logo.
  3. Sign-in Page Text – Although not showing on the above picture, you can supply sign-in page text. This text could be used to display a legal statement, simple instructions, or even contact information for your help desk.

SharePoint Online Tenant

To customise your sign-in page, you need to do this through Azure AD. If you have just a SharePoint Online tenant of the domain *.onmicrosoft.com and never used Azure, you will find that you cannot get to Azure. Luckily this isn’t too much of a problem if you have a credit card. (Don’t worry it doesn’t cost any money).

If you go to https://manage.windowsazure.com or https://portal.azure.com and attempt to sign in with your account, that you use for SharePoint online, you will see the screen below.

Chris O’Brien blog explains it further here (http://www.sharepointnutsandbolts.com/2014/04/using-azure-instance-behind-your-office-365-tenant.html), but basically you just need to click on “Sign up for Windows Azure” then follow the instructions and enter your credit card details. (Again it doesn’t cost you anything). It gives you a pay-as-you-go Azure instance.

Configuring your directory with company branding

  • Sign into your Azure classic portal (https://manage.windowsazure.com) as an administrator of the directory you want to customise, and select your directory.
  • Along the menu/toolbar list, Click Configure.
  • Under Directory properties click Customize Branding.
  • Modify the elements listed below. All fields are optional. See below for screenshots and details of all customisable elements.
    • Banner Logo
    • Sign-in Page Text
    • Sign-in Page Illustration
    • Sign-In Page Background colour.
  • Click Save.

Note: If you have applied changes to your sign-in page, it can take up to an hour for the changes to appear. Mine happen within a few minutes.

Any time you wish to change your customisation, just by going back and clicking the Customize Branding button again. It is also here where you can add different branding settings for a specific language.

Different Branding for different languages

After you have configured your default branding settings, by going back and clicking the Customize Branding button, the first screen you are presented with is changing existing settings (which if you have just followed this blog, only see Default here) or you can add branding settings for a specific language. Select the language and then click the arrow button, and upload pictures/text as you did before. Once set, this branding will only show for the given browser language.

Customisable elements details

Below you will find the screen shot of the customising branding wizard with descriptions that you would find if you click the help tool tip icon.

  • Banner Logo (60 x 280 pixels) – The banner logo is displayed on the Azure AD sign-in page, when users sign in to cloud application that use this directory. It’s also used in the Access Panel service.
    • Max pixel size: 60px by 300px
    • Recommended to keep under 30 pixels high to avoid introducing scrollbars on mobile devices.
    • Recommended file size: 5-10kb
    • Use a PNG image with a transparent background if possible.
    • Avoid using a logo with small text on it, as the image may be resized to fit smaller screens.
  • Square Logo (240 X 240 pixels) – The square logo (previously referred to as “Title Logo”) is used to represent user accounts in your organization, on Azure AD web UI and in Windows 10.
    • Max pixel size: 240px by 240px
    • Recommended file size: 5-10kb
    • Use a PNG image with a transparent background if possible.
    • Avoid using a logo with small text on it, as the image may be resized to fit smaller screens.
  • Square Logo, Dark Theme (240 x 240 pixels) – If configured, this image will be used instead of the “Square Logo” image in combination with dark backgrounds, such as Windows 10 Azure AD Joined screens in the out-of-box experience.
    • If your logo already looks good on white and on dark blue/black backgrounds, there’s no need to configure a separate Dark Theme logo.
  • User ID Placeholder – This will replace “someone@example.com” that’s shown as a hint in the user ID input field on the Azure AD login page.
    • Important: you should only configure this if you only support internal users. If you expect external users to sign in to your app(s), we recommend you leave this blank (Azure AD will show “someone@example.com”).
  • Sign-In Page Text Heading – Add a heading above your customized sign-in page text. If not configured, this space is left blank on Azure AD web login pages, and replaced by “Need help” on Azure AD Join experience on Windows 10.
    • Plain text only.
    • Don’t exceed 30 characters.
  • Sign-In Page Text – This text appears at the bottom of the Azure AD sign in page, on the web, in apps and in the Azure AD Join experience on Windows 10. Use this space to convey instructions, terms of use and help tips to your users.
    • Plain text only.
    • Can’t be longer than 500 characters (250-300 characters recommended).
    • Remember, anyone can see your login page so you shouldn’t use this space to convey sensitive info!

  • Sign-In Page Illustration – This large image is displayed on the side of the Azure AD sign in page. By design, this image is scaled and cropped to fill in the available space in the browser window.
    • PNG, JPEG or GIF
    • 1420×1200 resolution recommended.
    • Recommended file size: 300 kb (max file size 500 kb).
    • Use an abstract illustration or picture. Since the image gets resized and cropped, avoid using rasterized text and keep the “interesting” part of the illustration in the top-left corner.
  • Sign-In Page Background Colour – On high latency connections, the sign-in page illustration may not load, in which case the login page will fill in the space with a solid colour.
    • Enter an RGB colour code in hex format (e.g. #FFFFFF).
  • Hide KMSI (Keep Me Signed In) – Choose whether your users can see the “Keep me signed in” check box on the Azure AD sign-in page. This option has no impact on session lifetime, and only allows users to remain signed in when they close and reopen their browser.
    • Important: some features of SharePoint Online and Office 2010 have a dependency on users being able to check this box. If you hide this option, users may get additional and unexpected sign in prompts.
  • Post Logout Link Label – If this is configured, Azure AD will show a link to a web site of your choice, after users sign out of Azure AD web applications.
    • Make sure to configure both the label and URL properties!
    • Link can be plain text only.
    • URL can be HTTP or HTTPS.
  • Post Logout Link URL – If this is configured, Azure AD will show a link to a web site of your choice, after users sign out of Azure AD web applications.
    • Make sure to configure both the label and URL properties!
    • Link can be plain text only.
    • URL can be HTTP or HTTPS.

References: https://azure.microsoft.com/en-gb/documentation/articles/active-directory-add-company-branding/

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 


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.