Setup Guide - Part 1: Configure SQL and SSAS

Introduction

In the first part of our setup guide, you will configure SQL and SSAS. Complete the entire guide before you move on to installing our software. 

Before you start

Before proceeding, confirm the service identities are available:

• Kepion SI – Review the Kepion SI consideration section to determine the appropriate credential.

• SSAS SI – Review the SSAS SI consideration section to determine the appropriate credential.

  • For multi-server environments: If SQL Server and SSAS are on different machines, the SSAS service must run under a domain account so that it can authenticate to SQL Server.

Configure Kepion SI on SQL server

Create login

1. Launch SQL Server Management Studio (SSMS) and connect to the SQL Server instance.

2. Expand Security, right-click on Logins, and select New Login.

3. Enter your Kepion SI credentials for the Login name.

Note: The username should be in the form of DOMAIN\UserName. For example, CORP\KepionSI.

Grant server permissions

Next, we need to set the permissions needed by our Kepion SI on both the server and database levels. If you currently do not have any Kepion databases in your environment, you can return to the Database Permissions section later. 

1. From the Select a page pane, go to Server Roles and select dbcreator to enable it. Enabling dbcreator allows the Kepion SI to create new applications in the Kepion UI.

Alternatively, you can use the following SQL script to grant the permission instead:

USE MASTER;
EXEC sp_addsrvrolemember N'<KEPION_SI>', N'dbcreator'

Attention: Replace <KEPION_SI> with your credentials in all scripts, including the one above. 

2. Right-click the server and select New Query. 

3. (Only for Kepion versions below 6.1.230000.0) Copy and paste the script below into the query window and select Execute. This script grants the Kepion SI the VIEW_SERVER_STATE permission to cache database assets on the Web Server. 

USE MASTER;
GRANT VIEW SERVER STATE TO [<KEPION_SI>];
GO

Attention: Granting the VIEW_SERVER_STATE permission exposes your server and information to unnecessary risk. Refer to this article for why newer versions of Kepion do not require this permission.

4. Copy and paste the script below into the query window and select Execute. This script sets the Default Schema to dbo, so that all databases created under this service identity are generated correctly.

USE MASTER;
GO

IF NOT EXISTS (SELECT * FROM [sys].[database_principals] WHERE [type] = 'U' AND [name] = N'<KEPION_SI>')
BEGIN
 CREATE USER [<KEPION_SI>] FOR LOGIN [<KEPION_SI>] WITH DEFAULT_SCHEMA = [dbo];
END;
ALTER USER [<KEPION_SI>] WITH DEFAULT_SCHEMA = [dbo];

Grant database permissions

Note: If you don't have any Kepion databases, you can skip this step. We will create the database Kepion_System later, with the required permissions by default.

In addition to configuring the login at the server level, we'll also need to ensure the Kepion SI is configured properly at an individual database level. This will require:

• Mapping users to the Kepion databases currently in your environment.
• Setting their permissions to db_owner.
• Ensuring the Default Schema is set to dbo.

1. In SSMS, under Security > Logins, right-click your Kepion SI and select Properties. 

2. Navigate to the User Mapping tab.

3. In the Map column, select the checkbox of the Kepion database. In the lower box, select db_owner.

Note: You will need to perform these steps to both the Kepion_System database and any application database.

4. Select OK.

You can also perform the same actions with the following script instead:

USE [<KEPION_APPLICATION_DATABASE>]

IF NOT EXISTS (SELECT * FROM [sys].[database_principals] WHERE [type] = 'U' AND [name] = N'<KEPION_SI>')
BEGIN
 CREATE USER [<KEPION_SI>] FOR LOGIN [<KEPION_SI>] WITH DEFAULT_SCHEMA = [dbo];
END;
ALTER USER [<KEPION_SI>] WITH DEFAULT_SCHEMA = [dbo];

EXEC sp_addrolemember N'db_owner', N'<KEPION_SI>'

Note: You must configure these permissions and default schema with any future databases you add.

Configure SSAS SI on SQL Server

Identify the SSAS service account

As mentioned before, the SSAS SI only needs permissions on the SQL Server. This is for both proactive caching and data processing during the Deploy step to work correctly.

1. On the SSAS Server, open the Services app.

2. Identify the SSAS SI by locating the SQL Analysis Services Server.

3. Right-click the server and select Properties.

4. Go to the Log On tab and copy the account name.

Note: For single-server installations, this may be a built-in virtual account created by SQL Server setup. For multi-server installations, this should be a domain account that can be granted a login on the SQL Server instance.

Create login

1. Launch SQL Server Management Studio (SSMS) and connect to the SQL Server instance.

2. Expand Security, right-click Logins, and select New login.

3. Paste the SSAS SI account you copied in Step 3 to the Login name field.

4. Select OK to close the window.

Grant server permissions

We have to configure permissions for the SSAS SI at both the server and database levels.

1. Open SSMS, connect to the database engine and the server where Kepion application databases will be restored to. 

2. Open a new query window.

3. Run the following SQL statements to grant the required permissions. Be sure to replace <SSAS_SI> with the respective service identity:

USE MASTER;
GO

GRANT ALTER TRACE TO [<SSAS_SI>];
GRANT CONNECT SQL TO [<SSAS_SI>];
GRANT VIEW ANY DATABASE TO [<SSAS_SI>];

Note: For multi-server environments, [<SSAS_SI>] must be a domain account that matches the Log On account of the SSAS Windows service on the SSAS Server.

Grant database permissions

If this is your first time setting up Kepion, you likely do not have any application databases. We recommend you proceed with the rest of the article and refer to part three of the setup guide once you have Kepion applications. 

Configure SQL Server memory limits

Restricting the amount of memory used by the SQL and SSAS Servers is important to ensure a predictable and stable environment. Make sure to follow our recommendations below: 

Production environment

Development environment

1. In SSMS, right-click on the server in the Object Explorer pane and select Properties.

2. Select the Memory page and set the Maximum server memory field.

Tip: There are 1000 MB in 1 GB. For example, if you want to set 4 GB of memory, enter 4000.

Configure Kepion SI on SSAS Server

Now that we have our Kepion SI and SSAS SI credentials with the necessary permissions to access the SQL Server, the next step is to configure our Kepion SI credentials to have administrator privileges on the SSAS Server.

Add Kepion SI to SSAS

1. Connect to the SSAS Server from SSMS.

2. Right-click on the server and select Properties.

3. Go to the Security page and select Add to include Kepion SI as an SSAS administrator.

4. Enter your Kepion SI. 

5. Select OK.

(Optional) Incremental deployment

In instances where you cannot have Kepion SI as an admin on the SSAS server, please configure the following in each of your Kepion application SQL databases: Set the IncrementalOLAPDeploy to 1 in the table ApplicationSettings.

This instructs the Kepion Server to never drop the existing OLAP database when deploying changes to OLAP.  Once this is set, please ensure Kepion SI has admin rights to each of the OLAP databases associated with a Kepion application.

If this is not a concern for your environment, feel free to skip this step.

Configure SSAS memory limits

Note: Please refer to the memory configuration recommendations here. 

1. In SSMS, right-click the server in the Object Explorer pane and select Properties.

2. Go to the General page and configure the Value column for Memory \ TotalMemoryLimit.

Tip: This field is entered as a percentage of the available memory on the server.  For example, if your server has 20 GB of memory, you can use "20" to represent 20% (or 4GB) of 20 GB.

Next steps

In the first part of this guide, we have configured and set up your SQL and SSAS Servers to run Kepion. All that's left is to install our software.

As mentioned in the introduction, we strongly recommend most users follow the standard installation procedure. It's a simple and easy automatic installation that will, for the most part, set up Kepion for you.  We do have instructions for manual and multitenant installations for those who need to have more control of the installation process or set up multiple users on a single server. 

• Standard Installation
• Manual or Multitenant Installation