2. Prerequisites

2.1. Prepare the SQL-server

Make sure that a databse ‘red_demo’ and schema ‘emp’ already exist.

In the database ‘red_demo’ in the schema ‘emp’ create an employee table of division WEST:

  • Each time a row is changed the column UpdatedOn is set to the current time.

  • Rows can be logically deleted by setting the Deleted flag to true.

  • We assume that rows will never be physically deleted and, therefore, we it would be safe to configure an incremental load.

 1USE red_demo;
 2
 3create table emp.Person
 4(
 5    EmpId bigint,
 6    FirstName varchar(100),
 7    LastName varchar(100),
 8    Salary decimal (8,2),
 9    Birthday date,
10    Department varchar(10),
11    CreatedOn datetime not null,
12    UpdatedOn datetime,
13    Deleted bit,
14    primary key (EmpId)
15);

Now create some entries:

1USE red_demo;
2
3insert into emp.Person (EmpId, FirstName, LastName, Salary, Birthday, Department, CreatedOn, UpdatedOn, Deleted)
4values
5('001', 'William', 'Cooper', 3500.64, '19720707', 'HR', convert(date, '2025-08-25'), null, null),
6('002', 'Cynthia', 'Wilkes', 4500.55, '19651010', 'IT', convert(date, '2025-08-30'), convert(date, '2025-09-01'), null),
7('003', 'Robert', 'Stanton', 6400.00, '19680726', 'IT', convert(date, '2025-08-30'), convert(date, '2025-09-01'), null);

Finally create a read only user RED_RO:

1CREATE LOGIN red_ro
2WITH PASSWORD = 'Sx123456';
3
4USE red_demo;
5CREATE USER red_ro FOR LOGIN red_ro;
6GRANT SELECT ON emp.person TO red_ro;

2.2. Prepare the Connection in Exasol

As SYS user create a connection in Exasol to the SQL-server.

1create connection RED_DEMO_MSSQL to 'jdbc:sqlserver://<ip_address>:<port>;trustServerCertificate=true' 
2user 'RED_RO' identified by 'Sx123456'
3
4grant connection RED_DEMO_MSSQL to R_OWNER_DBITSA;
5grant access on connection RED_DEMO_MSSQL to R_OWNER_DBITSA;

Finally test the connection:

1import from jdbc at RED_DEMO_MSSQL statement 'select 1';

2.3. Create an Appilication & Security Manager Account in Exasol

As SYS user create a user ‘king’ and grant him the roles R_APPMGR and R_SECMGR. These two roles make this user an application and security manager.

 1execute script API_SCURTY.CREATE_USER (
 2      'ADAM'                            -- p_user_name
 3     ,'HU'                              -- p_user_type
 4     ,'sx123'                           -- p_authentication
 5     ,true                              -- p_private_sbx
 6     ,null                              -- p_quota
 7     ,'AppMgr. & SecMgr.'               -- p_comment
 8     ,'<your mail address>'             -- p_email
 9);
10
11grant R_APPMGR, R_SECMGR to ADAM;

If the RED framework has been configured properly you should receive a mail which tells you how to change the deault password. If for whatever reason you have not received such a mail, use the following sequnce to reset the password:

1alter user ADAM identified by "sx123" replace "Firstpw123+";

Note

‘Firstpw123+’ is the RED installation default password for HUs. You should change it by setting the parameter sec_hu_def_passwd in SCURTY.ADM_PARAMS.

Finally, check the user settings in SCURTY.REP_ALL_USERS.

2.4. Configure BDomains in RED

As application manager ‘ADAM’ add the two BDomains for divions EAST and WEST.

1execute script API_REDADM.ADD_BDOMAIN(
2    'DIV_EAST'                   -- p_bdomain
3   ,'file loads from div. EAST'  -- p_description
4);
5
6execute script API_REDADM.ADD_BDOMAIN(
7    'DIV_WEST'                   -- p_bdomain
8   ,'DB loads from div. WEST'    -- p_description
9);

Check the table REDADM.RED_BDOMAINS for an entry with this name in the field ‘BDOMAIN’.

2.5. Logon and Suspend the Framework

Logon to the RED-server and switch to the redsys user:

sudo -iu redsys

Assuming that you have prepared an environment named ‘DEV1’ swich to it and suspend the framework:

[redsys@red01 ~]$ dev1
----------------------------------------------------------
current environment: DEV1
exit with CTRL-D
----------------------------------------------------------

DEV1 [redsys@red01 dev1]$ redctl restart -S
INFO MainProcess[2025-08-30 16:00:41]: stopping server
INFO MainProcess[2025-08-30 16:00:41]: server stopped
INFO MainProcess[2025-08-30 16:00:42]: CTL-version: 1.2.16
INFO MainProcess[2025-08-30 16:00:42]: DB Name: red_dev1
INFO MainProcess[2025-08-30 16:00:42]: Exasol version: 8.31.0
INFO MainProcess[2025-08-30 16:00:42]: BVersion: 1.0.0 (2024-06-04 10:17:57 - OPEN)
INFO MainProcess[2025-08-30 16:00:42]: TVersion: 3.10.0 (2025-07-30 09:17:13 - OPEN)
INFO MainProcess[2025-08-30 16:00:42]: starting server (DB jobs SUSPENDED)
DEV1 [redsys@red01 dev1]$