Configuring the MS SQL Database

NOT AVAILABLE IN SAAS

Table of Contents

Step 1. Log in to Microsoft SQL Server Management Studio as "sa"

Step 2. Create a New SQL Server Login

1. In the Object Explorer pane, expand the System Databases sub-node.

2. In the System Databases sub-node, right-click on master, and select New Query.

3. In the SQLQuery window, enter the following command to create a new SQL Server login:

CREATE LOGIN [username] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

    Where:

• user_name: A new SQL Server login.

• password: The password of a new SQL Server login.

4. To execute the command, click Execute or press F5.

Step 3. Create an Activity Database and Configure it

1. In the Object Explorer pane, expand the System Databases sub-node.

2. In the System Databases sub-node, right-click on master, and select New Query.

3. In the SQLQuery window, enter the following commands to create the activity database, tune its performance, and create a database user with the necessary permissions: 

CREATE DATABASE EkranActivityDB
GO
ALTER DATABASE EkranActivityDB SET AUTO_UPDATE_STATISTICS_ASYNC ON
GO
ALTER DATABASE EkranActivityDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE EkranActivityDB SET ALLOW_SNAPSHOT_ISOLATION ON
GO
ALTER DATABASE EkranActivityDB SET READ_COMMITTED_SNAPSHOT ON
GO
ALTER DATABASE EkranActivityDB SET MULTI_USER
GO
USE EkranActivityDB
GO
CREATE ROLE db_executor
GO
GRANT EXECUTE TO db_executor
GO
CREATE USER [username] FOR LOGIN [username] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_datareader] ADD MEMBER [username]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [username]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [username]
GO
ALTER ROLE [db_executor] ADD MEMBER [username]
GO

4. To execute the commands, click Execute or press F5.

Step 4. Create a UBA Database and Configure It

1. In the Object Explorer pane, expand the System Databases sub-node.

2. In the System Databases sub-node, right-click on master, and select New Query.

3. In the SQLQuery window, enter the following commands to create the UBA database, and create a database user with the necessary permissions:

CREATE DATABASE EkranUbaDatabase;
GO
USE EkranUbaDatabase
GO
CREATE ROLE db_executor
GO
GRANT EXECUTE TO db_executor
GO
CREATE USER [username] FOR LOGIN [username] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_datareader] ADD MEMBER [username]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [username]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [username]
GO
ALTER ROLE [db_executor] ADD MEMBER [username]
GO

4. To execute the commands, click Execute or press F5.

Step 5. Create a Management Database and Configure It

1. In the Object Explorer pane, expand the Databases sub-node.

2. In the Databases sub-node, right-click on master, and select New Query.

3. In the SQLQuery window, enter the following commands to create the management database, and create a database user with the necessary permissions:

CREATE DATABASE EKRANManagementDatabase;
GO
USE EKRANManagementDatabase
GO
CREATE ROLE db_executor
GO
GRANT EXECUTE TO db_executor
GO
CREATE USER [username] FOR LOGIN [username] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_datareader] ADD MEMBER [username]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [username]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [username]
GO
ALTER ROLE [db_executor] ADD MEMBER [username]
GO

4. To execute the commands, click Execute or press F5.

Step 6. Create an Archived Database and Configure It

1. In the Object Explorer pane, expand the Databases sub-node.

2. In the Databases sub-node, right-click on master, and select New Query.

3. In the SQLQuery window, enter the following commands to create the archived database, tune its performance, and create a database user with the necessary permissions: 

CREATE DATABASE archiveDB
GO
ALTER DATABASE archiveDB SET AUTO_UPDATE_STATISTICS_ASYNC ON
GO
ALTER DATABASE archiveDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE archiveDB SET ALLOW_SNAPSHOT_ISOLATION ON
GO
ALTER DATABASE archiveDB SET READ_COMMITTED_SNAPSHOT ON
GO
ALTER DATABASE archiveDB SET MULTI_USER
GO
USE archiveDB
GO
CREATE ROLE db_executor
GO
GRANT EXECUTE TO db_executor
GO
CREATE USER [username] FOR LOGIN [username] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_datareader] ADD MEMBER [username]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [username]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [username]
GO
ALTER ROLE [db_executor] ADD MEMBER [username]
GO

4. To execute the commands, click Execute or press F5.

Step 7. Install Ekran System Application Server Using the Database Created.

To install the Application Server, do the following:

1. Run the installation file (EkranSystem_Server.exe) to open the Ekran System Setup wizard.

2. On the Welcome to the Ekran System Setup page, click the Next button.

3. On the License Agreement page, carefully read the End User License Agreement, and then click the I agree button.

4. On the Choose the Installation Mode page, make sure the New Ekran System Application Server option in the drop-down list is selected, and then click Next.

5. On the Choose Install Location page, enter the installation path or click Browse to navigate to the Application Server installation folder, and then click Next.

6. On the Database Type page, select the MS SQL Server option, and click Next.

7. On the MS SQL Server Database Configuration page, define the connection parameters for the database created, and then click Next.

• Enter the MS SQL Server instance name, which is the instance name assigned to the TCP/IP port. Optionally, you can enter a custom MS SQL database port by entering it after the Server instance name, separating them by a comma (e.g. <server_instance_name>,<port>).

NOTE: If the default instance of the MS SQL server is used, only the name of the machine where the MS SQL server is located needs to be defined.

• Select the type of authentication required: SQL Server Authentication or Windows Authentication.

• Enter the User name and password of the user created in Step 2 (Create a new SQL Sever login):

• When using SQL Server Authentication, enter the SQL Server User name and Password of the user account via which the connection to the server will be established.

NOTE: You must enter either “sa” (system administrator) credentials or the credentials of a user with the “dbcreator” permission.

• When using Windows Authentication, enter the User name as <domainname>\<user name> and the Password.­­

NOTE: The specified user must have already been added as a user in MS SQL and have system administrator permissions.

8. On the Binary Data Location page, define the location to be used to store the binary data (i.e. screen captures) recorded by Ekran System Clients.

NOTE: The SQL Database option is not recommended unless used for small deployments, due to the large amount of data that will be accumulated over time.

NOTE: More information can be found on the Editing the Database Parameters page, and for the "Amazon S3-like storage" option, more information can be found on the Configuring Ekran System to Store Binary Data in Amazon S3 page.

9. On the Ekran System Database Names page, define unique names to be used for the Ekran System databases.

10. In the pop-up window that opens, click Yes to use the Ekran System database (i.e. actually consisting of the 3 separate databases) created.

11. On the Administrator credentials page, define the credentials to be used for the administrator user of Ekran System (i.e. the admin user of the built-in default tenant, who has full system permissions) in the corresponding fields as follows, and then click Next:

• Login: Optionally change the login name from the default name “admin” (where the login name can be from 5 to 50 characters long, and only contain letters and numbers, or can be an email address).

• Password and Confirm password: Enter a password (where the password must be at least 8 characters long, and contain at least one lowercase letter, one uppercase letter, one numeric character, and one special character, and cannot contain more than 3 consecutive identical characters).

NOTE: These credentials can be changed at any time later in the Management Tool (on the User Mangement page) by editing the administrator user.

12. On the Ekran System Client Uninstallation Key page, enter a key that will be used for uninstalling the Client locally, and then click Next.

13. Click Install.

14. The installation process starts, and its progress is displayed on the Installing page.

15. When the installation is complete, click Finish to exit the Setup wizard.