Integrate Kepion with Azure AD

In this article, we provide a step-by-step guidance on how to integrate your Kepion environment with Azure Active Directory (Azure AD).

Before You Start

Here are some things to know before you begin this process.

  • You understand which authentication mode works best for your organization.
  • You have already configured a Kepion environment. If not, follow this guide to create one.
  • Your organization has set up an Azure AD with valid users.

 

Azure Portal Setup

First of all, you need to register a new app for Kepion on Azure Portal. This will enable the communication between Azure AD and Kepion.

Register and configure a new app for Kepion

a) Add new application registration

Sign in to your Azure Portal. Switch to the Azure AD you want Kepion to use by selecting your account on the top right corner of the page.

Click Azure Active Directory icon in the left navigation, select App registration, and click New application registration.

Fill the Name and Sign-on URL (the base URL of your app where users can sign in), and click Create button.

b) Configure reply URLs

Select the newly registered app.

Copy and save the Application ID, as you will need to use it in later configuration.

Then click Reply URLs.

Add two /login/callback records.  One for external use and the other for localhost, and SavePlease save the external reply URL together with the Application ID, as you will need to use them in later configuration.

c) Configure required permissions

Go back to the App Settings page, click Required permissions, and click Add.

Click Select an API, choose Windows Azure Active Directory, and click Select.

Click Select permissions, check Sign in and read user profile, click Select, and click Done.

Then, click Add again, select Microsoft Graph as the API, check Read directory data, click Select, and click Done.

Click Grant Permission button.

Click Yes when you see this message.

d) Configure key

Go back to the App Settings page, and click Keys.

Enter {Customer Name} Key, select Never expires, and click Save.

Save the key value together with the Application ID, you will need to use them in later configuration.  Please be aware that this is the only chance to save this key value.  It is not retrievable after you leave this blade.

Add users to the new app

Next, you need to assign users and user groups to the application we just created. Assume all the users you want to grant access to Kepion are already added to your Azure AD. If not, add them to your Azure AD before going forward.

Click Azure Active Directory icon in the left navigation, select Enterprise applications, select All applications, and click the application we created for Kepion.

You can manage users and user groups in Users and groups blade.

Gather additional info from Azure Portal

While you are on the Azure Portal, note down the following information as you need them in the configuration later.

a) Kepion system administrator

After integrating with Azure AD, only users with a valid Azure AD credential (Windows AD credential is also required for Azure AD Integrated mode) can access to Kepion. If Kepion was not originally installed by such user, you need to select one from the Azure AD and add it to the CPMAppHost database.

Go to Azure portal, switch to the AD directory, click Azure Active Directory icon in the left navigation, select Users and groups, select All users, and click the user that you want to add as the initial system admin.

In the Overview blade, you will find the ObjectID and User nameCopy and save the User name and Object ID, you will need to use them in later configuration.

b) Azure AD domain

Click Azure Active Directory icon in the left navigation, select Custom domain names, and locate the PRIMARY domain name. Save the NAME value, as you will need to use it in later configuration.

 

Kepion Server Setup

After configuring a Kepion app on your Azure Portal, let’s configure the authentication mode on the Kepion server. Make sure you can connect to SQL Server and have permission to update tables in [CPMAppHost] and application databases. Please jump to the section that is applicable to you.

 

Azure AD Integrated

This section is only applicable to the Azure AD Integrated mode.

a) Update [dbo].[SystemAdmins] in [CPMAppHost] database

After integrating with Azure AD, only users with a valid Azure AD credential and Windows AD credential can access to Kepion. If Kepion was not originally installed by such user, you need to select a user from the Azure AD and add it to the [dbo].[SystemAdmins] table.

Add the administrator by entering into the following fields:

Column Description
UserName Use the Windows AD credential, e.g. <DOMAIN>\<NAME>. It should be the Windows AD credential of the user you saved in Gather additional info from Azure Portal -> a) Kepion system administrator
DisplayName Also use the <DOMAIN>\<NAME> you identified above, or any friendly name that helps you identify the user.
ModifiedBy -1
AzureADObjectId Update this column with the Object ID you saved in Gather additional info from Azure Portal -> a) Kepion system administrator
Type 1

Here is an example of adding a system admin user to the table [dbo].[SystemAdmins] in the Azure AD Integrated mode.

 

b) Update [dbo].[SystemSettings] in [CPMAppHost] database

Update the table [dbo].[SystemSettings] with the following settings:

Key Description Value
AzureADMode
  • This defines the Azure AD modes that are permitted to access to the application.
  • If this row doesn’t exist, please manually insert it into the table.
AAD+AD

Here is an example of the [dbo].[SystemSettings] table in the Azure AD Integrated mode:

 

c) Update [dbo].[Memberships] in [CPMAppHost] database

Update the ConfigJson column in table [dbo].[Membership] using the following template after replacing the highlighted regions:

{

“issuer”: “https://login.microsoftonline.com/middleearthad.onmicrosoft.com/”,

“client_id”: “2444b2a0-4531-4440-9c2b-a222f454abff“,

“redirect_url”: “https://shire.kepion.com/login/callback“,

“secret_key”: “y+R86zRsIwldiKloxIrTLaCi3nZ6vk/eCs3fqUECkU8=“,

}

Item Description
issuer This is the NAME of the primary Azure AD domain you saved in Gather additional info from Azure Portal -> b) Azure AD domain
client_id This is the Application ID you saved in Register and configure a new app for Kepion -> b) Configure reply URLs
redirect_url This is the external reply URL you saved in Register and configure a new app for Kepion -> b) Configure reply URLs
secret_key This is the key value you saved in Register and configure a new app for Kepion -> d) Configure key

 

d) Perform AD Sync

Now you’ve configured the CPMAppHost database to support Azure AD Integrated mode, the last thing is to perform AD Sync in each application.

To do so, navigate to the target application in the ADMINISTRATOR module, select User & Group on the right pane, and click the AD Sync button. Once finished, the Azure Object ID column will be populated automatically. Repeat the steps for the other applications in your environment.

 

e) Use PivotTable (Optional)

To allow connection to SSAS that resides on Azure VM from Excel PivotTable, please follow this guide to configure.

 

Now you’ve complete all the steps to integrate Kepion with Azure AD in the Integrated mode. It’s time to test and Connect to Kepion.

 

Azure AD Stand-alone

This section is only applicable to the Azure AD Stand-alone mode.

a) Update [dbo].[SystemAdmins] in [CPMAppHost] database

After integrating with Azure AD, only users with a valid Azure AD credential can access to Kepion. If Kepion was not originally installed by such user, you need to select a user from the Azure AD and add it to the [dbo].[SystemAdmins] table.

Add the administrator identified in Gather additional info from Azure Portal -> a) Kepion system administrator to [dbo].[SystemAdmins] by entering into the following fields:

Column Description
UserName Update this column with the User name you saved in Gather additional info from Azure Portal -> a) Kepion system administrator
DisplayName Also use the User name you identified above, or any friendly name that helps you identify the user.
ModifiedBy -1
AzureADObjectId Update this column with the Object ID you saved in Gather additional info from Azure Portal -> a) Kepion system administrator
Type 5

Here is an example of adding a system admin user to the table [dbo].[SystemAdmins] in the Azure AD Stand-alone mode.

 

b) Update [dbo].[SystemSettings] in [CPMAppHost] database

Update the table [dbo].[SystemSettings] with the following settings:

Key Description Value
AzureADMode
  • This defines the Azure AD modes that are permitted to access to the application.
  • If this row doesn’t exist, please manually insert it into the table.
AAD

Here is an example of the [dbo].[SystemSettings] table for the Azure AD Stand-alone mode:

 

c) Update [dbo].[Memberships] in [CPMAppHost] database

Update the ConfigJson column in table [dbo].[Membership] using the following template after replacing the highlighted regions:

{

“issuer”: “https://login.microsoftonline.com/middleearthad.onmicrosoft.com/”,

“client_id”: “2444b2a0-4531-4440-9c2b-a222f454abff“,

“redirect_url”: “https://shire.kepion.com/login/callback“,

“secret_key”: “y+R86zRsIwldiKloxIrTLaCi3nZ6vk/eCs3fqUECkU8=“,

}

Item Description
issuer This is the NAME of the primary Azure AD domain you saved in Gather additional info from Azure Portal -> b) Azure AD domain
client_id This is the Application ID you saved in Register and configure a new app for Kepion -> b) Configure reply URLs
redirect_url This is the external reply URL you saved in Register and configure a new app for Kepion -> b) Configure reply URLs
secret_key This is the key value you saved in Register and configure a new app for Kepion -> d) Configure key

 

d) Update [dbo].[Users] in application database

If you already have active users in the Kepion environment, and they need to access the saved plans after integrating with Azure AD, then you need to manually update the table [dbo].[Users] in each application database. Otherwise, they cannot access the “old” saved plans (i.e. saved when Kepion was in the Windows AD mode).

Below is a comparison of the [dbo].[Users] table in different authentication modes (click image to enlarge).

Here is an example of the table [dbo].[Users] in a sample application database in the Windows AD mode (click image to enlarge).

To provide a seamless transition to the Azure AD mode, we should keep their UserID intact while updating the following fields:

Column Description
UserName Update this column with the User name field from the Azure portal
Type 5
AzureADObjectId Update this column with the Object ID field from the Azure portal

Here is an example of the same [dbo].[Users] table in the Azure AD Stand-alone mode (click image to enlarge).

 

Now you’ve complete all the steps to integrate Kepion with Azure AD in the Stand-alone mode. It’s time to test and Connect to Kepion.

 

Connect to Kepion

Now you’ve complete all the steps to integrate Kepion with Azure AD. It’s time to test it out!

Launch Kepion externally https://shire.kepion.com, or internally http://localhost:8888/

On the next page, use your Azure AD credential to sign in, e.g. Gandalf@MiddleEarthAD.onmicrosoft.com.

You will be directed to a dashboard that shows all the Microsoft applications that you have access to.  Click the app you created in Register and configure a new app for Kepion, and you will be directed to the Kepion APPS page!

 

Troubleshooting

Azure user cannot see Apps

Symptom

When an Azure user logs into Kepion and does not see their APPs, this issue potentially can be caused by an out of sync state between Azure AD and Windows AD.

Reason

This Azure user has been previously deleted from Azure and then added back in. For example, user@corp.com (an external user) was deleted from Azure AD “CloudAD” using portal.azure.com and was then added back in. Each time user@corp.com was added back through Azure portal, it received a new “object id”. However, the information about this user from the active directory server will returns a stale version of their object id.

Cause

User not fully deleted from cloud.

Resolution

Run the following command to initiate a connection to Azure Active Directory.

Run the following commands to get all deleted users and their object id.

Then use the following command to delete the target object id. Replace <ObjectId> with the Azure Object ID of the target user.