Now that we understand the structure of a Kepion environment, we can configure the SQL Server. In this article, we'll look at configuring the logins for the Kepion and SSAS Service Identities, along with their permissions. In addition, we'll make sure to properly restrict memory on the SQL Server.
Kepion SI - Create Login
To configure a Kepion SI login, let's open SQL Server Management Studio (SSMS) and connect to the SQL Server instance.
Note: Refer to the Kepion Service Identity section in Part 1 to determine the credential to use for your Kepion SI.
Expand Security and right click on Logins:
Select New Login. Enter your Kepion SI credentials for the Login Name.
Tip: The username should be in the form of DOMAIN\UserName. For example: CORP\KepionSI.
Kepion SI - Set Permissions
The next step is to set the permissions needed by our Kepion SI on both the Server Level, and the database level. For the database permissions, if you don't currently have any Kepion Databases in your environment, there won't be anything you need to do there.
Server Permissions
Select Server Roles from the top left pane and check the dbcreator Server Role. If enabled, this will allow the Kepion SI to create new applications in the Kepion UI.
If preferred, you can use the following SQL script to grant the permission, as well:
USE MASTER;
EXEC sp_addsrvrolemember N'<KEPION_SI>', N'dbcreator'
Next, open a new query window in SSMS. You'll need to ensure that the Kepion Service Identity has the VIEW SERVER STATE permission in order to perform caching of database assets on the Web Server.
We'll also need to set the Default Schema to dbo, so that all databases created under this service identity are generated correctly.
Be sure to replace <KEPION_SI> with the relevant credentials and run the following script:
USE MASTER;
GRANT VIEW SERVER STATE TO [<KEPION_SI>];
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];
Database Permissions
Note: If you don't currently have any Kepion Databases, you can skip this step. We will create the database CPMAppHost in a future step, and it will have the required permissions by default.
In addition to configuring the login at the server level, we'll also need to make sure the Kepion Service Identity is configured properly at an individual database level. This will require:
- Mapping users to any Kepion Databases (CPMAppHost, Sample App-Advanced, etc.) currently in your environment.
- Setting their permissions to db_owner.
- Ensuring the Default Schema is set to dbo.
In SSMS, under Security -> Logins, right-click your Kepion SI and select Properties. Navigate to the User Mapping tab. Check all Kepion Databases, make sure to select db_owner for the role in the lower tab, and change the Default Schema.
As before, you can perform the same actions with the following script:
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: If you add any databases to the environment afterwards, you must make sure all are configured with these permissions and Default Schema.
SSAS SI - Create Login
Now we'll create and configure the login for our SSAS SI.
As mentioned before, the SSAS SI only needs permissions on the SQL Server. This is for both Pro-Active Caching and data processing during the Deploy step to work correctly.
We can identify the SSAS SI by looking at the Log On As in Services.
Right-click and select Properties. Then navigate to the Log On tab and copy the account name.
Then open SSMS again. Under Security right-click Logins and create a new login using the SSAS SI you copied.
SSAS SI - Set Permissions
As before we'll have to configure permissions for the SSAS SI at both the server and database level. However, just as before, if you don't currently have any Kepion Databases, there won't be anything to configure at the database level.
Server Permissions
Now open a new query window in SSMS. 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>];
Database Permissions
As before, if you have any existing Kepion Databases at this time, make sure you add your SSAS SI to each one in the User Mapping tab, grant the role of db_datareader, and specify the dbo Default Schema.
As always, you can perform the same actions with a SQL script:
USE [<KEPION_APPLICATION_DATABASE>]
GO
IF NOT EXISTS (SELECT * FROM [sys].[database_principals] WHERE [type] = 'U' AND [name] = N'<SSAS_SI>')
BEGIN
CREATE USER [<SSAS_SI>] FOR LOGIN [<SSAS_SI>] WITH DEFAULT_SCHEMA = [dbo];
END;
ALTER USER [<SSAS_SI>] WITH DEFAULT_SCHEMA = [dbo];
EXEC sp_addrolemember N'db_datareader', N'<SSAS_SI>'
Note: Just as in the previous step, if you add new Application databases to your environment later on, make sure your SSAS SI is configured with the correct role and Default Schema for all databases.
Restrict SQL Memory
Now that our Kepion SI and SSAS SI have the correct permissions on the SQL server, let's restrict the amount of memory used by SQL.
Note: This step is important, as by default, SQL consumes all available memory on a server. This can cause unexpected behavior and poor performance if not configured correctly. Review the recommendations in the memory section from the first article before we continue.
Connect to your SQL Server using SSMS, then right click on the server in the Object Explorer pane and select Properties.
Select the Memory page and set the Maximum server memory field.
Tip: There are 1000 MB in 1 GB, so if you wanted to set 4 GB of memory, enter 4000.
Next Steps
In this article we walked through the steps to configure new credentials for our SQL server, grant the necessary permissions to use Kepion, and restrict the memory used by SQL.
In Part 3 we'll follow a similar plan to configure our SSAS Server.
Comments
0 comments
Please sign in to leave a comment.